Navigation list create with a SQL view not showing
Brenda asked 5 years ago
Nicole Albertson Staff answered 5 years ago
If you created any calculations or anything in Navigation List Builder, those could also have an issue in them that could cause it not to return data.
The easiest way to find out if it is an issue in the setup or permissions is to run a SQL Trace (with the errors messages turned on) or a DEXSQL.log. When you run that, you will see the error that is causing it not to return data.
Let us know what you find!
Patrick Roth Staff replied 5 years ago As you both suggest it is very likely permissions. If you are logging in as ‘sa’ however then it wouldn’t be since they have all permissions.
To troubleshoot these things, you’d always need to run a dexsql.log as a start – otherwise you’re just guessing.
Otherwise, what I also suspect to be an issue is a Primary Key Violation.
In NavLists in GP, the table that holds the display data MUST have a unique primary key for each row.
So in NLB, our backing table has that key field as well and when you choose your primary table for the NavList (your view in this case) you must choose the Key Fields.
So lets say this is perhaps navlist for all the SOP documents and you choose Customer Number as your key.
It’ll preview fine in NLB but then when the data gets pushed into the backing table – there would be a PK violation since you likely have customers with multiple orders – so the CUSTNMBR would be duplicated and fail.
Go back to the NavList and choose more/different fields to make the new “key” unique. Or if you don’t have one – change your view to add one so that it is now unique and can be chosen in NLB.