If you are finding that your SmartList Builder SmartList isn’t returning results only when a specific column is set to display, you may want to keep reading. This happens due to a SQL syntax error caused by a leading space in the column name. In this article, Pat will go through how to fix this issue in order to have your SmartList run successfully.
If you’d rather watch a step-by-step video demonstration of this, see below or click here.
SmartList Builder Fails on Column With Leading Space in the Name
Recently, a partner was using a SQL View from Victoria Yudin’s website – view_AR_Apply_Detail with SmartList Builder.
The view was created in the company database and returned results from SSMS using:
select * from view_AR_Apply_Detail
The Partner then set this up in SmartList Builder and picked a few “Default Fields” and that seemed to work fine from SmartList as well.
Until we added the “Applied to Doc Number” field. Didn’t notice the issue here the first time testing this – it’s subtle.
Since the report ran previously, we don’t suspect this is a “data issue” or “restriction” since we didn’t enter one – so most likely there was some kind of “SQL Issue”.
To debug SmartList Builder errors, (or Microsoft Dynamics GP issues in general) we can enable the Dynamics GP DEXSQL.log per KB 850996 and then re-relaunch Dynamics GP and re-run the list.
Doing so showed us the SQL Error that we knew would be there – but it does seem unexpected.
The SQL Query seems to show as expected but SQL is telling us that column does NOT exist and thus the failure.
Viewing the text of the original “create view” statement shows us the underlying issue that we missed when adding the column in Fig 2.
T.ORTRXAMT Original_Trx_Amount, T.CURTRXAM Current_Trx_Amount, -- T.amountApplied Total_Applied_Amount, -- A.APTODCTY Applied_to_Doc_Type, RTRIM(A.debitType) [Applied_to_Doc_Type_Name], RTRIM(A.APTODCNM) [ Applied_to_Doc_Number], <---------This is the problem field! A.APPTOAMT Amount_Applied,
If we look carefully at the alias name of the problem column in the SQL text, there is a leading space between the left bracket and “Applied_to_Doc_Number” name. While this is OK from a SQL perspective, when SmartList Builder created the field in SmartList Builder meta data, the application must have trimmed the leading & trailing spaces. This means from a SmartList Builder point of view, the field is named “Applied_to_Doc_Number” without the space and thus doesn’t exist in the table and the SQL query fails.
The reason it worked in SSMS is because we never referenced the field “by name” and used the * to get the data. The leading space in the column name was overlooked.
We could fix this from the SmartList Builder side by “fixing” the name of the column in the SLB10200 and re-adding the leading space.
While having a leading space in a SQL Column Name is possible, it is not “best practice” and the best way to fix this is by removing the space from the column in the SQL Script for the view itself and then altering or dropping/recreating the view.
After removing that space and re-creating the view in SQL- the SmartList Builder SmartList ran successfully without any other changes.
