Some Priority internals (SCREENMSG)

I had developed a form for a client and I wanted to document it. There are reports that purport to do this but they don't always give all the information, and my improved report doesn't exist on the client's computer. So I used the WINDBI Dump capability; one can dump a form and one can dump a form's cursor; the difference between the two is that the latter only gives the SQL query that populates the form whereas the former also gives triggers.

Looking at some of this information, my eye was caught by this statement:

SELECT 192 INTO :SCREENMSG

FROM DUMMY WHERE :RETVAL = 0;

I wondered what this could mean. Could it be that :SCREENMSG is a special variable in the same way that :ERRMSG is? Storing a number in :ERRMSG will cause the corresponding error message (from the procedure) to be displayed. So presumably the above statement will cause message 192 to be displayed on the screen. But where is 192 coming from? There were similar statements but with different numbers.

Maybe, I thought, there is a table called SCREENMSG or SCREENMSGS from which the value is automagically retrieved. And so it is: the table is SCREENMSG and value 192 is (translated into English), 'Value does not exist in the table'. I looked at all the values in the table and have to admit that I could not imagine any personal use for them.

At least another internal has been explained.

Determining if the ouput of an HTML document is a printer, the screen or email

Due to legal requirements, delivery notes and invoices have a field 'Printed' that determines when the document is printed whether the word 'original' or 'copy' should appear in the output. Displaying the document on the screen always results in 'copy'. A client wanted a similar mechanism but for customer orders - they want to know if  the order has been printed at any stage.

In an HTML document, the first three stages are usually INPUT, HTMLCURSOR and SQLI. In that third stage, the code for determing which text should be added ('original'/'copy') can be seen, but there is nothing in the document that says whether the document is being printed or displayed. Looking though the SDK, there's nothing explicit on this topic, although there is a hint.

Saving a Certified Copy when Printing a Document

You can define a procedure so that, when the document is printed or sent by e-mail (:SENDOPTION = 'PRINT' or 'AMAIL'), a certified copy is saved that can be printed later (using the Create Certified Copy program).

In order to save a copy:

• Include an HTMLEXTFILES step which goes over the attached files.
• In the first INPUT step, set the :HTMLPRINTORIG variable to 1.
• In the SQLI step after the HTMLCURSOR step, set the :SAVECOPY variable to 1.

These variables are updated during runtime, so that if several documents are being printed together, the variables will be set for each individual document, depending on its status.

So it would seem that the undocumented system variable :SENDOPTION is the key to the client's request. I added a little code to the SQLI stage of the procedure WWWSHOWORDER that would save the value of :SENDOPTION in a table of private constants. From this I was able to ascertain that when printing, the value is 'PRINT' and when displaying on the screen, it's 'DISPLAY'. Presumably when sending email the value is 'AMAIL' and for fax (??) it's probably 'FAX', although I don't need to know this.

For my purposes (or for the client), I have to update the private field 'PRINTED' in the ORDERS table if SENDOPTION is not equal to 'DISPLAY'. I think it better to save a username and date/time than just the fact that the order has been printed, but that's only my opinion.

I can't imagine any further use for this undocumented variable, but I vaguely recall that once I would have liked to have known its value, although for what, I do not remember.

Recursive FILELIST

The SDK documents under heading Browsing the Contents of a Folder (page 233 in my copy of the V23 document) how to obtain a list of files in a folder. 

:DIR = '../../tmpDir'; 
SELECT SQL.TMPFILE INTO :ST6 FROM DUMMY; 
SELECT SQL.TMPFILE INTO :MSG FROM DUMMY; 
EXECUTE FILELIST :DIR,:ST6,:MSG; 
/* In the linked file of the STACK6 table, you will find all files
and folders under the input directory :DIR. */ 
LINK STACK6 TO :ST6; 
GOTO 99 WHERE :RETVAL <= 0; 
DECLARE NEWFILES CURSOR FOR 
SELECT TOLOWER(NAME) FROM STACK6 
WHERE TOLOWER(NAME) LIKE ' loadorder*'; 
OPEN NEWFILES;
...

I've written code based on this to poll a specific directory; if a file is found, then the procedure performs some action based on this file (e.g. reads the file and uses it as input for an interface). At the end, the procedure deletes the file so that it won't be found again.

This is all well and good when Priority is hosted on a company server, but is problematic when the web interface is used and Priority is hosted 'in the cloud'. The new online SDK discusses this scenario and states that The system/sync folder is a special folder available in Priority installations on the public cloud. It provides a location, accessible by SFTP, where users can upload files from an external source. The folder behaves a bit differently than regular Priority folders. I am working with a client for whom some external company is uploading files to a subfolder of system/sync; the client wants that these files (or rather, a reference to these files) be stored as an attachment to a financial journal entry. I tried using the 'standard' FILELIST code as written above but this was not bringing me any joy.

After approaching Priority Software, an undocumented feature of FILELIST was revealed: in order to traverse system/sync, one has to perform a recursive search by means of the flag '-R'. There is also another flag whose meaning escapes me at the moment, '-d'. Most importantly, instead of linking STACK6 to the results, one links STACK_ERR. It's not clear to me why this change is required: STACK6 has the fields NUM (equivalent to STACK_ERR.LINE), TYPE (equivalent to CHARDATA) and NAME (equivalent to MESSAGE), where the length of NAME is 100 characters. But the proof is in the pudding, and a recursive search does not work with STACK6.

Here is the required code when using system/sync as written for WINDBI as a test:

SELECT SQL.TMPFILE INTO :ST6 FROM DUMMY;
:CHKDIR = SYSPATH ('SYNC', 0);
EXECUTE FILELIST :CHKDIR, :ST6, '-R', '-d', SQL.TMPFILE;
LINK STACK_ERR TO :ST6;
SELECT LINE, CHARDATA, MESSAGE FROM STACK_ERR FORMAT;
UNLINK STACK_ERR;

This code does provide the names of files in the target directory.

While writing these words, I've had a problematic insight: if a procedure traverses the given folder and find files, it will attach them to journal entries. The next time the procedure runs, the same files will be found - it seems possible that one can attach the same file twice to some entity! The keys of the EXTFILE table are IV, TYPE and EXTFILENUM, and a quick test shows that indeed one can attach the same file more than once to the same entity! Obviously I will have to implement some form of check to prevent this; as opposed to the polling code that deletes the file after handling it, here the files have to remain 'forever'. No date information seems to be passed so one can't use this as a filter.

Importing problematic data from an external source into Priority

I was recently faced with the challenge of writing a procedure that would import data from an external source and then build an "over the counter invoice" from that data. The procedure has several stages, but basically it involves the following:

1. Copying a source file from wherever it is to a given filename in ../../system/load where the 'given filename' is the name of an interface
2. Executing a table interface whose name is according to stage 1. This interface places the data into the table LOADORDERS for the sake of convenience.
3. Optional: visually checking the data in the LOADORDERS form to ensure that they are correct (for example, Hebrew is written right to left, quantities). This is necessary only in the development stage.
4. Reading the data from LOADORDERS into GENERALLOAD then executing a form interface on this data in order to create the invoice.

I've done this several times before so it doesn't faze me. But when I started running tests on the procedure, all kinds of error messages starting appearing, mainly concerned with the data that was entered into the invoice's lines. Some of these errors are standard (e.g. insufficient inventory) and some appear to be local to the client. After asking for guidance, I was told to try and ignore all of these errors and so a invoice would be created with lines that may be problematic.

Part of the documentation for flags concerning interface reads: Ignore Warnings — Priority forms generate two types of messages: errors and warnings. Leave this column blank if you want the INTERFACE program to treat warning messages as errors. To ignore warning messages, flag this column. Note: The same purpose is served by the –w parameter, which can be included during the form load [emphases mine].

So one can ignore warning messages, but there is no option to ignore error messages. But of course, the client was asking me to make the procedure/interface ignore those messages. It turns out that there is a badly documented (if at all) method for doing this: enter the :FORM_INTERFACE and :FORM_INTERFACE_NAME standard variables. One can add or update a trigger in the target form (in this case, EINVOICEITEMS) that will skip a check if the trigger is called by any interface or by a specific interface only.

GOTO 1 WHERE :FORM_INTERFACE = 1;
ERRMSG 1 WHERE [some check is true]
LABEL 1;
GOTO 2 WHERE :FORM_INTERFACE_NAME = 'TEST_TEST';
ERRMSG 2 WHERE [some other check is true]
LABEL 2;

In other words, assuming that the above is part of a PRE-INSERT trigger, the first check will be skipped if the data is being entered by any interface, whereas the second check will be skipped only if the interface name is TEST_TEST. This is fine where the form is privately developed, but problematic if the form is standard; whilst it is possible to alter the PRE-INSERT trigger by adding code to skip over check, such a change is liable to overwritten when the Priority installation is upgraded.

So what can one do to skip over the tests in a standard PRE-INSERT trigger? There is a very simple solution: first, one creates a trigger that will be executed before the PRE-INSERT trigger, e.g. AAAA_PRE-INSERT, then this trigger is defined with the following line:

END WHERE :FORM_INTERFACE_NAME = 'TEST_TEST';

My simple test showed that the standard PRE-INSERT trigger was skipped, although the internal PRE-INSERT trigger was executed - this enters the data into the database.

Obviously I have to check this solution on the client's site but all the signs are positive.
-Update: well, it didn't work as I expected on the client's site, but fortunately the primary error that I was trying to avoid was in a private trigger, so I used the first technique of bracketing the check (actually a call to a private buffer on a different form) with the 'goto 310325 where :form_interface_name = 'test_test' statement and the label 310325.

An undocumented command - GENMSG

Maybe I'm inattentive* but it seems that I never noticed until a few days ago that there are two son forms to the 'step query' form (that in itself is a son form of Procedure Steps that is a son form of the procedure generator): 'Procedure Messages' and 'General error messages'. Maybe the son form has always been there.

Anyway, today my curiosity was piqued and I started to investigate what data this form displays and how it can be used. Both son forms are based on the EXTMSG table whose primary key is based on two fields, EXEC (the name of the form) and NUM (the message number). It turns out that there are only two general messages, both of which say something like 'An error has occurred; please contact the system manager'. I can't remember at the moment how to get the English version of these messages but that's not too important.

How does one display these messages? Here a little ingenuity is called for. I fired up the WINDBI program, chose 'Queries' then 'Find string' and wrote 'GENMSG' (that's the name of the form) before pressing enter. This useful functionality displays wherever the search string is used, be it a form trigger or a procedure. Unfortunately it doesn't work too well with strings composed of more than one word so one often gets the message back that there are too many results for all to be displayed. Of course normally the results that one wants are those that aren't displayed. Fortunately GENMSG doesn't return that many results. Here's an example of the output

ORDERITEMS           BUF69                GENMSG 1 WHERE :RETVAL <= 0;   

Obviously one uses the GENMSG command instead of WRNMSG (or ERRMSG) when one wants to display one of these messages. To be honest, this isn't very useful as I prefer the error message like 'An error has occurred' to show the name of the procedure or trigger where the error occurred so that I can immediately find the source. Otherwise one has to depend on users giving an accurate answer.

This GENMSG functionality would be useful if one could define one's own general messages instead of having to define them in every procedure. For example, I could define a message with the value 599 and the text '<P1> <P2> <P3>' so that I wouldn't have to define this string every time but simply write GENMSG 599 whenever I want some debugging information. This isn't such a good idea as I suspect that GENMSG is equivalent to ERRMSG, not WRNMSG. But I could use it for my version of error 1 that includes the procedure name. 

The value of EXEC for these strings is 208799, and getting that was slightly different from the usual method. The GENMSG form has a pre-form trigger that I copied into WINDBI in order to get the number.

:GENMSGEXEC = 0;
SELECT EXEC INTO :GENMSGEXEC
FROM EXEC
WHERE ENAME = 'GENMSG'
AND TYPE = 'C'
;

I would have expected the EXEC type to be F, not C (curiously enough, there is an EXEC number where TYPE = 'F' but it doesn't lead to the correct error messages). *As a side effect, knowing the EXEC number allows me to discover when this form was added: during the upgrade to version 23.1. It may or may not be in version 22, as we went straight from v21.1 to v23.1. I do know that my first addition with an EXEC higher than 208,799 was added on 18 April this year, after the upgrade. So I wasn't as unobservant as I thought at the beginning of this blog entry.

Saving a report in HTML format

Until recently, I've never needed to save a report (i.e. create a disk file) in HTML format - or maybe I've always managed to dodge my way around this requirement. Recently, though, I was asked to save a report that I distribute via email in PDF format to save it also as a disk file in HTML. As the report in question is actually an 'HTML document', this is documented as using two extra parameters to the WINHTML program ('-o', presumably meaning 'output' and the output file).

LINK ORDERS TO :$.PAR;
INSERT INTO ORDERS
SELECT * FROM ORDERS ORIG
WHERE ORDNAME = 'KL210001';
:FNAME = '//server/Sharing/AutoReports/$.HTM';
EXECUTE WINHTML '-d', 'TEST_HTMLFURNDAILY', 'ORDERS', :$.PAR,
'-o', :FNAME
;
UNLINK AND REMOVE ORDERS;
LINK ORDERS TO :$.PAR;
INSERT INTO ORDERS
SELECT * FROM ORDERS ORIG
WHERE ORDNAME = 'KL210001';
:FNAME = '//server/Sharing/AutoReports/$.PDF';
EXECUTE WINHTML '-d', 'TEST_HTMLFURNDAILY', 'ORDERS', :$.PAR,
'-pdf', :FNAME;
SELECT ENTMESSAGE ('$', 'P', 20) INTO :SUBJECT FROM DUMMY;
:GROUP = 'FURNDAILY';
LINK GENERALLOAD TO :$.GEN;
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT, TEXT6)
VALUES (1, '1', :SUBJECT, :GROUP);
INSERT INTO GENERALLOAD (LINE, RECORDTYPE, TEXT)
VALUES (2, '3', :FNAME);
EXECUTE INTERFACE 'TEST_SENDREPORTEMAIL', SQL.TMPFILE, '-L', :$.GEN;
SELECT ATOI (KEY1) INTO :MB
FROM GENERALLOAD
WHERE RECORDTYPE = '1'
AND LOADED = 'Y';
LINK MAILBOX TO :$.MBX;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO MAILBOX
SELECT * FROM MAILBOX ORIG
WHERE MAILBOX = :MB;
EXECUTE SENDMAIL :$.MBX, :XMSG;  /* send the letter! */
UNLINK MAILBOX;
UNLINK GENERALLOAD;
UNLINK ORDERS;

Today I was asked to do something similar, but in this case, the report is created by a normal multi-step procedure (i.e. not an HTML document), and so there is no use of WINHTML. I looked at the documentation for saving a normal report as HTML - this uses WINACTIV.

The :F variable implies that the report will be saved to a file with this name, but this variable is not used! I looked at the SDK version on the Internet and exactly the same code appears. THIS IS A MISTAKE! The correct code is as follows:

:FNAME = '//server/Sharing/AutoReports/$.HTM';
EXECUTE WINACTIV '-R', 'TEST_HTMLFURNDAILY', 'ORDERS', :$.PAR,
'-o', :FNAME;  

In other words, in the same way that WINHTML receives an extra two parameters when saving a report to disk, WINACTIV does also, only that this is not documented.

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

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.

Another undocumented variable in Priority - :ERRMSG

I was faced today with the problem of trapping a certain condition when a line is entered into a form by means of an interface. I want to check all lines of a certain type that a condition does not occur; if it does, then I want to output an error message and stop the procedure. My initial code was as follows:

ERRMSG 353
FROM TEST_LOADSIFORDERS
WHERE USER = SQL.USER
AND RECORDTYPE = '2'
AND U1 = ''
AND PARTNAME = '000'
AND STRIND (TEC_PN, 1, 8) = 'ASSEMBLY';

The only important thing here is the first line: if the clause is true, then error message 353 will be displayed. The error message itself says that a line has something missing. Unfortunately, this error message doesn't say on which line the error occurred (which wouldn't be useful, either) or which part is incompletely defined. It would be good if some identifier (TEC_PN) could be shown in the error message, but this concise syntax doesn't allow this. Another possibility is a loop, but that's something that could/should be avoided.

Then I remembered  that I once wrote about two undocumented variables that can be useful in designing screen triggers, :GOTO and :WRNMSG. These two variables were used in a similar context: if some condition occurred, then the appropriate warning message would be displayed and execution would transfer to the label stored in :GOTO. I wondered whether there is an undocumented variable, :ERRMSG ... and it turns out that there is. So my code is now

SELECT 353, TEC_PN INTO :ERRMSG, :PAR1
FROM TEC_LOADSIFORDERS
WHERE USER = SQL.USER
AND RECORDTYPE = '2'
AND U1 = ''
AND PARTNAME = '000'
AND STRIND (TEC_PN, 1, 8) = 'ASSEMBLY';

with the error message containing both unchanging text and P1, thus killing two birds with one stone. The disadvantage of this code as opposed to a loop is that the above ignores the possibility that two lines might have the error. I don't have any statistics at hand but I imagine that this error occurs about once every two hundred lines, which is why I don't want to slow down the 199 lines that are correct.

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.

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.