STD.File.AddFileRelationship( primary, secondary, primaryfields, secondaryfields, [ relationship ] , cardinality, payload [ , description ] );

The AddFileRelationship function defines the relationship between two files. These may be DATASETs or INDEXes. Each record in the primary file should be uniquely defined by the primaryfields (ideally), preferably efficiently.

The primaryfields and secondaryfields parameters can have the same format as the column mappings for a file (see the SetColumnMappings function documentation) , although they will often be just a list of fields.

They are currently used in two different ways:

First, the roxie browser needs a way of determining which indexes are built from which files. A "view" relationship should be added each time an index is built from a file, like this:

STD.File.AddFileRelationship(DG_FlatFileName, DG_IndexFileName,
                             '', '', 'view', '1:1', false);

To implement the roxie browser there is no need to define the primaryfields or secondaryfields, so passing blank strings is recommended.

Second, the browser needs a way of finding all the original information from the file from an index.

This stage depends on the nature of the index:

a) If the index contains all the relevant data from the original file you don't need to do anything.

b) If the index uses a fileposition field to FETCH extra data from the original file then add a relationship between the original file and the index, using a special value of __fileposition__ to indicate the record is retrieved using a FETCH.

STD.File.AddFileRelationship('fetch_file', 
                             'new_index',
                             '__fileposition__',
                             'index_filepos_field', 
                             'link',
                             '1:1', 
                             true);

The original file is the primary, since the rows are uniquely identified by the fileposition (also true of the index), and the retrieval is efficient.

c) If the index uses a payload field which needs to be looked up in another index to provide the information, then you need to define a relationship between the new index and the index that provides the extra information. The index providing the extra information is the primary.

STD.File.AddFileRelationship('related_index', 
                             'new_index',
                             'related_key_fields',
                             'index_filepos_field', 
                             'link',
                             '1:M', 
                             true);

The payload flag is there so that the roxie browser can distinguish this link from a more general relationship between two files.

You should ensure any super-file names are expanded if the relation is defined between the particular sub-files.

While going through all the attributes it may be worth examining whether it makes sense to add relationships for any other combinations of files. It won't have any immediate beneficial effect, but would once we add an ER diagram to the system. A couple of examples may help illustrate the syntax.

For a typical example, datasets with a household and person file, the following defines a relationship linking by house hold id (hhid):

STD.File.AddFileRelationship('HHFile','PersonFile', 'hhid','hhid', 'link', '1:M', false);

Here's a more hypothetical example--a file query with firstname, lastname related to an index with phonetic names you might have:

STD.File.AddFileRelationship('names', 'inquiries','plastname{set(phonetic)},
                             pfirstname{set(phonetic)}',
                             'lastname{set(fail)},firstname{set(fail)}','link', '1:M', false);

Note, the fail mapping indicates that you can use the phonetic mapping from inquiries to names, but there is no way of mapping from names to inquiries. There could equally be get(fail) attributes on the index fields.

Example:

Maps := STD.File.GetColumnMapping('Thor::in::SomeFile');