Error messages from a procedure invoked by a form trigger are not displayed

I have a private POST-UPDATE trigger in form ORDERS that invokes a procedure whose task is to extract certain lines from the current order and insert them into a new order (this isn't particularly relevant). The procedure checks certain data and displays error messages if the checks fail. It transpires that these error messages are displayed when the procedure is run from a menu, but not when invoked from a form, which is when they are needed the most.

Below I describe a slightly complicated way to get around this problem. First I defined a new table TEST_ERRMSGS that has four fields: AKEY, USER, PROG and MESSAGE, where the first three fields create the composite primary key ('U' in Priority-speak). I can't be sure that someone else is going to run the same procedure, probably on a different order but who knows, so I need maximum 'separation'. AKEY will be the order number, USER is of course the user's number and PROG is the number of the procedure. Here is the relevant part of the form trigger pre-enhancement

SELECT SQL.TMPFILE INTO :ECORDERS FROM DUMMY;
LINK ORDERS OEC TO :ECORDERS;
GOTO 999 WHERE :RETVAL <= 0;
INSERT INTO ORDERS OEC
SELECT * FROM ORDERS ORIG
WHERE ORD = :$.ORD;
EXECUTE WINACTIV '-P', 'TEST_SEPARATE', 'ORDERS',
:ECORDERS;
UNLINK AND REMOVE ORDERS OEC;

Here are the lines that have to be added before the line 'EXECUTE WINACTIV'; one private integer field is utilised although this could be any field in the order as long as the procedure doesn't need it in the course of its normal execution. There's no need to worry about overwriting important data as it's a linked table that is being updated. Of course, it's best to use a private field. 

:TEST_PROG = 0;
SELECT EXEC INTO :TEST_PROG
FROM EXEC
WHERE TYPE = ‘P’
AND ENAME = 'TEST_SEPARATE'
;
SELECT SQL.TMPFILE INTO :ECORDERS FROM DUMMY;
LINK ORDERS OEC TO :ECORDERS;
GOTO 999 WHERE :RETVAL <= 0;
INSERT INTO ORDERS OEC
SELECT * FROM ORDERS ORIG
WHERE ORD = :$.ORD
;
UPDATE ORDERS OEC 
SET TEST_FIELD = -1 /* this signifies that the procedure is called from an order */
WHERE ORD = :$.ORD;

At the beginning of the procedure, the following query is executed to get the order number

LINK ORDERS TO :$.PAR;
ERRMSG 1 WHERE :RETVAL <= 0;
SELECT ORD, ORDNAME, CUST
INTO :OLDORD, :ONAME, ::CUST
FROM ORDERS WHERE ORD > 0;
UNLINK ORDERS;

To the SELECT list, I add the private field TEST_FIELD that is selected into the variable :FROMFORM. After this is the block

GOTO 10 WHERE :FROMFORM <> -1;
:TEST_PROG = 0;
SELECT EXEC INTO :TEST_PROG
FROM EXEC
WHERE TYPE = 'P'
AND ENAME = '$';
DELETE FROM TEST_ERRMSGS
WHERE USER = SQL.USER
AND AKEY = :ORD
AND PROG = :TEST_PROG;
LABEL 10;

In other words, if there is already an entry in the table for this user, order and procedure, clear it so that we can start afresh. Now the procedure runs, and every time that it detects an error, the rather cumbersome code that appears below has to be included. If before the code was

ERRMSG 62 FROM ORDERSA
WHERE ORD = :ORD
AND TEST_CONDITION = 'Y';

It now becomes

:ERR = 0;
SELECT 62 INTO :ERR
FROM ORDERSA
WHERE ORD = :OLDORD
AND TEST_CONDITION = 'Y';
GOTO 862 WHERE :ERR = 0;
GOSUB 9990;
ERRMSG 62;
LABEL 862;
...
SUB 9990;
GOTO 9991 WHERE :FROMFORM <> -1;
SELECT ENTMESSAGE ('$', 'P', :ERR) INTO :PAR1 FROM DUMMY;
INSERT INTO TEST_ERRMSGS (AKEY, USER, PROG, MESSAGE)
VALUES (:OLDORD, SQL.USER, :GLOB_PROG, :PAR1);
LABEL 9991;
RETURN;

Going back to the form trigger, after the line 'EXECUTE WINACTIV', the following has to be added

SELECT MESSAGE INTO :PAR1
FROM TEST_ERRMSGS
WHERE USER = SQL.USER
AND AKEY = :$.ORD
AND PROG = :TEST_PROG;
ERRMSG 990 WHERE :RETVAL = 1;

In other words, without digging into the code, the calling trigger has to signify to the procedure that it is being run from a trigger (TEST_FIELD = -1). The procedure then inserts a record into a special table if an error is detected, where the record includes the order number, the user, the procedure and the error message to be displayed. When the trigger resumes execution, it checks whether there is a suitable record and if so, displays the error message. This means that the record will remain in the table but this doesn't matter much for if the user runs the same procedure on the same order, the record will be deleted at the beginning of the procedure.

Continuing the MAILMERGE topic

After successfully developing the letter, my next task was to write a procedure that would be executed as a direct activation from the orders screen and would send the letter via email. My original code was something like this

LINK ORDERS TO :$.PAR;
ERRMSG 1 WHERE :RETVAL <= 0;
:FNAME = 'X:/1/TEST_SENDORDLETTER.pdf';
EXECUTE WINHTML '-d', 'TEST_ORDLETTER', 'ORDERS', :$.PAR, '-pdf',
:FNAME;
:EMAIL = '....';
MAILMSG 10 TO EMAIL :EMAIL DATA :FNAME;

I'm leaving out all the changes that were added later - I ended up using the interface for mail messages instead of MAILMSG and that's not really relevant.

Every time that I tried this, the procedure worked fine and sent the mail containing the letter. Every time the user wanted to run it, the letter was created but contained only with the logo and signature: the important part of the letter was missing.

It took some time to figure out what was missing: I had originally run the mail merge program and had defined what the format was to be. Someone running the 'envelope' procedure as a direct activation would not get the chance to define the format of the mail merge.  OK: there is a paragraph in the SDK that shows how to overcome this -

/* Set print format */
:EXEC = 0;
SELECT EXEC INTO :EXEC FROM EXEC
WHERE TYPE = 'P' AND ENAME = 'TEST_ORDLETTER';
UPDATE PRINTFORMAT
SET VALUE = -102  /* required format */
WHERE EXEC = :EXEC AND USER = SQL.USER;

I've used the same code (albeit with a different ENAME and format) for a similar program and there this code worked fine. But the print format was still not being set for the user. At first, I thought that the user would have to run the mail merge procedure once from the menu in order to set the print format, which is when the light finally went on in my head. The above code will fail because there is no record in the PRINTFORMAT to update! So I added the code to insert the necessary values into that table; if the insert succeeds, the code jumps over the 'update' statement. Looking at this again, the 'update' statement should come first, and only if this fails should there be the 'insert'. 

The above is not written in the SDK!

There are two pitfalls here: (1) The inner procedure has to appear in a menu that the user has permission for; (2) The format has to be set.

More about transferring data from screen triggers to procedures

I wrote two months ago about calling a procedure from a screen trigger, showing this code

LINK CUSTOMERS TO :FILE;
INSERT INTO CUSTOMERS
SELECT * FROM CUSTOMERS ORIG
WHERE CUSTOMER = :$$.NSCUST;
EXECUTE BACKGROUND ACTIVATE '-P', 'TEST_COPYCUSTOMER', 
'CUSTOMERS', :FILE;
UNLINK AND REMOVE CUSTOMERS;

I didn't write about the procedure that this code calls, namely TEST_COPYCUSTOMER. I want that the procedure can be called from a menu, where the user enters a list of customers, as well as this background call, where there is no user interface. In order to accomplish this, the procedure has two stages: the first is an INPUT stage, which has one parameter named PAR which is linked to the customers table, and the second stage is an SQLI stage that does the actual work.

An improvement that appears in later Priority versions (possibly from 17, definitely from 18) is that there is a new file type, NFILE. In previous versions, if someone entered * (i.e. all records), then all the records in the given table would be copied into the linked table. This can take time, so the new file type, NFILE, was created: the documentation says "Select NFILE if the linked file comprises a group of records and you want the link table to remain empty when the user enters * or leaves the field empty".

What the documentation doesn't show is when and how to use this new file type. I use it for parts, customers, suppliers and sometimes customer orders and inventory documents, when there is a strong possibility that the user will leave the parameter empty, meaning 'choose all records'. There's no point in using an NFILE if the user is always going to choose a list of values.

How is the NFILE used in practice?

LINK CUSTOMERS TO :FILE;  /* NFILE */
ERRMSG 1 WHERE :RETVAL <= 0;
GOTO 1 FROM CUSTOMERS WHERE CUST > 0;
UNLINK CUSTOMERS;
LABEL 1;

If the user left the parameter empty (or *), then there will be no records in CUSTOMERS, and so the statement GOTO 1 FROM CUSTOMERS WHERE CUST > 0 will not execute, thus unlinking the CUSTOMERS table, meaning that future references to this table will mean the original table. But should the user enter even one customer number, the branch to label 1 will be taken and future references will mean the linked table.

Now back to the procedure TEST_COPYCUSTOMER: for reasons which at the moment escape me, the PAR parameter being passed from the screen trigger to the procedure was empty. As CUSTOMERS is defined as an NFILE and is empty, the linked table is unlinked and so the procedure works on ALL the records in the CUSTOMERS table - not what I wanted! In order to prevent this, I defined CUSTOMERS as a regular FILE, accepting that there would be a performance hit when the procedure is called directly from a menu.

But why is the PAR parameter empty? I don't know (as yet), but when I removed the BACKGROUND part of the statement EXECUTE BACKGROUND ACTIVATE, the code worked properly. It appears that there is a difference between installations, as this code worked properly at one site but did not at another site.

Another point to notice is the use of ACTIVATE: normally I would use the external program WINACTIV. The documentation states that The difference between the WINACTIV command and the ACTIVATE and ACTIVATF commands is that WINACTIV has a user interface, meaning that you can define a progress bar and/or messages that require a response from the user (using a PRINTF, PRINTCONTF or CHOOSEF command) and these will be visible to users while the procedure is running, whereas the ACTIVATE and ACTIVATF commands will not display these elements. As such, the WINACTIV command cannot be used when working with the Priority web interface. The client where the code did not work properly uses the web interface as well as the traditional interface so I thought it prudent to use ACTIVATE instead of WINACTIV.

To conclude, my original code used two optimisations, namely BACKGROUND and NFILE. These optimisations may not work on some systems.

[Edit from 28/02/23] I faced a similar situation today: a procedure is executed automatically every few minutes, and if it finds a candidate order, that order is passed to another procedure that does something complicated to the order. The calling procedure was as follows

SELECT SQL.TMPFILE INTO :NEW FROM DUMMY;
LINK ORDERS TO :NEW;
GOTO 99 WHERE :RETVAL <= 0;
INSERT INTO ORDERS
SELECT * FROM ORDERS ORIG
WHERE ORD = :ORD;
EXECUTE BACKGROUND WINACTIV '-P', 'TEST_PROC', 'ORDERS', :NEW;
UNLINK AND REMOVE ORDERS

Whilst the test procedure was activated, it never received an order number. Eventually the solution dawned upon me: the temporary file was being deleted before the test procedure could get its hands on the file. Solution: remove 'background' and wait for the called procedure to complete before deleting the file.

Calling a procedure from a screen trigger

I am starting work as a sub-contractor (i.e. someone else is the consultant who interfaces with the client and defines their work procedures whereas I'm the programmer who implements those procedures in Priority) for a company that wants to have one master company in which are defined the customers, suppliers, parts, etc. and satellite companies in which the actual transactions take place. My primary task is to write interfaces that will automatically transfer data from the master company to the satellites. 

For some tables (e.g. supplier type), new/updated data will be copied to all the satellites, whereas for other tables (e.g. customers), the user can decide to which satellites will be copied the new/updated customer. Upon hearing this, I realised that I have to define a table with four fields: the id of the new datum, the type of the new datum, satellite name and whether the data should be copied to this satellite. I also developed a form based on this table; this forms will be a 'son' form to all the forms that allow a choice of satellite. This form is easy to define as it takes its inspiration from another general form, EXTFILES. The fun starts in the copying procedure.

Within Priority, there exists a documented method for sending data from one procedure or trigger to another procedure or report. I use this frequently when building procedures which by means of the scheduler will send reports by mail. The procedure uses a local copy of one of the 'stack' tables (normally STACK which has precisely one field - ELEMENT - which is of course the table's primary key). For example,

:GROUP = 'NO_CNC';
EXECUTE WINACTIV '-R', 'TEST_NOPROD_METAL', 'STACK', :$.STK, 
'-g', :GROUP;

My original thought would be to do something similar in the form's post-form trigger: pass the parent form's id number to a procedure that then iterates through the chosen satellites, invoking an interface to do the actual addition to the satellite. As each procedure/interface is specific to a given form (i.e. the code necessary for updating customers is similar but different to the code necessary for updating suppliers), there is no need to pass the data's type (customer, etc) to the procedure. As only one datum is required, I could use the STACK table as shown above.

The communal form's POST-FIELD trigger had code like this:

GOTO 2 WHERE :TEST_CHANGED = 0;
SELECT SQL.TMPFILE INTO :FILE FROM DUMMY;
LINK STACK TO :FILE;
INSERT INTO STACK (ELEMENT) VALUES (:$$.NSCUST);
GOTO 2 WHERE :RETVAL <= 0;
GOTO 1 WHERE :$$.EXTTYPE <> 'p'; /* not a part */
EXECUTE BACKGROUND ACTIVATE '-P', 'TEST_COPYPART', 'PART', :FILE;
GOTO 2;
LABEL 1;
GOTO 1 WHERE :$$.EXTTYPE <> 'C'; /* not a customer */
EXECUTE BACKGROUND ACTIVATE '-P', 'TEST_COPYCUST', 'PART', :FILE;
GOTO 2;
LABEL 1;
...
LABEL 2;
UNLINK AND REMOVE STACK;

This seemed reasonable but it didn't work! I spent a very frustrating hour discovering that the procedure was not receiving data. It transpires that every procedure has to have its data passed in a linked table of the correct type: the 'copy part' procedure has to receive a linked table of parts and the 'copy customer' procedure has to receive a linked table of customers, etc. So the final code became

GOTO 2 WHERE :TEST_CHANGED = 0;
SELECT SQL.TMPFILE INTO :FILE FROM DUMMY;
GOTO 1 WHERE :$$.EXTTYPE <> 'p'; /* not a part */
LINK PART TO :FILE;
INSERT INTO PART
SELECT * FROM PART ORIG
WHERE PART = :$$.NSCUST;
EXECUTE BACKGROUND ACTIVATE '-P', 'TEST_COPYPART', 'PART', :FILE;
UNLINK AND REMOVE PART;
LABEL 1;
GOTO 1 WHERE :$$.EXTTYPE <> 'C'; /* not a customer */
LINK CUSTOMERS TO :FILE;
INSERT INTO CUSTOMERS
SELECT * FROM CUSTOMERS ORIG
WHERE CUSTOMER = :$$.NSCUST;
EXECUTE BACKGROUND ACTIVATE '-P', 'TEST_COPYCUSTOMER', 'CUSTOMERS', :FILE;
UNLINK AND REMOVE CUSTOMERS;
LABEL 1;
...
LABEL 2;

Using the BACKGROUND parameter after EXECUTE means that the actual copying occurs in the background and so allows the user to continue working without interruption.

One final problem to look out for: users must be able to execute the copying procedures, which means that they have to appear in a menu for which regular users have access.