MSSQL Browse not returning consistant records for display.

[JPMacDonald]

Bruce,

I am having a number of  issues with a web application using MSSQL tables.

In the attached example I have 2 MSSQL tables, a Chart of Accounts (CoA) and GL Transactions (glTrans). They are related through the field called AcctNo. They both have a primary key consisting of a SQL identity column.

My goal is to show the Chart of Accounts in a browse and next to the Balance amount field I include an “Other” button that is to be used to drill down on a given row to see the details that make up the balance. When the drill down button is clicked I want to open a popup form that has a browse of the GL transactions (file loaded with totals) filtered by the AcctNo of the row that was clicked on. I am setting the filter by hand in the embed using Prop:SQLFilter (also tried Prop:Filter). When the user clicks on a row in the CoA I get the data for that record and set a session value that is to be used to filter the drill down form/browse.

Here are the issues I am hitting:

1.   When the CoA browse opens if you click on the drill down button immediately I get an Error 47 and the web server terminates. If however you open one of the rows for edit first and then cancel out of that form you can now click on the drill down button successfully, well except for the other problem.
2.   The other problem is that on the form that opens to show the GL details you get a different number of records when you open the form repeatedly. To reproduce, click on a row (not the drill down button) use AcctNo 2000-6100, then click on the drill down button. The first time it usually shows all the records. Close the form and click the button again, you may have to do this 3-9 times to see it but you will get a different number of records or sometimes no records at all.
3.   The 3rd issue is that you have to click on the row before you can click on the drill down button. It seems to me that any code placed in the “.. when button pressed” embed doesn’t fire until after the popup window closes so I cannot set the data for the filter there. Clicking on the button before clicking on the row uses the previously selected row as the target.

The attached example runs on port 88. The connect string for the SQL tables in set in the WebServer Init embed. This using NT version 7.6

Thanks for taking a look.

Regards

Parker


[attachment deleted by admin]

[kevin plummer]

Hi Parker,

I have an app that does a similar thing so the following may help:

For problem 1 & 3, you want to use the session id based on the unique record key for the browse rather than when a user clicks on a row. Use that session ID in your popup to get the record you user had clicked the button on and set your other session stuff for your filter here.

For problem 2 it sounds like you don't have MARS and Busy handling set. I think there is something in the docs or just search the NG here. My experience is that both need to be set via FM3.

[Bruce]

Hi Parker,
I'm running a bit behind at the moment, so let me know if Kevin's suggestions help your app.

cheers
Bruce

[JPMacDonald]

Bruce,

I had originally set the turbosql switch on in the dictionary which obviously made the error 47 go away. As per Kevin’s suggestion, changing it to MARS also made it go away but I don't understand the reasoning behind that.

I tried Kevin's other suggestion and moved the code for retrieving the current row data down into the drilldown detail form (the called procedure instead of the calling procedure). That still seems to be hit and miss in that it usually doesn’t select the primary key for the row the drill down button is on but rather the last selected row in the browse.

When you get a chance I’d appreciate your take on this one.

Regards

Parker

[JPMacDonald]

I think i have this working now, Kevin's idea sent me in the right direction.

I had been using session values to get the primary key for the chart of accounts and used that to get the filter values in the called procedure.

What is working for me now is that on the drill down buttons I turn on the "Include Row ID field" on the OnClick tab and now in the called procedure I use p_web.getValue('vCoA:RowID') instead of a GSV.

This coupled with the MARS setting on the tables seems to be working for me, fingers crossed!!!

Thanks Kevin/Bruce

Regards

Parker