An undocumented function in Priority - ATOR

There have been times, especially when I am programming interfaces that read external files and turn them into Priority data, that I wish that there was an Ascii2Real function, presumably ATOR, in the same way that there is an Ascii2Integer function (ATOI) and an Integer2Ascii function (ITOA). We have RTOA but no ATOR.

I've overcome this in the past by writing a subroutine that receives a string holding a real number (:QUANT) and returns a real number (:RQUANT).

SUB 879;
:RQUANT = :TT = 0E9;
SELECT STRPIECE (:QUANT, '.', 1, 1) INTO :TMP FROM DUMMY;
:RQUANT = ATOI (:TMP) + 0.00000;
SELECT STRPIECE (:QUANT ,'.', 2, 1) INTO :TMP FROM DUMMY;
GOTO 102 WHERE :TMP = '';
/* This is the decimal part of the number. Figure out what the
divisor has to be by the length of the number: e.g. num=3, div=1;
num=30104, div = 5
*/
:TT = 0.00000 + ATOI (:TMP);
:DIV = 1;
:I = 0;
:LEN = STRLEN (:TMP);
LABEL 101;
:DIV = :DIV * 10;
:I = :I + 1;
LOOP 101 WHERE :I < :LEN;
:RQUANT = :RQUANT + (:TT / :DIV);
LABEL 102;
RETURN;

Whilst this code works, I would prefer a predefined function in Priority. Today I was looking at a very old program that I did not write and saw the use of the ATOR function! As I know that this old program has correct syntax - and is used at least ten times a day - I felt fairly safe in replacing my subroutine with a simple function call, :RQUANT = ATOR (:QUANT). I also checked the syntax in WINDBI; lo and behold, this function turns a string like '3.1234' into a real number with six digits of accuracy, 3.123400.

Why would this useful function be undocumented, especially as it has existed in Priority since 2006? I won't even try to guess.

Creating Excel files from Priority

A question that I see frequently asks how one can create an Excel file from Priority. There are two different answers to this question - one solution creates CSV files that can be read into Excel with no problem, and the other solution creates XLSM files that are native Excel files. The 'M' in this extension stands for Macro; one can include in these files a macro that executes on the data. Unfortunately, such files are often caught by email anti-virus filters, so sending them to third persons can be problematic.

For the purpose of this blog entry, I assume that there exists a report TEST_OUTPUT2EXCEL. It doesn't matter what the contents of this report are.

CSV method: this is very simple and creates, as expected, a CSV file. This means that the file does not allow any formatting of the contents.

:XLFILE = 'C:/TMP/TEST_OUTPUT2EXCEL.CSV';
EXECUTE WINACTIVF '-x', :XLFILE, '-R', 'TEST_OUTPUT2EXCEL';
:EMAIL = 'someone@somecompany.com';
MAILMSG 1 TO EMAIL :EMAIL DATA :XLFILE;

The first line defines a file name and the second line executes the TEST_OUTPUT2EXCEL report and sends the output to the denoted file. As far as I can figure out, the first parameter to WINACTIVF, i.e. '-x', means create an Excel/CSV file; the second parameter is the name of the output file; the third parameter is the report type: R = simple report, P = procedure, and the fourth and final parameter is the name of the report/procedure to be executed. The third line in the above snippet defines an email address and the fourth line shows a simple way to send a file to someone via email. The report itself does not have to be defined in any menu but the person executing the above code has to have permission for the report. This means that the report does not have to appear on a menu if it is executed solely by the task scheduler, but execution by 'normal' users requires the report to appear on a menu.

XLSM method: this is more complicated, naturally. There is a preliminary stage for this method that requires that the report appear on a menu: one has to create an Excel template for the report. One performs a right click on the menu option and chooses 'Create Excel template' then one runs the report. All the required formatting is done on the Excel file that is created, then one saves the file without changing its name, extension or location (the file name will be the name of the report and the extension XLT; the location will be ..\..\system\document). Once the template has been prepared, the report can be removed from the menu if it is not intended to be run manually. The template can be edited at a later stage, but again requires that the report appear on a menu.

The next stage is to find the template's number. There is no simple way of doing this; I opened WINDBI then typed 'SELECT * FROM EXCELTEMPLATES WHERE UDATE > SQL.DATE8 FORMAT'. This query will list all the templates that have been created today - there shouldn't be very many! The value of the EXEC field will be the EXEC number of the report. For the purposes of this blog, the template number will be 207, but I warn that this number has to be found empirically.

Once all the above has been done, the execution is simple:

:XLFILE = 'C:/TMP/TEST_OUTPUT2EXCEL';
EXECUTE WINACTIV '-R', 'TEST_OUTPUT2EXCEL', '-X', :XLFILE, 207; 
:EMAIL = 'someone@somecompany.com';
MAILMSG 1 TO EMAIL :EMAIL DATA STRCAT (:XLFILE', '.XLSM');

Here the order of the parameters seems more logical: type of report, name of report, Excel, name of output file (note that one does NOT give an extension) and number of template. This creates the file and adds the extension name; the extension has to be added automatically when sending the file by email, although as I noted at the beginning, such a file is likely to be stopped by an email anti-virus filter. This file will be formatted according to the format of the template file; one can also define a macro within the template file that is supposed to execute automatically when the file is opened (according to this page; I haven't tried this myself and there are likely to be problems executing macros on network files).

Update regarding how to find the template number: the template file will be in directory ..\..\system\document and its name will be something like TEST_OUTPUT2EXCEL_207.xlt, i.e. the name of the procedure or report for which the template was defined, followed by the number of the template. One can take the number directly from here instead of issuing a query via WINDBI.

Secret tip for tabular reports

In a regular report, the number of columns are fixed and the number of rows depends on the data that the report displays. In a tabular report, neither the number of columns or rows is fixed. In Excel, a tabular report is called a pivot report. The most common use of tabular reports is for displaying some datum over a year, where each column represents a month - it is easier to define what will appear in a column and have the tabular report framework do the hard work of displaying the correct data in each column than to define 12 columns and manually calculate the data per month. But a tabular report is not limited to displaying monthly data: it can be used for displaying data per something when one is not sure how many 'somethings' there will be - e.g. displaying sales per order type or branch.

One of my clients complained that a report that had displayed data from previous years was not working for the current year (this is not a report that I wrote). When I began investigating, I discovered that the report was tabular and that it was displaying data per department: the number of departments depended on the time period of the report (some departments only had data in one month). Some months would cause the report to display successfully, but one month in particular would cause the report to fail, displaying the error message "Too many X values". 

To me, this means that the report is trying to display too many columns. I know that there is a parameter used when defining tabular reports, "maximum table width", that is always 300 (I checked all the tabular reports defined in my system: some have the value 300 and some have 0) but does not seem to affect anything. My first suggestion was to shorten the department names: these are displayed in each column and maybe they are taking too much space. Changing the length of the names made no difference: the error message really means that there are too many columns to be displayed. As this is a problem with the data and not with the report, there wasn't much that I could do to help the client.

Showing initiative, the client eventually found a help page from Priority Software (the company) that deals with the error message; the solution is to change the value of the system constant TABLEMAXX (or TableMaxX, to make it clearer). This information was passed on to me for implementation. I first checked the 'system constants' form: no constant by this name appeared. The client is using a relatively old - but good - version of Priority and maybe the constant had been made visible in later versions. This left me no option but to add the above constant to the system constants table - something that is not possible via the system constants form. Thus I had to do this via the command interface, aka WINDBI.

INSERT INTO SYSCONST (NAME, VALUE) VALUES ('TABLEMAXX', 300);
INSERT INTO SYSCONSTA (NAME, ORIGVALUE) VALUES ('TABLEMAXX', 300);

The second line isn't required to change the value of the constant, but without it, the constant won't appear in the form.

After adding the constant, I ran the report for the problematic month, held my breath and ... the report worked! Magic! I then ran the report for an entire year and the data appeared. 

I got the impression that 300 is the maximum value for this variable; I hope that the client doesn't add any more departments for otherwise at some stage we are going to run into the same problem again.

And what is the unit of measurement for this constant: columns, characters or doodlebugs? At the moment, I don't care; all that matters is that the report now works and that the client is satisfied.

Edit from a few days later: The TableMaxX constant does not appear in the System Constants form in Priority 21, the current version, so even there it would have to be added manually. I suspect that the unit of measurement is columns.

Defining a dynamic target form name for a form column: ZOOM1

This blog will describe a technique that is documented in the SDK but could be written more clearly, as always.

In previous installments, we have seen how to define a dynamic form target for a field in a report. For example, a report might display data from both purchase demands and purchase orders, where one field shows either the purchase demand number or the purchase order number. Clicking on this field will cause the appropriate form to be opened in order to see in detail the purchase demand or the purchase order.

The same mechanism exists in forms, but the implementation is different. I developed a few years ago a form that can display invoices of different types ('A', 'C' or 'F') and allows editing of certain fields in the invoice. Pressing F6 on the invoice number opens an intermediate form, not the specific form for the specific type (AINVOICES, CINVOICES or FINVOICES). This annoyed me sufficiently to seek the solution, which is ...

One defines a form column named ZOOM1, whose value should be the 'exec' number of the required form (not the name of the target form). In the case of the invoices, the exec number is in IVTYPES.EXEC, and so the column name will be EXEC and the table name IVTYPES. This can be seen in the screenshot on the left. This form column is used in the IVNUM field: in the form column extension sub-form, the target form name is set to ZOOM1.

ZOOM1 is predefined so one does not have to define it somewhere. Using 'ZOOM' without a numerical suffix does not work! Apparently ZOOM2-ZOOM9 are also defined, should one need more than one target form for a given form.

I have another private form that displays data about all the programs that I have developed: their name, date of creation, notes and most importantly, for whom it was developed. This form can display reports, procedures, forms and interfaces, so a dynamic target form name is required should one wish to open the chosen program (and frequently I do). In this case, however, I don't want the EXEC number of the program itself, but rather the EXEC number of the form that displays the program: if the program is a report then the form EREP should be opened, and if the program is a procedure then the form EPROG should be opened. As the table EXEC is already being used in this form, I will need to alias the table and use this in what Pascal would call a 'case' statement:

(form column) EXEC1.ENAME
(expression) = (EXEC.TYPE = 'R' ? 'EREP' :
(EXEC.TYPE = 'P' ? 'EPROG' :
(EXEC.TYPE = 'F' ? 'EFORM' :
(EXEC.TYPE = 'I' ? 'EINTER' : 'NULL'))))

Another form column will be required: its name will be ZOOM1 and its value EXEC1.EXEC. I later discovered that the condition EXEC1.TYPE = 'F' should be added in order to prevent duplicate rows in the form (apparently there are two entries in EXEC with the name EREP: one is a form and the other is a menu).

Strange report bug

My company has a private table called TEST_BATCHES in which are stored 'batches', groups of work orders that are processed together in our new nesting machine. Generally one batch contains all the work orders required to manufacture 10 items of a given order line. We've recently discovered that the nesting machine produces a file that lists all the batches that it has processed in the past day and I have put this to good use, writing a procedure that extracts the relevant data from this file (not particularly easy) and reporting all the work orders as completed.

It happens that the file might have eight batches but only five are reported by this procedure, because three have already been reported manually. I wanted to add a little code to the procedure that would send me an email listing all the batches that are in the nesting file but not reported. I won't show how this is done because it is not particularly relevant; the unreported (or previously reported) batches are stored in a linked copy of STACK2 and this table is passed to the report that creates the email.

Every day I have been receiving an email listing all the batches that have ever been created (not that many!) which is clearly wrong. I checked the procedure, and STACK2 holds at most a few records at the end. I emptied the real STACK2 table in case this was causing problems; it wasn't. I checked the very simple report to see that there were no mistakes there - like marking TEST_BATCHES.BATCH with a question mark, so that all records would be shown in the report. No.

Today I had the idea of dumping the report in WINDBI. I was expecting to see the following

SELECT TEST_BATCHES.BATCHNAME
FROM TEST_BATCHES, STACK2
WHERE TEST_BATCHES.BATCH = STACK2.ELEMENT
AND TEST_BATCHES.BATCH > 0
AND 1 = 1;

What I saw, though, was something different that explained why the report contained all the records in TEST_BATCHES:

SELECT TEST_BATCHES.BATCHNAME
FROM TEST_BATCHES
WHERE TEST_BATCHES.BATCH > 0
AND 1 = 1;

Where had STACK2 gone to? After contemplating this for a few moments, I decided to add STACK2.TYPE to the report; lo and behold, dumping the report now showed the correct SQL query. Marking STACK2.TYPE as hidden removed STACK2 from the join statement again. As this field is always going to be zero (the procedure doesn't use it; it is already using STACK otherwise I would use this simple table), I compromised by marking the "don't display if zero" field. 

Tomorrow I expect to see only a few lines in the emailed report.

Considering this example, it's very much an edge case. I'm trying to think of other reports that might use only the key(s) of a table; most of my reports use STACK4 where both the key and data fields are displayed in the report. Otherwise there's no point in using STACK4 - I could just as easily use STACK. There might be a case of using only KEY1 and KEY2 of STACK8; I'll try and find such an example.

I did find an example of using only the keys of Stack8 and the above bug did not appear. I then created a new procedure with report that initially stored all the orders opened today in STACK.ELEMENT and then in STACK2.ELEMENT; the bug did not appear.

Moral of this story? Use the 'dump' function more often.