Priority: adding a field whose description can change

One of the companies for whom I consult asked me to program a screen which would be a 'son form' of the device/serial numbers screen; this new screen would hold values which are defined in a separate screen. The company calls this table 'waivers', which is defined as 'the act of intentionally relinquishing or abandoning a known right, claim, or privilege'. They're using 'waivers' as a way of documenting changes which are knowingly made, such as using a different kind of power supply than that listed in the bill of materials.

This type of requirement is fairly standard, so first I created the 'waivers' table along with the screen which allows one to define the waivers. This table initially held an automatic, incremental number, a unique code and a description. A log of changes was requested, so I added another table to the database which would hold the changes log, then programmed the required screen.

The next step was to create a 'junction table' which links between the 'father' screen, the serial numbers, and the 'waivers' table. After defining this, I was then told that there would be at least one type of waiver whose value could be changed when entered into the junction table. Although I am familiar with screens which have such a variable (for example, the OrderItems table allows certain parts to have differing values; this requires that the part have a 'allow changes to description' flag set to true), I have never programmed one myself.

My first step was to add a 'non-standard' field to the junction table, and a 'allow changes' flag to the waivers table. I reasoned that two 'general' waivers could be added to the junction table for the same device, and so I would need to add the non-standard field to the junction table's primary key. I copied and adapted the code which is used in the OrderItems screen for the variable part variable, but it took me some time to get this code working partially correctly. Even so, I was unable to store a record into the junction table; I kept on getting the message 'missing nonstandard value' error.

Eventually the penny dropped: I was trying to store the value 0 in the non-standard field; Priority does not allow fields which are part of a table's primary key to be 0. So I had to remove the non-standard field from the primary key, and replaced it with a synthetic 'kline' field, whose value would be calculated at run time. Whilst writing the code to establish the value of kline, I realised that the 'waiver' code did not need to be part of the primary key either; this should consist of only a pointer to the device and the synthetic key, kline.

I then fell foul of a mistake which I have made a few times. The original code to calculate 'kline' was

SELECT MAX (KLINE) + 1 INTO :$.KLINE 
FROM TEST_SERNWAIVERS
WHERE SERN = :$$.SERN;

This snippet gets the maximum value of 'kline' which has been assigned to this device and stores the incremented value in the screen variable 'kline', which will be stored in the record. This is almost correct: what happens when this is the first time a waiver is being stored for the given device? The above code will fail and the screen variable 'kline' will still be at its default value, 0 - and we learnt a few paragraphs ago that Priority does not allow fields which are part of a table's primary key to be 0. The trick is to use an auxiliary variable before the 'select' statement, and increment this variable after the select.

:MAX = 0;
SELECT MAX (KLINE) INTO :MAX
FROM TEST_SERNWAIVERS
WHERE SERN = :$$.SERN;
:$.KLINE = :MAX + 1;

This is a lesson which I have learnt several times and sometimes forget.

But what about the code needed to store a changing description? First off, the screen should show the value stored in a field which I called 'wdes', whose value is

(TEST_SERNWAIVERS.NONSTANDARD <> 0 ? 
          NONSTANDARD.TEXT : TEST_WAIVERS.WAIVERDES)

which means 'if the value of non-standard for this line is greater than zero, then use the value stored in the 'non-standard' table else use the standard description. A piece which I missed originally was that WAIVERDES has to be a hidden field in the screen and it has to have a POST-FIELD trigger as follows:

SELECT :$.@ INTO :$.WDES FROM DUMMY;

which means 'save the value of this field (i.e. the waiver's description) into the WDES field. This causes the WDES field to display the original description before it is changed. The WDES field needs a CHECK-FIELD trigger to check that the current waiver allows its description in the junction table to be changed.

I'm sure that most of the above is somewhat incomprehensible; I shall try and streamline it in the future so that I can use the technique again, and teach it in my 'classes'. This blog shows how I got things wrong at first and how I corrected them.