Adding text to a purchase order item

One of my clients needs to add text to a purchase order item (i.e. to the form PORDERITEMSTEXT) when a certain condition is met. This can be an exceedingly frustrating exercise as I have found out (repeatedly, it turns out).

My original trigger code was in the private post-insert and post-update triggers. At first I tried to enter the text by means of an interface (this is the 'proper' way of doing so) but it seemed that I didn't work. Normally, one passes the variable SQL.TMPFILE as the second parameter to the EXECUTE INTERFACE call (i.e. after the interface name), but one can pass the name of a file, such as R:/1.txt, or whatever. In this case, the result of the interface (such as 'three lines have been added successfully') will appear in the file. Thus one can check that the interface is working properly. Despite this, no text was appearing in the text form.

Looking at other triggers that also enter text into this form, I see that they use a direct insert into the table PORDERITEMSTEXT, as opposed to using an interface. The problem with this approach is that one has to include the necessary HTML tags, as well as values for the fields TEXTLINE and TEXORD, so most (if not all) of these triggers copy text from another form as shown below. Of course, if they want to copy text from another form then using an interface would be problematic as it would copy formatting code as well as the desired text.

INSERT INTO PORDERITEMSTEXT (ORDI, TEXT, TEXTLINE, TEXTORD)
SELECT :$.ORDI, TEXT, TEXTLINE, TEXTORD
FROM PARTTEXT
WHERE PART = :$.PART;

So, in order to use this method, I had to add text to a default supplier (www, whose number is -20). But I also wanted to add something to the final line of text, meaning that I couldn't use the above example as is. In WINDBI, I selected the fields, and saw that for one line of text, four lines existed in the table. OK: I would copy three lines (adding to the SQL code the condition WHERE TEXTLINE < 4) and would add directly the fourth line, including the necessary HTML terminating code.

Still no text appeared.

Tearing at what is left of my hair, I looked at all the triggers of the PORDERITEMS form and saw to my horror that standard buffer BUF1 deletes all text from PORDERITEMSTEXT! No wonder my code appeared to work but apparently added nothing. Once I saw this, I realised that my private post-insert trigger should execute after the standard post-insert trigger, meaning that its name should be something like ZTST_POST-INSERT. There already was a trigger by this name ... that was calling a private buffer of mine that also inserted text into PORDERITEMSTEXT! In other words, I had already suffered from this problem in the past but had forgotten the solution.

Having another trigger that could potentially add text means that first I had to establish how many lines of text had already been added (SELECT MAX (TEXTLINE) INTO :TL FROM PORDERITEMSTEXT WHERE ORDI = :$.ORDI), then add this offset to the TEXTLINE and TEXTORD fields.

Finally the trigger worked! I am documenting this here in the hope of saving someone (not only myself) a great deal of time and frustration in the future.

The FORM_INTERFACE variable

The SDK documents this as: :FORM_INTERFACE (INT type) — when assigned a value of 1, indicates that form records are filled by a form load interface rather than the user.

What does this mean in practice? First of all, the variable has to be placed in a form trigger for it to take action. When data is entered into a form via an interface, the given trigger will not execute if the variable has the value 1. I had a case (that I don't remember now) where an interface would enter data, but some value was missing (deliberately) and this would cause a trigger in the target form to execute, saying that a field required a value. I had to invent some arcane mechanism in order to prevent this error, but it would have been much simpler had I known about (or remembered) this variable. 

Circumventing a standard check-field trigger

One is not supposed to change standard check-field triggers defined in forms. Unfortunately there are cases where such a trigger does not provide the check that a client might want. In these cases, one has to be creative, and this is what I want to document today.

Specifically I am writing about the form ECOUSER that displays the names of users that have to sign off on an engineering change order. The standard check-field trigger checks that the user who signs off (i.e. sets the UFLAG field to 'Y') is the user whose name appears in the line. Fair enough. But my client wanted that any member of a given group could sign.

Before I start with ECOUSER, there is a challenge in that previous sentence: a flag has to be added to the UGROUPS table to mark which group (and thus which users) can sign. But UGROUPS is one of those pesky tables that does not allow developers to add fields; such tables are generally multi-company tables, like USERS and USERSB. So first I had to add a new table TEST_UGROUP that has two fields, UGROUP and ECOFLAG, then add this table to the UGROUPS form so that the flag could be saved. Once I had progressed past that stage, it was time to look at ECOUSER.

It transpires fortunately that this form has a field ALTUSER (and ALTUSERLOGIN) that allows a substitute user to be added to a line in this form; the substitute/alternative user can sign in place of the default user. Both USER and ALTUSER are checked in the standard check-field trigger. In order to achieve what my client wanted, I had to subvert this trigger.

As non-built-in triggers are executed in alphabetical order, I needed a check-field trigger that would execute prior to the standard trigger. This means that I had to define a trigger with a name like AAAA_CHECK-FIELD (A comes before C). Within the trigger, I had to check that the current user is a member of the given group, and if so, place her user number in ALTUSER. Code follows:

:OK = 0;
SELECT 1 INTO :OK
FROM USERGROUP, TEST_UGROUPS
WHERE USERGROUP.UGROUP = TEST_UGROUPS.UGROUP
AND TEST_UGROUPS.ECO = 'Y'
AND USERGROUP.USER = SQL.USER;
GOTO 1 WHERE :OK = 0;
:$.ALTUSER = SQL.USER;
LABEL 1;

I could probably tighten this up by somehow inverting the check, but in this case, it's better to have something simple that works as opposed to something clever that might fail in unpredictable conditions.

Misleading error message

I developed a private form several years ago that displays values of private constants. When I open this form, the error message 'Value exceeds permitted quantity' appears; I press on this three times and it disappears whilst the data is being displayed. As this sort-of works and I'm the only person who accesses this form, I hadn't given much thought as to what could be the problem.

The same thing happened at one of my external clients: they want to load an external file then display the contents in a 'load form' for visual checking before converting the data into invoices. The load form displays this 'value exceeds permitted quantity' error message and clicking on it a few times doesn't help. I originally thought that this behaviour was due to some system constant being too low - the example file has 1220 lines. Changing various system constants didn't make any difference.

Last night I decided to solve this problem for once and forever. My first act was to delete the form entirely - maybe something had contaminated it - then redefine it. The data displayed in the form all come from the same table, with no joins, so 'programming' the form was a simple matter. Retrieving all the data caused the same error message; I had to close the form via the task manager. I then retrieved the first record - it displayed correctly. The first ten records display correctly. The first hundred records display correctly. The first seven hundred records display correctly. 

It was only when I retrieved record number 1000 that I found what the problem was and finally understood the misleading error message. The familiar error message appeared, I clicked on it; the error window disappeared and the record appeared. The line number field displayed 1,00 instead of 1000 as I had been expecting - this is the smoking gun. The record number is stored in a field called KLINE that I had defined as an integer of length 4, as I wasn't expecting more than 9999 lines in the file. But the form is displaying this number with a comma separating the thousands, so 1000 was displayed as 1,000 - and this is five characters! Hence the value (i.e. required column width) exceeds the permitted quantity (of characters in the column). All I had to do was lengthen the field from 4 to 6 characters and rebuild the form. Now all the data displays correctly. I suspect that the error message appeared once for every problematic line, meaning that had I pressed on the error message 220 times, the data would have appeared.

The English error message is a faithful translation of the Hebrew message that I was seeing. I don't know which came first, but in both languages the message is extremely misleading and doesn't say what the real problem is. A far better message would be '<column name> is trying to display X characters but is defined of length Y only'.

Removing links from report fields

The genesis of this blog is when a client requested that a certain field in a certain report would be coloured. We were talking about the status of a purchase order line, and I had previously added to the table of possible line statuses a field that defines in which colour will appear the status. I explained to the client that there was no problem in defining the background colour for the given field, but defining the font colour was problematic as the field is linked to a table. The client was not prepared to compromise and requested that the font colour be set according to the value of the colour in the table.

In other words, I should remove the link from the field. My first solution was to move the field from its position in the report generator as a regular field to being an expression (i.e. moving the field definition to the first son form of the line). This worked, but I don't like it when reports have standard fields as expressions: the field title gets lost and one can't see what the field definition is without looking closely. Then another solution occurred to me, exactly at the same time as the client suggested something that he must have come upon by accident.

My solution was to set the 'target form' value to NULL; this field is in the same son form as where expressions are defined. I mentioned it in a previous blog albeit in a different context. And indeed, defining the target form of the line status field as NULL removes the link and allows the font colour to be changed.

The client said that entering NULL in the field 'form defining a choose list' in the HTML definitions son form for a report field would also work. I was not previously aware of this field. Anyway, I entered NULL in this field ... and saw that in the field that I do know, the target form for the field, NULL also appeared! 

I've just spent about ten minutes going over several reports and making the target form NULL for fields such as order type, part unit and order status. One generally has no reason for clicking on these values.

Beware when using SQL.LINE

In my experience, there are two general cases of insertion into a table during a procedure: the table is normally one of the STACK tables or GENERALLOAD, but in certain cases can be another table, normally a private one that has been defined specially for the procedure. The identity of the table is not important in the cases that I am going to describe. The two types might be called 'explicit' and 'implicit', with reference to the key field of the table into which data will be inserted.

I am going to describe in general terms the copying of a customer order. This would use the GENERALLOAD table; the fields of the order header would go into a tuple whose value for RECORDTYPE would be '1' and whose LINE would be 1. The technique for copying the order lines depends on whether only the lines are being copied, or whether any sub-forms of the lines are being copied as well. In the first case (no sub-forms), one can simply write

  INSERT INTO GENERALLOAD (LINE, RECORDTYE, ...
  SELECT 1 + ORDERITEMS.LINE, '2', ...
  

If one were feeling adventurous, or there was no natural key for the sub-form, one could replace ORDERITEMS.LINE with SQL.LINE. What is important is that this number is incremented by one every time, as line 1 in GENERALLOAD holds the header line. This is what I would describe as 'implicit' inserting.

Should there be sub-forms, the data has to be entered by means of a cursor, where first line data is added then sub-form data. As there will no longer be any correspondence between GENERALLOAD.LINE and ORDERITEMS.LINE, one has to maintain a local variable (normally :LINE) whose value is incremented prior to every insert. This is 'explicit' inserting. At the same time, data for the sub-form could be inserted either implicitly or explicitly.

  :LINE = 1;
  INSERT INTO GENERALLOAD (LINE, RECORDTYE, ...
  SELECT :LINE, '1', {header data};
  DECLARE C1 CURSOR FOR
  SELECT ORDERITEMS.ORDI, ....
  OPEN C1;
  GOTO 200 WHERE :RETVAL <= 0;
  LABEL 100;
  FETCH C1 INTO :ORDI, ....
  GOTO 200 WHERE :RETVAL <= 0;
  :LINE = :LINE + 1;
  INSERT INTO GENERALLOAD (LINE, RECORDTYPE, ...
  VALUES (:LINE, '2', {line data} ...);
  /* sub-form */
  INSERT INTO GENERALLOAD (LINE, RECORDTYPE, ...
  SELECT :LINE + SQL.LINE, '3', {sub-form data} ...;
  LOOP 100;
  LABEL 200;
  CLOSE C1;
  EXECUTE INTERFACE ....
 

There is a deliberate mistake in the above code, but first let's think it through. The value of GENERALLOAD.LINE for header data will obviously be 1, and the value of this field for the first line's data will be 2 (as the variable :LINE is explicitly incremented). For the first line of the sub-form data, GENERALLOAD.LINE will be :LINE + SQL.LINE, i.e. 2 + 1, or 3. The second line will have GENERALLOAD.LINE = 4. This can be represented in the following table

 

GENERALLOAD.LINE	DATA
1	order header
2	first line of order
3	first line of sub-form for order line 1
4	second line of sub-form for order line 1

So far so good. For the second order line, :LINE will explicitly be incremented, so this line will have GENERALLOAD.LINE = 3 ... except for the fact that there is already a tuple with this key value in GENERALLOAD, and so the second line will not be inserted. What is missing is the following line:

SELECT MAX (LINE) INTO :LINE FROM GENERALLOAD;

This line should appear just before LOOP 100. As a result of this line, :LINE will have the value 4 after the insertion of the second sub-form line, and as this value is incremented prior to inserting the second order line, this line will have GENERALLOAD.LINE = 5.

One can generalise this: whenever one uses the construct 'INSERT INTO ... SELECT SQL.LINE', one must remember to increment SQL.LINE with a variable (such as :LINE or :MAX), then after the INSERT statement should come the statement that selects the maximum key number inserted so far into the above variable.

Such a simple heuristic, so easy to forget: if this variable (:LINE) is not used again, it seems that there is no point in extracting its value. For example, I have written many procedures that send a report by email: the procedure starts by defining some variables, then there is an insert statement into STACK4 (or similar) based on those variables, using SQL.LINE as the key field, then a report is executed passing STACK4 as its data. There is no loop and so there is no need to select the maximum value of KEY from STACK4. 

The problem rears its head when such code - which was written for a non-looping procedure - gets copied into a procedure that does have a loop. For example, I wrote a somewhat complicated procedure that collects BOM data from all the lines in a given order; this was then extended to work on several orders. Each order is selected via a cursor; local variables have to be reinitialised for each new order, but as the data are being inserted into the same STACK table, the key value should continue to increment. In other words, the maximum value of KEY should be extracted before the LOOP command that causes a new order to be selected.

This blog is of course being written because I fell foul of this heuristic. In this case, the procedure had to call an external program for calculating budget use; this program appears to work on one year's data at a time. As a result, I was forced to use the rather arcane structure of having a loop at the level of SQLI stages as shown below.

Stage	remarks
10	Set up variables
20	increment variables
30	external procedure
40	insert the data returned from the external procedure into a special table. At the end check the terminating condition and set :$.GO appropriately
50	GOTO 20 if the end has not been reached, 60 if it has been reached
60	cleanup

One can guess what the problem was: the code in stage 40 had originally been written for one year's data and used the 'INSERT INTO ... SELECT SQL.LINE' construct without having 'SELECT MAX (LINE)' at its end. When the procedure was run, it seemed to work. Let's say that the first year had 200 lines to be inserted, the second year 300 lines and the third year 100 lines. In this case, the 200 lines of the first year would get inserted without problem; the first 200 lines of the second year would not be inserted because SQL.LINE would return values that had already been inserted, but the final 100 lines (i.e. 201-300) would be inserted, giving the impression that the procedure worked for this year. Data from the final year would not be inserted at all.

It took me quite some time to figure out what the problem was; this was exacerbated by the facts that the procedure was working on a client's data (that are unfamiliar to me) and that the procedure was based on complicated code involving budgets (that too are somewhat unfamiliar to me). All the checks that I inserted (sorry for the inadvertent pun) showed the expected results, but somehow data was not being inserted into the table. I think that I wrote about a similar problem years ago: when data does not get inserted into a table, check its primary key.

It was less clear in this case because the loop construct was not within the same stage as the data insertion, but even so .... After 'INSERT INTO ... SELECT SQL.LINE', ALWAYS add 'SELECT MAX (LINE)' at the insert statement's end.

A quasi-tabular report

Scenario: the customer had a list of parts (coming from a BOM, but that's not important), where each part has several manufacturers. He wanted to have all of the manufacturers listed in the same row as the part - what we would call a tabular report in Priority, or a pivot table in Excel. I told the customer that this was not a good idea, as the manufacturers for one part would not necessarily be the same as the manufacturers for another part; the tabular report would create a column for every manufacturer, so the resulting report would have many columns and would also be sparse. Not a good idea. Then he told me that he wanted to send the output to Excel - this made the tabular report a really bad idea, as the export to Excel causes the tabular report to lose its distinguishing character: it becomes a normal columnal report, with each manufacturer on a different line.

What can be done?

I hit upon the idea of numbering each manufacturer for each part: let's say that for a given part, there are four manufacturers. These will be numbered 1, 2, 3 and 4, the number being stored in a private field. Another part has three manufacturers: these will be numbered 1, 2 and 3. The actual number and the order are meaningless. In the report, there is one column for the part number, one for its name, one for manufacturer #1, whoever that might be, one for manufacturer #2, etc. Of course, there are many more fields, but the result is a quasi-tabular report that can be exported to Excel without problem.

The very easy parts were adding the private field to the PARTMNF table and form. The easy part was writing a procedure that would iterate over all the parts that have manufacturers (i.e. the PARTMNF table), assigning the numbers and updating the table. Whilst writing the previous sentence, I realise that I don't need to incorporate the PART table in this procedure; it can be based entirely on PARTMNF. This procedure can be run every day, updating only those lines in PARTMNF whose private field's value is 0.

The hard part was writing the report. Assuming that the report is limited to four manufacturers per part, the PARTMNF table has to be joined four times; just to make things harder, these joins should be left joins, as there is no guarantee that every part has four manufacturers. Not only that, a left join is also required between PARTMNF and MNFCTR in order to obtain the name of the manufacturer (is it possible to have a manufacturer's part name without the manufacturer itself having a name??). After working hard on this yesterday evening, I finally got the report working.

I hope that the above is comprehensible. There's no easy way of listing a report in order to display how this was done, and anyway there are many more fields in the report than I have described.

Edit from later the same day: another client had a report that he also wanted to turn into a quasi-tabular form, so I had the solution at my fingertips!

A clever way of deleting a report

Credit where credit is due: this is not my idea, but it's brilliant. The scenario is that a report has been created but now has to be deleted. Normally I would go through the fields in the report, deleting them one after the other. This normally isn't problematic; the problems arise primarily when a report based on a standard report has to be deleted, as there may be translations of various fields' captions.

Someone suggested creating an upgrade where the type of event is DELENT. I very rarely work with upgrades as I nearly always develop on the target machine, but one of my clients uses it all the time. As it happens, I copied a report then discovered that the original report showed the fields that I wanted (or more accurately, didn't show the fields that I didn't want), so the copy was extraneous and could be deleted. I did this in my usual manner, then read about this tip. 

The screen shot below shows my manual deletion of the report in the shape of an upgrade.

Basically all one needs to do to delete a report is to create a new upgrade, then in the sub-form instead of choosing lines as one normally does, one manually enters the event DELENT, then writes the name of the report to be deleted (TEKN_KIYUT_FAMZIVNT in this case) and the type of entity (R). Presumably one then runs the upgrade and the report gets deleted.

The original tip was about deleting a form, something that I very rarely need. In this case, the entity type would be F. This tip should also be useful in deleting an entire procedure - in this case, the entity type would be P.

An SQL tip for comparisons

I want to describe a technique that I found a few days ago ('necessity is the mother of invention'), but I don't really know how to title it. I had a procedure that found work orders, but only those connected to customer orders. This requires the condition SERIAL.ORDI > 0. Someone else wanted the same report, but for work orders that are not connected to customer orders, namely SERIAL.ORDI = 0. I know how to use a flag in order to change a condition, but this always assumes that the comparison operator (>, =) is the same - but it isn't in this case.

I found a solution, although I am fairly certain that there are other ways of doing this. Let's say that :$.FLG = 'Y' if the user wants only work orders connected to customer orders. My solution converts the comparison operator into BETWEEN in the following manner:

  GOTO 1 WHERE :$.FLG = 'Y';
  :FROM = 0;
  :TO = 0;
  GOTO 2;
  LABEL 1;  
  :FROM = 1;
  :TO = 0;
  SELECT MAX (ORDI) INTO :TO FROM ORDERITEMS;
  LABEL 2;
  SELECT .......
  WHERE SERIAL.ORDI BETWEEN :FROM AND :TO
  ....
  

When :$.FLG <> 'Y', the users wants only work orders not connected, so FROM and TO will be zero. Otherwise the values are 1 and max (ordi) - guaranteed to work. Had the request been for work orders that might be connected to a customer order, then FROM would have been redundant: it would be 0 in both cases.

Programming challenge with BOMs (2)

Somehow I doubt that anyone is going to take up my challenge, so I'll try and document my code here.  First, I'll go through some descriptions of the challenge: every line/part in the BOM has a level; direct sons of the father part have level 1, sons of the sons have level 2, etc. If the BOM consisted solely of direct sons, then their numbers would be 1, 2, 3, etc. When a grandson is encountered, the current level 1 number has to be remembered whilst the grandsons get numbered consecutively (i.e. 3.1, 3.2, 3.3). When there are no more grandsons then the program has to continue incrementing the previous level number.

In simple programming terms, a 'previous level' variable is maintained; this variable is compared to the 'current level' variable. If the two levels are the same then the 'level counter' is incremented; if the current level is greater than the previous level then a new counter should commence, and should the current level be less than the previous level, then the previous counter should be restored.

What took me some time to figure out was how I maintain these different level counters. If I were using a normal imperative programming language then I would use an array to do this, but the Priority flavour of SQL doesn't have arrays. Another possibility would be pushing the current level counter onto a stack, then later popping that value; this could be done by means of recursion or a software stack implementation (which can be emulated by an array), but again, these are tools that I don't have at my disposal.

I'm not sure whether the hint came from that phrase, pushing onto a stack, or maybe it was my right brain hemisphere delivering the goods, but suddenly it became clear that I could use a database table (specifically, STACK2) to implement the array. STACK2 has two fields: ELEMENT (the key) and TYPE, so ELEMENT will hold the level and TYPE the counter for that level. Once I had this key element (sorry!) sorted, the rest of the algorithm fell into place.

While I was still going over the algorithm in my head, I considered how many rows I would need in STACK2: what would happen if I defined a given number of rows then the BOM had one more level? Then I realised that I could first establish the maximum level in the BOM then enter rows appropriately. Entering the rows at the beginning means that from hereon the procedure only has to UPDATE STACK2 - there will be no need to test whether the required row exists thus no need for INSERTs.

LINK STACK2 TO :$.ST2;
ERRMSG 1 WHERE :RETVAL <= 0;
:MAX = 0;
SELECT MAX (VAR) INTO :MAX
FROM PARTARC;
:PREVIOUS = 0;
LABEL 1;
:PREVIOUS = :PREVIOUS + 1;
INSERT INTO STACK2 (ELEMENT) VALUES (:PREVIOUS);
LOOP 1 WHERE :PREVIOUS < :MAX;
:PREVIOUS = 0;
DECLARE C30A CURSOR FOR
SELECT SONACT, VAR
FROM PARTARC
WHERE SONACT > 0
ORDER BY SONACT;
OPEN C30A;
GOTO 300 WHERE :RETVAL <= 0;  /* ??? */
LABEL 100;
FETCH C30A INTO :SONACT, :LEVEL;
GOTO 200 WHERE :RETVAL <= 0;
GOTO 110 WHERE :PREVIOUS < :LEVEL;
GOTO 120 WHERE :PREVIOUS = :LEVEL;
GOTO 130 WHERE :PREVIOUS > :LEVEL;
LABEL 110;
:PREVIOUS = :LEVEL;
UPDATE STACK2
SET TYPE = 1
WHERE ELEMENT = :PREVIOUS;
GOTO 150;
LABEL 120;
UPDATE STACK2
SET TYPE = TYPE + 1
WHERE ELEMENT = :PREVIOUS;
GOTO 150;
LABEL 130;
:PREVIOUS = :LEVEL;
UPDATE STACK2
SET TYPE = TYPE + 1
WHERE ELEMENT = :PREVIOUS;
LABEL 150;
:STRING = :STR = '';
SELECT ITOA (TYPE) INTO :STRING
FROM STACK2
WHERE ELEMENT = 1;
GOTO 160 WHERE :PREVIOUS = 1;
:N = 1;
LABEL 1;
:N = :N + 1;
SELECT ITOA (TYPE) INTO :STR
FROM STACK2
WHERE ELEMENT = :N;
:STRING = STRCAT (:STRING, '.', :STR);
LOOP 1 WHERE :N < :PREVIOUS;
LABEL 160;
UPDATE STACK4
SET DOCNO = :STRING
WHERE KEY = :SONACT;
LOOP 100;
LABEL 200;
CLOSE C30A;
LABEL 300;
UNLINK AND REMOVE STACK2;
 

The first part of the code (upto 'DECLARE C30A CURSOR') is concerned with setting up STACK2 as I described previously. The cursor then iterates over the exploded BOMs, retrieving the key number (saved in SONACT) and the line level (saved in VAR). As I noted in an earlier blog on the topic, these fields have one set of meanings when PARTARC is 'raw' and another set of meanings when PARTARC holds exploded BOMs, after having run SONRAW. This is somewhat distracting.

The next part of the code - the three comparisons between the previous level and the current level - implements what I described in the second paragraph of this blog. Following this, the code beginning at label 150 is concerned with building the string to be saved; level 1 is made into a special case - this is in order to handle the separating dots more easily. It doesn't matter that there might be values in the later rows of the table - the loop (that ends with LOOP 1 WHERE :N < :PREVIOUS) means that these values won't be accessed. The final statement saves the calculated string in the field STACK4.DOCNO.

I am proud to say that I worked all of this out in my head before I typed it last night, although there was one small glitch that caused the procedure not to work. By mistake, I had defined that the insertions into STACK2 at the beginning were into field TYPE, which is completely wrong. ELEMENT is the key of this table and as no value was inserted, nothing went into STACK2, making the rest of the program fairly useless. Once I corrected this, the procedure worked exactly as it was supposed to.

I really would be interested to see how other people would solve this challenge, as I make no claims that the above code is optimal.

Programming challenge with Bills Of Materials

The standard reports for displaying bills of materials (BOMs) that come with Priority show the depth of a part in the BOM in a field that is slightly complicated to program. Instead of trying to explain this in words, I'll use a table

Number	Part description	Father	Level
1	A chair	-	0
2	Arm rests	1	1
3	Mechanism	1	1
4	Back	1	1
5	Wood	4	.2
6	Sponge	4	.2
7	Fabric	4	.2
8	Back rest	1	1
9	Wood	8	.2
10	Sponge	8	.2
11	Fabric	8	.2

A chair is built from arm rests, a mechanism (for raising and lowering the seat), a seat and a back rest. For these parts, their father is the chair, or in terms of the above table, part #1. A seat (and a back rest) is made from wood, sponge and fabric; for these parts, their father is the seat (or back rest), part #4 or part #8. With regard to levels, the chair is at level 0; the arm rests, mechanism, seat and back rest are at level 1 (direct sons of the chair); the wood, sponge and fabric are at level 2, which is shown as .2 in the above table.

A customer did not want the level to be displayed as above; instead, he wants that in the field equivalent to level, the arm rests will appear as 1 (no change), the mechanism as 2, the seat as 3, the wood of the seat as 3.1, etc. The table below shows how the customer wants the level to be displayed.

Number	Part description	Father	Level
1	A chair	-	0
2	Arm rests	1	1
3	Mechanism	1	2
4	Back	1	3
5	Wood	4	3.1
6	Sponge	4	3.2
7	Fabric	4	3.3
8	Back rest	1	4
9	Wood	8	4.1
10	Sponge	8	4.2
11	Fabric	8	4.3

This might seem to the customer to be a reasonable request, but in terms of Priority (or any SQL based language) this - at least to me - is extremely difficult to program. It took me half an hour of walking the dog to come up with a solution, but I am not convinced that this is the best/only way of creating those values.

So I am opening this problem as a programming challenge: send me code that achieves what the customer requested.

Some assumptions:

The external program SONRAW has been run, and the exploded BOMs are in a linked copy of PARTARC.

There is a linked table STACK4 whose key is PARTARC.SONACT, i.e. an index (see here for a discussion how SONACT can be used in conjunction with STACK4).

The level string should be stored in STACK4.DOCNO.

Even more on POST-FORM triggers

A few months ago, I documented my need to write a POST-FORM trigger and how to execute it even if there were no changes in the displayed record. The code worked fine in most situations, but I found one situation in which it didn't work. 

Let's say that the current price quotation is for 51,000 NIS: the error message should appear. It will appear if I change anything in the quotation, such as its status, and it will appear if I leave the form with ESC. But the error message won't appear if I press PageDown or Ctrl-Enter (which is effectively the same), in order to create a new price quotation.

After some cogitation, I realised that I had been defining a POST-FORM trigger: ESC will cause the form to close and so POST-FORM triggers. But PageDown leaves the form displayed on the screen, albeit with no data. I was confusing the POST-FORM event with a non-existent event, POST-RECORD (there's no such event or trigger by this name, which is a pity).

Once I realised this, the only solution appears to trap the error condition (or calculate the value of the price quotation) before each line is entered, i.e. CPROFITEMS.PRE-INSERT. Actually I need to trap the PRE-UPDATE event as well. I thought that there is a difference between the two, regarding the current line, but writing this out has undermined my confidence. In PRE-INSERT, the current line won't be in the database whereas in PRE-UPDATE, the current line will be in the database albeit with the wrong price. Now I realise that this doesn't matter: in PRE-UPDATE the wrong total will be created, but it will be higher than what it should be as opposed to lower.

First I'll show the new trigger query and then I'll discuss my reservations.

GOTO 57 WHERE :$$.BRANCHNAME <> '200';
GOTO 57 FROM DOCSTATUSES
WHERE ORIGSTATUSID = :$$.CPROFSTAT
AND SORT = 99;  /* cancelled */
:TEST_TOTAL = :TEST_THISLINE = 0.0;
/* Total of lines previously entered */
SELECT SUM (PRICE * (100.0 - PERCENT) * (100.0 - :$$.PERCENT)
* 0.0001) INTO :TEST_TOTAL
FROM CPROFITEMS
WHERE PROF = :$$.PROF;
/* Add this line */
SELECT (:$.PRICE * (100.0 - :$.PERCENT) * (100.0 - :$$.PERCENT)
* 0.0001) INTO :TEST_THISLINE FROM DUMMY;
:TEST_TOTAL = :TEST_TOTAL + :TEST_THISLINE;
ERRMSG 5000 WHERE :$$.CCNUM = '' AND :TEST_TOTAL >= 50000;
LABEL 57;
  

For the sake of argument, let's say that the first line in the price quotation has the price 20,000 NIS. TEST_TOTAL will be 0 and TEST_THISLINE will be 20,000, so of course the error message will not be displayed. The second line also has the price 20,000 NIS: now TEST_TOTAL will be 20,000 and TEST_THISLINE will be 20,000 NIS, so the total is 40,000, again below the limit. But should I then edit the second line and change it to 30,000 NIS, TEST_TOTAL will be 40,000 and TEST_THISLINE will be 30,000, giving a total 70,000 which is well over the limit. The combined total should be only 50,000, but the extra doesn't matter. The way to overcome this would be to enter a negative amount initially then change it to a positive amount; I don't think that anyone is going to go to such lengths.

I've just thought of another possibility. Let's say that at the beginning, before entering any lines, a discount of 10% was entered into the price quotation. One line (to make this simpler) for 54,000 NIS is entered. The total will be 54,000 - 5,400 = 48,600 NIS and so no error message will be displayed. But I'm a devious character and now remove the discount: the price jumps to 54,000 but again no error message will be displayed as no line has been changed. This means that I have to retain the CPROF/PRE- UPDATE trigger that currently exists.

These mental contortions are a good reason why I don't like programming forms.

---

[Addition from 13/07/22] I'm very embarrassed to say that there are two bugs with the code that I published above, one HUGE and one not so important. SUM (PRICE ...) gets the sum of the prices, not the total cost of each line - i.e. there is no multiplication by the quantity! This is why my tests of 1 unit @ 50,000 NIS caused the error message to be displayed, but 50,000 units @ 1 NIS did not. This is really poor programming. The field QPRICE should be used, with no need to take the line's discount into account.

The other bug concerns DOCSTATUSES: there should be another line in this clause, AND TYPE = 'C', to limit the check solely to statuses of price quotations.

Winrun redux

Several months ago, I wrote about using the program WINRUN in order to send data to Priority from a computer that is not running Priority. I wrote a program in Delphi that would accept barcodes (as read from a dumb barcode reader) and then would transmit them to Priority, invoking a procedure that could handle these barcodes. By the time I wrote that blog entry (in November), the Delphi program was working as designed.

That is, working until about a month ago, when we upgraded our version of Priority from 18.2 to 21.1 (a giant step that caused all kinds of problems). At first it seemed that the only change necessary to my program was to enclose the procedure name with quotation marks, but unfortunately this transpired to be not true. The program would work on a computer that was running Priority but would not work on a computer that was connected to the network and could access the Priority directories but was not running Priority.

With hindsight and after rereading the documentation, I could see that the problem was with accessing the tabula.ini file. Documented was a change in the environmental variable required for the program. Originally the variable was called TABULAINI and as I wrote then, this only has to be done once on the computer. In the mean time, the name of this variable has been changed to TABULAINIORIG (one might ask why this was done; I presume that the answer would be 'davka', a wonderful Hebrew word that supposedly means 'precisely', but has the connotation of being contrary).

Even after I changed the value of the environmental variable, my program still would not work. I had noticed before that the date of the file tabula.ini would change every time that it was accessed, but it hasn't changed since the end of May. This no doubt explains why the program doesn't work. But why?

Today I had the insight to run the SET command (without parameters) on the computer: this lists all the environmental variables that had been defined on the computer. TABULAINIORIG was not there. I have been using a batch file to check WINRUN, and indeed the first command was SET TABULAINIORIG = x:\1\tabula.ini as per the book. I added the variable again, and after unsuccessfully running the batch file, I ran SET again - and the variable that I had just added had disappeared! It seems that WINRUN 'eats' this environmental variable which is really strange behaviour.

As by this time I had removed the SET command from the batch file, I typed it in manually. Then I ran the batch file - and it worked! I ran SET again and saw that now there was a variable tabulainiorig as well as TABULAINIORIG; it seems that WINRUN wants its environmental variable in lower case!

I changed the Delphi program so that instead of setting the (wrong) environmental variable only once at program start-up, the two variables are set immediately prior to running WINRUN (I'm not taking a chance with the upper/lower case problem). And now the program works again.

Syntax problem in 'clever dick' HTML documents

In this blog,  I showed a way to fool the HTML document generator by passing it one linked file, but then creating a new linked file and inserting into it only the records that one wants. I am in the process of upgrading our Priority version, and the program that checks procedures gave me an error message for this procedure: "Unresolved identifier TEST_WWWSHOWCUSTA.PAR". This is a mystifying message as the procedure works perfectly and of course PAR is a recognised identifier.

I discovered that the error came from this line, the first in the procedure:

:OLDPAR = :$.PAR;

This saves the value of the parameter holding the list of customers (or whatever) in the local variable 'oldpar'; internally the value of the parameter is the name of an external file somewhere on the server and so can be viewed as a regular string. If I commented out this line, the error message would disappear, although of course, the procedure wouldn't work. This led me to believe that the error message was probably a bug in the syntax checker: I was using syntax that the checker didn't recognise.

My colleague Yitzchok suggested an alternative (and a more SQL-like) syntax:

SELECT :$.PAR INTO :OLDPAR FROM DUMMY;

This has the same effect but also passes the syntax check. Once I discovered this, I quickly changed the three or four procedures that I had written using this 'replacing PAR' technique so that they too would not fail the syntax check. 

Beware of single character variables in form triggers that log changes

I'm going to describe something which isn't a bug in the strict sense of the word, but will cause Priority to act in a way that we don't want and is unexpected.

There is code in almost every screen to record changes; frequently, there are fields in the screen for which changes are not logged and so we have to add them to our own screen triggers, using the same mechanism for storing the changes. There is a table called GEN_CHANGE_LOG which holds data about changes for most screens and there is a standard procedure for entering data into this table (#INCLUDE TRANSORDER_p/BUF2-CH).

Now down to specifics: I added code a few years ago to the PARTPARAM screen to record changes in the default warehouse of the part using the above procedure. The original value of the field being checked is stored in a variable called OLDVALUE and the new value is stored in - you guessed it - NEWVALUE. As the name of the trigger came after the standard POST-UPDATE-CH trigger, my code executed after the standard code. This shouldn't be a problem as the order of checking should make no difference.

But unfortunately it does. I noticed in the changes log for a few parts that the default warehouse had been changed from 'M' to 'M'. How could this be, when the warehouse names are something like M271 or M504? Why was the trigger storing only one character when the field itself has four characters? I looked at the standard trigger and noted that it was saving the value of a field that could be either Y or empty. Priority handles single character strings in a different manner from longer strings; this gave me the clue that somehow the standard trigger was defining the length of OLDVALUE and NEWVALUE to be single character strings, so that when my trigger executed, only the first character of the value would be stored.

There's no way of redefining the length of a string (AFAIK) in Priority, so I had to look at the problem with some lateral thinking. The solution was to change the name of my trigger so that it executed before the standard trigger. I implemented this and checked: lo and behold! Now warehouse names are being stored in their entirety and are not truncated.

---

I wrote the above a few years ago. Today I ran into a similar case: changing the process of a part to Issue in the PART form caused the error log to show that the new process is 'I'. The previous change in the log was changing the type from O to I - that's where the 'single character variable' comes from.

Edit: this bug appears to have been fixed in later versions of Priority; it's certainly fixed in version 21.

Loading data from external files

This is a topic that has received almost complete documentation in the SDK. The usual scenario is that a file arrives from an external source; this may be an Excel file (xlsx or csv) or some other format. The file has to be converted into a tab delimited text file and placed in the system\load directory; Priority comes with a program that converts xlsx to tab delimited (EXL2TXT) so theoretically this should not be a problem. Both the file name and its source directory name have to be in English: Priority gets confused with Hebrew in the full file name. After the file has been converted, it has to be copied to the system\load directory where its name must be the same as the interface program that loads it. The interface program is usually invoked as a step in a procedure. So far, so good.

I have been working with a file that sometimes causes errors when loaded; as far as I can establish, this is due to there being quotation marks in one of the fields that causes fields in the tab delimited file not to align with the definitions in the interface. This causes an error message to be displayed, explaining the error (normally 'Conversion failed when converting character string to smalldatetime data type'); unfortunately this error message does not state on which line the error occurred. This obviously is not a problem when only a few lines are in the file, but is problematic when there are a few hundred lines.

Reading the SDK chapter again on load files, I came across the section that explains that the interface can be invoked with flags, one of which (-E) causes non-loaded lines to be exported to an error file. Exactly what I need. I couldn't figure out at first how to use this flag when the interface is invoked as a separate step, but I could use the alternative syntax of invoking the interface directly (EXECUTE DBLOAD '-L', <interface name>, '-E', <output file>. Whilst the interface was invoked, the error file was empty.

Today I had the time and lack of pressure to find examples of invoking an interface as a procedure step with flags; I found gold with the procedure BANKPAGE_AMEXCARD. The way one defines the flags is to define a parameter (let's call it E although the name itself is irrelevant) in position 10 (very important) whose value is '-E' and is of type CHAR. Following this, a second parameter is defined (named DBF, again not important) in position 11 (very important - it must follow the previous parameter) with value 'name of error file' (this will normally be stored in system\load), again of type CHAR.

I quickly wrote an interface for a very simple file consisting of one integer field and one date field. I then created such a file where the second line had the integer 16 in the date field - an obvious error. I wrote a procedure to empty the work space then invoke the interface. As expected, I received the error message but more importantly the error file contained the line that was not loaded by the interface. Exactly what I needed.

I will try and figure out why the bad lines in the real file did not get copied to the error file. But more importantly, this is a very useful technique that I will introduce to most, if not all, of my interface programs, especially those that receive external files (as opposed to those whose layout I defined).

More on POST-FORM triggers

Following my previous post on the topic, I've come across a problem with this trigger. Price quotations with a total greater than 50,000 NIS that were created before the trigger was added are obviously not connected to a sales opportunity. Simply entering the price quotation in order to look at it will trigger the error message when exiting the quotation.

The practical solution to this is to press F7 which causes the current record to be removed from the screen. Possibly inelegant but it works.

POST-FORM triggers

I was recently tasked to create an error message when a price quotation's total price is over 50,000 NIS and the quotation is not connected to an opportunity. This is more difficult than it sounds, as the price comes from the price quotation's lines. The standard scenario would be that a user opens the price quotation, adds lines then exits the price quotation, possibly changing the status of the quotation. If the status is changed, then a pre-update trigger would seem to be the place to put the check about the quotation's price. But if the status is not changed, no event occurs at the quotation level and so no code will be triggered. After all, the post-form trigger performs operations when a form is exited, provided there were insertions, updates or deletes in the form [emphasis mine].

One event that is guaranteed to occur is the item form's post-form trigger (this saves having to write both post-insert and post-update triggers) that will be triggered when the amount in the line changes. Here one would have to write code that sums the prices in the lines connected to the price quotation and then checks whether this total is greater than the designated price. I don't like this too much as the same check will be executed several times (once for each line) and also variables that belong to the price quotation form have to be named with a double dollar sign (because they're in the parent form), like :$$.BRANCHNAME. Whilst this isn't a problem, it's one more thing that has to be remembered. 

Whilst idly looking through the SDK for the description of the post-form trigger, the search found a system variable whose acquaintance I had yet to meet: ACTIVATE_POST_FORM. Its description is assign it [the variable] a value of Y in a PRE-FORM trigger to activate the form's POST-FORM trigger upon exiting the form, even if no changes have been made [emphasis again mine]. This is exactly what I need! In the private post-form trigger I included the private pre-update trigger, so one way or another, the error message will be activated as requested.

This technique makes certain checks in forms much easier.

By the way, I couldn't use a business rule as I had to check more than three conditions.

Sonraw (2)

Nearly a year ago, I wrote about the external procedure SONRAW and how it changes values in the linked PARTARC table. I've just made an important discovery about this table after having run SONRAW.

Let's say that there is a part P0 that is the 'grandfather'; P1 will be both a son of P0 and a father, and R1 is a purchase part that is a son of P1. Assuming that there are no more sons in the BOMs of P0 and P1, and that SONRAW is 'fed' part P0, after SONRAW is run, there will be two rows in the linked PARTARC table. In both rows, PARTARC.PART will be P0; in the first row, PARTARC.SON will be P1 and in the second row, PARTARC.SON will be R1. So far, nothing new.

What I have just discovered is that in the first row, PARTARC.SONPARTREV will be P0 (not very useful), but in the second row, PARTARC.SONPARTREV will be P1! This means that the direct father of any purchase part can be found in the linked PARTARC table, something that I thought was not possible. This is even better if there is a phantom between P1 and R1, because looking at the unlinked PARTARC of P1 will show the phantom. Also, there's no real way of getting the parent of R1 - until now!

Not surprisingly, this is very important for a report that I am trying to write; the original version obtained the purchase parts, which is fine, but now I need to get some values from the direct father.

[Edit from 03/10/22: After SONRAW, PARTARC.SONQUANT also contains the internal number of the direct father, although again, this is a real number so one has to write PART.PART = ROUND (PARTARC.SONQUANT).]

:HTMLFNCTITLE

The documentation about this variable is as follows: The report title can be changed at runtime in the SQLI step preceding the report step, using the :HTMLFNCTITLE variable. When would one need/desire to use this variable? When one wants the title of a report to contain the value of a variable; for example, 'List of orders entered on 18/01/22' could be achieved by the following means

:PAR1 = DTOA (SQL.DATE8 - 24:00, 'DD/MM/YY');
SELECT ENTMESSAGE ('$', 'P', 10) INTO :HTMLFNCTITLE FROM DUMMY;
  

where message 10 would be 'List of orders entered on <P1>'  (I use this method to get Hebrew strings into a program - it's easier this way than writing naked strings in the program text that get reversed ... or not). As the documentation states, the above should be in an SQL statement prior to the report whose title one is creating.

This technique is especially useful in 'HTML documents', especially here. But should one does use this technique when there are more reports to be displayed after the one whose title is to be changed, then one must insert another SQLI stage after the given report to nullify the value of :HTMLFNCTITLE.

One might be tempted to use :HTMLFNCTITLE in an 'ordinary' procedure consisting of an SQLI step to collate/calculate data, followed by a CHOOSE step then a few reports, where one will be activated according to the value of the CHOOSE step. Don't get me wrong: the technique will still work, provided one inserts an SQLI step before the desired report. 

So where's the pitfall? 'Ordinary' reports created without :HTMLFNCTITLE display their title (this may come from the report's title - which is limited in length - or from the sub-form 'output title) along with the parameters used to create the report. I find this information very useful when debugging reports that other users have created (e.g. answering the question why a given order doesn't appear in a report - because the parameters of the report don't match those of the order).  This information does not appear in a report whose title has been changed by :HTMLFNCTITLE.

So: it's ok to use :HTMLFNCTITLE in an 'HTML document' but not advisable to use it in an ordinary multi-step report.