Continuing the MAILMERGE topic

After successfully developing the letter, my next task was to write a procedure that would be executed as a direct activation from the orders screen and would send the letter via email. My original code was something like this

LINK ORDERS TO :$.PAR;
ERRMSG 1 WHERE :RETVAL <= 0;
:FNAME = 'X:/1/TEST_SENDORDLETTER.pdf';
EXECUTE WINHTML '-d', 'TEST_ORDLETTER', 'ORDERS', :$.PAR, '-pdf',
:FNAME;
:EMAIL = '....';
MAILMSG 10 TO EMAIL :EMAIL DATA :FNAME;

I'm leaving out all the changes that were added later - I ended up using the interface for mail messages instead of MAILMSG and that's not really relevant.

Every time that I tried this, the procedure worked fine and sent the mail containing the letter. Every time the user wanted to run it, the letter was created but contained only with the logo and signature: the important part of the letter was missing.

It took some time to figure out what was missing: I had originally run the mail merge program and had defined what the format was to be. Someone running the 'envelope' procedure as a direct activation would not get the chance to define the format of the mail merge.  OK: there is a paragraph in the SDK that shows how to overcome this -

/* Set print format */
:EXEC = 0;
SELECT EXEC INTO :EXEC FROM EXEC
WHERE TYPE = 'P' AND ENAME = 'TEST_ORDLETTER';
UPDATE PRINTFORMAT
SET VALUE = -102  /* required format */
WHERE EXEC = :EXEC AND USER = SQL.USER;

I've used the same code (albeit with a different ENAME and format) for a similar program and there this code worked fine. But the print format was still not being set for the user. At first, I thought that the user would have to run the mail merge procedure once from the menu in order to set the print format, which is when the light finally went on in my head. The above code will fail because there is no record in the PRINTFORMAT to update! So I added the code to insert the necessary values into that table; if the insert succeeds, the code jumps over the 'update' statement. Looking at this again, the 'update' statement should come first, and only if this fails should there be the 'insert'. 

The above is not written in the SDK!

There are two pitfalls here: (1) The inner procedure has to appear in a menu that the user has permission for; (2) The format has to be set.

The Letter Generator and Mail Merge

One of my coworkers requested a few days ago that I create a form letter for a given customer order in which certain data from the order would be displayed. I wasn't initially aware of any possibility of doing so in Priority, but after a quick look through the SDK, I discovered that the possibility not only exists but is seemingly simple.

As usual, it was very difficult to understand anything from the SDK, so what follows is my explanation. The goal is to create a form letter with data fields, like this

Dear <customer name>, <address> <contact's telephone number>

With regard to your order <purchase order number>, we are pleased to inform you that we intend to supply the order on <supply date>.

How does one do this? My explanation is as follows: 

First, one creates a report that contains all the necessary fields for the mail merge. The standard example is a report called CUSTLETTERREP; this report can be used whenever one wants mail merge based on customers. As I needed it for customer orders, I had to create a similar report that is based on ORDERS. One creates what seems to be a standard report, but with no parameters; these are supplied by joining ORDERS.ORD to a field called LETTERSTACK.KEY1. Also, LETTERSTACK.USER should be the current user.

Once this is done, one copies the procedure CUSTLETTER to a new procedure (let's call it TEST_ORDLETTER) that is very similar to an HTML document. There are a few changes that need to be made: 

1. The Input stage should reference ORDERS.ORDNAME, not CUSTOMERS.CUSTNAME
2. HTMLCURSOR now becomes SELECT ORD FROM ORDERS WHERE ORD > 0
3. SQLI (stage 30) now becomes

   
       /************ Get ORD from HTMLVALUE by priform ************/
   :$.ORD = ATOI(:HTMLVALUE);
   :$.CLN = 'ORDERS.ORD';
   INSERT INTO LETTERSTACK (USER, KEY1, KEY2, STATUSTYPE)
   VALUES (SQL.USER, :$.ORD, 0, 'O');
   SELECT ORDNAME INTO :$.NUM
   FROM ORDERS ORIG
   WHERE ORD = :$.ORD;
   

4. The final stage is now TEST_ORDERSREP instead of CUSTLETTERREP

Maybe that sounds very complicated but it's actually reasonably simple.

Then one goes to the 'letter generator' program which is here: CRM > Customer Relations Management > Letter Generator. Here one defines the name of the desired letter and the name of the procedure that will create the letter (in this case, TEST_ORDLETTER). If this is the first time that this procedure has been listed, there will be a pause of a few minutes before one can continue.

In the son form, 'free text', one obviously enters the form letter. I wondered at first how one selects the fields that appear in the letter: look carefully and one will see that there is a new icon that has been added to the icons in the text form. To the right of the picture of a diskette now appears an icon that looks like a rubbish bin (that can't be right!); pressing on it brings up a list of fields that were defined in the TEST_ORDERSREP report. This works in the same way that one can add a field to be displayed in a business rule.

I have designed my procedure to be called as a direct activation from the ORDERS screen but it doesn't have to be. However the procedure is invoked, a list of possible formats is displayed from which one must choose (in the same way that one chooses a format for an HTML document) - the names of these formats are the names of the desired letters than one entered in the letter generator.

Now one can read the documentation in the SDK and make sense of it.