Relational Fields TrackVia gives you the ability to link, or relate, records in one database to records in another database. This is done with link to parent record fields, which we also call relational fields. Example. Suppose we have a database called Contacts and another called Companies. Each Contact record would have a field called Employer that links to a Company record. It would look like this:   Contact Record   Company Record   Name Bill Gates Title Chairman Employer  Microsoft   Age 50   Stock Symbol  MSFT Comp. Name  Microsoft HQ City Redmond HQ State Washington In this example we would call the Contacts database a child database, and Companies a parent database. Similarly, we say that Bill Gates is a child record of Microsoft, and Microsoft is a parent record of Bill Gates. The value Microsoft in the Bill Gates record is a link to the parent record. We're using the Comp. Name field in the parent database to refer to the parent record. If we edit the Bill Gates record, the Employer field will appear as a drop-down list containing all of the company names in the parent database. Behind the scenes, TrackVia keeps track of the unique record locator number of the Microsoft record. So the link from child to parent is to the record as a whole, not just to the word "Microsoft." In fact if we change "Microsoft" to "Microsoft, Inc." in the parent record, TrackVia will automatically update all children. If we delete the Microsoft record, TrackVia will automatically set the Employer field in all child records to (none). The relationship above is called a many-to-one relationship, because many employees can link to each company. TrackVia does not support many-to-many relationships. However, you can accomplish something similar by having multiple link to parent record fields in the child database that all link to the same parent database. Creating relational fields. To create a relational field, follow these steps.

In the child database, go to the Edit Database page. Use the Field Chooser to add a new field whose data type is link to parent record. Give the field a name. In the example above, it was Employer. In the drop-down that appears, select the parent database. In the next drop-down that appears, select the parent field you want to use to choose and represent parent records. In the example above, this was Comp. Name. * Click Save Changes.

*You can link to any Short Answer, Number, Auto-Counter, Email, or URL field including those that are calculated.

TrackVia only allows you to link to a single field in the parent database. If you want to refer to parent records by more than one field, you need to create a calculated short answer field in the parent that combines those values, and link to it. This is a very useful trick, and is explained in more depth in the Two Relational Tricks Help article.

You can also create a relational field by converting an existing field into a link to parent record field. Use the edit icon next to the existing field on the Edit Database page. If you create the relational field this way, TrackVia will try to match up the existing field values to a record in the parent. It will warn you if any child records have no match or too many matching records in the parent database.

Using Relational Fields. In the child database, you can create a view that pulls in other fields from the parent database. In the example above, a new custom view in the Contacts database would give you the following column choices:

The columns prefaced by [Employer] are called associated parent fields. They will display data from whichever parent record the child links to. They will automatically update if the parent record is changed, or the child is edited and Employer is set to a new parent. In your view, you can display them, sort by them, or filter by them.

Similarly, you can refer to associated parent fields in mail merge documents and in email campaigns, using a placeholder like ~[Employer] Stock Symbol~ or ~[Employer]_Stock_Symbol~.

To view an associated parent field in the child record itself, and thus be able to search on it, you need to use a calculated field and the parentlookup() function. This is a powerful trick, and is described in depth in the Two Relational Tricks Help article.