Solving two conradictory requirements

In the past, there have been two outside customers that were granted permission to connect to our database, but only to see their data. In order to satisfy this requirement, I turned on the 'Authorisation for Sales Reps' option and gave them the authorisation to see anything with their agent number. In order to compensate for this, I marked for all our regular users the 'All Sales Reps Auth?' flag in the Details of Current Company form. So far so good.

I was given a thorny problem to solve a few weeks ago: someone who works with our company had full access to the database, but he has been 'demoted' and now works only as an agent dealing with after sales orders (e.g. a customer wants an extra table or similar). For this capability, he should have the Authorisation for Sales Reps' option marked for his agent number and the 'All Sales Reps Auth?' flag unmarked. But he also has to see orders of other agents so that he can deal with their installation when required.

On the face of it, then, this person has to be able to both see only his orders and to see all orders: two contradictory requirements. For some time (a week), I deliberated how I could achieve this; I wasn't sure that it was a problem that could even be solved. Then I had a brainwave (in the shower, of course, where all good ideas come): I could write a procedure that marked the 'All Sales Reps Auth?' flag via an interface, then all the required orders could be saved in a temporary table, then the permission would be revoked. This sort of worked, but when I tried it under the person's username, I got no data. I realised that this was that by the time the user came to look at the report containing all the required orders, he was no longer be able to see them as the permission to do so had been removed.

The solution then was to divide this procedure into three stages: the first would give the permission, the second would show the report (i.e. the orders) and the third would retract the permission. Despite trying very hard, I couldn't get the third part to work which was very frustrating. I had also missed the fact that this person could do whatever he wanted to do with these orders when displayed, which was not part of the mission requirement.

A day later, the perfect solution popped into my mind. Instead of being fixated on a procedure and a linked report, I should display the data in a form that would be based on the ORDERS table, but allowing write access only to the fields that this person was allowed to change. Also, many fields that appear in the ORDERS form wouldn't have to be displayed thus greatly simplifying the form's logic. The magic in this form comes from something that is documented but that most people would never need. On page 80 of the SDK for version 23.0 appears the following

PRE-FORM triggers perform operations before the form is opened. This applies
to all root forms, as well as sub-level forms for which the Automatic Display
column of the Sub-Level Forms form (a sub-level of the Form Generator) is
blank. This type of trigger may be used, for example:
 to reset the value of a user-defined variable
 to generate a warning or error message concerning retrieved data
 to retrieve and display all records when the user opens the form —
:KEYSTROKES = ‘*{Exit}’;
 to refresh all retrieved records in a form following a Direct Activation —
:ACTIVATEQUERY = 1;
 to deactivate data privileges in a form — :$.NOCLMNPRIV.T = 1;
 to deactivate data privileges for a specific table in a form: in a new PRE-FORM 
trigger for the form in question, define the :$.NOTBLPRIV.T variable
with the name of the desired table; if the table you want to exclude has a
join ID, this should also be specified

In other words, if I include in the pre-form trigger the command $.NOTBLPRIV.T = 'AGENTS' then the user would not be limited to seeing only his orders but could see everyone's (where a different field contained his order order). This didn't work, but swapping this command with :$.NOCLMNPRIV.T = 1 did.

So I managed to solve two conradictory requirements.

Cursors in combined form triggers

Over the past few months, I sometimes recieved an error message about a cursor being declared twice in TRANSORDER_E/TEST-POST-INS-UPD. This trigger is activated after entering a record, either inserting it or updating it. I would look at the code and not see any problem. Last week I was working with someone on a private form where a trigger, again combined, wasn't doing what it was supposed to do (it did nothing).

A few days ago I closely read the new SDK 25.1 and came across this statement at the beginning where changes were listed: Added a warning that cursors cannot be used in combined (e.g. POST-UPD-INS) form triggers. This explained concisely why the private from trigger wasn't working, and why I received once again the error message about a cursor being declared twice in a trigger.

The answer is to create separate post-insert and post-update triggers. In the private form, there was a slight difference required between the two triggers, but the triggers for TRANSORDER_E will be the same. I don't know whether extracting the cursor code to a buffer will solve the problem so I'll probably just repeat the code.

Now that I think of it, I do have a way of testing whether I can extract the cursor code to a buffer: we have a test server running Priority 25.1, so I can change the code there and see what happens without disturbing anybody.

Multi-company forms

Yesterday I was asked to create a multi-company form. I know how to create a multi-company report but I had yet to create a form like this. Here are the conditions for a multi-company report:

• A displayed column, with a Column Name of TITLE and a Table Name of
ENVIRONMENT.
• A hidden column, with a Column Name of DNAME and a Table Name of
ENVIRONMENT. Its Expression/Condition should be: = SQL.ENV

Naively, I added these fields to the form. As it happens, my customer wanted the company name to appear anyway, so ENVIRONMENT.TITLE was necessary. When I opened the form, there was massive duplication of data (a cartesian join); basically there were no conditions on the Environment table which is why everything appeared several times.

I then turned to the SDK; as it happens, I have a new copy of the SDK for version 25.1 that documents many things that previously were not documented, but unfortunately there is very little about multi-company forms. There is a section heading that reads simply To prevent users from defining a given form as a multi-company form, specify x in the Oneto-many column, but it doesn't mention exactly how to create one as opposed to preventing one. There are a few more mentions about multi-company forms but these are about variables and not relevant.

That one sentence did give me an idea, though. I went to the one-to-many column in the form header and saw that there was an option 'm' that defines a multi-company form. Choosing that option gave me the message 'There must be a field called ZOOMDNAME, a character string of length 8 characters. I guessed that this should replace the field with the column name DNAME (even though the field name is still DNAME as this is the name of the field within the ENVIRONMENT table), and that the TITLE column should be replaced by ZOOMDTITLE. 

Lo and behold, the cartesian join disappeared and the correct data appeared. So this functionality is still undocumented. There are at least 20 standard forms with the 'm' flag (the actual number depends on the version) so one can learn from these.

WINHTML flags

WINHTML is the procedure to call when one wants to print/save/view an HTML document. There are two flavours to this procedure: the earlier flavour receives a linked table of records and the later flavour that allows each record to be printed separately.

There is an error in the documentation for the later flavour that I will show here. The syntax is

EXECUTE WINHTML '-d', 'document_name', 'table', 
'linked_file', '-v', 'record_id', ['-trc', debug_file,] ['-s',] 
['-e',]  ['-edoc' | '-signpdf',] ['-format', format_num,] 
['-lang', lang_num,] ['-AMAIL',]
['-o' |'-pdf' | '-wo' | '-wpdf',] ['output_file',]

Here is an example using this syntax. The output is sent to a PDF file that is later sent to an email address.

:FNAME = STRCAT (:DISK, :PAR2, '.PDF');
EXECUTE WINHTML '-d', :ENAME, '', '', '-v', :ADOC, 
'-format, -10', '-pdf', :FNAME;
MAILMSG 5 TO EMAIL :EMAIL DATA :FNAME;

Returning to the documentation, the description of the '-s' flag is that it supresses the notification window that pops up when preparing the document. In my experience, it does not supress the notification, but more importantly, it causes the document not to appear, or in the case of my example code, no PDF code is created!

So DO NOT USE THE -s FLAG!

Error messages from a procedure invoked by a form trigger are not displayed

I have a private POST-UPDATE trigger in form ORDERS that invokes a procedure whose task is to extract certain lines from the current order and insert them into a new order (this isn't particularly relevant). The procedure checks certain data and displays error messages if the checks fail. It transpires that these error messages are displayed when the procedure is run from a menu, but not when invoked from a form, which is when they are needed the most.

Below I describe a slightly complicated way to get around this problem. First I defined a new table TEST_ERRMSGS that has four fields: AKEY, USER, PROG and MESSAGE, where the first three fields create the composite primary key ('U' in Priority-speak). I can't be sure that someone else is going to run the same procedure, probably on a different order but who knows, so I need maximum 'separation'. AKEY will be the order number, USER is of course the user's number and PROG is the number of the procedure. Here is the relevant part of the form trigger pre-enhancement

SELECT SQL.TMPFILE INTO :ECORDERS FROM DUMMY;
LINK ORDERS OEC TO :ECORDERS;
GOTO 999 WHERE :RETVAL <= 0;
INSERT INTO ORDERS OEC
SELECT * FROM ORDERS ORIG
WHERE ORD = :$.ORD;
EXECUTE WINACTIV '-P', 'TEST_SEPARATE', 'ORDERS',
:ECORDERS;
UNLINK AND REMOVE ORDERS OEC;

Here are the lines that have to be added before the line 'EXECUTE WINACTIV'; one private integer field is utilised although this could be any field in the order as long as the procedure doesn't need it in the course of its normal execution. There's no need to worry about overwriting important data as it's a linked table that is being updated. Of course, it's best to use a private field. 

:TEST_PROG = 0;
SELECT EXEC INTO :TEST_PROG
FROM EXEC
WHERE TYPE = ‘P’
AND ENAME = 'TEST_SEPARATE'
;
SELECT SQL.TMPFILE INTO :ECORDERS FROM DUMMY;
LINK ORDERS OEC TO :ECORDERS;
GOTO 999 WHERE :RETVAL <= 0;
INSERT INTO ORDERS OEC
SELECT * FROM ORDERS ORIG
WHERE ORD = :$.ORD
;
UPDATE ORDERS OEC 
SET TEST_FIELD = -1 /* this signifies that the procedure is called from an order */
WHERE ORD = :$.ORD;

At the beginning of the procedure, the following query is executed to get the order number

LINK ORDERS TO :$.PAR;
ERRMSG 1 WHERE :RETVAL <= 0;
SELECT ORD, ORDNAME, CUST
INTO :OLDORD, :ONAME, ::CUST
FROM ORDERS WHERE ORD > 0;
UNLINK ORDERS;

To the SELECT list, I add the private field TEST_FIELD that is selected into the variable :FROMFORM. After this is the block

GOTO 10 WHERE :FROMFORM <> -1;
:TEST_PROG = 0;
SELECT EXEC INTO :TEST_PROG
FROM EXEC
WHERE TYPE = 'P'
AND ENAME = '$';
DELETE FROM TEST_ERRMSGS
WHERE USER = SQL.USER
AND AKEY = :ORD
AND PROG = :TEST_PROG;
LABEL 10;

In other words, if there is already an entry in the table for this user, order and procedure, clear it so that we can start afresh. Now the procedure runs, and every time that it detects an error, the rather cumbersome code that appears below has to be included. If before the code was

ERRMSG 62 FROM ORDERSA
WHERE ORD = :ORD
AND TEST_CONDITION = 'Y';

It now becomes

:ERR = 0;
SELECT 62 INTO :ERR
FROM ORDERSA
WHERE ORD = :OLDORD
AND TEST_CONDITION = 'Y';
GOTO 862 WHERE :ERR = 0;
GOSUB 9990;
ERRMSG 62;
LABEL 862;
...
SUB 9990;
GOTO 9991 WHERE :FROMFORM <> -1;
SELECT ENTMESSAGE ('$', 'P', :ERR) INTO :PAR1 FROM DUMMY;
INSERT INTO TEST_ERRMSGS (AKEY, USER, PROG, MESSAGE)
VALUES (:OLDORD, SQL.USER, :GLOB_PROG, :PAR1);
LABEL 9991;
RETURN;

Going back to the form trigger, after the line 'EXECUTE WINACTIV', the following has to be added

SELECT MESSAGE INTO :PAR1
FROM TEST_ERRMSGS
WHERE USER = SQL.USER
AND AKEY = :$.ORD
AND PROG = :TEST_PROG;
ERRMSG 990 WHERE :RETVAL = 1;

In other words, without digging into the code, the calling trigger has to signify to the procedure that it is being run from a trigger (TEST_FIELD = -1). The procedure then inserts a record into a special table if an error is detected, where the record includes the order number, the user, the procedure and the error message to be displayed. When the trigger resumes execution, it checks whether there is a suitable record and if so, displays the error message. This means that the record will remain in the table but this doesn't matter much for if the user runs the same procedure on the same order, the record will be deleted at the beginning of the procedure.

The single character bug returns

This morning I was tasked with retrieving the location of inventory within a specific warehouse. I wrote the following code that is part of a larger procedure that creates a warehouse transfer document.

:LOC = '0'; /* default value */
SELECT WAREHOUSES.LOCNAME INTO :LOC
FROM WAREHOUSES, WARHSBAL
WHERE WAREHOUSES.WARHS = WARHSBAL.WARHS
AND WARHSBAL.PART = :PART
AND WAREHOUSES.WARHSNAME = 'Main';

When the procedure containing this code was run, there was a complaint that location R does not exist for warehouse Main. This error was doubly confusing - at first, I thought that the R might refer to the part's type, which would make no sense whatsoever. When I ran the snippet in Windbi, the result was something like R.1.2, which was when I understood what was happening.

:LOC is initially set to the single character '0', for if the retrieval fails (for example, if the part has no inventory) then at least :LOC will have a value that is guaranteed to exist. Unfortunately, as I discovered several years ago¹, initialising a string with a single character confuses Priority who thinks that this is a character variable, not a string, and so saving further values into this variable will only save the first character.

The solution is to have an initialisation line at the beginning, like :LOC = '00000000' (I imagine that the length doesn't matter too much, as long as it's longer than one character. Once this line had been added, the addition to the procedure worked flawlessly. 

I notice that I wrote in the earlier blog that this issue had been fixed in version 21. I'm not sure with which version I was working today (it was for an external client), but it was web-based and probably not less than version 23.

Internal links
[1] 51

WARNINGYES

It's very easy to define a business rule: if such and such happens, then either: display an error message, display a warning message, send an email, send an SMS, open a task or send a push message (whatever that is). A warning message displays some text along with two buttons, "continue" or "cancel". I've never noticed until now that the default button is "continue". Thus it's very easy to consider the scenario whereby a user does something that causes a rule to fire, but the user ignores the warning message and simply presses "continue", thus basically negating the whole point of the warning.

I discovered (very belatedly) that there is a system constant, WARNINGYES, that controls which button is the default; 1, which is the predefined value, means that the "continue" button is the default, whereas 0 means that "cancel" is the default. I changed the value of the constant to 0 and now hopefully users will actually read any message displayed.

This is to a certain extent shooting myself in the foot: once every couple of days I have to create delivery notes from packing lists (don't ask), and when I paste an order number into the delivery notes screen, two or three messages are displayed. I don't read them and simply press enter to continue. Now I'm going to have to press an arrow key and then enter. The message are generally "Packing list X is connected to this order" and some text referring to the customer. Because I belong to the 'tabula' users group, I can't execute the privileges program, otherwise I would turn these warning messages off. Such are the tribulations of a system manager.

I made that change 20 minutes ago and no one has written to me about it yet. Normally a change like that gets noticed very quickly.