Monday 19 July 2021

Choose-field trigger for procedural parameters

One of my clients wanted a report that would compare the price of the same part at different dates. The comparison part is simple (although the report should include dynamic column titles, an interesting technique) but choosing the price lists is complicated: a supplier can have several price lists that are active concurrently, and a price list does not have an easily choosable primary key.

The ideal user interface would be supplier number and two dates, where the dates come from the supplier's price lists. This requires what could be termed a conditional choose-field trigger, as the trigger's code is dependent on the supplier's number. This is, to be blunt, very difficult to achieve in Priority.

I wrote on this topic a few years ago and quoted text from the SDK that purports to explain. I'll quote the text again here so that I might comment upon it: You can also write a specific CHOOSE-FIELD or SEARCH-FIELD for the procedure. Your trigger can contain references to any input value specified by the user within the same [emphasis mine] procedure step. For instance, if the procedure step contains an input parameter called CST, its value will be stored in the :PROGPAR.CST variable. This is useful, for example, if a given procedure step contains an input column for a Sales Rep and another input column for a Customer, and you want the Choose list for the latter column to display only those customers that are associated with the specified sales rep.

It would have helped greatly had there been presented a detailed example of this usage, or at least to give an example of a procedure that uses this technique. I tried to write a trigger using this technique; I assumed that PROGPAR was the name of the procedure so tried to do something similar with TEST_COMPAREPRICELIST.SUP but I received an error message.

In the end, I wrote a slightly complicated report that displays the text of a given trigger for parameters in procedures; using this report, I was able to see that standard report INCOME_BUDGETS uses this technique in step 4 for the parameter VR -
SELECT VERDES,VERCODE,VALID FROM BUDVERSIONS WHERE GL = (SELECT GL FROM GENLEDGERS WHERE GLNAME = :PROGPAR.GLF AND GL <> 0) ORDER BY 3 DESC,2;
From this code, it can be seen that :PROGPAR is the name of a variable and not the name of a procedure; GLF is a parameter that is connected to a table. But this is not as simple as it seems: GLF receives a value that is calculated in a previous stage. Is this necessary?

After banging my head against a brick wall for a while, I finally found the correct combination of parameters that will allow me to choose a supplier then receive a list of dates for the supplier's price lists. The procedure uses a regular 'line' parameter, SUP, that is defined to receive a supplier's number. The important fact to remember is that in the choose-field trigger, :PROGPAR.SUP is the value that is entered in the SUP parameter (in this case, the supplier's external number), not the internal number of the supplier and not the name of the table that is linked to the parameter.

The choose-field trigger for DT1 that actually works is as follows. Converting the dates to integers allows them to be sorted in reverse order (newest first); otherwise they get sorted in ASCII order which is incorrect.
SELECT DTOA (SUPPRICELIST.SUPPLDATE, 'DD/MM/YY'), DTOA (SUPPRICELIST.SUPPLDATE, 'DD/MM/YY'), ITOA (SUPPRICELIST.SUPPLDATE) FROM SUPPRICELIST, SUPPLIERS WHERE SUPPRICELIST.SUP = SUPPLIERS.SUP AND SUPPLIERS.SUPNAME = :PROGPAR.SUP ORDER BY 3 DESC;

No comments:

Post a Comment