Linking To 2+ Fields

Note: You should read the main article Using Relational Fields before reading this one.

In TrackVia, a single link to parent record field can link to only one field in the parent table. This is sometimes inconvenient. For example, suppose we're linking from an Orders child table to a Customers parent table.

Neither the Company Name field nor the City or State field in the parent table is sufficient for picking companies from the drop-down list in the child record. In addition, two companies could have the same name but different locations. What we really want is to have the drop-down list that we choose companies by to include the Company Name, City, and State fields.

To accomplish this, we need to create a new calculated field in the parent table that combines all three of these fields. We'll then link to this new field from the child table.

The formula for the calculated text field uses the & operator to glue together the company name, a dash with spaces, the city, a comma and space, then the state.  Calculated fields are explained in the Formulas Help article, and formulas that combine text values like this are explained in the Text Formulas Help article.)

When this is done, each parent record will contain this Link Field that is automatically updated whenever the company name, city, or state changes. In the Contacts child table, we can now create a link to parent record field called Company that links to Link in the parent table. Choosing parent records from the drop-down list in the child record is now much easier.

Finally, if you change the formula for the calculated Link-To Field in the parent table, TrackVia will reapply the formula to all parent records and all of their children automatically.  Creating such a Link Field in your parent table is therefore a good way to ensure future flexibility and ease-of-use in your TrackVia applications.