Creating "Reference" type fields with multiple values

This article describes how to properly cross-link items from different item groups using ""reference"" fields with multiple values.

You may run into a situation in which you need to set up a two-way linking between items in two different item groups. Imagine you are tracking your employees, their passports and visas in three different item groups (trackers). When logging a visa, you link it to a passport; when logging a passport you link it to an employee record. Finally, you want to see all the visas linked to every particular passport, all passports linked to each particular employee records and all visas related to all passports of a particular employee record.

While the direct linking should be made manually (e.g. specifying a passport record when creating a visa record), ""reverse"" linking (e.g. all visas linked to a passport record) can be achieved by using an expression language formula in a calculated field.

Let's break this situation down and take a closer look at how such linking can be set up in CMW Tracker.

Example 1:

You need to link the visas and passports records between each other to keep track of your staff's business travels. Visas are tracked in one item group, passports in another. Basically, what you need to do is create one field in your visa template to contain a link to a passport, and another field in your passport template to list all the visas connected to each passport record. One visa can be placed only in one passport, but passport can have many visas in it. Let's write it down:

Visa -> Passport = 1

Passport -> Visa = many

To achieve that, you should create a ""reference"" type field in the Visa template (let's give it an ID of pass_for_visas), and another ""reference"" type field in the Passport template (the ID is up to you, let's make it visas_for_pass) which should get the ""calculated"" option enabled for it. Here is how it looks when configured in CMW Tracker.

We have 2 fields:

pass_for_visas - ""Reference"" type field in the Visa template. We want it to link to the passport records so the ""Reference"" option is set to ""Passport"" template:

visas_for_pass - calculated ""reference"" type field in the Passport template. Make sure to enable the ""Multiple values"" and ""Calculated"" flags in Field Attributes to make sure this field shows multiple visa records.

We want it to show all the visas linked to the Passport record you open so we need to add a custom expression that would do just that, in the ""Field Expression"" tab of field properties. This expression will be ""$<-pass_for visas"":

Here is what you will see in the actual Passport records when several visas are linked to them. This list will be created automatically thanks to the formula we specified before:

And this is what you will see in Visa records after specifying the corresponding passport record manually:

Example 2:

Similar to the previous example, you need to link passport records to your employee records and see how they are linked both ways (see which employee records relate to each passport and which passports are linked to each employee record). Once again, we will need to create one field in each template to show these relations. One in your employee record template, the second one in the passport template. Obviously, one passport can belong to only one employee, but employee can have more than one passports. Let's also write it down:

Passport -> Employee = 1

Employee -> Passport = Many

As you already know from the previous example, we have to create a ""reference"" type field in the passport template (we will use the ID of employee_for_passport), and a calculated ""reference"" type field for employee template (passport_for _employee).

Let's take a closer look:

employee_for_passport - ""Reference"" type field in the Passport template. Reference is set to ""Employee"" template.

passport_for_employee - Calculated, multiple value ""reference"" type field. Expression for pulling up all passport records linked to the employee records will be ""$<-employee_for_passport"".

Here is an example of an Employee who has several Passports:

Example 3:

Let's look at a more complex example now. In the previous two examples we have set up a chain of Visa-Passport-Employee records linked to each other in that succession. What if you want to see all the visas linked to all the passports of a particular employee and also see the employee name in each visa without specifying the name manually (just by looking up the related passport/employee data)? As you remember, we didn't link Visa records to Employee records directly but just because we have a chain of connections, CMW Tracker lets you trace it and pull up data from such interconnected trackers. Let's picture our current scheme and what we want to get, as we did before. The logic here is following: Visa can only be placed into one passport and one passport can belong to just one employee. Thus one employee can have several passports, which can have many visas in it:

Visa -> Passport -> Employee = 1

Employee -> Passport -> Visa = many

In this case both fields are going to be calculated ""reference"" type fields, since the are no direct reference links between them and we don't want to enter these references manually.

These fields will be:

employee_for_visa - Calculated ""reference"" type field in the Visa template. Expression here will be ""$->passport_for_visa->employee_for_passport"" and it will pull up a link to the employee record specified in the passport record the visa record is linked to:

visas_for_employee - Calculated, multiple value ""reference"" type field in the passport template. Expression here will be ""$->passport_for_employee->visas_for_passport"" and it will pull up all the visa records linked to all passport records linked to the employee record you view:

Here is what we will get in the end when looking at a record of an employee who has 2 passports and 3 visas linked to those two passports. These links are calculated automatically after you specify passports when logging visas and specify employee records when adding passport records to the system: