Logging changes in bypass forms

I am often asked to create 'bypass' forms: this is my term for a form that is based on a standard table but only exposes a few fields. The most common example is a bypass form for customer orders; assume that the order is at a status that does not allow changes, but the user would like to change a few non-essential or private fields. The bypass form exposes those fields but is not bound by the order's status.

There is a relatively simple means of logging changes in this form so that they can be seen in the 'real' orders form.

:DETAILS = '';
:SONEXEC = 0;
SELECT EXEC INTO :EXEC
FROM EXEC
WHERE ENAME = 'ORDERS'
AND TYPE = 'F';
:CHARKEY1 = ITOA (:$.ORD);
:CHARKEY3 = :CHARKEY2 = :CHARKEY4 = :CHARKEY5 = ''
;
GOTO 71 WHERE :$1.DETAILS = :$.DETAILS;
:OLDVALUE = :$1.DETAILS;
:NEWVALUE = :$.DETAILS;
:COLNAME = 'DETAILS';
#include func/UpdateChangesLog
LABEL 71;

The first part of this code (upto the line containing a single semicolon) sets up necessary data so that the changes will be written to the standard orders form changes log. If one wants to use these lines for a different form, then ENAME will have to be the name of the target form. ORD is of course the number of the order being edited.

The second part checks whether a change has been made in the field 'details'; if so, the original value and the new value are saved along with the column name. The column name will appear in the change log the same way in which it is defined in the form.

There is a very large 'gotcha' in that last sentence. One of the fields displayed in the bypass screen is a private field added to the ORDERS form that is based on another table (i.e. like displaying CUSTOMERS.CUSTNAME). The column name for this field in the ORDERS form will be something like TEST_CUSTNAME. But in the bypass form, there's no need to use four letter prefixes as the form is private, so I had defined this column as CUSTNAME.

Changes in this field were not being logged - or rather, they were being added to the CHANGES_LOG table but were not appearing in the change log form. After a bit of head-scratching, I realised that the column name (:COLNAME) should be the name of the column as it appears on the 'real' form and not how it appears on the bypass form. Once this seemingly minor change had been made in the trigger code, changes to the CUSTNAME field now appeared in the change log.

Incidentally, if one wants to log changes in a bypass form for orderitems, the prologue code becomes

:DETAILS = '';
SELECT EXEC INTO :EXEC
FROM EXEC
WHERE ENAME = 'ORDERS'
AND TYPE = 'F';
SELECT EXEC INTO :SONEXEC
FROM EXEC
WHERE ENAME = 'ORDERITEMS'
AND TYPE = 'F';
:CHARKEY1 = ITOA (:$.ORD);
:CHARKEY3 = ITOA (:$.ORDI);  /* This is the order line number */
:CHARKEY2 = :CHARKEY4 = :CHARKEY5 = ''
;

For want of another screw ....

Following on from my previous blog, I had to deal with another part whose internal unit is 'unit' and external unit 'box' with a conversion factor of 125. Unfortunately, I am told that the part is ordered in units, not boxes.

This case is somewhat similar to the one that I described previously, but here I had to deal with the fact that the part had to be ordered in units that are smaller than the external unit defined for the part. First of all, I defined a new unit, X25, with the name 'factor 125:1'. I then defined that 125 boxes are equivalent to one X25. I then changed the external unit for the part to X25.

A side effect of changing the external unit is that the 'parts per supplier' son form of the supplier is automatically updated to the new unit, i.e. X25. I had to change this back to 'box'.

Once this was done, I tried entering an order to the supplier for 500 units: in the purchase order appears 500 boxes and 500 units. Success.

For want of a screw ....

It is well known that Priority allows one to manage a purchase part in two different units, one for internal use and one for ordering from a supplier. There is a factor that converts between the two units, so if for example one purchases a 6 metre metal rod where the supplier views this as one rod whereas the factory views this as 6 metres, the conversion factor would be 6 (i.e. 6 metres = 1 rod). This is all fine and good until one comes to items like screws and nails, where there can be a few thousand items in a box; the maximum value that the conversion factor can hold is 3,500 (for some reason unknown to me). Until yesterday, I thought that there was no way around this.

A post on our Telegram discussion group showed a way around this: let's say that there are 5000 screws in a box. One defines that the internal unit will be 'unit' (unsurprisingly) but that the purchase unit will be an intermediary unit, such as 'scr' (the name itself is unimportant) with a conversion factor of 1000. Then one defines that five 'scr' are equivalent to one 'box'. Finally, in the 'parts per supplier' son form of the supplier, one defines the part to be purchased in boxes. The use of the intermediate 'scr' unit will cause the purchase of one box to be recorded as entering 5000 screws into inventory! (Thank you, MA).

This post was extremely welcome - and timed perfectly - as today I came across a case where a screw had been defined with the purchase unit 'box' and the internal unit also 'box' with a conversion factor of 1 (of course). Originally both units had been 'unit' and someone changed them to 'box', although this made no difference whatsoever. Well done! (heavy sarcasm). It is not possible to change the conversion factor once there are inventory records for the part. As a result, any part that contained this screw would have an inflated  cost. What I did not know at first was how many screws are in a box, and so by how much the price would be inflated.

As any correction would be constrained by the fact that the conversion factor could not be changed from 1, it appeared that I would have to use more than one intermediary unit if there were more than 3,500 screws in a box. When I tried this, Priority complained that there was no conversion factor between the first intermediary unit and the unit in the supplier's list. So two intermediary units are out.

Fortunately, there are only 200 screws of this type in a box, so I defined an intermediary unit 'sc2', where one unit is equivalent to one 'sc2' (thus maintaining the conversion factor) and that 200 'sc2's are equivalent to one box. This should sort everything out. 

I also had to deal with the cost of the screw: I entered 200 screws at the price of a box into inventory, then removed them at the same price. This should cause the single screw to have its cost reduced by a factor of 200. I'll know this only tomorrow....