CURREGITEMS

CURREGITEMS is the name of the table that contains historical data regarding representative rates of different currencies. It has three fields of interest, namely CURRENCY, CURDATE and EXCHANGE: these are self-explanatory. The table is populated automatically upon the entry of new data into the connected CURRENCIES table. So if one runs the report Financials > Maintenance of Financials > Exchange Rates > Daily Exchange Rates (CURRENCIES) for USD, one gets the following data:

Date	Rate
30/11/23	3.714
01/12/23	3.739
04/12/23	3.708
05/12/23	3.728

Thus if one wants the dollar representative rate for an invoice dated 04/12/23, simple code to do so would be 

SELECT EXCHANGE FROM CURREGITEMS
WHERE CURRENCY = -2
AND CURDATE = 04/12/23;

Of course, one would use parameters for CURRENCY and CURDATE; The result for this query would be 3.708. 

But what happens if one wants the exchange rate for Sunday, 03/12/23? One must remember that the Bank of Israel does not publish representative exchange rates for Saturdays and Sundays, and indeed the above table does not have an entry for 03/12/23. The way that I was taught (or taught myself) many years ago when first working with Priority is that when there is no value in CURREGITEMS, one uses the value of the EXCHANGE field in the CURRENCIES table which is the most up-to-date value. As a result, I have written in many reports or procedures expressions such as

SELECT (:CURR = -1 ? 1.0 :
(CURREGITEMS.EXCHANGE > 0.0 ? CURREGITEMS.EXCHANGE :
CURRENCIES.EXCHANGE)) INTO :EXCH
FROM CURRENCIES, CURREGITEMS ?
WHERE CURRENCIES.CURRENCY = CURREGITEMS.CURRENCY
AND CURRENCIES.CURRENCY = :CURR
AND CURREGITEMS.CURDATE = :DATE;

A problem with this code is if one is showing data from several months ago; the representative rate for the dollar might have been 3.5 but now it's much higher. I thought that I would have to write a loop to get the representative rate of the date previous to the desired rate, something like

LABEL 10;
:EXCH = 0.0;
(:CURR = -1 ? 1.0 :
(CURREGITEMS.EXCHANGE > 0.0 ? CURREGITEMS.EXCHANGE)) INTO :EXCH
FROM CURREGITEMS 
WHERE CURREGITEMS.CURRENCY = :CURR
AND CURREGITEMS.CURDATE = :DATE;
:DATE = :DATE - 24:00;
LOOP 10 WHERE :EXCH = 0.0;

But it transpires that this is totally unnecessary! There is a value for 03/12/23 in CURREGITEMS - it's the same value as for 01/12/23, or in other words, the last known representative rate. There's no need for left joins between CURRENCIES and CURREGITEMS.

I have no idea whether this has always been the case and that the queries that I wrote in 2007 were misguided even then, or whether the method of inserting tuples in CURREGITEMS has changed in the passing years. I suspect that there has been no change and that I have been perpetuating a misunderstanding for the last 17 years. I learn something new every day.

APPENDUM: the original code with a left join between CURRENCIES and CURREGITEMS is necessary when the given date is in the future. In this case, there definitely will not be an appropriate entry in CURREGITEMS and so it's 'correct' to use the value from CURRENCIES. 

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.

How to include a backslash ('\') in a string

Someone wondered how they could convert a string holding a file name (such as 'Z:/ABC/DEF.TXT') into a file name that the file system would recognise ('Z:\ABC\DEF.TXT'). This turned out to be unexpectedly difficult, primarily because Priority regards the backslash (\) as an escape character and there is no real mechanism for obtaining or assigning a single character to a variable.

The first part - replacing the forward slashes with hyphens - was simple.

:PAR3 = 'Z:/ABC/DEF.TXT';
:SLASH = '-';
LABEL 10;
:PAR1 = STRPIECE (:PAR3, '/', 1, 1);
GOTO 20 WHERE :PAR1 = :PAR3;
:PAR2 = STRPIECE (:PAR3, '/', 2, 9);
:PAR3 = STRCAT (:PAR1, :SLASH, :PAR2);
LOOP 10;
LABEL 20;
/* at this stage, :PAR3 will be Z:-ABC-DEF.TXT */

I could output the string to a file then use the FILTER program to change the hyphens into blackslashes, but then the user would be faced with the problem of getting the string out of the file.

This morning, the answer hit me when I was doing something else entirely. The problem of the backslash is the same as the problem of the dollar sign, so the solution is the same: create a message (in this case, 11) whose text is simply \. The code now becomes

:PAR3 = 'Z:/ABC/DEF.TXT';
SELECT ENTMESSAGE ('$', 'P', 11) INTO :SLASH FROM DUMMY;
LABEL 10;
:PAR1 = STRPIECE (:PAR3, '/', 1, 1);
GOTO 20 WHERE :PAR1 = :PAR3;
:PAR2 = STRPIECE (:PAR3, '/', 2, 9);
:PAR3 = STRCAT (:PAR1, :SLASH, :PAR2);
LOOP 10;
LABEL 20;
/* at this stage, :PAR3 will be Z:\ABC\DEF.TXT */

Web interface report bug

One of my clients exclusively uses the web interface of Priority; whilst this is the face of the future, it's also more expensive as every user needs a licence (the classic Windows interface allows concurrent licences). I find the user interface much more difficult to work with (there are some items that I use frequently in the classic interface that I have yet to find in the web interface) but this is no doubt a function of habit. Upkeep and maintenance is probably less with the web interface as one can have one's database(s) hosted on another company's computer, thus one does not have to purchase and maintain servers. This is good for new companies but less good for existing companies.

But I digress. Unfortunately it often happens that I develop a report that I know will work with the classic interface but causes the web interface to crash. I was able to identify one cause of this behaviour that I am going to describe here.

In a report, one can define that certain fields will appear in a header line. In the classic interface, one can define however many fields as necessary to appear in the header, and the web browser that displays the report will automatically split the fields over two lines if the total width of the header exceeds the width of the report. Thinking about this, this probably occurs in the Priority procedure that takes the data, reads the report interface and creates the HTML code.

This does not happen with the web interface! Should a report have a header that is too wide, the feared error message will appear. The solution of course is very simple: one has to manually move the surplus fields to a second header line instead of having this occur automatically. Naturally this is not documented: it was only by observing what happened to a report with many fields in its header did I understand what was triggering the bug.

COC document (2)

As Maslow's hammer puts it, when the only tool you have is a hammer, every problem looks like a nail.

This happens to me every now and then in Priority, when I choose a complicated solution involving procedures when there exists a simpler method. For example, someone wanted me to send him copies of delivery notes when the status of those notes is changed to 'final'. I wrote something similar to this for the same person, sending a letter when the status of a customer order changes. This involved writing a procedure that sends an email, that is invoked by a trigger. Complicated. I was about to write the same sort of procedure for the delivery notes when I remembered that I could define a simple rule that would fire when a delivery note reaches a given status: it can send email with an attached document. Request solved after a few minutes of thought and one minute of development. As it happens, a few days later, the person making this request asked me to stop sending the documents!

So, again, with the COC document. I couldn't figure out how to have the HTML document work on devices when the imput is customer orders, so I wrote a procedure that would obtain the devices from the order then invoke the HTML document with this list of devices. I was looking at the documentation again when this statement caught my eye:

HTMLCURSOR (Create HTML Document) — Declares the cursor for a document. This step first creates a linked file that holds the records selected in the PAR input parameter.

If the PAR input parameter contains orders, then the internal cursor will obtain an order number from this linked table, but what is important is the value selected in the query - this value will be stored in the internal variable HTMLVALUE and will be a device number.

My original code in the external procedure was as follows

LINK ORDERS TO :$.ORD;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK SERNUMBERS TO :$.SER;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO SERNUMBERS (SERN, SERNUM)
SELECT ORIG.SERN, ORIG.SERNUM
FROM ORDERS, ORDERITEMS, SERNUMBERS ORIG
WHERE ORDERS.ORD = ORDERITEMS.ORD
AND ORDERITEMS.ORDI = ORIG.TEST_ORDI
AND ORIG.SERN > 0
AND ORDERS.ORD > 0;
EXECUTE WINHTML '-d', 'TEST_WWWCOC', 'SERNUMBERS', :$.SER, '-e';
UNLINK ORDERS;
UNLINK SERNUMBERS;
  

My new code in the HTMLCURSOR stage became

SELECT SERNUMBERS.SERN
FROM ORDERS, ORDERITEMS, SERNUMBERS
WHERE ORDERS.ORD = ORDERITEMS.ORD
AND ORDERITEMS.ORDI = SERNUMBERS.TEST_ORDI
AND SERNUMBERS.SERN > 0
AND ORDERS.ORD > 0;
  

ORDERS is automatically linked to the PAR parameter, so this code provides a list of all the devices linked to the lines in the given list of orders. A much simpler solution than my baroque construction!

I wonder now what I had written in this statement in the first version of the document that receives an order number.

Certificate of conformance document

Many companies issue a 'certificate of conformance' (COC) document for the products that they ship. There is a default document in Priority that is based on delivery notes, but of course, the wording in this document is fixed, whereas every company wants different text. One of my customers asked me to create such a document that would be based on shipping documents, so this was a fairly simple exercise in HTML document programming.

My contact person for this customer used to work with another of my customers, and presumably during the course of a conversation comparing notes, he mentioned that I had created such a document for them. So customer #2 wanted a document ... except that they wanted the document to be based on orders with completely different text.

I started work on creating the new document and immediately ran into the first problem: how many documents do they expect to create from one order? One, for the entire order? One per line? One per device connected to each line? It turns out that they want a document for each device, making my work problematic.

An order line can have a quantity greater than one, and so there may be more than one device with its unique serial number connected to the line. In the standard form for shipping documents, the quantity in the line is displayed, along with a separate report (in Priority terms) that prints a list of serial numbers. This is not what the customer wanted.

My problem was that the input to the HTML document would be orders but the cursor would have to run on devices. I tried various approaches but couldn't get the HTML document to work properly (if at all). After writing a letter explaining why I couldn't provide what the customer wanted, the rubber duck to whom I had been explaining the problem spoke up, pointing out that I could write a separate program that would create a linked file of devices, then invoke the COC document with that linked file.

Indeed. This approach works, although I had some problems at the beginning. In the end, I deleted the HTML document that I had created and created a new one by copying an HTML document that I wrote that prints data about devices. I removed most of the subreports of this copied document and restored the few subreports that I had written for the original COC document. This works!

One more problem that took some time to solve: the COC document will be in English, and I added the E flag in the procedure header. But the document created by the calling procedure had some left to right problems. Eventually I remembered that I had written about this problem a few years ago and how to solve it.

Following is the code of the calling procedure (this is helped by the fact that the customer had previously requested the addition of the order line to which the device is connected; this is default behaviour with shipping document lines):

LINK ORDERS TO :$.ORD;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK SERNUMBERS TO :$.SER;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO SERNUMBERS (SERN, SERNUM)
SELECT ORIG.SERN, ORIG.SERNUM
FROM ORDERS, ORDERITEMS, SERNUMBERS ORIG
WHERE ORDERS.ORD = ORDERITEMS.ORD
AND ORDERITEMS.ORDI = ORIG.TEST_ORDI
AND ORIG.SERN > 0
AND ORDERS.ORD > 0;
EXECUTE WINHTML '-d', 'TEST_WWWCOC', 'SERNUMBERS', :$.SER, '-e';
UNLINK ORDERS;
UNLINK SERNUMBERS;
  

Defining colours for a report header column/2 - the easy method

Some time after writing yesterday's extravaganza, I wondered whether it would be possible to define the background colour of a report header column by using the 'design report' functionality. Assuming that a report (or the procedure calling it) appears on a menu, one right clicks on the appropriate option and chooses the 'design report' option from the pop-up menu. A document design will be created if the report/procedure does not have a previously created design.

One then clicks on the appropriate report and then right clicks on the appropriate column. From the pop-up menu, one chooses the title attributes option. A dialog box appears where one can choose various options: one clicks on the 'paint can' button, causing another dialog box to open, from which one can choose the desired colour. One repeats this process for all the fields whose background colour one wishes to choose.

This is the user's method, whereas yesterday I documented the programmer's method.

Defining colours for a report header column

It is possible to define the background colour of a report header column, as shown below.

In order to achieve this, one has to enter the 'report columns - HTML design' sub-form for each field; in the field 'title design', one enters D, and in the 'column title - HTML design' sub-form of this form, one defines the desired colour.

This is very easy to do - at least, when one knows the technique. From what I have seen, this technique works with stand-alone reports, reports that are invoked by a regular procedure and reports that are invoked as part of an HTML document.

Shown below is a procedure that I wrote to update all the visible column titles (but not of columns that appear in a header) of any given report.

:PAR1 = '$';
:EXEC = 0;
LINK EXEC TO :$.EXE;
ERRMSG 1 WHERE :RETVAL <= 0;
SELECT EXEC, ENAME INTO :EXEC, :ENAME
FROM EXEC
WHERE EXEC > 0
AND TYPE = 'R';
UNLINK EXEC;
ERRMSG 2 WHERE :EXEC = 0;
LINK HTMLCOLORS TO :$.COL;
ERRMSG 1 WHERE :RETVAL <= 0;
SELECT COLOR INTO :COLOR
FROM HTMLCOLORS
WHERE COLOR > 0;
UNLINK HTMLCOLORS
;
DECLARE CUR CURSOR FOR
SELECT POS
FROM REPCLMNS
WHERE EXEC = :EXEC
AND RSELECT <> 'N' /* not hidden */
AND FGROUP < 100   /* not a header */
AND POS > 0;
OPEN CUR;
GOTO 300 WHERE :RETVAL <= 0;
LINK GENERALLOAD TO :$.GEN;
ERRMSG 1 WHERE :RETVAL <= 0;
:LINE = 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT13)
VALUES (1, '1', :ENAME);
LABEL 100;
FETCH CUR INTO :POS;
GOTO 200 WHERE :RETVAL <= 0;
:LINE = :LINE + 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT1) 
VALUES (:LINE, '2', :POS);
:LINE = :LINE + 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, CHAR1)
VALUES (:LINE, '3', 'D');
:LINE = :LINE + 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, INT1)
VALUES (:LINE, '4', :COLOR);
LOOP 100;
LABEL 200;
CLOSE CUR;
EXECUTE INTERFACE '$', SQL.TMPFILE, '-L', :$.GEN;
GOTO 250 WHERE NOT EXISTS (SELECT 1 FROM ERRMSGS
WHERE USER = SQL.USER AND TYPE = 'i');
EXECUTE BACKGROUND WINACTIV '-R', 'INTERFACEERR';
LABEL 250;
UNLINK GENERALLOAD;
LABEL 300;
  

The interface called has the same name as the procedure (that's the dollar sign) and is defined as follows:

Screen name	identifier	column name	field name
EREP	1	ENAME	TEXT13
REPCLMNS	2	POS	INT1
REPCLMNSHTML	3	ADDTITLE	CHAR1
REPCLMNSTITLEHTML	4	BGCOLOR	INT1

Colouring fields in reports

As usual, the SDK mentions the possibility of showing report fields in colour, but doesn't give the full explanation. You can change the font and/or font color of a specific report column, as well as determine its background color. This can be defined directly in the report, or indirectly, by designating another report column whose value determines the font or color. For example, use the Font Color Def. Column to specify the Col. Number of the report column whose value sets the font color. If the designated report column is hidden, that column must have a value in the Sort Priority column. 

This is about 90% of the story. Ignoring the final sentence for a moment, the way that I discovered how to use colours was to define a column that checks a condition and returns a value (e.g. ORDERS.CURDATE < SQL.DATE8 ? 0 : 3) then one uses this column as written above. With regard to the returned value, 0 is 'no colour' and 3 is red. The table HTMLCOLORS lists the various colour available and one can add more if necessary. One quickly discovers that hiding this column causes the target column not to be coloured. So I was taught that one displays the column in the report then creates a display format (right click on the menu option for the report) that hides the column. Not perfect, but it works.

I discovered an easier way to achieve the desired result; this may not work in versions prior to 21. One defines the condition column as before and assigns it a value in the Sort Priority column (normally 9 or 99). One runs the report: the column that is to appear coloured does so, but the condition column also appears. Now hide the condition column and run the report: the first column is still coloured whilst the condition column does not appear. There is no need to create a display format then hide the condition column.

The value of the sort priority is irrelevant (although it has to be higher than anything in the report otherwise it is liable to affect the display order of the lines) but it has to be there. As far as I can see, having a sort priority is a type of 'escape clause' for the report generator; it knows that it has to perform some magic if there is a sort priority and the column number appears somewhere in the HTML definitions sub-form. 

More on converting a currency string to a real (introducing STRINDEX)

As an astute comment pointed out, the code that I presented the other day for converting a currency string (like $1,234.56) to a real would have problems if the string represented an amount greater than one million. I was going to write that Priority has no STRPOS function; if it had one, then locating the commas and removing them would be simpler than my laborious code. 

I have just discovered that whilst there is no STRPOS function, there is a function called STRINDEX that is more or less the same. So if there was a string like '$1,234,567.89', it could be turned into a string that can be turned into a real with the following code.

SUB 850;
/* :ASTRING will be something like '$1,234,567.89';
*/
:ASTRING = SUBSTR (:ASTRING, 2, 24); /* Lose the leading dollar */
:AREAL = '';
:RESULT = 0E9;
:POS = 0;
LABEL 851;
SELECT STRINDEX (:ASTRING, ',', 1) INTO :POS FROM DUMMY;
GOTO 852 WHERE :POS = 0;
:AREAL = STRCAT (:AREAL, SUBSTR (:ASTRING, 1, :POS - 1));
:ASTRING = SUBSTR (:ASTRING, :POS + 1, 24);
LOOP 851;
LABEL 852;
:AREAL = STRCAT (:AREAL, :ASTRING);
:RESULT = ATOR (:AREAL);
RETURN;
  

Note that the $ sign is liable to be converted into the procedure's name (see here). Fortunately, in my case, the currency sign is ₪ which is ignored by the preprocessor.

Now that I know about the existance of the function STRINDEX, I am liable to use it whenever necessary. I doubt that I am going to update existing code but I will use it in the future. 

It turns out that STRINDEX exists only from version 21 onwards - and of course, the client who needs this code is running version 20.

More on the undocumented function ATOR

A year and a half ago, I wrote about the undocumented function ATOR that takes a string and turns it into a real number with a decimal point. A couple of days ago, I was dismayed to learn that the function wasn't working properly with an Excel file that someone had sent intended as input for an interface that uses ATOR. After looking at the file for a while, I realised that every line had four fields in the following format $1,234.56, whereas previously the fields had held 1234.56. ATOR chokes on this new input and so I had to write a new function. First, though, some examples:

  SELECT ATOR ('$1,061.25') FROM DUMMY FORMAT; >> error
  SELECT ATOR ('1,061.25') FROM DUMMY FORMAT; >> 0.010000  Why?
  SELECT ATOR ('1061.25') FROM DUMMY FORMAT; >> 1061.250000
  

So in order to parse the field (that was being passed as a string), first I had to strip off the dollar sign. It wasn't clear at first whether the dollar sign was at the beginning or end of the string, but it turned out to be the beginning. This was easy to do. Then I looked for the decimal point: anything before this is a number and should be read as is, the two digits after this are the digits after the decimal point and should be multiplied by 0.01. 

But this didn't work properly, either. It seems that the comma was causing problems, so first I had to find where the comma was - if at all - and make allowance for this. The final code is as follows

SUB 850;
/* AREAL is something like $ 12,345.67 */
:RTMP = 0.0;
:AREAL = STRIND (:AREAL, 2, 10); /* lose the $ */
SELECT STRPIECE (:AREAL, '.', 2, 1) INTO :TMP FROM DUMMY;
:RTMP = ATOI (STRIND (:TMP, 1, 2)) * 0.01; /* fractional part */
SELECT STRPIECE (:AREAL, '.', 1, 1) INTO :TMP FROM DUMMY;
SELECT STRPIECE (:TMP, ',', 1, 1) INTO :THOUSAND FROM DUMMY;
GOTO 851 WHERE :THOUSAND = :TMP;
:RTMP = :RTMP + (ATOI (:THOUSAND) * 1000.0) +
ATOI (STRPIECE (:TMP, ',', 2, 1));
GOTO 852;
LABEL 851;
:RTMP = :RTMP + ATOI (:THOUSAND);
LABEL 852;
RETURN;
  

This isn't something that I'm proud of, but at least it works. Before the subroutine is called, there is a check to see whether the field holds an 'ordinary' real like 1234.56 - this is accomplished with the ISFLOAT function, and if this returns 1, then ATOR is used on the field. Otherwise the subroutine is called.

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....

Sending email with data in the body of the letter

The standard method of sending reports via email causes the report to be sent as an attachment to the letter. Someone, though, wanted to see how to include data in the body of the email. Following is an example of what I call masochistic programming, as it is very tedious. Basically, one has to combine data from Priority along with HTML coding; I imagine that the skill of hand-written HTML code has long disappeared, and even if it were still extant, the majority of Priority programmers have never come across it.

OK: down to work. The following example will include some data from a customer order, whose internal number can be found in the variable :$.ORD. First off, one has to link a few tables, then insert what will be the header to the letter.

LINK GENERALLOAD TO :$.GEN;
ERRMSG 1 WHERE :RETVAL <= 0;
LINK MAILBOX TO :$.MBX;
ERRMSG 1 WHERE :RETVAL <= 0;
:LINE = 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT, TEXT2)
SELECT :LINE, '1', ORDNAME, :GROUP
FROM ORDERS
WHERE ORD = :$.ORD;

That wasn't too difficult, was it? But now things are going to be a bit harder. The words or codes enclosed by angle brackets are HTML tags - these are what accomplish the required formatting.

:LINE = :LINE + 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT)
VALUES (:LINE, '2', '<HTML><BODY><dir=ltr><P>);
:LINE = :LINE + 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT)
VALUES (:LINE, '2', STRCAT (
'Customer number: ', CUSTOMERS.CUSTNAME,
' Customer name: ', CUSTOMERS.CUSTDES,
' Order date: ', DTOA (ORDERS.CURDATE, 'DD/MM/YY'), '<BR>');
:LINE = :LINE + 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT)
VALUES (:LINE, '2', STRCAT ('<TABLE>',
'<tr><th>Line</th><th>Part number</th>',
'<th>Part description</th><th>Quantity</th></tr>');
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT)
VALUES (:LINE + SQL.LINE, '2', STRCAT (
'<tr><td>', ITOA (SQL.LINE), '</td><td>',
PART.PARTNAME, '</td><td>', PART.PARTDES, '</td><td>',
RTOA (ORDERITEMS.QUANT, 2), '</td></tr>')
FROM ORDERITEMS, PART
WHERE ORDERITEMS.PART = PART.PART
AND ORDERITEMS.ORD = :$.ORD;
SELECT MAX (LINE) INTO :LINE FROM GENERALLOAD;
:LINE = :LINE + 1;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT)
VALUES (:LINE, '2', '</TABLE></BODY></HTML>');

Now that the data is in the load table, it can be sent. First, one has to create a letter, then send it. The code below assumes that there is only one letter to be sent.

:EFILE = 'C:/TMP/2.TXT';
/* one has to define a suitable interface */
EXECUTE INTERFACE 'TEST_SENDMAIL', :EFILE, '-L', :$.GEN;  
SELECT MESSAGE INTO :PAR1 FROM ERRMSGS
WHERE USER = SQL.USER AND TYPE = 'i';
ERRMSG 99 WHERE :RETVAL > 0;
:MB = 0;
SELECT ATOI (KEY1) INTO :MB FROM GENERALLOAD
WHERE LINE = 1;
INSERT INTO MAILBOX
SELECT * FROM MAILBOX ORIG
WHERE MAILBOX = :MB;
:XMSG = '';
EXECUTE SENDMAIL :$.MBX, :XMSG;  /* send it */
UNLINK MAILBOX;
UNLINK GENERALLOAD;

Wishing you the best of luck! I should explain at least one line of cryptic HTML - the one that starts '<tr><th>Line</th>'.  <tr> means 'start a new line', <th> means a header column, Line is the name of the column, and the concluding </th> means that this is end of the column name. Then follows the same combination (without <tr>), terminating in </tr>, that means 'end of table line' (the mnemonics are: tr = table row, th = table header, td = table data).

I have finally discovered how to enclose (apparent) HTML codes in a blog post: the left angle bracket should be written in the HTML source as &lt; and the right angle bracket as &gt;