A Link is used to join two tables together so that fields in one table may be filtered or sliced by fields in a second table, or so that values may be displayed from more than one table at the same time. For example, to query on Opportunities filtered by the Customer's Country necessitates a Link between Customer and Opportunity.

A Link has a base table, and is constructed using Linked Fields, but it is a separate entity in Digital-Clay and is defined and stored separately. This sometimes means an additional development step is required when setting up a Clay system, but it also provides great benefits to the end user (as described below). This separation means that:

• A 'Linked Field' may have zero, one, or even multiple links associated with it. I.e. just because a Linked Field exists, this does not automatically mean that the tables may be joined in a Browser or Analysis query unless a Custom Link exists that uses this Linked Field.

• The same two tables can be linked in different ways via several Linked Fields or via other tables using multiple links. For example, Contact→User via the link 'Creator', Contact→User via 'Modifier' and Contact→Task→User via the link 'User'

• Links are based on Linked Fields, or on other links. A chain of links between distant tables can be abstracted as one link (see The Implementation Stage).

• Links can be grouped together in the same Link Group if they are all based on the same table. I.e. if they all reach the same destination (see Link Grouping). A Link Group may have zero, one or multiple links contained inside it.

• All connected fields and filters in Digital-Clay are based on links and Link Groups. For example, when the system displays “Creator:Name”, “Creator” is a Link Group (linked to the User table) and “Name” is a field in the table User. In other words, table names are never displayed in field names and filter lists, only Link Groups.

• The same two tables can not be linked together twice within the same Link Group (with one exception: see Intermediate Links)

Links are built by developers and provide the following benefits:

• The user doesn't have to build his own joins, know SQL or understand the structure of the database in order to build new queries.

• The user only has to deal with concepts (AKA indicators and link names). For example, if he selects Sales by Workgroup, he is not aware that he is using the link: Product Details→Opportunities→Team Members→User→Workgroup. Thus, dozens of tables can be linked via complex paths with simple clicks.

• The user can easily combine or switch between two or more indicators and slice/filter them by the same fields even though they are joined in completely different ways (see Link Grouping). For example, a query displaying Sales by User with 5 filters can be changed to or combined with Amount of Tasks by User with the same filters using a single click.

Whenever a new Linked Field is added, the following options are displayed for quickly adding Custom Links based on this Linked Field:

This provides a convenient way of adding Custom Links in both directions based on this Linked Field. For example, if you are adding a Linked Field in a Members table to the Clubs table, this would automatically create the links Member→Club and Club→Member so that each of these tables may be filtered by the other.

If the default Link Group for each of these tables already contains a link to the other table (i.e. another Linked Field was already added that joins these two tables), this form gives you the option to add a new Link Group for joining these two tables in a new way. For example, you may have already added a 'Member' link and now you are adding a 'Secondary Member' link.

This form covers the majority of simple cases when two tables need to be joined. However, in the following cases, you will need to build a Custom Link using the advanced Custom Links tool:

• You need to build a join between distant tables. E.g. Member→Club→Manager.
• The Custom Link has been deleted, or, the developer unchecked the option to create a link when he created the Linked Field.
• You want to rename or edit existing links.
• You want to build alternative links or paths between tables.
• You want to build an optional join (see Optional Joins).

The following sections outline the steps needed to build a Custom Link:

When building a new link, it is recommended to plan the link and even scribble it down first using the following technique:

1. First formulate the following phrase “I want to see records/fields from table A filtered by/linked to table D”, replacing table A and table D with your tables. In our example, we want to find all Managers of Clubs with special Members and we therefore want to filter Managers by MemberStatus.
2. Draw the path from Manager to MemberStatus using linked fields, marking the tables containing each of these fields. For example: Manager(Branch)→Club→(Club)Member(Status)→MemberStatus
3. Flip this drawing backwards: MemberStatus→(Status)Member(Club)→Club→(Branch)Manager

All links in this chain must then be built one at a time, in the same order. The next section will explain how to build such a link.

Examples on how to build the link: MemberStatus→(Status)Member(Club)→Club→(Branch)Manager

The base table of the Link Group must always be the first table, in this case MemberStatus. You can create a new Link Group or use an existing one.

Within this Link Group, you must create all the necessary/missing links using any of the three types of links:

1. “Link using a linked field in the base table” e.g. Member(Club)→Club
2. “Link using a linked field in another table” e.g. Club→(Branch)Manager
3. “Link via other tables” - this means chaining two existing links together. For example, if we already have MemberStatus→Member linked via a link called StatusLink and we have Member→Club via ClubLink, we could link MemberStatus to Club by chaining StatusLink and ClubLink together.

In other words, the foundations of all links are Linked Fields, but larger links can be created by chaining links together. When two tables are not linked to each other directly, they must be linked through such a chain.

In the above picture we can see the Custom Links interface listing Link Groups on the left, all of the links grouped together under the 'User' Link Group on the right, and the form for adding a new link. Note the help box that displays the currently constructed link as a formula.

Example A

In a typical system, links are created automatically when a linked field is added. So, for example, we would already have MemberStatus→Member linked via the link group Status, Member→Club via Members and Club→Manager via Club. What we are missing is a link for MemberStatus→Manager (via Member & Club). So:

1. Select the Link Group for table MemberStatus (Status)
2. Add a link via another table where table=Club and link=Members
3. Add a link via another table where table=Manager and link=Club

Example B

In this case we only have one existing link for Member→Club→Manager called Members. The table MemberStatus is not linked to anything:

1. Add a new Link Group with base table MemberStatus called Status
2. Add a link from a linked field in another table where table=Member and field=Status
3. Add a link via another table where table=Manager and link=Members

Technically, 'intermediate' means that the links are not usable except as a bridge for other links. In other words, they can be used as a link in a longer chain and nothing more.

The only practical use for this is if you want to link the same table twice within the same Link Group. For example, say we want to create a link from a contact to the company of his boss. Flipped backwards this would be:

Customer→(fldCustomer)Contact→(fldBoss)Contact

Since Contact can't be linked to twice using the same link group (Customer), the first link Customer→Contact must be declared an intermediate link in order to allow the second distant link (Customer→Contact→Contact) to be created.

Grouping means that you can see values from two or more tables, or two query results, both filtered using the same filter or field.

Example 1

In analysis, you want to see both Amount of Tasks AND Amount of Opportunities by their Owners. In tables, this means:

Tasks(fldOwner)->User
and
Opportunities(fldOwner)->User

Each of these links use a different linked field in two tables and are therefore two different paths, but they are linked to the same table. Also, these two must not be dependent on each other (i.e. Tasks cannot be linked to Opportunities because that would limit the results).

Solution: Make a Link Group based on the User table and add two links: one to Tasks and one to Opportunities.

Example 2

A dashboard contains:

• a Customer Chart showing all contacts/associates/associated Customers
• a list of Tasks

Both need to be associated with a specific Contact (dynamic filter).

for the Chart (which starts with a list of Customers filtered by Contact):
Customers->(Customer)Contact
and for the list: 
Tasks(Contact)->Contact

Solution: Use the Link Group based on Contact as a dynamic filter for both, and make sure it includes links to both Customers and Tasks.

Note: The power of link grouping is one of the main reasons why links must be built backwards.

A Link Group can be defined as an Optional Join (left or right joined) at the level of the group, not an individual link. This is equivalent to a LEFT/RIGHT JOIN in SQL queries and it means that when returning results from two or more tables, records don't necessarily need to be linked to records in the other table in order to be included in the results.

Examples:

• Normally, without an Optional Join, if you request a list of Contacts with Customer:Country as a column, it will only return Contacts that are linked to Customers.

• If the Customer Link Group is defined as an Optional Join (Left) the list will include Contacts that aren't linked to any Customer. This is because the query is: “Contacts left-joined to Customers whether the Contacts are linked to Customers or not”.

• In Analysis, if you want to display “Amount of Contacts by Customer” but you want to include Customers that have 0 Contacts, this would be a right-joined link. I.e. “Contacts right-joined to Customers whether the Customer has a Contact or not”.

It is strongly suggested that, due to the way right/left joins affect and change results, new Link Groups should be created with helpful names (e.g. “All Customers” vs. “Customers”) in order to avoid confusion. This will also allow the user to select which join he wants by simply selecting the appropriate Link Group.

Using filters through optionally joined links sometimes requires special handling: For example, a Contact left-joined to its Customer which doesn't have a Customer means that all the Customer fields are Null. So even if you would build a query “all Contacts with an empty Customer:Country”, the records with Null values would be skipped (not returned) since Null is not the same as empty. To include Contacts with Null Customer:Country values, you must select the “Include Null values” option in the Filter context menu.

The same goes, for example, in the analysis example above that returns Contacts right-joined to Customers. If you would filter on the Contact:Created field, all Customers without Contacts would be skipped and you would not see the Customers with 0 Contacts, unless you select “Include Null values” for the filter.

Note that in the case of distant links via other tables, defining a Link Group as Optional means ALL the links in the chain will be left/right joined. For example Task→Opportunity→Customer defined as an optional join means that you can return Tasks with their Customer:Country values whether they are linked to Opportunities or not, which are linked to Customers or not.

See Links to Multiple Records for definitions and issues related to one-to-many and many-to-many links.

This is an advanced topic concerning multiple link paths in a query which make use of the same table(s). This issue is only relevant when it involves many-to-many or one-to-many link paths.

Example:

The query is for a list of Tasks, with two filters:

• Contact=John
• Customer=IBM. Note that the Customer table is linked via the Contacts table.

Now say there is a single Task that is linked to two Contacts:

• John working for Microsoft
• Sue working for IBM

There are three possible ways to interpret this query:

• 'Give me all Tasks that are linked to IBM OR to anyone called John'. This is a simple OR query (optional filters) and will even return a Task that is linked ONLY to John from Microsoft.
• 'Give me all Tasks that are linked to IBM AND anyone called John'. In this case, the above Task will be returned in the results because it has two Contacts which, together, fulfill the filter requirements.
• 'Give me all Tasks that are linked to anyone called John who is working for IBM only'. In this case, the above Task should not match the filter requirements.

The last two interpretations are what concern us here. The key to understanding this is whether the joins to Contact and Customer are done via unique paths, or via the same path:

• In the first scenario, the links are unique: Task→Contact1 (for the Contact filter) and Task→Contact2→Customer (for the Customer filter) and therefore the filters on Contact will not affect the results returned for the Customer.
• In the second, the links use the same path: Task→Contact1 and Task→Contact1→Customer therefore the Customer table is also limited by the Contact filter.

Which of these behaviours you should expect, depends on how you built the 'Customer' Custom Link. Remember that building distant links (links via other tables) is basically a chaining of two or more links together. So:

• If you built a Link Group called 'Contact' that contains the link Task→Contact. Then you added a link from Customer to Contact to Task (Task→Contact→Customer) using the Link Group 'Contact' as its intermediate link, then the two link paths will not be unique. I.e. the second scenario above.
• If, in addition to the above, you built a second Link Group called 'Contact2' that also links Task→Contact, then you used 'Customer' and 'Contact2' as your filters, then they will be unique paths (first scenario).

Note: Another way to force non-unique paths in the above scenario is to use the same Link Group for both filters: I.e. Filter by 'Contact:Name' and 'Contact:Customer'. This trick can sometimes be used even for fields inside the Customer table, by adding a custom function in the Contact table. E.g. Filter by 'Contact:Name' and 'Contact:CustomerCountry' where 'CustomerCountry' is a custom function field that gets its value from the 'Customer:Country' field.

To demonstrate this issue, here is another example using an analysis query:

'Return the amount of Products sold to the USA (Customer:Country) and in the past 6 months (Opportunity:ClosedDate)'

This query may or may not return Products that have one sale to the USA 12 months ago, and another sale to the UK 5 months ago, depending on how you built the links.