Multi-company forms

Yesterday I was asked to create a multi-company form. I know how to create a multi-company report but I had yet to create a form like this. Here are the conditions for a multi-company report:

• A displayed column, with a Column Name of TITLE and a Table Name of
ENVIRONMENT.
• A hidden column, with a Column Name of DNAME and a Table Name of
ENVIRONMENT. Its Expression/Condition should be: = SQL.ENV

Naively, I added these fields to the form. As it happens, my customer wanted the company name to appear anyway, so ENVIRONMENT.TITLE was necessary. When I opened the form, there was massive duplication of data (a cartesian join); basically there were no conditions on the Environment table which is why everything appeared several times.

I then turned to the SDK; as it happens, I have a new copy of the SDK for version 25.1 that documents many things that previously were not documented, but unfortunately there is very little about multi-company forms. There is a section heading that reads simply To prevent users from defining a given form as a multi-company form, specify x in the Oneto-many column, but it doesn't mention exactly how to create one as opposed to preventing one. There are a few more mentions about multi-company forms but these are about variables and not relevant.

That one sentence did give me an idea, though. I went to the one-to-many column in the form header and saw that there was an option 'm' that defines a multi-company form. Choosing that option gave me the message 'There must be a field called ZOOMDNAME, a character string of length 8 characters. I guessed that this should replace the field with the column name DNAME (even though the field name is still DNAME as this is the name of the field within the ENVIRONMENT table), and that the TITLE column should be replaced by ZOOMDTITLE. 

Lo and behold, the cartesian join disappeared and the correct data appeared. So this functionality is still undocumented. There are at least 20 standard forms with the 'm' flag (the actual number depends on the version) so one can learn from these.

Writing multi-environment procedures

I have been tasked a few times to write a procedure that iterates over all the active environments and saves data in a special table that is going to be accessed by an API. There are some special gotchas that need to be overcome; the following will not work

DELETE FROM MYTABLE;
SELECT SQL.ENV INTO :HOME FROM DUMMY;
:LINE = 0;
DECLARE C1 CURSOR FOR
SELECT DNAME
FROM ENVIRONMENTA
WHERE ACTIVE = 'Y'
AND DNAME <> '';
OPEN C1;
GOTO 300 WHERE :RETVAL <= 0;
LABEL 100;
FETCH C1 INTO :NAME;
GOTO 200 WHERE :RETVAL <= 0;
ENV :NAME;
INSERT INTO MYTABLE (.................)
;
LOOP 100;
LABEL 200;
CLOSE C1;
LABEL 300;
ENV :HOME;

Presumably there is a form based on table MYTABLE; initially all the data is wiped, then the procedure iterates through the environments and saves whatever data need to be saved. At the end, the procedure returns to the initial environment (:HOME) and presumably the data is displayed in the appropriate screen. Unfortunately, as I noted before the code, this doesn't work: the MYTABLE table will have different instances in each environment, or in other words, MYTABLE in environment A is not the same as MYTABLE in environment B. The following will work

SELECT SQL.TMPFILE INTO :ROL FROM DUMMY;
LINK MYTABLE TO :ROL;
SELECT SQL.ENV INTO :HOME FROM DUMMY;
...
LABEL 300;
ENV :HOME;
DELETE FROM MYTABLE ORIG;
INSERT INTO MYTABLE ORIG
SELECT * FROM MYTABLE; /* the linked table */
UNLINK AND REMOVE MYTABLE;