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.