L-Type Records

The problem: How do I join two tables in OpenQM?

Solution: L-Type Records

Let’s say we have a couple of tables files, SKUS and VENDORS

LIST DICT SKUS Page 1
@ID......... TYPE LOC........... CONV.. NAME........ FORMAT S/M ASSOC...
@ID D 0 SKUS 10L S
DESCR D 1 Description 24L S
VENDORS D 2 8R M
VDR L VENDORS VENDOR
S

4 record(s) listed

LIST DICT VENDORS Page 1
@ID......... TYPE LOC........... CONV.. NAME........ FORMAT S/M ASSOC...
@ID D 0 VENDORS 10L S
COMPANY D 1 32L S
ADDR D 2 Address 24L M

3 record(s) listed

According to the OpenQM documentation, the four fields of an L-Type record are:

1: L { descriptive text  }
2: Id expression
3: File name
4 { File expression }

The key here, pun intended, is that we need to link an expression in one file to another. We aren’t limited to simple keys here… we can actually build complex expressions. The example in the documentation and given in the demo files extracts a key defined in the file as 3-7 while it links to the @ID of the target file, which is needs just the left half of the expression. Now, in my own example here, I’m using just a simple key to join the two files so that the VENDORS field in SKUS and the id field of the VENDORS file will match. When we LIST  the SKUS file, we can list the vendor information as follows:

LIST SKUS DESCR VDR%COMPANY
SKUS...... Description............. Company.........................
5192       STAPLER, RED             BOBCO LTD
                                    VILE INDUSTRIES
2121       STAPLER, BLUE            VILE INDUSTRIES

2 record(s) listed

And just like that, it works.

Now, I’m missing a piece of the puzzle here, because if you list both the company name and address in the skus listing, it has no association between the two values and ends up looking like this garbage:

list skus DESCR VDR%COMPANY VDR%ADDR
SKUS......   Description.............   Company.........................   Address.................
5192         STAPLER, RED               BOBCO LTD                          JOBO MCDUGNUTS
                                        VILE INDUSTRIES                    ACCOUNT EXECUTIVE
                                                                           BOBCO LTD
                                                                           1010 THREE TWO ONE ST
                                                                           NOWHERESVILLE EX 00000
                                                                           SNAKE SLITHERY
                                                                           PRESIDENT
                                                                           VILE INDUSTRIES
                                                                           814 W CATAHULA AVE
                                                                           TOXIC DUMP EX 00100
2121         STAPLER, BLUE              VILE INDUSTRIES                    SNAKE SLITHERY
                                                                           PRESIDENT
                                                                           VILE INDUSTRIES
                                                                           814 W CATAHULA AVE
                                                                           TOXIC DUMP EX 00100

2 record(s) listed

Hrm…