Tip for sorting tabular reports in reverse order

The scenario: let's assume that there is a report that displays data in a tabular form where each column represents a year. In a Hebrew display, the lowest value will be on the right whereas the highest value will be on the left; with an English display, the lowest value will be on the left and the highest on the right.

2023	2022	2021	2020	2019	2018
12	13	14	15	16	17

The challenge: the tabular report must be sorted in reverse order, i.e. the highest value on the right and the lowest value on the left, in a Hebrew display.

What doesn't work: Priority offers the option of Ascending sort or Descending sort in a regular report; there is a field called 'sort direction' where one can add A or D. As reports have ascending sort as the default, there's no need ever to add A, but D does cause the report to be displayed in descending order for the given field. This option does not work in tabular reports.

The solution: let's assume that the year is being held in STACK4.KEY, just to make the explanation easier; the year could be in any integer field. In a tabular report, as this field is going to be the column header, it receives the graphic display value X, that will be sorted alphabetically in ascending order. One can also add a hidden field with graphic display value O that will overide the value in the X field - this is useful when displaying months so that 11/23 will come before 01/24. Alphabetically 01/24 preceeds 11/23, but if one uses a function like YEAR (SOMEDATE) * 12 + MONTH (SOMEDATE) as the 'O field' then the months will be sorted correctly. 

In order to display the table in reverse order, one needs to define a field with the graphic display value O whose value will be something like 3000 - STACK4.KEY. This will cause this field's value to decrease while the X field's value increases. If the X field value is 2023 then the O field value will be 977; if the X field value is 2022 then the  O field value will be 978. Thus the table will be displayed in reverse order, viz.

2018	2019	2020	2021	2022	2023
17	16	15	14	13	12

Improving links in messages generated by a BPM

Priority has several BPM form that manage the various statuses of a document (customer order, purchase order, etc). One can define that messages can be sent according to rules defined on the BPM form, e.g. send a message to user X when an order reaches a given status. One can also include pre-defined fields in these messages, e.g. order number, so that clicking on the order will open the appropriate form (e.g. customer orders) with the appropriate order.

What happens when one wants a different form to be opened automatically? As opposed to the form manager or report manager, there is no possibility of defining a target form for the predefined field. How does one overcome this?

I've never thought about this before, but presumably every field that appears as a predefined field in the rules manager is displayed on the given form. I checked a somewhat obscure field that appears on the purchase orders screen and is defined as read-only; this field appears in the list of predefined fields for addition to a message.

So, it seems quite probable that adding a private field to a form, e.g. adding a copy of the field PORDERS.ORDNAME to the PORDERS form, where this field is defined as read-only and has a defined target form would solve my problem. I could add this new field to a message sent by a rule, click on the message received and have the new target form open.

Unfortunately not. Whilst adding the private field to the form allows it to be attached to the message that is sent by a rule, the order number comes without a link and so is valueless. This requires more thought and experimentation.

A better way of calculating the dollar value of sales invoices

I wrote the other day about code to calculate the dollar value of sales invoices. I had written code that iterated by means of a cursor over the lines of an invoice and calculated the dollar value of each line, then summing these values. I was sure that there was a better way of doing this but I hadn't managed to find this better way. While I was describing the problem, I saw the quick way of getting the dollar value, using values in the connected transaction. This is yet another case of rubber duck debugging: describing the problem in words leads to a solution. Here is the code that is stored as a trigger in the 'func' form.

/***********************************************************
TEST_CNVINV2DOLLAR - Convert an invoice's total to dollars
11/11/23: New, much simpler version that doesn't require a cursor
and so can be used multiple times in a container procedure.
SUB 6200 -
Input: :IV
Output: :QP
************************************************************/
SUB 6200;
:VAT62 = :SUM62 = :EXCHANGE62 = :QP = 0.0;
:TMP62 = 0E9;
:DEBIT62 = '\0';
SELECT INVOICES.VAT, INVOICES.DEBIT, FNCTRANS.SUM2,
FNCTRANS.EXCHANGE2
INTO :VAT62, :DEBIT62, :SUM62, :EXCHANGE62
FROM INVOICES, FNCTRANS
WHERE INVOICES.FNCTRANS = FNCTRANS.FNCTRANS
AND INVOICES.IV = :IV;
:TMP62 = :VAT62 / :EXCHANGE62;
:QP = (:DEBIT62 = 'C' ? -1 : 1) * (:SUM62 - :TMP62);
RETURN;

Short and simple.

Version control for screen triggers

Two and a half years ago, I developed a system for version control of procedures. This system has proved invaluable at times when a change to a procedure did not not give the desired output; I could revert the code to the previous version thus restoring the status quo. At one time I extended the system to work with interface code - this was very simple. For some time now, I have been wanting to extend the system to provide version control for screen triggers, but this is not so simple.

In the same way that a procedure can have several SQLI stages, a screen/form can have several triggers. But as opposed to procedures where every SQLI stage has a different value in the PROGRAMS table, triggers are managed in a similar albeit different manner, by means of the FORMTRIG table that has both a pointer to the parent form (e.g. ORDERS) and a pointer to the given trigger. 

Originally I thought that I would have to develop a completely new system for form triggers, albeit influenced by the procedures code. But when I devoted some serious thought to the topic, I realised that all I need do was add a new field to the table that manages the versions; this field would be a pointer to the given trigger that along with the pointer to the parent form would completely identify the trigger. Obviously the code that saves a version would have to be written, but this would be very similar to the code that saves a version of a procedure or interface.

Once I had this insight, there wasn't very much that I could do to advance the subject, as the new field would have to be part of the primary key (U) of the manager table that would mean having to add the field to the appropriate form, which is something that I don't do during regular work hours. Fortunately Friday afternoon is a time when no one ever seems to be connected to Priority, so for me it's an ideal time to work on form code. 

First I added the new field, then I updated all the existing tuples in the table to have the value -10 in this field; every tuple has to have a value, but it is meaningless when the tuple is for a procedure. Then I had to add this field (and its value) to the existing screen, then prepared all the forms. Then I updated the code for saving procedure versions to include this new field and saved this new version: the form displayed it correctly. Why -10? 0 would appear to be a better value, but I am wary of saving 0 in a key field. There are five predefined triggers (four search triggers and CHOOSE-FIELD) that have values -5 to -1; as I didn't want a collision with these values, I chose a different number. It occurred to me later that a tuple for a procedure couldn't possibly have the same EXEC value as a tuple for a trigger, so I could have used -1 without problem.

Then I had to start work with the trigger code. First I defined a new form for displaying versions that is almost exactly the same as the existing form albeit with different values for the EXEC and TRIG fields. This form is a son of the FTRIG form. Once I had the form defined, I could define the interface for saving a new version, and once I had the interface I could define the required procedure. This is almost a chicken and egg problem, but there is a way through the convoluted path.

Debugging the procedure was a different story to the simple steps that I had taken so far. The first - and relatively minor - problem was that I had neglected to define correctly the TRIG field in the new form. Once this was fixed, I could save new versions of each trigger, but the text - the point of the entire exercise - was not being saved. In true debugging fashion, I took the dog for a walk and considered my options. It turned out that the procedure was saving the code text in GENERALLOAD.TEXT, but the interface was expecting the text in TEXT2. A hard to find but easy to correct bug. It then occurred to me that I could improve the system by saving the date of the trigger as the version date; this saves me some work when saving existing triggers, although of course it won't make any difference when saving a new version of a trigger.

An ounce of prevention is worth a pound of cure.

A new problem with 'clever dick' HTML documents: redeclaration of a cursor

The comptroller of a company for whom I do contract work complained that two standalone reports that present similar data have different values. These reports give sales invoice totals converted in dollars; the invoices may be in at least three different currencies, some with and some without VAT. Years ago I developed an algorithm for calculating the value of a single line: multiply the quantity by the price of the line, taking into account both the discount on the line and the invoice level discount. Once this is known, this intermediate value can be converted to dollars by means of a routine that I saved as a global trigger (i.e. as a trigger in the 'func' screen), that checks the currency of the line (I've seen invoices that have different currencies in the lines and in the header) and acts accordingly. It looks something like this:

  GOTO 110 WHERE :$.CURR = -2;   /* dollars */
  SUB 900 WHERE :$.CURR <> -1;   /* convert to NIS */
  SUB 910;                       /* convert to dollars */
  LABEL 110;
  ...
  #INCLUDE func/TEST_CNV2DOLLAR
  

Subroutine 900 converts the given amount of currency X to NIS according to the exchange rate at the invoice date; subroutine 910 converts NIS to $ is similar to subroutine 900 but does the same thing in reverse. 

One of the reports uses this global trigger in a subroutine that iterates over all the lines of a given invoce. The other report used the FNCTRANS.SUM2 field that gives the dollar value of the financial transaction - whilst it might seem that it gives the appropriate value with no work, this is true only of export invoices; Israeli invoices include VAT and the SUM2 field gives the complete total for the invoice. I tried various approaches that would use this field in order to give the dollar value of the invoice but never succeeded (a new approach to this problem has just occurred to me: subtract from the SUM2 field the value of the VAT field in the invoice header divided by the exchange rate of the transaction. If the VAT is zero then SUM2 will be returned). Eventually I converted the local subroutine that uses a cursor to iterate over the invoice lines to a global trigger and saved it in the 'func' screen.

---

This company has a prediliction for what I am now calling 'container documents', aka complicated 'clever dick' HTML documents: the one that I am working on now (and have been doing so for maybe the past two months) has ten or eleven different reports displayed within a single container. Most of the reports are standalone, but it happens that one report uses a total that has been calculated in a previous report in the container. This requires the use of global parameters, e.g. a variable such as :$.AM, whose name is preceeded by a dollar sign. Internal variables appear to have scope that is restricted to the SQLI section in which they appear, although I am not totally convinced about this*. I suppose that I should check this by defining such a variable in one SQLI section of type integer then redefining it as a real in a different section. If this is so, then there's no real need for the use of global parameters, whose name is constricted to three letters.

Several of these reports display data regarding invoices, so it occurred to me that I could use my new global trigger code instead of having several SQLI stages have the same subroutine. I updated the various stages to use the global trigger; the syntax check of the entire container procedure showed no errors but I was unable to run the procedure. This normally means that I've redeclared a cursor in the procedure. I checked all the stages but could see no duplication; I renamed cursors but saw no duplication; I ran the entire procedure source through my cross referencer but still saw no duplication. I tried removing sections of the code but still the problem existed. After about an hour of extreme frustration, I restored previous versions of the sections (making use of the version control system that I developed a few years ago) and decided to leave things as they are.

This morning, in a splendid display of right brain thinking, the reason for the cursor redeclaration suddenly appeared. I was using a global trigger ... that was using a cursor! In other words, stage 89 defined a cursor C6200, stage 99 - by virtue of including the global trigger - also defined cursor C6200, as did stage 109. In other words, the redeclarations arose because of the use of the global trigger that contains a cursor. Ooops.

The idea that I had at the beginning of this blog as to how to calculate the dollar value of an invoice without using a cursor will provide a solution to the redeclaration problem, assuming that the new function provides the correct result.

* After declaring the same variable as a real in one stage and as a string in another stage, I found out that local variables have scope of the entire procedure and not of a SQLI stage. So there's no need to use 'global variables' prefixed with a dollar sign - one can use regular variables to carry values between stages.

Private table interface errors when upgrading to version 23.0

My company is currently in the process of checking the upgrade path to version 23.0; I have come across a problem with some private table interface errors. To be clear, a private interface does not use GENERALLOAD or a filename that is defined in the interface; this type of interface expects a file in the system/load directory whose name is the same as the interface, normally without extension.

In one interface, I defined a table variable DEBIT to be a character value of length 1. The upgrade error report says that this variable is an incompatible data type. I understand this to mean that I have reused a predefined variable name and instead of using the predefined data type - presumably int - I am redefining it as a character. This results in a page full of errors in the upgrade error report. The fix, of course, is simple - change the variable name to DEBITXX, or similar; of course, the code that inserts the data into the load table must also be changed.

In another interface, the same type of error occurs with a variable named DUEDATE (but not with a variable named QUANT). Again, the fix is simple.

Your experience, of course, may differ.