Injecting an English title for a report sent by TTS

I wrote¹ several months ago about the tedious process of adding English titles to reports and forms. That kind of work has settled down, but instead I've had to face some rather strange problems. The latest example is a report that is sent by email via TTS; the data is prepared by a procedure that sends the report with the EXECUTE WINACTIV command. The problem, as one might guess, is that when it is sent based on Indian data, the report is in Hebrew.

The immediate problem about the body of the report was fixed quite easily as the form for a TTS task has a 'language' field. When this was set to English, the body of the report was indeed sent in English (and left to right) but the title of the report stayed in Hebrew, despite my having defined a translation of the report title. When the report is run from the menu by a user, this English title appears.

I thought at first that there might be an undocumented flag for WINACTIV that would force an English environment; notionally such a flag would be -e, but this character is already used to signify that the report should be sent to an email address. I tried saving the output to a file (then send it on via MAILMSG) to see whether this would make any difference, but it didn't. There is no possibility of assigning a value to SQL.LANGUAGE; in comparison SQL.ENV holds the name of the current environment that can be changed by invoking the ENV function.

I'm not sure where the inspiration for my solution came from, but it occurred to me that all I needed to do was to update the title of the report in the EXEC table. So I added the following code towards the end of the procedure, after the data has been collected but before the report is sent.

GOTO 10 WHERE SQL.COUNTRY = 'ISR';
/* India */
:MSGNUM = 10;
:GROUP = 'INDIAGROUP';
GOTO 20;
LABEL 10;
/* Israel */
:MSGNUM = 20;
:GROUP = 'ISRAELGROUP';
LABEL 20;
GOSUB 900;
EXECUTE WINACTIV '$', SQL.TMPFILE, 'STACK', :$.STK, '-g', :GROUP;
GOTO 99 WHERE :MSGNUM = 20;
:MSGNUM = 20;
GOSUB 900;
LABEL 99; /* End */
/***********************************************/
SUB 900;
SELECT ENTMESSAGE ('$', 'P', :MSGNUM) INTO :PAR1 FROM DUMMY;
UPDATE EXEC
SET TITLE = :PAR1
WHERE ENAME = '$'
AND TYPE = 'R';
RETURN

Message 10 holds the English title whereas message 20 holds the Hebrew title. There's no point in resetting the title after the report has been created if it has already been set to Hebrew. This hack only works because the report is being sent by a spawned process; if it were run from the menu, the report is effectively already loaded into memory when the procedure starts and so run-time changes do not take effect (unless one is using the documented methods which aren't applicable here).

Internal links
[1] 119

The LOADFNC external program

This program takes the lines that are in the Interim Table-Journal Entries form and transforms them into journal entries. I needed to deal with this program as the next step in the long journey that began with the recursive FILELIST¹. There's one subtle pitfall with this program which I'll describe.

My procedure takes the results from the FILELIST program, connects to a given environment and loads a file found by FILEIST into the interim table. The sequence of commands is as follows

:TOFILE = STRCAT (SYSPATH ('LOAD', 0), :COMPANY, '\loadfnc.txt');
SELECT MESSAGE INTO :FN 
FROM STACK_ERR
WHERE LINE > 0 
AND STRPIECE (MESSAGE, '/', 1, 1) = :COMPANY
AND INTDATA2 > 30;   /* size of file - exclude dummy.txt */
:FNAME = STRCAT (:CHKDIR, '/', :FN);
EXECUTE MOVEFILE :FNAME, :TOFILE;
EXECUTE DBLOAD '-L', 'loadfnc.txt', '-E', 'loadfnc.err', SQL.TMPFILE;

The above is actually run in the framework of a cursor, but for simplicity, I am assuming that there is a single file. TOFILE is the name of the destination file; its name has to be the same as the name of the interface. The documentation says that such a file can either be in the system/load directory or in a subdirectory of system/load according to company/environment name. Once the file is found, FNAME restores the directory structure of the filename that was removed initially prior to the FILELIST command, and then FNAME is copied to TOFILE. Once copied, this file can then be uploaded via the DBLOAD program.

This first half of the program went well but the second half had me stumped. One can find a menu option that runs LOADFNC and there is the trigger LoadAccAE2 of the 'func' table that runs LOADFNC. There are three parameters that appear to be a message, the environment's name and a linked table of users. The last parameter is probably the easiest to create. The command in LoadAccAE2 is EXECUTE LOADFNC SQL.TMPFILE, :DNAME, :USERLNK. The first and third parameters were easy to figure out but the second parameter seemed problematic. DNAME is one of the fields in the ENVIRONMENT table so I thought that this was the name of the current environment. There was some code in the menu option LOADFNC2 which I didn't initially understand that would have helped me.

In order to figure out what the parameters to LOADFNC needed to be, I manually loaded some data into the interim table then ran the menu option. I received an error message saying that 'test' (the value that I had stored in the the interim table's 'source program' field) had not been defined in the Definition of Load Parameters form. Once I added 'test' to this table, I was able to run LOADFNC successfully. At this time, the code in the menu option became clear: DNAME is not the name of the environment, but the value stored in the 'source program' field. Not only that: this value has to be the same in all the lines being loaded. This was the pitfall of which I was not aware.

My test data had created a temporary journal entry that would have to have its status changed to 'final'. In order to find the entry, I would have to read the value of a certain field in the interim table to find this value in the journal entry, but successful loading of lines into journal entries removes the lines from the interim table. I thought at first that I would have to copy the interim table before loading then iterate over it, finding the required values, but I discovered that one of the flags in the Definition of Load Parameters form causes the newly entered journal entry to be finalised automatically. So there was no need to copy the interim table.

I have to figure out what to do if a file cannot be loaded successfully into the interim table or data cannot be turned into journal entries. Supposedly DBLOAD can create a file with data that did not get loaded, but if this program is running in a loop, that error file is going to be overwritten. Similarly, if LOADFNC can't turn data into a journal entry, the data will be left in the interim table, but this again will be overwritten with the data from the next file. I could attach the contents of the table to an email, but the recipient would have to enter the data manually into the interim table.

Internal links
[1] 122