The Smartlist Builder GoTo won't open my window
In a Microsoft Forums post a few days ago, an ISV had created a custom window and a table to store parallel data to the Item Serial Number Master table.
Her goal was to use Smartlist Builder (SLB) to make a Smartlist to show the GP & custom data in one Smartlist. That part was successful.
The 2nd goal was to make a GoTo to drill back to the custom window to display the Item & Serial Number (her parallel key) and display the custom data that goes with it.
Using a simple "Set Value and Run script" GoTo, this should have been easy as well. Except the custom window didn't open. No errors, just didn't open the window.
Not supporting SLB (or having source code to it since it is OEM) but understanding the mechanics of how it was probably written, I attempted a bit of basic troubleshooting. After that proved unsuccessful, my suggestion was to just make an "Open" type of procedure and have SLB call that. This should be fairly quick and you know it would work since her own code would be opening the window, setting fields, etc.
Except that didn't work either. And oddly this gave "syntax errors" on execution.
At that I had her open an incident so I could take a closer look.
After the case was placed, I created a simple Smartlist using just the Sales Transaction Work. Next I made a GoTo using the "set field and run a script" and also one to "execute a procedure" to display the transaction. Both worked for me.
Hmmm. I did a script.log of pressing the GoTo button to see if you could tell more of how SLB was processing the GoTo. Note that it is SLB that processes the GoTo- Smartlist reads the GoTo command and passes the script to the dictionary it is registered to. SLB in this case.
The script.log looked relatively straightforward. Handle the GoTo, we see the "key" for the current record passed into the procedure. Next it appears to be parsing the key and looking for the physical column names of the tables. And lastly I see my SOP Entry form open and everything works.
In order to make testing easier here, the ISV had recreated the issue just using the Item Serial Number Master table and the Item Maintenance window. This took "3rd party stuff" out of the picture even though I didn't believe that had anything to do with her issue.
Looking at the script.log from the ISV attached to the incident (for both the set field & execute script GoTo's) they looked very similar to mine. Except that in both of her GoTo's, the form never opens.
One clue we did have was that when calling the execute procedure GoTo, SLB was throwing a "syntax error" message.
Syntax error: ';'.
call OpenWindow of form IV_Item_Maintenance,;
Looking at the message, which would surely be pass through sanScript executed from SLB, we can see why we get this message - no value was set on the first parameter. Not an empty string, just nothing. My guess was that SLB wasn't able to detect the name of the field somehow and so couldn't put in a value.
I called the ISV and we discussed the issue and did a screen share. I received the XML export of her Smartlist and tried this test here on both 9.0 and 10.0 (she was on 9.0) and it failed on both versions.
As another test, we created a new SLB Smartlist using her custom parallel table and having the GoTo open her custom window. That worked this time.
So what is different? Nothing fundamenal as far as I could tell. One table in each test and it was the 3rd party table that worked and not the GP Core table. So you wouldn't suspect a Dexterity bug - however surely there must be a SLB bug lurking.
The ISV needed the Smartlist showing data from both the GP table & her custom table and the GoTo working by the end of the day and my suggestion was to just make a SQL view and build on that. This should work considering we were able to make her custom table Smartlist work.
After leaving the incident at that, I wanted to look to see if there was an existing SLB bug on this. Speaking with the SLB captain, she didn't know of any. But as I went through the repro steps, when SLB defaulted in the key fields for the Item Serial Number Master table (Date Received, Date SEQ Number, Item Number, Location Code, QTY Type) the product captain told me to "remove all but the fields you care about because only the fields for the GoTo were required". That seemed odd to me so I left the Item Number & Location Code fields and removed the rest. When I then recreated the Smartlist and the GoTos, it now worked.
Well, great. I updated the ISV with the news and after removing the same fields hers worked now as well so this obviously was an easier approach than deploying a custom SQL View just for the GoTo.
While happy that this case was "solved" but not real happy about the solution, I wanted to look at this a bit more. I've been working with Smartlist since it was acquired in v6.0. Having a good understanding of how it works as well as how the ISV would write their 'Handle GoTo' code, the "just remove segments from the key" didn't seem like a good solution. And while this was more of SLB issue and not a Smartlist issue, it just didn't seem right that you had to define the key so narrowly.
To test, I recreated the Smartlist again using the Item Serial Number Master as the only table and all the key segments. I created both types of GoTos again and was able to reproduce the issue again. Thinking that SLB must be parsing the key incorrectly somehow, I wanted to see the SQL Query generated by the procedure so I enabled my Dexsql.log and tried to open my GoTo.
Sure enough, the problem at that point was obvious in the log:
Snippet from Dexsql.log (nicely formatted)
/* Date: 03/23/2010 Time: 21:39:41
stmt(20939824):*/
select T1.[ITEMNMBR] from TWO..IV00200 T1 with (nolock)
WHERE T1.[DATERECD] = 2017-04-21 00:00:00.000 and
T1.[DTSEQNUM] = 8.00000 and T1.[ITEMNMBR] = '100XLG' and
T1.[LOCNCODE] = 'WAREHOUSE' and T1.[QTYTYPE] = 1
/*
/* Date: 03/23/2010 Time: 21:39:41
SQLSTATE:(37000) Native Err:(102) stmt(20939824):*/
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '00'.*/
We see that SLB didn't set the where clause of the DATERECD field correctly. It put in the Time piece of the sqldate (which is ok), however it didn't put the single quotes around the field. Everything else looks correct. When I fixed that and ran the SQL statement in SSMS, it correctly returned "100XLG".
Knowing this, I looked through the list of bugs and found bug "52450 - Cannot use datetime field to set a time field value in Go To tasks" from last summer. I see this bug is fixed in 11.0. Now it does say "time field" and not "date field" and so I'm hoping they fixed it for both dates & times - I'll see if I can find time to test this issue on the latest version. Not sure if this will be ported back to 10.0 or not but at least there is a workaround.
Best Regards,
Patrick