Adding line items to an existing document

There is a section in the SDK with the above title (in chapter 6, 'Documents) that reads as follows: When an interface that adds line items to a document is executed, by default the new items are inserted first in the document (that is, they receive a smaller line number than existing records). For instance, if a given interface adds lines to an existing order that contains two lines, the new record will appear on line 1, the first existing order item will move to line 2 and the second existing order item will move to line 3. Unfortunately this is true. Following this is an explanation of how to insure that new lines will be added at the end of the document, but as usual, this isn't particularly clear (or at least, not clear to me).

As it happens, I have a procedure that I run most days that may add lines to a warehouse transfer document (form DOCUMENTS_T). I won't go into the whys and wheres of this, but I do know that whenever I run this, lines will be added to the beginning of the warehouse transfer and not at the end. For my purposes, it doesn't matter where the lines are added, but I want to use this example in order to deepen my understanding as I have a procedure for an external client that does something similar, except that there I am adding a line to an invoice, and for reasons that aren't relevant, it's very important that the added line be at the end.

So what does one do? In the case of the warehouse transfer document, I added to the interface for form TRANSORDER_T the field TRANS (saved in INT10), where TRANS is the automatic key of the table and is also hidden. At first I thought that the following would  suffice

SELECT MAX (LINE) INTO :LINE
FROM TRANSORDER
WHERE DOC = :DOC;
SELECT TRANS INTO :TRANS
FROM TRANSORDER
WHERE DOC = :DOC
AND LINE = :LINE;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT10)
VALUES (2, '2', :TRANS);

Although TRANS is the A key, the form is sorted by LINE, so I thought it best to get the TRANS of the last line then insert this into GENERALLOAD. Lines that the procedure will add will come after this line.

The interface manager complained that line 2 didn't have a part. This led me to realise that I need to enter all of the necessary fields that already exist in TRANSORDER, as well as TRANS into GENERALLOAD before the added lines. Thus my procedure now contains the lines

INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT1)
VALUES (1, '1', :DOC);
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT10, TEXT1, INT1,
TEXT13, TEXT17, TEXT18)
SELECT 1 + SQL.LINE, '2', TRANSORDER.TRANS, PART.PARTNAME,
TRANSORDER.QUANT, CUSTOMERS.CUSTNAME, CUSTOMERS.CUSTNAME,
WAREHOUSES.LOCNAME
FROM TRANSORDER, PART, CUSTOMERS, WAREHOUSES
WHERE TRANSORDER.PART = PART.PART
AND TRANSORDER.CUST = CUSTOMERS.CUST
AND TRANSORDER.WARHS = WAREHOUSES.WARHS
AND TRANSORDER.DOC = :DOC;
SELECT MAX (LINE) INTO :LINE FROM GENERALLOAD;

I tested this by deleting the last few lines of the existing warehouse transfer, then running the procedure. If the above code works correctly, then the lines that I deleted should be restored to the document and they should be the final lines. Indeed they were added at the end.

What about invoiceitems, especially considering that this table doesn't have an automatic key but rather a composite key of IV and KLINE? The following works nicely to copy the lines

INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT1)
VALUES (1, '1', :IV);
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT10, TEXT1, INT1,
REAL1)
SELECT 1 + SQL.LINE, '2', INVOICEITEMS.KLINE, PART.PARTNAME,
INVOICEITEMS.TQUANT, INVOICEITEMS.PRICE
FROM INVOICEITEMS, PART
WHERE INVOICEITEMS.PART = PART.PART
AND INVOICEITEMS.IV = :IV;
SELECT MAX (LINE) INTO :LINE FROM GENERALLOAD;
....
/* then new lines get added without INT10 */
:LINE = :LINE + 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT1, INT1, REAL1)
VALUES (:LINE, '2', '11055332YA307S', 6000, 250.0);

This new line does get added at the end of the invoice.

Using NFILE prevents complications

Someone sent me some code written by a third party that inserts data into the table LABELS, naturally for printing labels. The person who sent me the code wanted the sort order of the labels to be changed so that it would be according the part numbers sorted alphabetically. As the original programmer used the INSERT INTO/SELECT FROM syntax, it wasn't possible to simply add 'ORDER BY PART.PARTNAME' at the end of the query. 

The solution was provided by what seemed to be a somewhat pointless subroutine earlier in the program.

SUB 20;
INSERT INTO STACK4 (KEY)
SELECT PART
FROM   PART
WHERE  PART <> 0;
RETURN;

Saving the parts in this table gave me the opportunity to add a sort order to STACK4 as per the following.

:SORDER = 0;
DECLARE CSUB50 CURSOR FOR
SELECT PART, PARTNAME
FROM PART
WHERE PART > 0
ORDER BY PARTNAME;
OPEN CSUB50;
GOTO 530 WHERE :RETVAL <= 0;
LABEL 510;
FETCH CSUB50 INTO :PART, :PNAME;
GOTO 520 WHERE :RETVAL  <= 0;
:SORDER = :SORDER + 1;
UPDATE STACK4
SET INTDATA = :SORDER
WHERE KEY = :PART;
LOOP 510;
LABEL 520;
CLOSE CSUB50;
LABEL 530;

Then I could make use of STACK4.INTDATA in the statement that inserts the data into LABELS, ensuring that the labels would indeed be sorted by partname. Later on I noticed that the programmer had not used the field LABELS.SORT - STACK4.INTDATA can be inserted into this field in order to ensure the correct sort order [see below *].

Reviewing the code. I saw that the programmer had declared a subroutine 30 that on first glance appeared to be the same as subroutine 20; the only difference was that instead of FROM PART, the second subroutine had FROM PART ORIG. In other words, using the unlinked PART table instead of the linked table. How was the programmer checking whether PART was linked? By the following code

:E_COUNT = 0;
SELECT COUNT(*)
INTO   :E_COUNT
FROM   PART
WHERE  PARTNAME NOT IN ('', :E_CHVAL)
;
:E_ORIGCOUNT = 0;
SELECT COUNT(*)
INTO   :E_ORIGCOUNT
FROM   PART ORIGPART
WHERE  PARTNAME NOT IN ('', :E_CHVAL)
;
:E_LINKPART =
( :E_COUNT <> :E_ORIGCOUNT AND :E_COUNT <> 0 ? 'Y' : '\0' )

Either SUB 20 or SUB 30 would be invoked, depending on the value of the variable :E_LINKPART . Although I haven't seen the definitions of the various parameters,I am sure that the parameter PAR (implying that this code is called from another procedure) is defined as FILE. If it were defined as NFILE, then there is no need for :E_LINKPART and only one subroutine would be needed.

LINK PART TO :$.PAR;
ERRMSG 500 WHERE :RETVAL <= 0;
GOTO 1 FROM PART WHERE PART > 0;
UNLINK PART;
LABEL 1;
...
GOSUB 20;

I realise that I've made a mountain out of a molehill, but it seems that the original programmer had to build a baroque solution to solve a problem that has a very simple solution. This is not the first time I've seen this, and I think that it stems from an incomplete understanding of how to program - not necessarily how to program in Priority.

Even without using NFILE, the programmer still could have saved the SUB 20/30 duplication:

:E_COUNT = :E_ORIGCOUNT = 0;
SELECT COUNT (*) INTO :E_COUNT FROM PART;
SELECT COUNT (*) INTO :E_ORIGCOUNT FROM PART ORIG;
GOTO 1 WHERE :E_COUNT <> :E_ORIGCOUNT;
UNLINK PART;
LABEL 1;

After all, there's no point in the clause WHERE PARTNAME NOT IN ('', :E_CHVAL) if the same clause is used in both queries; at worst, both values might be one too high (e.g. 37 instead of 36) but as equality is being checked, it makes no difference. Incidentally, :E_CHVAL appears not to be defined anywhere in the procedure; another rookie mistake (it turns out that :E_CHVAL was defined in a previous stage of the procedure).

* To show that I too am not immune to writing sub-optimal code, the code that enters data into STACK4 could be rewritten as below, without the need of a prior INSERT INTO statement. There's no real need to use a subroutine at all as this code is called only once, although I understand why the programmer did this: using subroutines gives the procedure a sense of structure.

SUB 20; /* Insert data into stack4 in partname order */
DECLARE CSUB20 CURSOR FOR
SELECT PART, PARTNAME, SQL.LINE
FROM PART
WHERE PART > 0
ORDER BY PARTNAME;
OPEN CSUB20;
GOTO 230 WHERE :RETVAL <= 0;
LABEL 210;
FETCH CSUB20 INTO :PART, :PNAME, :LINE;
GOTO 220 WHERE :RETVAL  <= 0;
INSERT INTO STACK4 (KEY, INTDATA)
VALUES (:PART, :LINE);
LOOP 210;
LABEL 220;
CLOSE CSUB20;
LABEL 230;
RETURN;

The pitfalls of the PARTVAR table/form

Recently I was asked to write a report that would show the time required to build a sales part (actually for all the parts that were ordered over a year). For those that don't know, this time data is to be found in the table PARTVAR that is displayed as a son form of the PART form. One very important field in this table is VALID - a part can have several activities listed in its PARTVAR form, but not all of them necessarily are valid. A report that wants to show how much time would be required to build a sales part should ignore lines not marked as valid.

I wrote the report, ran it - and discovered that the values that the report was showing did not match those in a 'normal' BOM report. After getting very dirty hands by examining the data being summed at a very low level, I discovered that the report was including PARTVAR lines that were not valid, despite the fact that the query explicitly excluded those lines.

Even stranger, if I ran the report again after having examined the data in the PARTVAR form, the cumulative time for the part examined would be reduced!

Eventually I found the reason for this strange behaviour (actually I had discovered it several years ago but had forgotten). When descending from the PART form to its son PARTVAR form, the latter has a pre-form trigger than runs an external program called PARTVAR (the actual command is EXECUTE PARTVAR :$$.PART). Presumably this program updates the data stored in the PARTVAR table so that a query accessing it will receive up-to-date values.

This is a really important pitfall! Eventually I was reduced to calling the external program for every part whose time I was accumulating. A procedure would descend through the exploded BOM and call PARTVAR for every P part. This adds no small amount of time to the procedure's execution, but it's very important. 

At the same time I added the EXECUTE PARTVAR command to the procedure that displays the BOM - I had called it for the 'father' part whose BOM was being exploded, but not for the sons. Thus I discovered that the previously reported time for my test part was wrong and did not include a certain activity; the opposite behaviour to the big 'time only' report that was over-reporting by including non-valid activities.

An advanced subversion of HTML documents

I've written several times about HTML documents in the past, primarily about what I call 'container documents'. This time around, I am writing about an HTML document in its regular sense: the creation of an invoice document, but of course subverting it to display something for which the HTML document framework was not designed.

The customer sells electricity; they want an invoice to show the lines for each electricity meter contained in the invoice (so far, standard functionality) ... and then they want a separate, additional, page for each meter - very much non-standard. In order to create a page for each meter, the HTMLCURSOR of the procedure should return a meter (i.e. SERNUMBERS.SERN) from those contained in the invoice. But the procedure has to be passed an invoice number (or maybe a range of invoices), so how can one display what would appear to be a standard invoice along with non-standard extras?

'Obviously' (it was far from obvious when I started work on this) the list of invoices passed to the procedure in the PAR parameter has to be somehow saved as something else (I used STACK2), then the PAR parameter has to be repositioned in order to receive a list of meters in each invoice. My original code for this pre-HTMLCURSOR stage was

/* Stage 20: Save the invoices passed as a parameter to this procedure */
:PAR1 = '$';
LINK STACK2 TO :$.ST2;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK INVOICES TO :$.PAR;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO STACK2 (ELEMENT, TYPE)
SELECT SQL.LINE, IV
FROM INVOICES
WHERE IV > 0;
UNLINK AND REMOVE INVOICES;
:$.IDX = 0;  /* global iterator */
SELECT MAX (ELEMENT) INTO :$.MAX FROM STACK2;
UNLINK STACK2;
SELECT SQL.TMPFILE INTO :$.PAR FROM DUMMY;

The IDX and MAX variables are necessary for looping through the list of invoices, as will be seen in the next stage that begins as follows

/* Stage 30: Set up for HTML document */
:$.HDR = 1;
LINK STACK2 TO :$.ST2;
:$.IDX = :$.IDX + 1;
SELECT TYPE INTO :$.IV
FROM STACK2
WHERE ELEMENT = :$.IDX;

Following this originally was this code

UNLINK AND REMOVE SERNUMBERS;
LINK SERNUMBERS TO :$.PAR;
INSERT INTO SERNUMBERS
SELECT * FROM SERNUMBERS ORIG
WHERE EXISTS (SELECT 1 FROM INVOICEITEMS
WHERE SERNUMBERS.SERNUM = INVOICEITEMS.TEST_SERN
AND SERNUMBERS.SERN > 0
AND INVOICEITEMS.IV = :$.IV);

Then in the next stage, HTMLCURSOR would retrieve a value from :$.PAR, i.e. SERNUMBERS, and the appropriate documents would be created. Except this didn't work!

After some trial and error, I discovered that if the PAR parameter initially contains (in this case) invoices, as defined in the 'table name' of the line for the parameter, then it has to contain invoices all the time. Redefining PAR and linking it to SERNUMBERS doesn't work. So how can I pass a list of devices? By saving SERN and SERNUM (i.e. the A and U keys) of SERNUMBERS in a linked INVOICES table! It doesn't matter that an invoice with this key number may not exist; eventually I will not be accessing an invoice but a device. For the moment, the INVOICES table is simply a database table that fits my requirements, regardless of what its purpose is within Priority. Here is the continuation of stage 30 - still before HTMLCURSOR.

/* It seems that PAR always has to be linked to invoices. So insert
into the linked table values from SERNUMBERS */
UNLINK AND REMOVE INVOICES;
LINK INVOICES TO :$.PAR;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO INVOICES (IV, IVNUM)  /* SUBVERSION!!! */
SELECT SERN, SERNUM
FROM SERNUMBERS
WHERE EXISTS (SELECT 1 FROM INVOICEITEMS
WHERE SERNUMBERS.SERNUM = INVOICEITEMS.TEST_SERN
AND SERNUMBERS.SERN > 0
AND INVOICEITEMS.IV = :$.IV);

The HTMLCURSOR stage is simply 'SELECT IV FROM INVOICES WHERE IV > 0'. The SQLI stage following this begins as follows

/* Stage 50: This turns an 'invoice' number back into a sernumber */
SELECT SERN INTO :$.SRN
FROM SERNUMBERS
WHERE SERN = ATOI (:HTMLVALUE);

Stage 50 contains all the complicated boiler-plate code from the regular procedures, e.g. stage 10 of WWWSHOWCIV. In order to make the entire document display, I had to change certain statements of that boiler-plate code, specifically replacing any usage of ATOI (:HTMLVALUE) with :$.IV - this is what the code expects to receive. In the body of the procedure I am including standard reports such as WWWLOGO and WWWDOCNUM along with special reports of mine. The standard reports receive the :$.IV parameter and the special reports receive :$.SRN. Finally at the end, prior to the closing INPUT stage, there is a small SQLI stage with the following statements

:$.GO = (:$.IDX < :$.MAX ? 30 : 500);
:$.HDR = :$.HDR + 1;

Should there have been more than one invoice in the original list passed to the procedure, then the procedure will loop back to stage 30, where the subverted list of invoices is unlinked and then a new list created. The :$.HDR variable is needed so that the initial summation page can be printed for each invoice in the original invoice list, but not printed for every meter returned by HTMLCURSOR. This is something internal to this procedure and cannot necessarily be generalised.

This actually works! I very much doubt that the customer realises how much subversion is required to supply what is asked for.

An insight into displaying HTML text in a report

I hope that I am not revealing something new that one has to use the DAYS table in order to display HTML text in a report - e.g. WWWORD_2XT. This is even documented on page 105 of the SDK for version 21.0 in a section entitled 'Displaying HTML Text in Reports'.

I have always thought that the use of the DAYS table was some kind of magical sign to the program sitting behind the report generator that HTML text has to be shown (in the same way that any field containing the strings FILENAME or BIRTHDAY will act in a special way).

It turns out that this is not true. Idly I was looking at some standard reports when I came across the procedure COMPMSG; the SQLI stage of this procedure links the STACK table and enters two tuples, 1 and 2. I wondered what this would do in the report: line 30 has this expression

(STACK.ELEMENT = 1 ? EXTMSG.MESSAGE : EXTMSGTEXT.TEXT)

In other words, one doesn't have to use the DAYS table to display HTML text in a report; it's simply convenient as this table already has tuples 0 and 1 (it has more, but that's not relevant). And so my 'magic value' hypothesis is proven false. Intriguingly there is no use of EXTMSGTEXT.TEXTLINE in this report.

Will I use this option? I doubt it, as one has to set up the linked STACK table first whereas the DAYS table is always available.

Is this the lamest code I've ever seen?

In the course of debugging a procedure that someone else had written years ago for a customer, I came across the following code:

DECLARE UPD_DATA9 CURSOR FOR                                        
SELECT DTOA (:$.DAT, 'DD/MM/YY') FROM DUMMY;                         
OPEN UPD_DATA9;                                                     
LABEL 10;                                                           
FETCH UPD_DATA9  INTO :IVDATE;                                      
GOTO 100 WHERE :RETVAL <= 0;                                        
UPDATE TEST_INVLOADDIV                                               
SET TEST_IVDATE = ATOD (:IVDATE, 'DD/MM/YY');                         
LOOP 10;                                                            
LABEL 100;                                                          
CLOSE UPD_DATA9;  

This code simply updates the field 'TEST_IVDATE' in all the rows of the table 'TEST_INVLOADDIV' with the date :$.DAT. This must be the lamest code that I've ever seen as there are so many stylistic errors. That is not to say that this code won't achieve what it's supposed to but it is so wrong!

Where should I start? The global parameter :$.DAT is guaranteed to be a date so there's no need to turn it into a string in the second line, then turn it back into a date in the eighth line. There is absolutely no reason to use a cursor that iterates over ... a global parameter! Totally pointless. The above code was written as a separate SQLI stage in a procedure; there's no real reason why it couldn't have been included in another stage with more (bad) code. The above code can be written succinctly as

UPDATE TEST_INVLOADDIV SET TEST_IVDATE = :$.DAT;

Of course, if the programmer were being paid by lines written, then the original version is much better, having 11 lines. My streamlined version wouldn't earn the programmer very much, having only one line. But if the programmer is being paid by her level of sophistication ....

More fixing a complicated problem with BOM revisions and 'info only' parts

The code given in the previous blog was almost correct, but subtly wrong. I had ignored the fact that each son part would appear three times, and that the first instance would set SONACT to be -1 thus causing the son not to appear in the report, even though there would be a correct instance later on. A more correct version of this code is

 SUB 820;
 :OK = 0; /* the query below may fail */
 :RVDATE = 01/01/88;
 SELECT MAX (ORIG.RVTILLDATE) INTO :RVDATE
 FROM PARTARC ORIG
 WHERE ORIG.PART = :PARENT
 AND ORIG.SON = :SON
 AND ORIG.INFOONLY <> 'Y';
 :OK = (:RVDATE = 01/01/50 ? 1 : 0);
 GOTO 821 WHERE :OK = 1;
 UPDATE PARTARC /* effectively remove the part from the tree */
 SET SONACT = -1
 WHERE SON = :SON;
 LABEL 821;
 RETURN;

Saving the maximum value of RVTILLDATE into a variable was not in my original code; this was intended to help with running the procedure with the debugger. But for some reason the 'wonderful' web interface didn't work so I didn't get an automatic debug output.

There was a strange phenomenon whose source I failed to track down, even after wasting an hour on it. The part that I was using for testing should have four active sons, but for some reason five were being displayed. That fifth part had been marked 'for info only' in all the versions so the 'select max' query above should have failed. But somehow this fifth part never even got to the subroutine, a failure that I could not track. Eventually I added a cursor that is called immediately after the SONRAW procedure.

 DECLARE C840 CURSOR FOR
 SELECT SON, SONPARTREV
 FROM PARTARC
 WHERE SON > 0;
 OPEN C840;
 GOTO 843 WHERE :RETVAL <= 0;
 LABEL 841;
 FETCH C840 INTO :SON, :PARENT;
 GOTO 842 WHERE :RETVAL <= 0;
 :A = :B = 0;
 SELECT COUNT (*), SUM ((INFOONLY = 'Y' ? 1 : 0)) INTO :A, :B
 FROM PARTARC PA
 WHERE PART = :PARENT
 AND SON = :SON;
 LOOP 841 WHERE :A <> :B;
 UPDATE PARTARC /* this is the linked table */
 SET SONACT = -1
 WHERE SON = :SON;
 LOOP 841;
 LABEL 842;
 CLOSE C840;
 LABEL 843;

This cursor got rid of the spurious part and probably helped the rest of the procedure run a bit faster as there would have been fewer parts to check in the main cursor.

Working with BOM revisions is very complicated!

Fixing a complicated problem with BOM revisions and 'info only' parts

For a customer, I wrote a year ago a procedure and report that explodes a part's BOM and calculates various data for the part's sons. I thought that the report was working fine until the customer brought to my attention a specific part: despite the fact that its sons appear as "for info only" (FIO) in the BOM table, these son parts were appearing in the report.

How I reached the solution for the next step is lost to me, so I'll say that by divine intervention I saw that the father part had three BOM revisions, and that the sons that were marked FIO in the BOM table were not marked as such in one of these revisions.

The procedure uses the SONRAW program to explode the BOM, where the fourth parameter is 0, i.e. don't include parts marked as FIO. How is it that these parts still appear? Could it be that SONRAW is working on all the BOM revisions and so is finding the revision in which the parts are not marked as FIO?

If one dumps the data contained in the table PARTARC for the given father part, one will see that the son parts appear three times, once for each revision. The difference between these apparently identical lines, apart from the FIO flag, is that their RVFROMDATE and RVTILLDATE fields differ; these values are identical to the revisions in the BOM revisions table of the father.

Whilst walking the dog after the consultation, I thought about this problem and concluded that first I would have to find the line with the maximum value for RVFROMDATE then check whether this line is marked as FIO. It seems that the right hemisphere of my brain was working on this problem all night because at about 2:30 am, I suddenly realised that I don't have to look for a maximum value - I simply need to find the line with RVTILLDATE = 01/01/50.

This morning I came in fired up with enthusiasm and added this condition to the cursor that iterates over the sons. Nothing changed. It turns out that SONRAW zeroes out this field in the linked PARTARC table: annoying. I tried another track: I know that PARTARC.SONPARTREV contains the internal part number of the direct parent. Via this field I could access the original, unlinked, tuples of PARTARC in order to find the appropriate line. As the check has to occur twice in the procedure, I wrote a subroutine that contains the code that checks. 

The part at the end of the subroutine that sets SONACT to be -1 is because the linked PARTARC table is passed to the report that 'knows' not to display lines where SONACT = -1. This was something that eluded me until late in the day. The variable :PARENT is PARTARC.SONPARTREV. The returned variable :OK will cause the procedure to skip parts of the code if the value is 0.

/**************************************************************
 SUB 820 - Check whether son is in the current version
 of the BOM and if so, is info only
 ***************************************************************/
 SUB 820;
 :OK = 1;
 SELECT 0 INTO :OK FROM PARTARC ORIG
 WHERE ORIG.PART = :PARENT
 AND ORIG.SON = :SON
 AND (ORIG.INFOONLY = 'Y'
 OR ORIG.RVTILLDATE <> 01/01/50);
 GOTO 821 WHERE :OK = 1;
 UPDATE PARTARC /* effectively remove the part from the tree */
 SET SONACT = -1
 WHERE PART = :FATHER
 AND SON = :SON;
 LABEL 821;
 RETURN;
 

Device trees, or Implementing recursive tree descent in Priority (part three)

To recap, this series of blog entries is discussing device trees and how to turn simple one-level (father:son) data into hierarchical  (grandparent: parents: children, etc) data. Once again, here is the tree that I am using as the example:

Father	Son
A	B1
A	B2
A	B3
B1	B1C1
B1	B1C2
B2	B2C1
B2	B2C2
B3	B3C1
B3	B3C2
B1C1	B1C1D1

In the previous installment, I showed how one can write code to descend through the tree; the devices accessed were B1, B1C1 and B1C1D1. Now the code has come to an impasse and has to backtrack (i.e. ascend back up the tree) in order to continue. When :NODE is equal to B1C1D1,the following query will fail and so control passes to LABEL 20.

LABEL 10;                                                           
SELECT SON INTO :SON                                                
FROM TEST_SERNTREE                                                  
WHERE FATHER = :NODE                                                  
AND USER = 0;                                                       
GOTO 20 WHERE :RETVAL <= 0; 
.
.
.
LABEL 20;                                                                                               
DELETE FROM STACK2 WHERE ELEMENT = :NODE;                                                                                    
GOTO 30 WHERE NOT EXISTS (SELECT 1 FROM STACK2 WHERE ELEMENT > 0);  
SELECT MAX (TYPE) INTO :MAX FROM STACK2;                            
SELECT ELEMENT INTO :NODE FROM STACK2 WHERE TYPE = :MAX;            
:LEVEL = :MAX;                                                      
LOOP 10;                                                            
LABEL 30;                      

This is the kludgey part of the code. One of the final lines prior to LABEL 20 sets :NODE equal to :SON (not shown) before the code loops back to LABEL 10. So at the first statement after LABEL 20, the current device (B1C1D1) is removed from STACK2, the table that is serving as the recursion's memory. Then there is a check that there is still data in STACK2 - this is the terminating condition. Following this, the maximal value of TYPE  is obtained - at this stage, this should be 3. Having this value enables the value of the final element in STACK2 to be copied into :NODE (or as they say in computer science circles, the value is popped off the stack). This should be B1C1. As there are no more sons of this device, LABEL 20 will be reached again and B1 popped off the stack. The code at LABEL 10 will ensure that the new value of :SON will be B1C2.

Eventually the final value (:ANCESTOR) will be deleted from STACK2 and so the code will jump to LABEL 30. At this stage, if everything has gone correctly, STACK4 will look like the following

KEY	INTDATA	INTDATA2
1	B1	2
2	B1C1	3
3	B1C1D1	4
4	B1C2	3
5	B2	2
6	B2C1	3
7	B2C2	3
8	B3	2
9	B3C1	3
10	B3C2	3

So at this stage, we have indeed achieved the goal of turning simple one-level data into hierarchical data. This is equivalent to the output of the SONRAW program. But there is one more detail that needs to be added: B1 should be displayed with the level 1, B1C1 as 1.1, B1C1D1 as 1.1.1, B1C2 as 1.2, etc. Fortunately there is no need to reinvent the wheel: I figured out how to do this with SONRAW data here. Again, this is a recursive requirement, and is turned into an iterative solution by means of a stack, again STACK2.

Below is a screen shot from Priority with the tree displayed with the levels.

 

---

Since writing these blog entries, I've been having second thoughts about some of the code. There is definitely no need for the field 'ANCESTOR' in the table TEST_SERNTREE. In the procedure that displays the tree, there is no need to access the 'USER' field either. STACK has to be given an alias in the form trigger code otherwise this doesn't work (presumably there is use of STACK somewhere else in the SERNTREE form. The first part of the procedure is now

LINK STACK4 TO :$.STK;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK STACK2 TO :$.ST2;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK STACK SST TO :$.ST0;
ERRMSG 1 WHERE :RETVAL <= 0;
:LINE = 0;
:LEVEL = 1;
INSERT INTO STACK2 (ELEMENT, TYPE)
VALUES (:ANCESTOR, 1);
:NODE = :ANCESTOR;
/***************************************************/
LABEL 10;
SELECT SON INTO :SON
FROM GLOB_SERNTREE
WHERE FATHER = :NODE
AND NOT EXISTS (SELECT 1 FROM STACK SST
WHERE SST.ELEMENT = GLOB_SERNTREE.SON 
GOTO 20 WHERE :RETVAL <= 0;
INSERT INTO STACK2 (ELEMENT, TYPE)
VALUES (:SON, :LEVEL + 1);
INSERT INTO STACK SST (ELEMENT) VALUES (:SON);
:LINE = :LINE + 1;
INSERT INTO STACK4 (KEY, INTDATA, INTDATA2)
VALUES (:LINE, :SON, :LEVEL + 1);
:NODE = :SON;
:LEVEL = :LEVEL + 1;
LOOP 10;

Device trees, or Implementing recursive tree descent in Priority (part two)

In the previous entry on this topic, I explained what a device tree is. In Priority, I defined a simple table (TEST_SERNTREE) that has three main fields: FATHER, SON and ANCESTOR. Using the previous example of a car and its engine, this table would hold the following tuple {Father: car serial number; Son: engine serial number; Ancestor: car serial number}. The Ancestor field* may not be needed for a strict implementation of device trees, but it helps in order to identify all the serials for a given tree. As I explained before, this is a simple one-level representation of data; it is required to write a procedure that turns this simple data into hierarchical data. A recursive procedure to create the hierarchical data in pseudocode would be something like

 Descend (ancestor);
 
 Procedure Descend (sern);
 begin
  if sern does not exist then exit;
  save data about this sern;
  node = first son of this sern that has yet to be accessed
  descend (node)
 end

As computer science tells us, any recursive algorithm can be replaced by an iterative one, and of course that is what is necessary for Priority. The above pseudocode requires some database definitions. "Save data about this sern" means saving it in a table such as STACK4. Initially I wrote a step procedure to display a hierarchical report where the data is saved in STACK4 but afterwards converted the procedure to a form trigger in order to display the data in a form; the trigger uses a dedicated table for this named TEST_SHOWSERNTREE. 

The second database definition needed handles the "sern that has yet to be accessed" requirement. Originally I saved the serns that have been accessed in a linked copy of STACK but for some reason this didn't work too well, so eventually I cheated somewhat and added a new field to the TEST_SERNTREE table, USER*. Initially the trigger sets this field to zero for every entry that has the given serial number as ancestor, then sets the value for each device encountered. 

Priority programmers are conditioned to use cursors as the primary means of extracting multiple tuples of data from the database. This is the correct means for 99.99% of all procedures, but for this particular procedure, a cursor is not the way to obtain the data. First, we have to obtain data for the first son/sern of the ancestor that has yet to be accessed (i.e. USER = 0), then use this sern in order to obtain data for its first son that has yet to be accessed, then use that sern in order to obtain data for its first son. If there is no unaccessed son left, then the procedure must back up a level and continue from there.

Here is the tree that I am using as the example:

Father	Son
A	B1
A	B2
A	B3
B1	B1C1
B1	B1C2
B2	B2C1
B2	B2C2
B3	B3C1
B3	B3C2
B1C1	B1C1D1

The output should be (ignoring A): B1, B1C1, B1C1D1, B1C2, B2, B2C1, B2C2, B3, B3C1, B3C2.

Going back to computer science, turning recursion into iteration requires a table to retain the depth of recursion. This table gets accessed when the procedure has to backtrack, i.e. after the sern B1C1D1 is accessed, the procedure would try to access the next unaccessed son of B1C1, but as there isn't one, the backtracking table is required in order to 'let the procedure know' that it should continue with B1C2. As this sern has no sons, again the procedure has to backtrack in order to know that is required to continue with B2, then the sons of B2, etc.

Let's write a little code that will be the start of the procedure.

LINK STACK4 TO :$.STK;                                              
ERRMSG 1 WHERE :RETVAL <= 0;                                     
LINK STACK2 TO :$.ST2;                                              
ERRMSG 1 WHERE :RETVAL <= 0;                                     
:LINE = 0;                                                          
:LEVEL = 1;                                                         
INSERT INTO STACK2 (ELEMENT, TYPE)                                  
VALUES (:ANCESTOR, 1);                                              
:NODE = :ANCESTOR;                                                  
UPDATE TEST_SERNTREE                                                
SET USER = 0                                                        
WHERE ANCESTOR = :ANCESTOR; 
/********************************/
LABEL 10;                                                           
SELECT SON INTO :SON                                                
FROM TEST_SERNTREE                                                  
WHERE FATHER = :NODE                                                  
AND USER = 0;                                                       
GOTO 20 WHERE :RETVAL <= 0;                                         
INSERT INTO STACK2 (ELEMENT, TYPE)                                  
VALUES (:SON, :LEVEL + 1);                                          
:LINE = :LINE + 1;                                                  
INSERT INTO STACK4 (KEY, INTDATA, INTDATA2)               
VALUES (:LINE, :SON, :LEVEL + 1);                          
UPDATE TEST_SERNTREE                                                
SET USER = SQL.USER                                                 
WHERE SON = :SON;                                                   
:NODE = :SON;                                                       
:LEVEL = :LEVEL + 1;                                                
LOOP 10;
LABEL 20;  

The code prior to label 10 sets everything up. The variable :NODE will hold 'the current device' and initially is the ancestor. The SELECT statement after LABEL 10 gets 'the first unaccessed son' of :NODE into the variable :SON. If there is no such device, then :RETVAL will be 0 and the code will leave this loop and go to label 20. Assuming that there is a son, it will be stored in STACK2 (this table is the backtrack table), then the son is stored in STACK4. Note that this table has a simple incrementing key - this will ensure that the table will hold the data in the hierarchical order as required. Then the actual sern in the real table TEST_SERNTREE has its USER field updated to show that this sern has been accessed*. Finally :NODE is set to the value of :SON, the level is incremented and the loop continues.

Thus, the first time that LABEL 10 is reached, the value of :NODE will be :ANCESTOR (i.e. A). The SELECT statement will return B1; this serial is entered into both STACK2 and STACK4 as well as being marked as used. Finally :NODE is changed from :A to :B1. The second time that LABEL 10 is reached, the SELECT statement will return B1C1. The third time around, the SELECT statement will return B1C1D1. The fourth time around, the SELECT statement will fail as B1C1D1 has no sons (or one could say that there is no tuple in which FATHER = B1C1D1). So execution jumps to LABEL 20, but I'll leave what happens there for another day.

---

* This is not optimal and will be corrected in the next installment.

Device trees, or Implementing recursive tree descent in Priority (part one)

One of the standard entities in Priority is called 'Catalogue of Parts w/Serial Nos.' but I prefer the name 'devices'. In its simplest form, a device is a part that has a serial number. For example, all cars of a given model would have the same part number but would have individual serial numbers. Another standard entity that vaguely exists in Priority is a 'device tree'; for example, the car that I mentioned in the previous sentence has its own serial number, but its engine has its own serial number. Together the car and the engine form a device tree, where the car is the parent/father/ancestor device and the engine is the son device.

I wrote above that this standard entity vaguely exists in Priority: there are two tables that apparently hold data about device trees, SONSERN and SONSERNENV. These tables appear to be built every time a device tree needs to be shown (in a son form of the SERNUMBERS form) by an external program called SONSERN, but I have not been able to ascertain the source of the data that SONSERN uses to build the trees. The source may be in 'Warehouse Assemblies' but I haven't been able to verify this.

One of my clients uses a hand-rolled version of device trees that I developed several years ago. These device trees are what might be called 'degenerate trees': they only have two levels (parent and sons) and so aren't really trees. Another client approached me a few days ago with a similar need for device trees, except here we are talking about trees with at least three levels of device (I don't think that there will be four levels, but one never knows).

Device trees are very similar to BOMs, at least in concept: Priority maintains a simple table (PARTARC) in which data are held regarding the sons of a given parent. Let's say that part A is a parent; it will have sons B1, B2 and B3. But these sons can also have sons of their own: B1C1, B1C2, B2C1, B2C2, B3C1, B3C2, etc. A simple database table enables access to the sons of a given parent, but this table does not allow access (or more correctly, cannot store data relating) to 'grandchildren'. In order to display the complete structure of a BOM, one uses the SONRAW program about which I have written in the past. SONRAW converts the one level data into a hierarchical format.

Conceptually, the external program SONSERN is equivalent to SONRAW, but as I wrote earlier, I do not know from where SONSERN derives its raw data and so it is essentially useless for my purposes. In order to display a complete device tree, or turn simple one level data in hierarchical data, one needs to implement what is known in computer science terms as 'recursive tree descent'. That's all well and good in programming languages that support recursion such as Pascal, C and Python (there are many more), but not good in a very basic language such as SQL and its Priority implementation. 

Computer science tells us that recursion can be replaced by iteration; sometimes the conversion is simple and sometimes complicated. The first example always given is that of calculating factorials: the factorial of 2 is 2 * 1 (* is the multiplication operator) and the factorial of 3 is 3 * 2 * 1. This is actually an iterative method; the elegant method of calculating the factorial of a number X is simply X * factorial (X - 1). This is known as a recursive function as the function invokes itself albeit with a different parameter each time.

Here is the factorial function, first written in the recursive style then in the iterative style. I'm going to write them in Pascal as this is the most natural for me. It is important to note that every function - especially a recursive one - has what is called a termination condition. Here in both versions, the termination occurs when n = 0.

Function RecursiveFactorial (n: integer): integer;
begin
 if n = 0 
  then result:= 1
  else result:= n * RecursiveFactorial (n - 1)
end;

Function IterativeFactorial (n: integer): integer;
begin
 result:= 1;
 while n > 0 do
  begin
   result:= result * n;
   dec (n)
  end
end;  

The factorial is probably the worst function to use as an example as the iterative version is just as simple to understand and will have better performance than the recursive version, especially when n becomes large.

Descending a tree is a much more complicated function than calculating a factorial. A simplistic and idealised version of this would be something like

 Procedure Search (node: tnode);
 begin
  if node = nil
   then DoSomething
   else
    begin
     search (node^.left);
     search (node^.right)
    end 
 end;
 
 search (ancestor);

This pseudocode would traverse - or descent - through what is known as a binary tree, where every parent has only two sons. Normally the binary tree is sorted so that the left son (or node) sorts prior to the right son. The above pseudocode would display all the nodes in sorted order.

But that's not what we have with a device tree: each parent can have several sons and there is no specific order between sons of a parent. As this is getting further and further away from Priority, I'll leave this now and continue in another blog entry.

Using the COUNTRIES table as a parameter

One of my clients likes to use the COUNTRIES table as a parameter to reports. Unfortunately, the COUNTRY field in the CUSTOMERS table is not mandatory, so there can be customers without a defined country.

Running a procedure with this parameter set to a specific country works correctly - customers with that country are displayed. Running the procedure with country set to * returns all the customers that have a defined country. What about those customers without a defined country? Leaving the parameter empty apparently causes the error "String or binary data would be truncated (Error 8152)". I don't understand why this error message should appear.

In the past, I might have suggested 'solutions' such as setting the parameter to be 'not equal to Guadeloupe', in the hope that no existing customer is situated in Guadeloupe (where is Guadeloupe? It's "an overseas department and region of France in the Caribbean").

Yesterday my brain must have been working correctly as I saw a much better solution: define the parameter as type NFILE instead of FILE. Add to the code  this section

LINK COUNTRIES TO :$.COU;
ERRMSG 1 WHERE :RETVAL <= 0;
GOTO 1 FROM COUNTRIES WHERE COUNTRY > 0;
UNLINK COUNTRIES;
LABEL 1;

This means that if the parameter is left empty, the COUNTRIES table will be unlinked and the following query will use the original COUNTRIES table, in which the empty value exists. Indeed, after changing the parameter's type, running the procedure with the COUNTRIES parameter left empty worked perfectly.

An undocumented command - GENMSG

Maybe I'm inattentive* but it seems that I never noticed until a few days ago that there are two son forms to the 'step query' form (that in itself is a son form of Procedure Steps that is a son form of the procedure generator): 'Procedure Messages' and 'General error messages'. Maybe the son form has always been there.

Anyway, today my curiosity was piqued and I started to investigate what data this form displays and how it can be used. Both son forms are based on the EXTMSG table whose primary key is based on two fields, EXEC (the name of the form) and NUM (the message number). It turns out that there are only two general messages, both of which say something like 'An error has occurred; please contact the system manager'. I can't remember at the moment how to get the English version of these messages but that's not too important.

How does one display these messages? Here a little ingenuity is called for. I fired up the WINDBI program, chose 'Queries' then 'Find string' and wrote 'GENMSG' (that's the name of the form) before pressing enter. This useful functionality displays wherever the search string is used, be it a form trigger or a procedure. Unfortunately it doesn't work too well with strings composed of more than one word so one often gets the message back that there are too many results for all to be displayed. Of course normally the results that one wants are those that aren't displayed. Fortunately GENMSG doesn't return that many results. Here's an example of the output

ORDERITEMS           BUF69                GENMSG 1 WHERE :RETVAL <= 0;   

Obviously one uses the GENMSG command instead of WRNMSG (or ERRMSG) when one wants to display one of these messages. To be honest, this isn't very useful as I prefer the error message like 'An error has occurred' to show the name of the procedure or trigger where the error occurred so that I can immediately find the source. Otherwise one has to depend on users giving an accurate answer.

This GENMSG functionality would be useful if one could define one's own general messages instead of having to define them in every procedure. For example, I could define a message with the value 599 and the text '<P1> <P2> <P3>' so that I wouldn't have to define this string every time but simply write GENMSG 599 whenever I want some debugging information. This isn't such a good idea as I suspect that GENMSG is equivalent to ERRMSG, not WRNMSG. But I could use it for my version of error 1 that includes the procedure name. 

The value of EXEC for these strings is 208799, and getting that was slightly different from the usual method. The GENMSG form has a pre-form trigger that I copied into WINDBI in order to get the number.

:GENMSGEXEC = 0;
SELECT EXEC INTO :GENMSGEXEC
FROM EXEC
WHERE ENAME = 'GENMSG'
AND TYPE = 'C'
;

I would have expected the EXEC type to be F, not C (curiously enough, there is an EXEC number where TYPE = 'F' but it doesn't lead to the correct error messages). *As a side effect, knowing the EXEC number allows me to discover when this form was added: during the upgrade to version 23.1. It may or may not be in version 22, as we went straight from v21.1 to v23.1. I do know that my first addition with an EXEC higher than 208,799 was added on 18 April this year, after the upgrade. So I wasn't as unobservant as I thought at the beginning of this blog entry.

Fun and games with the :$.PAR parameter / 2

I proudly connect to the client's client's computer, update the IVSTORNO replacement procedure ... and watch it do nothing.

It took a while to realise that I work with the classic, Windows, interface where EXECUTE WINACTIV is fine, whereas the client works with the web interface that requires EXECUTE ACTIVATE. Once that little detail was fixed, the procedure worked flawlessly.

Fun and games with the :$.PAR parameter

A client of a client wanted, when cancelling an invoice, to add a reason for the cancellation. This would be done by creating a new procedure (actually two) that gets the reason from the user, updates the invoice then calls one of the standard procedures for cancelling invoices (IVSTORNO or IVSTORNO2). I falsely assumed that this wouldn't be much of a problem. First I defined a new table that would hold the reasons for cancellation, then I developed a screen for this table; next I added an appropriate field to the INVOICES table and added the appropriate fields to the forms AINVOICES and CINVOICES. So far so good. As the procedures that cancel invoices are called by direct invocation, they have a :$.PAR parameter that is a linked file holding the number of the invoice to be cancelled.

My original attempt at adding the reason code to the cancelling procedure went as follows

LINK INVOICES TO :$.PAR;
GOTO 99 WHERE :RETVAL <= 0;
:IV = 0;
SELECT IV INTO :IV
FROM INVOICES
WHERE IV > 0;
UNLINK INVOICES;
GOTO 99 WHERE :IV = 0;
UPDATE INVOICES
SET NEWFIELD = :$.NF
WHERE IV = :IV;
/* call original procedure */
EXECUTE WINACTIV '-P', 'IVSTORNO', 'INVOICES', :$.PAR;
LABEL 99;

The first part of this procedure worked perfectly: I extracted the id number of the invoice that was about to be cancelled and updated the reason field. But when I went to cancel it, the cancelling procedure failed. When I removed my code out of this procedure, the cancellation worked properly (thus showing that the 'execute winactiv' call was correct).

I was facing a conundrum: how can I update the invoice when the only way of getting its number is via the :$.PAR procedure? Should I access the parameter, apparently :$.PAR gets emptied and so the call to IVSTORNO will fail. I tried workarounds like saving the :$.PAR parameter in a local variable then using this variable for the IVSTORNO call but this too failed.

After much playing around, I did find a method that didn't involve accessing the :$.PAR parameter. My thinking was that instead of trying to locate the invoice that is to be cancelled, find the invoice that has been created to cancel out the original invoice; this new invoice holds a pointer to the original invoice in the field PIV. So my code went as follows

EXECUTE WINACTIV '-P', 'IVSTORNO', 'INVOICES', :$.PAR;
:IV = :PIV = 0;
SELECT MAX (IV) INTO :IV
FROM INVOICES
WHERE TYPE IN ('A', 'C')
AND STORNOFLAG = 'Y';   /* this will be the cancelling invoice */
GOTO 99 WHERE :IV = 0;
SELECT PIV INTO :PIV    /* this is the original invoice */
FROM INVOICES
WHERE IV = :IV;
GOTO 99 WHERE :IV = 0;
UPDATE INVOICES
SET NEWFIELD = :$.NF
WHERE IV = :IV;
LABEL 99;

This actually worked but I wasn't too happy with it as someone else might be cancelling an invoice at the same time. Let's say that it has a 99.99% chance of working as opposed to a 100% chance (i.e. certainty). In other words, a kludge.

An hour or so later, the correct solution popped into my mind

LINK INVOICES TO :$.PAR;
GOTO 99 WHERE :RETVAL <= 0;
:IV = 0;
SELECT IV INTO :IV
FROM INVOICES
WHERE IV > 0;
UNLINK AND REMOVE INVOICES; 
GOTO 99 WHERE :IV = 0;
UPDATE INVOICES
SET NEWFIELD = :$.NF
WHERE IV = :IV;
/* Restore the value in :$.PAR */
LINK INVOICES TO :$.PAR;
GOTO 99 WHERE :RETVAL <= 0;
INSERT INTO INVOICES
SELECT * FROM INVOICES ORIG
WHERE IV = :$.IV;
UNLINK INVOICES.
/* call original procedure */
EXECUTE WINACTIV '-P', 'IVSTORNO', 'INVOICES', :$.PAR;
LABEL 99;

I've bolded the new parts of this procedure. The first part of this code is the same as my original code albeit with one significant difference: :$.PAR is unlinked and removed. This basically frees up the parameter for future use. After the invoice is updated with the cancellation code, INVOICES is once again linked to :$.PAR, but now the linked file is empty; the original record is then inserted into the linked file and the file closed. As far as IVSTORNO is concerned, nothing has changed; :$.PAR contains the key to the invoice that must be cancelled.

Learning something new every day (GROUP BY without an aggregation function)

I'm sure that we've all written procedures that send a report to all customers that ordered today (it doesn't have to be customers; it can be vendors or users in service calls or similar). I've always done this by means of a cursor such as

SELECT CUSTOMERS.CUSTNAME, COUNT (*)
FROM ORDERS, CUSTOMERS
WHERE ORDERS.CUST = CUSTOMERS.CUST
AND ORDERS.CURDATE = SQL.DATE8
GROUP BY 1;
 

I saw that someone had done something similar, but instead of using COUNT, he was using DISTINCT and instead of CUSTOMERS.CUSTNAME, a conditional expression was used, something like

SELECT DISTINCT (ORDERITEMS.ICURRENCY <> ORDERS.CURRENCY ? 
ORDERITEMS.ICURRENCY : ORDERS.CURRENCY),
...

I try to avoid using DISTINCT like the plague; to me it always smacks of lazy programming. One use that I do condone is if I want to know how many separate customers ordered today, as opposed to knowing how many orders each customer made today. I think that the above doesn't work properly because the field that DISTINCT is trying to filter is a conditional field whose value probably isn't known when DISTINCT does its work.

Today I discovered a new twist on the first query shown above: it turns out that the count is unnecessary (assuming of course that I only want a list of individual customers who ordered today). The following gives the desired result and should be faster as there is no need for counting. To my surprise, this syntax works in Priority.

SELECT CUSTOMERS.CUSTNAME
FROM ORDERS, CUSTOMERS
WHERE ORDERS.CUST = CUSTOMERS.CUST
AND ORDERS.CURDATE = SQL.DATE8
GROUP BY 1;

In other words, one can use 'GROUP BY' without an aggregation function (but not the other way around!).

Unprepared forms

There are two rules that one has to know when preparing forms: one is obvious and one is less obvious. The obvious rule is that a form has to be prepared after adding a field or a trigger to it (a form does not have to be prepared if a procedure or report is added for direct activation).

The less obvious rule is that if one adds a table to a form, a table that was not previously defined on that form, then every form that is built on that table has to be prepared.

An example: I was asked to add the status of a purchase order line to the form DOCPORDI ('choose order items', a sub-form of DOCUMENTS_P, 'Goods Receiving Vouchers'). This form is built on the PORDERITEMS table; the order line status description can be found in PORDISTATUSES, and one has to use the PORDERITEMSA table to connect between these two. Thus one would add the following lines to the form

PORDERITEMS.ORDI = PORDERITEMSA.ORDI, identifier 5?
PORDERITEMSA.PORDISTATUS = PORDISTATUSES.PORDISTATUS (identifier 5 on both sides)
PORDISTATUSES.PORDISTATUSDES to be displayed, identifier 5

First of all, the identifier 5? is used to introduce the PORDERITEMSA table - 5 because it is not an original table of this form, and ? because not every line in the PORDERITEMS table has a corresponding line in PORDERITEMSA (e.g. not every line has a status). The identifier 5 has to be used with PORDISTATUSES because this table too is not original.

Obviously, DOCPORDI has to be prepared, but also any form built on the tables PORDERITEMSA and PORDISTATUSES has to be prepared (i.e. the forms PORDISTATUSES, PORDIORDI and ORDPORD), even though no changes have been made to these forms. If there are many forms that have to be prepared then of course it's best to run the 'prepare all unprepared forms' program.

How does one know specifically which forms have to be prepared? This is a question that I have often wanted to ask, and a few weeks ago I found the answer. A simple SQL query would be

SELECT EXEC.ENAME
FROM EXEC, EXECPREPLOCK
WHERE EXEC.EXEC = EXECPREPLOCK.EXEC
AND EXEC.TYPE = 'F'
AND EXECPREPLOCK.UPD <> 'N'
FORMAT;

I have built a very simple report based on this query and added it to the scheduler so that I would receive a report every 15 minutes if there is an unprepared form anywhere in the system. I have found that it's better to reprepare any forms that appear in this report as opposed to simply preparing  them. I don't know what the difference between preparing and repreparing is, except that the latter is 'stronger' and takes more time. This always reminds me of a scene in the film 'A few good men', when Demi Moore objects to something in the trial, then strenuously objects. This results in a sarcastic remark from Kevin Pollak (I forget the exact wording); if preparing a form doesn't work then reprepare it.

An exercise in how NOT to build private forms

I was recently asked to look at a form that someone (with whom contact has been lost) had defined that displayed purchase order lines belonging to a single vendor; the users wanted to update a private field in the form but were not succeeding in doing so.

I had been asked maybe a month earlier to program for the same customer the same kind of form (purchase order lines of a given vendor) so I wasn't prepared for the mess that I saw. I can understand why the person who programmed this form has disappeared, for it is an excellent example of how not to build a private form and shows many misunderstandings.

Probably the biggest error was not basing the form on PORDERITEMS but instead on a private table. Had the form been based on the standard table then adding a field would have been simple: add the field to the table and then to the screen. But no: this form was based on a private table that of course had a primary key based on PORDERITEMS.ORDI. But not only that: the current user was also part of the primary key! In other words, I could open the form and you could open the form and the possibility exists that we might be seeing different data!

The private field that the customer wanted to be able to update was held in this private table (the person who added the field [not the original programmer] presumably saw on what the table the form was based and so added the field to this table). I didn't see at first that the form was based on this table; I assumed that it was based on PORDERITEMS and so I added a form post-update trigger that would update the private table if the private field were changed. This didn't work. Changes in the field were maintained as long as the form was displayed on the screen, but would vanish when the form was reopened. Eventually I found the reason for this: the form has a PRE-FORM trigger that first empties the private table for the current user then enters the appropriate values for PORDERITEMS.ORDI. 

Excuse me????? What is this rubbish? Such a convoluted structure for something that should be far simpler. In the end, in order to satisfy the customer, I added the required field to PORDERITEMS and updated this (by placing the table/column name combination in the field continuation) instead of using the same field in the private table.

A further request for modifying the existing form was to add the connected customer order if one exists. I saw that already there was some connection to ORDERITEMS so I didn't need to add this; I added the appropriate fields and ran the form. The form would not open. I removed the fields that I had added and reopened the form - it worked properly. I then took a much closer look at the form definitions and saw that ORDERITEMS had been defined with the identifier (alias) 5! (the exclamation mark was not part of the identifier). This is correct if one is adding fields to a table in a standard form, but is totally unnecessary if one is doing this in a private form. This is a clear example of someone not understanding the SDK. Naturally the field that I had added did not have this identifier; adding it allowed the form to be displayed.

Another problem with this form became clear when the customer explained that users can change the status of the connected purchase order from within this form (that's why they thought that the private field that they added was capable of being updated from this form). Maybe the code for this was correct - I didn't waste much time in looking at it - but it was clear that the most basic check had been ignored. First check that the field holding the status had been modified and only then update the order status! But no, this trigger blindly modified the status over and over again (if I remember correctly, this abomination involved touching the table several times, a complete waste of time). Any kind of trigger like this should start as follows

GOTO 99 WHERE :$1.<FIELD> <> :$.<FIELD> ;
/* update field */
LABEL 99;

If at the beginning of my examination I wanted to put a gun to the head of the person that programmed this atrocity, by the end I wanted to put a machine gun there. This sort of programming gives independent programmers a bad reputation.

 

So what are the lessons to be learnt?

1. If one wants to display data from an existing table, it's best to base the private form on that table and define the form either as Q (read only) or N (no deletions). One can add private forms either to the standard table or to a private continuation form; doing the former 'contaminates' the standard table (although this is condoned) whereas the latter requires programming a trigger to update the private table.
2. There is no need to use the 5 identifier when one is working on a private form; this is required only when adding new tables to a standard form (or report).
3. Check whether a field has been modified first before writing code to update its value in the database.

Version 23.1 - Problem with private procedures to print purchase orders

We have upgraded to version 23.1 and have run into a problem with private procedures to print purchase orders. Such procedures do not print the totals part of an order. Preparing such a procedure brings up an almost impossible to understand error.

The actual problem is that new parameters have been added to report WWWPORD_3 that prints the totals. All one has to do to correct this annoying error is to add the new parameters as shown below. I've marked the new parameters in yellow.

There is a similar change to report WWWIV_3 that prints totals of invoices; the same new three parameters appear there and so have to be added to all private procedures for printing invoices.

Naming private variables in form triggers

Another problem when trying to upgrade to version 23: an error occurred when trying to add a line to a customer order. As this is an extremely common operation, it was of course very important to solve this problem. The error message said something about variable REF in a certain private trigger; had this variable appeared in the trigger then my life would have been easier.

I was reduced to removing all the private triggers from both ORDERS and ORDERITEMS forms: at this stage rebuilding the forms naturally was without problem. I added the triggers back one by one then rebuilt the forms each time until I received the confusing error. I had to do this several times (and of course, remove all the triggers) until I finally found the source of the problem: a trigger included a buffer, and in this buffer I had defined a variable REF. Renaming the variable to TEST_REF solved the problem. Obviously some standard trigger in ORDERITEMS also referenced the REF variable but as a different type.

As always, finding the source of a problem takes a very long time, whereas the fix is normally done in a minute or two.

What can one learn from this? In form triggers, always append the company's four letter prefix to all the variables defined in the trigger, even though these triggers are private. In modern programming terminology, a form and all its triggers constitute one namespace, and so variables must be unique. Employing the company's prefix will ensure that a future predefined variable will not clash with a user-defined variable.

Field names/titles in tables - revisited

My company is trying to update our Priority version from 21.1 to 23.1 and we've had several problems that prevented the update. One problem that can be fixed in advance is that if one adds fields to tables like GENERALLOAD and STACK*, then the title of the field should be the same as the name of the field (or at least, should have the four letter prefix). In other words, if I add a field TEST_INT11 to STACK4,  then the title of this field should also be TEST_INT11.

A previous blog discussed the same problem, but the real cause of the problem wasn't identified there.

Saving a report in HTML format

Until recently, I've never needed to save a report (i.e. create a disk file) in HTML format - or maybe I've always managed to dodge my way around this requirement. Recently, though, I was asked to save a report that I distribute via email in PDF format to save it also as a disk file in HTML. As the report in question is actually an 'HTML document', this is documented as using two extra parameters to the WINHTML program ('-o', presumably meaning 'output' and the output file).

LINK ORDERS TO :$.PAR;
INSERT INTO ORDERS
SELECT * FROM ORDERS ORIG
WHERE ORDNAME = 'KL210001';
:FNAME = '//server/Sharing/AutoReports/$.HTM';
EXECUTE WINHTML '-d', 'TEST_HTMLFURNDAILY', 'ORDERS', :$.PAR,
'-o', :FNAME
;
UNLINK AND REMOVE ORDERS;
LINK ORDERS TO :$.PAR;
INSERT INTO ORDERS
SELECT * FROM ORDERS ORIG
WHERE ORDNAME = 'KL210001';
:FNAME = '//server/Sharing/AutoReports/$.PDF';
EXECUTE WINHTML '-d', 'TEST_HTMLFURNDAILY', 'ORDERS', :$.PAR,
'-pdf', :FNAME;
SELECT ENTMESSAGE ('$', 'P', 20) INTO :SUBJECT FROM DUMMY;
:GROUP = 'FURNDAILY';
LINK GENERALLOAD TO :$.GEN;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT, TEXT6)
VALUES (1, '1', :SUBJECT, :GROUP);
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT)
VALUES (2, '3', :FNAME);
EXECUTE INTERFACE 'TEST_SENDREPORTEMAIL', SQL.TMPFILE, '-L', :$.GEN;
SELECT ATOI (KEY1) INTO :MB
FROM GENERALLOAD
WHERE RECORDTYPE = '1'
AND LOADED = 'Y';
LINK MAILBOX TO :$.MBX;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO MAILBOX
SELECT * FROM MAILBOX ORIG
WHERE MAILBOX = :MB;
EXECUTE SENDMAIL :$.MBX, :XMSG;  /* send the letter! */
UNLINK MAILBOX;
UNLINK GENERALLOAD;
UNLINK ORDERS;

Today I was asked to do something similar, but in this case, the report is created by a normal multi-step procedure (i.e. not an HTML document), and so there is no use of WINHTML. I looked at the documentation for saving a normal report as HTML - this uses WINACTIV.

The :F variable implies that the report will be saved to a file with this name, but this variable is not used! I looked at the SDK version on the Internet and exactly the same code appears. THIS IS A MISTAKE! The correct code is as follows:

:FNAME = '//server/Sharing/AutoReports/$.HTM';
EXECUTE WINACTIV '-R', 'TEST_HTMLFURNDAILY', 'ORDERS', :$.PAR,
'-o', :FNAME;  

In other words, in the same way that WINHTML receives an extra two parameters when saving a report to disk, WINACTIV does also, only that this is not documented.