Linking Collections by Field

Whilst Kinabase can be used to great effect with Collections acting as standalone databases, the ability to link collections together into a relational database with Linked Fields, Lookups into Linked Collections, and Aggregated Fields can create a new dimension of utility and facilitate far more advanced use cases.

Creating Linked Fields between Collections allow you to join collections together via a common field, making traceability of information clearer, easier, and faster.

Creating Lookups into Linked Collections creates a new field that is able to import a value from a record in another collection that shares the same value in the chosen linked fields.

Creating Aggregated Fields for Linked Collections creates a new field that is able to synthesise quantitative information from records in another collection that share the same value in the chosen linked fields.

Creating Linked Fields between Collections

When creating new fields for your collections in the Collection Configuration page - see Configure Collections - you have the option of creating a Linked Field. Linked Fields allow you reference records that appear in other collections.

For example, you can create a "Primary Contact" field in a "Sales Accounts" collection that references specific "Contact" records in your "Contacts" collection - this allows you to quickly access important information for relevant contacts directly from you "Sales Account" collection.

In the above example, the Primary Contact and Other Contacts fields are both linked with the Contacts collection. When the "Doro's Gallery" sales account is inspected, we can see "Doro" as a primary contact, and "Alex", "Harry", and "John" as other contacts - these names are links, and when clicked they will take you directly to those contact records in the Contacts collection.

To create a Linked Field in your collections:

• Navigate to the Collection Configuration page by right clicking the collection you want to edit in the Navigation Sidebar, then selecting Configure from the dropdown menu
• Under the Fields tab, select the Add Field button
• Within the Type dropdown box, scroll to Collections at the bottom of the list, then select the collection you want to link to
• Add a title and description for your field, then click Add

Allow Linked Record Creation and Filtering by Common Collections

When creating a Linked Record (see above), you will see the option to Enable Linked Record Creation, and to Filter by a Common Collection. This section will explain what these features are, why they're useful, and how to enable them.

Enabling Linked Record Creation

If you or one of your colleagues is creating a new record in a collection with a linked field, they will be prompted to select a record from that linked collection. If no relevant record yet exists, they will be able to create a new record in the linked collection directly if this setting is enabled.

In the above example, a user is creating a new "Sales Account" record for Doro's Gallery. Because "Primary Contact" is a linked field to the "Contacts" collection, they are prompted to select a primary contact for this account from the existing "Contacts" list.

Because "Doro" does not yet exist as a contact, the user has been prompted to Add Doro. By clicking Add, a new window will open to create that record directly into the "Contacts" collection.

Filtering by Common Collections

When creating a new record in a Collection with a Linked Field, users will be prompted to select a record from that linked collection. In the example of "Sales Account" and "Contacts", there may be a large number of "Contacts" that a user will need to scroll through when creating a new "Sales Account" record.

If the "Sales Account" and "Contacts" collections are both also linked with a third collection with Linked Fields, for example "Country", then the Filtering by Common Collection allows that list of "Contacts" to be filtered to only show contacts from the selected country. In this example, both "Primary Contact" and "Other Contacts" are filtered by the common collection "Country".

By selecting Countries in the Filter by Common Collections dropdown then saving the change to the record, when prompted to select a contact for a new "Sales Account" record, the list of contacts will be filtered by the value selected for "Country".

In this example, by selecting "Switzerland" as the country for "Neuman's Shop", the "Other Contacts" dropdown will only show contacts in "Switzerland".

Creating Lookups and Aggregated Fields

Where Linked Fields allow you to add more information to new records by manually attaching records from linked collections, Lookups and Aggregated Fields will automatically collect information from linked collections based on criteria set by you.

Lookup Fields will search through a linked collection based on criteria from a Singular linked field (see above) to return a value to the current collection.

Aggregated Fields will return a number that is calculated based on values from records in a Linked Collection that match a value present in a Singular Linked Field in the current collection.

Creating Lookups into Linked Collections

Create a Lookup Field to return a value from a specific field in a linked collection - this is useful if you need specific information like contact details or company from a Linked Collection present a different collection.

To create a Lookup Field

• Navigate to the Collection Configuration page by right clicking the collection you want to edit in the Navigation Sidebar, then selecting Configure from the dropdown menu
• Under the Fields tab, select the Add Field button
• Within the Type dropdown box, scroll to Computed Fields near the bottom of the list, then select Lookup
• Select the Linked Collection that you want this field to return a value from
• Select the Linked Field from the current collection that you want to use to search the Linked Collection
• Select the Field from the Linked Collection that you want to return a value from
• Add a title and description for your field, then click Add

The Lookup Field in the current collection will find the record in the Linked Collection that matches the record referenced in the Linked Field of the current collection, and return the value that appears in the Field in the Linked Collection

In this example, the Lookup Field for "Primary Contact Email" will search through the "Contacts" collection for the "Primary Contact" linked to the record in the current collection and return the value under "Email".

Creating Aggregated Fields for Linked Collections

Aggregated Fields will return a number that is calculated based on values from records in a Linked Collection that match a value present in a Singular Linked Field in the current collection - this is useful if you need to perform a calculation on values across multiple records in a separate but linked collection.

To create an Aggregated Field

• Navigate to the Collection Configuration page by right clicking the collection you want to edit in the Navigation Sidebar, then selecting Configure from the dropdown menu
• Under the Fields tab, select the Add Field button
• Within the Type dropdown box, scroll to Computed Fields near the bottom of the list, then select Aggregation
• Select the Linked Collection that you want this field to return a value computed from
• Select the Linked Field from the current collection that you want to use to search the Linked Collection for values to compute
• Choose a "Mode" operation to apply to the values in the Linked Collection that you want to compute, these operations are described further in this article
• Select the Field from the Linked Collection that you want to compute values from
• If you've chosen the "Change over time" mode, enter the time field that you wish the mode to evaluate
• Add a title and description for your field, then click Add

A description of "Mode" operations:

• Count - Integer count of the number of records that match the set conditions
• Sum - The sum of values that match the set conditions
• Average - The average of values that match the set conditions
• Minimum - The minimum value that matches the set conditions
• Maximum - The maximum value that matches the set conditions
• Change over time (Latest - Earliest) - The difference between the last and first recorded values that match the set conditions

In this example, the Aggregated Field in the current collection will find records in the linked Invoices collection that match the same Sales Account name to Sum values in the Value field that have the workflow Stage equal to "Paid".

In the above image you can see Paid Invoices and Unpaid Invoices fields returning the sum of invoices in the Invoices collection that match the "Paid" and "Unpaid" stages respectively.