Tuesday, November 28, 2017

Building Microsoft Dexterity Cross-Dictionary Applications with GP Power Tools - Part 2/2

In Part 1 of this two-part series, we saw how to use the Resource Information and the Runtime Execute Setup features in GP Power Tools to gather the name of a List View object and model pass-through sanScript code that would set the image state of a specific line to checked (marked), based on an entity value in the list. As a refresher, the following video shows where we are at this point:

Now this is all good, but technically speaking, this code should be parametrized to allow us to evaluate and mark a number of entities that will be coming from a setup table in my Dexterity application. We would want to iterate through that table, pass in the configured entities as parameters to this code, so they can be evaluated and marked if exists in the list.

In this article, I want to show 2 more GP Power Tools features that will make your cross-dictionary code usable and transferable from GP Power Tools directly into your Dexterity application.

SQL Execute Setup

In the Runtime Execute Setup code we added (shown in the video), we hard-coded the value of the Multi-Entity Management entity to have it marked when it matched a value in the list view. However, hard-coded values of any kind are evil and hardly represent the universe of values that could be selected by a user. Remember also that the primary intent is to transfer our code with as little edits as possible.

First, and to simulate this condition, we would want to build a simple SQL query to return a list of entities that we could use as part of a parametrized list:

1) Click on Options > Scripting > SQL Execute Setup

SQL Execute Setup

It is worth noting at this point that all these options can be accessed from the GP Power Tools navigation bar.

2) In the SQL Execute Setup window, we will want to setup a Script ID, Script Name and the SQL database we want the query to be executed again.

SQL Execute Setup window

3) The list we want is made up of account segment number 3 of our chart of accounts. We will need unique values for this. The following query should get the job done:

SQL Execute Setup window

We need a third column with the key value to be returned. The first two columns will be used as display values for a parametrized list.

Parameter Lists

Parameter Lists are a very interesting concept in GP Power Tools. They allow you to create everything from custom lookups to range values, or discrete values to be passed to anything from Power Tools' Triggers, Runtime Execute  and .NET Execute scripts, and even other SQL Execute scripts. For this particular case, we need to create a parameter list that will display a custom lookup with the SQL Execute script above. This in turn will allow users to interact with our cross-dictionary code, but will also provide absolute validation that our code is working as intended.

1. Click on Options > Scripting > Parameter Lists

Parameter Lists option

2. In the Parameter List Maintenance window, fill out the required fields. Parameter Title and Parameter Instructions will be presented to the user when the custom lookup is activated.

Parameter List setup

3. In setting up the actual parameter, we will need to define a prompt, set the type to Lookup, the mode to Single Field, the Options to Custom Lookup (SQL), Length/Decimal to 5, as shown below.

Parameter configuration

Once you have set the Options to Custom Lookup (SQL), an expansion button becomes enabled to allow you to select a specific SQL Script. Here we will choose the SQL Script created in our previous section.

Parameter List Lookup SQL Script window

4. Click OK to save the selected lookup SQL script and Save to save the new parameter.

5. Back in the Runtime Execute Setup window, we can reload our script then select the newly created parameter list as a Parameter ID to the script.

Parameter ID field selected

6. Now, it's time to insert the parameter instead of the hard-coded string. Highlight the hard-coded string, "20", then click the Parameters button and select the available parameter. The following video shows the full action:

The insert will create a moniker space for the parameter, {%01%}.

7.  The following video shows the script being tested with the parameter.

8. Finally, we will generate the sanScript pass-through code that will be placed in our Dexterity application. This is done from the Script button drop-down list on the toolbar. This final video shows the results of this process.

If you noticed, the cross-dictionary code includes a parameter being passed to the Dexterity execute() function and an inout variable in the pass-through code to use as parameter to the execution. This is definitely a time saver! Now, all is left is to put this code into a function in my dictionary and we are good to go.

I hope you, as a Dexterity developer, consider incorporating GP Power Tools in your arsenal of development tools. Having the ability to model your cross-dictionary code once, test it out, and incorporate it safely and confidently within your dictionary goes a long way and saves a lot of frustrations in the process of developing your solution.

Until next post!

Mariano Gomez, MVP

No comments: