I’ve created a SmartList Builder SmartList and a goto using that data to a Dynamics GP window.
The SmartList itself works fine and the GoTo runs without error – however the record that is being displayed by the GoTo is usually not the correct record.
How to I make SmartList Builder correctly show the record I selected?
When you use a GoTo in SmartList – for any SmartList including the out of the box reports – the process from SmartList only passes the “Key Fields” of the selected record to the GoTo that is invoked.
From GP SmartLists – these are all hard coded and something the end user doesn’t have to worry about and as far as eOne is aware – those all work correctly.
When you build your own custom SmartList using SmartList Builder, YOU have to select those “Key Fields” on the primary table of the report.
What fields you select and why this important is ONLY for GoTos – the SmartList Builder report will run fine no matter what Key Fields you select. But as noted, the “Key Fields” are used as part of a SQL Query in order to find the values used as the GoTo fields defined.
Looking at this simple example, I am going to make my own Customer Address example using the Customer Address Master table for my primary and only table.
I selected the key fields – Salesperson ID & User Defined 1 – to demo this issue.
Then I select whatever default fields I want and create my Goto so I can open the Customer Address Maintenance.
After selecting my Built-In goto I want to use, SLB defaulted the goto fields for me since my names in my only table matched the names of the goto fields. In this case, these ARE correct and what we want to use.
Running the new SLB_KEY_FIELDS_DEMO report and selecting the highlighted record (double click default) we see the Customer Address Maintenance window DOES open – but it is the wrong Customer/Address than what we clicked on?
As noted, because SmartList itself doesn’t pass you the record – only the Key Fields – SLB has to use those Key Fields defined on the report in order to then find the “goto fields” that were defined on the goto we were using.
Turning on the GP DexSQL.log and re-running the SmartList and GoTo, we can see what happened and why SLB opened the wrong record.
Since there are 2 fields defined on the goto, we use two queries to find them using the original query and then the “where” clause for the fields defined as Key Fields and then the value from the selected record.
We can see that the record I selected had a blank Salesperson ID and all the User Defined Fields (not shown in the report) are blank also – or at least THIS one was.
the “from [TWO22]..RM00102” comes from the original report. If I had 8 tables linked into the report – then we would have seen the entire table joins and everything as the original report was executed. But as this was one GP table – it would have been a simple query as we see here.
In the screenshot, we see SLB getting the value for the ADRSCODE and then CUSTNMBR.
Once it has the two values it needs, it uses those returned value to pull up the requested record.
If we run the two queries in SSMS:
We can then see why we end up with the AARONFIT0001/WAREHOUSE record being opened as that is the first value in the resultset in each query.
To fix this issue, we should define better key fields for this SmartList and in for this report Customer Number & Address Code will work best since that is the key field of the table we are reporting on.
This does get more complicated if multiple tables are added to the report and we want to use a GoTo. Because we can only currently define the “Key Fields” from the main table on the report – we might still have this same sort of trouble if we need one of the “joined table” fields in to make a proper/unique Key Field. In order to do that correctly, you would perhaps need to reorder your tables on the report so that the “line/detail” table is the main table and the “less detail table is joined to it. There isn’t a one size-fits-all solution and how to resolve it depends on the Goto requirements, the data itself, and how the report has to work to show the necessary data. The solution might end up being to write a “complex table join” as a SQL View (or a SQL Script) table so that the proper Key Fields can be selected to make the report run as desired and the proper goto working as expected.
