Don't mess with standard interfaces

A week ago, I wrote a procedure which iterates over a set of work orders and sets their status to 'cancelled'. In order to strengthen the cancellation, I decided to disconnect the work order from the connected customer order line by writing zero in the appropriate field. As the interface for updating the status of a work order only contains the work order number and status, I decided to add the customer order line, and because I need to write zero into this field, I marked the flag 'accepts null values'. I checked my procedure and it worked fine.

A few days later, someone drew my attention to the fact that after printing a work order (which changes its status from 'approved' to 'printed'), the connection to its customer order line was lost. For you, the reader, the reason should be obvious, but for me it wasn't. I checked the work orders form which - unusually - was free of any customisation which I might have done. There are rules defined for the screen, but they are only for me; one says that the status should be changed to 'cancelled' if the order line is zero - this is not what is happening and anyway, the problem was happening to other users.

Very problematic. I tried rebuilding the screen but this had no effect. As it happens, I had added to a daily report data about work orders which was based on the connected customer order line. There was data for the first two weeks in July but a mysterious disappearance this week. From this, I was able to conclude that the problem occurred only during the past few days. I didn't remember programming anything to do with the work orders form, and anyway I had already checked that there were no customisations.

Then inspiration struck: there is a module which I rarely use that prepares updates: one can choose a selection of changes that have been made in order to package them and transfer them to another installation. I opened this module and requested all the changes that had been made in the past week - all kinds of changes appeared. But one stood out: the change to the standard interface for updating the status of work orders.

Then the penny dropped: the procedure that prints a work order also calls a procedure called DocStat which updates the status of the work order to 'printed', presumably by means of the standard interface which I had modified. This procedure doesn't know about the field that I added and so the value passed would be 0 (null). And because I defined that this field can accept null values, this value was indeed passed ... and disconnected the work order from the customer order line. Ooooops! Red face time (again).

Immediately I removed my field from the interface whereupon printing ceased to disconnect. I then altered my original procedure to update the work orders table directly (nu nu nu) in order to disconnect.

Moral of this story: don't mess with standard interfaces!

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.

Order of tuples can matter in a form interface

One can suggest that there are several kinds of form interface: those which update one form, those that update two forms and so on. Of those that update two forms (father and son forms) there can be multiple lines to be updated in the son form. Apart from the obvious limitation that the information for a father form has to come before its sons, the order of lines within the son form is normally  irrelevant.

Yesterday I came across a case where the order of lines within the son form is vitally important, which is what I want to write about today. Specifically I am writing about reporting completed work orders: the father form is "Reporting Production" and the son form is "Production Details". We have begun working with several actions within a work order: to prepare the upholstery for a chair, the cloth has to be cut, the seat has to be made by glueing together wood and plastic, and then the cloth has to be stretched over the seat and sewn together. We are trying to introduce a system whereby each worker reports each action by means of a personal wireless barcode scanner.

Unfortunately, what management wants is not what is happening, so it frequently happens that someone will report only the final stage in the process. Doing so closes the work order but leaves the inventory in an unstable state, so it is desired that reporting the final stage will also report any previous unreported stages. I wrote code which is activated after a barcode is read: first it enters into a load table the number of the current production report (I open one report per day for the department), then a line is inserted for the action which is encoded in the barcode. Following this, a check is made to see whether this is the last stage in the work order, then if so, any previous unreported stages are added to the load table, which can look like this:

Line	Record type	Payload
1	1	Report number
2	2	Action as encoded in the bar code
3	2	Prior unreported stage in work order
4	2	Prior unreported stage in work order

The first two lines were doing their job but the final, additional, lines did not cause their stages to be reported. I looked at this for some time before I had an inkling of what might be the problem: reporting the final stage of a work order causes itself to close automatically. Then when the extra lines come along, the work order is closed and so will reject any extra lines. My solution was to number the barcode line as 99, so the work table looks like this prior to its being inserted:

Line	Record type	Payload
1	1	Report number
99	2	Action as encoded in the bar code
2	2	Prior unreported stage in work order
3	2	Prior unreported stage in work order

The lines are handled according to their line number, and so line 2 is handled before line 99 even though it was physically inserted after line 99. This indeed solves the problem.

Lesson of the story: sometimes the line number within a load table for a form interface is very important.

Field names/titles in tables

There is a standard table, STACK11, which I use frequently, as it has three key fields. It also has several real fields, but unfortunately has only two integer fields. So I added to this table two new fields, TEST_INTDATA3 and TEST_INTDATA4 and called them INTDATA3 and INTDATA4 respectively.

At some stage, I discovered that I was having problems with this table. The following code should fail the syntax checker but passes:

LINK STACK11 TO :$.S11;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO STACK11 (KEY1, KEY2, KEY3, INTDATA1, INTDATA2, INTDATA3)
VALUES (1, 2, 3, 4, 5, 6);
UNLINK STACK11;

Not only does it pass the syntax check, but when one selects the fields from the table, TEST_INTDATA3 has the value 6! Putting this simply, Priority has a bug in the syntax checker for procedures which allows it to get confused if the name of a field is the same as the title of a user-added field. The moral of the story is to give the field a different title, such as TEST_INTDATA3 (i.e. the title is the same as the field name).

Incidentally, any attempt to add STACK11.INTDATA3 to a report fails; this is because the name of each field is checked when adding to a report. Any name which is not in the directory is immediately marked as being false. This is not a syntax check.