Filters and Fun

Recently, I have been working on implementing a filter on a Visual Studio Integration Toolkit (VSIT) Custom Form in Microsoft Dynamics GP as well as solving an underlying bug that would delete the first row of the form’s scrolling window whenever a user clicked on it. The filter would only show data that was matched and another filter which showed if the data had been submitted. The difficulties faced with creating this filter stemmed from how scrolling windows function inside of GP. While it is straightforward to set up a scrolling window for data to be manually entered and saved, importing the data, and modifying it on the fly was fairly challenging.

At first, I believed this would be an easy task where simply changing the data contained in our SQL table would give the required results. The issue with this approach was that the filtered rows weren’t being brought to the top (i.e. if row 3 was the first item in the filtered data, it’d remain at row 3 instead of being brought to row 1). This was due to my lack of understanding on how the scrolling window functioned and believing the scrolling window simply read from our SQL table. Instead, there was an underlying scrolling window table (WVS00302) that was what the system used to determine the number of rows and which row to display where. I had falsely believed that we could not modify this table and that it was automatically modified when data was manually entered, so I began looking into ways I could directly modify the scrolling window. Looking through David Musgrave’s blogs about GP, I had come across a method which involved iterating through the scrolling window to then modify the fields. This approach was helpful in modifying individual fields and did effectively filter the rows. However, it complicated the logic behind saving changes as any changes would overwrite the data originally in that row in our underlying table, which would lead to data loss and duplicate entries. This approach also had an issue with the underlying bug that we had as it would attempt to modify the first row, which would cause the bug to occur and crash the program. With the bug being a constant thorn in our side, we decided it was best to deal with the bug before continuing.

To determine what was causing the bug, I compared our form with the sample provided by David Musgrave which was error free. Searching through and comparing the code I found no noticeable differences until Sonny suggested I look if there are any hidden or different fields. This is when I noticed that the sample was using the CF_LineDescription field which we had not utilized. Loading up my test environment, I added the field back to see if this would resolve the bug. Surprisingly it worked, I was able to modify all rows of the table without any issue. We readded the field to the main form and changed the logic so data would write to that field where we ran into another issue. Although I was able to modify the contents of the CF_LineDescription field in the test environment, the data would not write into it on the main form. Sonny had managed to fix this issue, so we tested the filter which caused the form to have the same issue again as well as data was writing into the incorrect fields. We reloaded the backup, but the issue persisted. Since it was working fine on the test environment, we decided to use the test environment instead. After a few hours of adding the required logic to the test environment to have the full functionality of our main form. We had also decided on a different approach to implement the filter. Sonny had discovered that we could indeed modify the WVS00302 table, so we would rebuild this table whenever the filter was applied, filling the SEQNUMBR field with the sequence numbers we had in our data table. After implementing this approach, the form was now fully functional.

It was 2 weeks of hard work and a lot of learning but with Sonny and Dawn’s encouragement and help made it that much more fulfilling to get it done!


David Musgrave – Winthrop Development

#GPPT Iterating through a Dexterity Scrolling Window Sample | Winthrop Development Consultants Blog (