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;
