1. When a linked field is defined, for example, in the Contact table that links to a Customer, this is a many-to-one link. I.e. many Contacts can be linked to one Customer. In Digital-Clay terms, this link has Contact as its base table and is a link “to another table”.
   
   
2. This same link from the viewpoint of the Customer is a one-to-many link. I.e. one Customer can be linked to many Contacts. In Digital-Clay, this means that a link is defined with Customer as the base table and Contact:Customer as the linking field “from another table”.
   
   
3. If a linked field is defined in Digital-Clay as a many-to-many field (e.g. User→Contacts linked via a field called “Contacted”), then the link is many-to-many because many Contacts can be linked to many Users. This applies regardless of whether it is “from another table” or “to another table”.
   
   

With Digital-Clay links “via another table”, the link can be any of the above three depending on what links are being chained together. Examples:

1. Task→Opportunity→Customer is many-to-one (many Tasks to one Customer).
2. Contact→Customer→Opportunity is one-to-many.
3. Tasks→User→Associated Workgroups is many-to-many.

A many-to-one link (#1) may be used anywhere, including list columns and custom functions.

One-to-many (#2) and many-to-many (#3) links cannot be used in the following cases:

• As columns in data lists. This is because this would result in duplicate records.
• As fields in custom functions. This is because the function needs a single value for calculations and fields via such links would return multiple values.

Note that #2 and #3 may be used as items, series or columns in analysis graphs/tables but results or totals may be incorrect depending on what you expect to see. For example, if the analysis shows the “amount of contacts per task”, and the record data is as follows:

TaskA: ContactA, ContactB
TaskB: ContactB, ContactC

Then the analysis results will show:

TaskA: 2
TaskB: 2

Which totals to 4 Contacts instead of 3. This may or may not be what is needed from the analysis but it's up to the user to interpret the results correctly.

Note: Try to avoid using one-to-many or many-to-many links in queries as much as possible. In many cases, these can cause a DISTINCT to be added to the SQL query, which can make the query run 20 times slower.

There are two common misconceptions regarding negative (NOT) filters on linked tables. The assumption is that by setting a filter to “Return results not matching this filter”, the results will automatically be a negative image of the previous results and return ALL records that weren't in the original results. (Note that this effect may be achieved using the “Inverse Results” feature instead, as long as Central is connected to SQL Server).

The first misconception includes all three types of links mentioned above and has to do with the fact that once a filter is added on a linked table, the join to this table affects and limits the results.

For example, even with a many-to-one link (#1) such as Contact→Customer, if you ask for all Contacts with Customer:Country = USA, only Contacts that have a Customer set will be returned. All Contacts with a blank Customer won't be returned in either query. I.e. when this filter is negated, the query becomes “all Contacts linked to Customers that aren't in the USA”.

To convert the above example into “all Contacts not linked to Customers in the USA” whether they are linked to Customers or not, either:

• Define the Customers Link Group as an Optional Join (see Custom Links) and select “Include Null values” from the filter context menu.
• Use the 'Inverse Results' setting in the list context menu. This will return all records that were not returned in the original query, including the Contacts that aren't linked to Customers. Note however that this only works with SQL Server and only in browser lists, not analysis.

The second misconception applies to links to multiple records (#2 & #3) only. For example, with the link Customer→Contact, if the query is: “return all Customers with Contact:Country = USA” which is then negated, the same Customer may appear in the both results. E.g. if the records are as follows:

Microsoft->Bill Gates(USA)
SAP->John Smith(USA)
SAP->Jack Martin(Germany)

Then 'SAP' will be returned in both queries (the regular and the negated one). In other words the query is “all Customers with Contacts that aren't in the USA” not “all Customers that have NO Contacts in the USA”.

Another advanced issue that may cause unexpected results when using many-to-many links and combining multiple link groups, is Unique Link Paths. Contact, if the query is: