Converting procedures and reports to English

I am in the middle of converting many procedures and reports to English for a client who is starting operations in India. This process is fairly straight-forward and boring, but there are some problems that have to be overcome.

The process can be divided into two: translating labels (of the procedure/report itself, procedural parameters, field titles in reports) and handling fields that have an English value as well as a Hebrew value. Translating labels is straight-forward; the only point worth noting is that as labels are (generally) limited to 20 characters, one should enter the translated label for language 3 (American English) first then copy the label to language 2 (British English). It seems that the check for length occurs only for language 3.

One place that requires slightly special handling is reports that have a different title to the given title. Normally, the translated title of the report goes in the 'translation of entity title' sub-form, but a specific title that can be longer goes in the 'output title' sub-form. The first would be displayed in a menu whereas the second is only for output. This sub-form has its own sub-form, 'Translation of Output Title'.

Handling fields with a separate English value is more involved. There is only one table, DOCSTATUSES, that has both STATDES and ESTATDES fields, and choosing which to display is very simple:

(SQL.LANGUAGE = 1 ? DOCSTATUSES.STATDES : DOCSTATUSES.ESTATDES)

But most other tables have the English value in a separate table, eg. CUSTOMERS.CUSTDES and CUSTOMERSA.ECUSTDES. This is slightly more involved, as one has to write both the conditional statement as well as adding a left join between CUSTOMERS and CUSTOMERSA.

The real problems start when this field is a parameter: the client has a propensity for including the part status in reports, where it is frequently a parameter. In this case, one has to add a CHOOSE-FIELD trigger for the field that in itself is very interesting as it displays how Priority manages a UNION ALL.

SELECT PARTSTATS.STATDES, ''
FROM PARTSTATS
WHERE PARTSTAT <> 0
AND INACTIVEFLAG <> 'Y'
AND SQL.LANGUAGE = 1;
SELECT /* AND STOP */ DOCSTATUSES.ESTATDES, ''
FROM DOCSTATUSES
WHERE DOCSTATUS <> 0 
AND TYPE = '4'
AND SQL.LANGUAGE > 1;

The (currently) insolvable problem is with part status being a parameter to an INPUT or SQLI stage. One can give the parameter an English title and define the above CHOOSE-FIELD trigger, but into which table is the value stored, PARTSTATS or DOCSTATUSES? One, somewhat clumsy, solution is to use different stages for the different languages, viz.

[STAGE 10] :$.GO = (SQL.LANGUAGE = 1 ? 30 : 50;
[STAGE 20]: GOTO; {$.GO}
[STAGE 30]; /* Hebrew */
LINK PARTSTATS TO :$.STA;  /* parameter */
....
[STAGE 40] GOTO; {60}
[STAGE 50] /* English */
LINK DOCSTATUSES TO :$.STD; /* parameter */
...
[STAGE 60] Report

For a moment, I thought that I could write a CHOOSE-FIELD trigger similar to the above for the procedural parameter that would choose either PARTSTATS.PARTSTAT or DOCSTATUSES.DOCSTAT, but there are two problems with this: (a) the parameter has to a character value, not numerical (and using ITOA doesn't solve the problem; (b) the form that Priority uses from which a value will be chosen is dependent on the table linked to the parameter that is in this case PARTSTATS. So it looks like I'm stuck with the GOTO solution. 

One possible improvement to this would be in stage 50 - instead of duplicating whatever is in stage 30 but using DOCSTATUSES, the appropriate values could be entered into a linked instance of PARTSTATS. Similarly stage 30 only gets values for PARTSTATS. Then there need be only one 'real' SQLI stage, 60, that does whatever is necessary for the report.

[Update] The GOTO solution won't work either. Stage 50 has the linked file DOCSTATUSES, and so the form that will be displayed or from which values will be extracted is also DOCSTATUSES. Any attempt to run this form results in the error message 'This form is reserved for internal use by the system'. To get around this problem, I defined a new form that is based on DOCSTATUSES, shows only ESTATDES and SORT and is defined as Q. In the 'continuation' sub-form of the procedural parameter, I defined the target form to be my private form.

This works! But it's still clumsy.

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;