LIKE cannot accept a variable

I wanted to write a form trigger which is dependent on the value of a certain field (the number of a previous order). Unfortunately, this field is not bound but rather a simple text field, which means that instead of it holding values like KL191234 (this would be the previous order number), it holds values like 1234, 191234 or even 1234/5. This is problematic, but one evening the answer came to me: use the standard SQL keyword LIKE, where the order number is preceded by *. Thus KL191234 will be matched by *1234 or *191234 (but not *1234/5). On this basis I wrote the following code, where TEST_PREVORDER holds the value of the previous order

:PREVORD = 0;
SELECT ORD INTO :PREVORD
FROM ORDERS
WHERE ORDNAME LIKE STRCAT ('*', :$.TEST_PREVORDER)
AND CUST = :$.CUST;
GOTO 99 WHERE :RETVAL <= 0;

The syntax checker told me that there was an error with STRCAT. In order to combat this, I placed the STRCAT function before the query, assigning its value to a variable, then used this variable in the query. This did not help matters.

Eventually I realised what the problem was - LIKE (at least, in its Priority definition) cannot accept a variable as its parameter; it has to be a 'naked' string like '*T' or 'T*'. As usual, this doesn't seem to be defined anywhere, but I discovered that I had commented on this a few years ago (not on this blog).

So add this to the growing list of improvements for Priority SQL. The syntax checker

1. should check that every cursor which is opened is also closed.
2. should check that every LINK has a matched UNLINK (this is probably very difficult)
3. should allow LIKE to take a variable as its parameter

The final code became

/* Stupid code required because LIKE cannot accept a variable as a
parameter */
:TPO = :$.TEST_PREVORDER;
:TLEN = STRLEN (:TPO);
GOTO 4 WHERE :TLEN = 4;
GOTO 6 WHERE :TLEN = 6;
GOTO 8 WHERE :TLEN = 8;
GOTO 99;
LABEL 4;
:TPO = STRCAT (ITOA (YEAR (SQL.DATE8) MOD 100), :TPO);
LABEL 6;
:TPO = STRCAT ('KL', :TPO);
LABEL 8;
:PREVORD = 0;
SELECT ORD INTO :PREVORD
FROM ORDERS
WHERE ORDNAME = :TPO
AND CUST = :$.CUST;
GOTO 99 WHERE :RETVAL <= 0;

Note that the code 'falls through' the labels: if TPO is '1234', then the code will jump to label 4, where TPO becomes '191234'. Then the code for length 6 executes: TPO becomes KL191234. Then the code for length 8 executes - which is the real code. In other cases, there would be another GOTO on the line before 'LABEL 6' but here it is not required.

Removing a private field from a form

Disclaimer: all of the below is written to the best of my knowledge. As this topic is barely documented, I have to base my comments on my experience which may well be limited. In other words, there may be a simple way of getting around the pitfall described below of which I am simply unaware.

Extra disclaimer: the pitfall described below seems to a problem specific to one company. I cannot reproduce the error on my system.

The scenario: a client wants to add a new personalised field to an existing form. My normal way of doing this is to add the required field to the base table of the form and then add the field to the form which will display it; a more complicated method involves adding the field to a continuation table (good examples of this are the service calls and projects forms). Built-in triggers on every form are responsible for loading and saving all data which is displayed on the form which is derived from the base table, whereas the developer is responsible for loading and saving data which comes from a continuation table. In other words, if I add a field to a base table and then to a form, I don't have to worry about it being loaded or saved. 

The problem: after a few days' testing, the client decides that she doesn't need the added field. OK: the automatic, unthinking, simple solution is simply to remove the field from the form by deleting the definition. WRONG!!!! Form preparation, after adding the field, will modify the built-in triggers to include the new field (if it was defined as belonging to the base table) and so deleting the field from the form will cause these triggers to scream that they are missing a field when a user accesses this form. What is worse is that 'form preparation' does not detect this problem. The developer has no way of accessing the built-in triggers so it is not possible to remove from them the references to the new field. 

The correct way to 'remove' the added field would seem to be to hide it, not delete it.

In my opinion, the bug is that after deleting the field, form preparation does not update the built-in triggers and instruct them to remove the added field.   

Ironically, displaying the field via a continuation table does not cause this problem as the field is loaded and saved 'manually' and so the developer can simply remove the references to the added field.

[Edit from 09/22: the same problem happened at another company running Priority 21. Fortunately we were working on a test server so the problem will not be propagated to their real server.]

Welcome

Welcome to 'Programming Pitfalls in Priority'! 'Programming' is to be read as an adjective, not a verb, so I'm writing about the pitfalls which await programmers in the Priority ERP system. I am not affiliated with this company; I work for a normal manufacturing company as an ERP developer and trainer. I have been working with Priority for 13 years and before that with a simpler ERP system, so I have plenty of experience in my field. 

In this blog I will be writing about pitfalls which I personally have come across in the Priority system during the course of my work. Generally these topics are not documented or hinted at in the Software Development Kit (SDK) and so they are quite advanced. As my descriptions and solutions are based on my own work, there may be better ways of overcoming the pitfalls; I am not privy to internal documentation and so am forced to depend on my own ingenuity, sometimes with the help of colleagues.