A multiple projection is a method of processing secondary files to create detail records. Multiple projections, which can be used only when the files are in a tree relationship, enable you to create records first from one file and then from the second file, instead of combining data from both files.
Hierarchical relationships
One or more secondary files linked indirectly to the primary file define a hierarchical relationship (File A links to File B, which links to File C).
When hierarchical relationships are processed, one detail record is created for each possible combination of matching key values in all files. For example, given the key values in figure 1:
|
|
these detail records would be generated:
A1, B1a, C1a A1, B1a, C1b A1, B1a, C1c A1, B1b, C1a A1, B1b, C1b A1, B1b, C1c A2, B2a, C2a A2, B2a, C2b A2, B2b, C2a A2, B2b, C2b
In hierarchical relationships, the total number of detail records created for a given primary file key value is the number of records for that key value in each of the secondary files, multiplied together. For instance, in the example above, the total number of detail records for a primary file value of “1” is 6 (the number of matching “1” records from File B multiplied by the number of matching “1” records from File C, or 2 * 3 = 6).
Tree relationships
One or more secondary files linked directly to the primary file create a tree relationship (File A links to File B and File A links to File C).
When tree relationships are processed, one detail record is created for each occurrence of a matching key value in either of the secondary files. For example, given the key values in figure 2:
|
|
these detail records would be generated:
A1, B1a, C1a A1, B1b, C1b A1, <null>, C1c A2, B2a, C2a A2, B2b, C2b
In tree relationships, the total number of detail records created for a given primary file key value is the maximum number of records with a matching key value in any one of the secondary files. For instance, in the example above, the total number of detail records for a primary file value of “1” is 3. (The maximum number of matching “1” records from any one of the secondary files is 3, from File C.)
Multiple projections enable you to alter the default detail record creation logic for tree relationships. (See Defining a multiple projection for instructions on setting up a multiple projection.) These are the requirements for using multiple projections:
- Two or more secondary files must be selected.
- These secondary files must be on the same level of the tree.
- The relationship between the primary file and the secondary files must be one-to-many.
Multiple projections enable you to create detail records from one file, then from the next file, for a given key value. For instance, in the example above, you could create detail records as follows:
A1, B1a, <null> A1, B1b, <null> A1, <null>, C1a A1, <null>, C1b A1, <null>, C1c A2, B2a, <null> A2, B2b, <null> A2, <null>, C2a A2, <null>, C2b
Notice that the total number of detail records created for a given primary file key value is the total of the number of records with a matching key value in all secondary files. For instance, in the example above, the total number of detail records for a primary file value of “1” is 5 (the number of matching “1” records from File B, plus the number of matching “1” records from File C, or 2 + 3 = 5).
When using the above method of detail record creation, the %GOTREC intrinsic function will tell you whether or not a record was found in a given file. You can assign the 0/1 value from this intrinsic function to a temporary field via a calculation, and then use the field in a record selection criterion or in a print or calculation conditional.
When using multiple projections to process the records in the secondary files separately, you can also specify common sort fields for each of the secondary files and then merge them. See Merging sort fields for more information.
For example, say you have two secondary files, a sales history file and a current year sales file. Possible uses for this alternate method of record processing are as follows:
- To generate all current sales data for a client, followed by all history data
- To generate subtotals for the current sales data and/or subtotals for the history data
- To merge the current and past sales data together into one sorted report
