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