EONE  BLOG

Tech Tuesday: Troubleshooting SmartList Builder Lists that Return No Data


As you are building out lists in SmartList Builder, you may go to review it and find that it returns no data. There can be many reasons for this, but troubleshooting is all the same. This is the same whether you are using the SmartList Builder, Excel Report Builder, or Navigation List Builder modules.

Here are a few things you can do to troubleshoot a list that doesn’t return any data:

Display SQL
  1. In the Builder tool that you have the list setup, pull up the list and then go to Options – Display SQL.
  2. Here you can mark to show the Fields, Calculated Fields, and Restrictions you have on the list.
  3. The script that is presented to you can be copied and pasted into SQL Management Studio for further review. 
    1. If it runs successfully, then we know the basic setup of the list is good and it is how the script is changed in the tool (SmartList, Excel, etc.) that is running the actual list that is causing the issues.   The script displayed in the Display SQL window is not the actual script that is run to return the data by the individual tools.  It is the basic script that the Builder tool is creating.  Each tool that runs your Builder setup will take that information and formulate it into a little bit different format to run the actual script.
    2. If it returns an error in SQL, then we can take that information and try to figure out what is wrong with the SQL Statement and correct it and then make the change in the Builder setup as well.

  DexSQL.log  (SmartList Builder and Navigation List Builder)
  1. You can create a DexSQL.log to see the script/error that the list is returning when you run it in GP.  The steps to create a DexSQL.log can be found in the following Microsoft Knowledge Base article.
  2. You can review the DexSQL.log by opening it in Notepad or something similar.
    1. For SmartList/SmartView, you would be looking for something like a “select top 1000…” statement.  The 1000 may change if you have changed the number of rows for the list to return.
    2. For Navigation Lists, you would be looking for a standard select statement against the tables you have in your setup. 
    3. You may also find errors in the log when that select statement runs that will indicate what is wrong.
  3. You can copy the select statement to Management Studio for further review.
  4. Once you find the issue, you can correct it in the Builder setup.

SQL Server Profiler Trace
  1. You can create a SQL Profiler Trace to see the script/error that the list is returning when you run it.  The steps to create a SQL Server Profiler Trace can be found in the following Microsoft Knowledge Base article.
  2. Once it is run, you can review the trace results and look for the script it is running and/or any errors being returned. 
    1. For SmartList/SmartView, you would be looking for something like a “select top 1000…” statement.  The 1000 may change if you have changed the number of rows for the list to return.
    2. For Excel Reports and Navigation Lists, you would be looking for a standard select statement against the tables you have in your setup. 
  3. Once you find the script that is running for the list, you can copy the select statement into Management Studio and review it further.
  4. When you find the issue with the script, you can correct it in the Builder setup.


As always, if you need assistance in troubleshooting the list, you can always contact the eOne Solutions support team.  You can reach them by calling 888-319-3663 or submitting a case online at https://www.eonesolutions.com/support-center/.

Leave a Comment




RECENT POSTS


Modify Excel Templates for compatibility with Excel Add-in
Meet eOne at Summit in Nashville
New Releases of SmartList Builder and Extender
VIP Roundup Party at Summit 2017 in Nashville
eOne Selling Kit for our GP Partners

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
New Releases of Extender and SmartList Builder
SmartList Builder 2013 New Feature of the Day: Auto Updating SmartList

CATEGORIES

ARCHIVES

open all | close all

TAGS

#fargo 2017 CRM Demo Dynamics Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight ERP Events Excel Excel Report Builder Extender GP GPUG integration integration manager Meet the Team Microsoft Microsoft dynamics crm Microsoft Dynamics GP ODBC Office Relationships partners release SalesForce SalesForce.com scripting SmartConnect SmartList SmartList Builder SmartList Designer SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.