Beginning my week, I began development on the receipt lookup feature. My first thought process was to simply create another custom form that would open, however this seemed like more work than was needed so I began researching. I asked Sonny if he had any knowledge on lookups, he referred me to David’s blog about a SQL lookup. Reading it over, it appeared to be the most simplistic approach and so I got to work trying to figure out how it worked and how to implement it. Creating the lookup began with first creating a SQL query in the project and checking “Published to Executer Window”. Then navigating to the “Go To” tile and selecting “Setup SQL Execute Gotos”. In the new window, select the plus on the right side to add a new goto and give it a name. When you click “OK” you will be prompted to create the script, click add. A new window will open that contains the template script for the SQL goto handler. Referring to the image below, the area labelled 1 is the template script for retrieving data from the lookup window and the area labelled 2 is where you can process your data and do as you wish. The main parts of the code in section 1 you should care about are the “Customer ID”, the integer after it, and the MBS_SQLGotoValue1 variable. “Customer ID” should be replaced with whatever column of the displayed data you want to retrieve. The integer should be changed to the corresponding data type. Finally, the variable can be changed to whatever variable you want to store the value in. Copy and paste this section or go into the helper functions and select the retrieve goto data. Now that the SQL query and the Goto was setup, I then added a trigger on the lookup button so that whenever it was clicked it called the SQL query. When selecting the query through the helper function, make sure you select display results with gotos enabled. Witnessing the lookup form in action was incredibly rewarding and made the hard work worth it.
The next part of my week was spent creating a way for a default filter and default company database to be set on launch. Setting the default database was easy enough, I just had to create a trigger set to “Focus Event, Window Pre, After Original”. I would then set whichever value I wanted changed, then “run script [name of field]” and once all my changes were done, I’d run “clear changes [window]”. The difficulty appeared when I had to apply a filter depending on the security role of the user. I asked Sonny if he had any way of accessing the security roles and the users tied to it through a SQL call, as my understanding of the overall Dynamics GP table list was still small. He gave me a SQL command that worked perfectly. After grabbing the global variable corresponding to the currently logged in user id, I was then able to pass it into the SQL call and check if the user had the correct role. If the user had the correct role, it would set a flag which would set the filter to the appropriate view. Below is a screenshot of the change script.
Every software development project comes with bugs. It’s impossible to avoid, which is why it is important to develop troubleshooting skills to solve them. The skills I have picked up over the past few weeks came in very handy as I had several bugs to resolve. One of these bugs was data from some fields not being updated in the database. This was simple enough as I had simply forgotten to add those fields to the save script. The more curious issue had to do with other data being overwritten when the invoice quantity field was changed. I had discovered that when the lookup was used, it was incorrectly transferring the data to the save script and so although the values were being displayed, they weren’t saved.
This week at Geoson solutions was filled with researching, problem solving, and troubleshooting, all important skills for a software developer. Putting all I had learned thus far to develop a new and exciting lookup feature and seeing it completed was incredibly rewarding. Discovering how to set default values and how to work with security roles opened up several avenues of development in Dynamics GP and I can’t wait to put it to use as I continue to contribute to Geoson Solutions’ and my own personal growth.
References:
David Musgrave – Winthrop Development
#GPPT Building Self Service SQL Scripts | Winthrop Development Consultants Blog (wordpress.com)
