Skip to content
+1-888-319-3663

COMMUNITY FORUM

Navigation list create with a SQL view not showing

Brenda asked 3 years ago
i created a SQL view, successfully add it as a table in NLB.  But it is returning no data where there should be.  i believe it may be a security issue but i don’t know where else to look.
Answers
Nicole Albertson Staff answered 3 years ago
Generally when it isn’t returning data when it is a SQL View, it is a SQL permission issue.  After you created the SQL View, did you give your GP user(s) select permissions to the view or give the DYNGRP Database Role select permissions to it?  Either the users or DYNGRP will need that select permission.  
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 3 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.

https://www.eonesolutions.com/tech-tuesday-troubleshooting-smartlist-builder-lists-that-return-no-data/

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.


If you would like to submit an answer or comment, please sign in to the eOne portal.