Learn About One-to-One and One-to-Many Relationships

Learning Objectives

After completing this unit, you’ll be able to:

• Describe the specifics of one-to-one relationships and one-to-many relationships.
  
• Fetch data from multiple one-to-many relationships.
  

One-to-One Data Relationships

Start by learning about the most straightforward type of data relationship in Spiff—the one-to-one relationship. In this type of relationship, for a record on object A, there’s one related record on object B. For example, for every opportunity record there is one matching account record. It’s important to know that relationships are a one-way street. If you consider the other direction, from accounts to opportunities, there can be many opportunities associated with a single account record, necessitating a one-to-many relationship. You learn more about those later. For now, check out these examples to learn more about one-to-one relationships and how to use them in Spiff.

Example 1

In data filters or payout rules you can refer to a relationship by referencing its name. Continuing the example from the previous unit, enter DealToAccount in the formula bar or data filter window. If Spiff recognizes this relationship, it changes color. To access a field on the record you bridged to, put a period after the name of the relationship and then type the field you want to access. For example, DealToAccount.fieldyouwanttoreturn.

DealToAccount is the bridge connecting the tables, and after you specify that bridge, you can return any field on the account record after the period. Let’s say you only want to pay commissions for a deal if the account is live, or if it’s after the contract start date. To access the contract start date for the account related to the deal, enter DealToAccount.ContractStartDate__c in a data filter or payout rule.

The accounts table has an Account_Manager field and a rep gets a bonus if they close a deal with a specific account manager. Typing DealToAccount.Account_Manager returns the account manager listed on that field for each record.

Example 2

Imagine a company that sells a variety of products, and the product type is tracked in the Item__c field on the Deals object. Some of these products fall into category X, which gets commissioned one way, and some of these products fall into category Y, which gets commissioned another way. The item category is tracked in the Categories object.

Create a one-to-one relationship from Deals to Categories using the Item__c field and the deal_item field because they hold related information. Follow the recommendations from the previous unit and name this relationship DealToCategory.

Example 3

Let’s say you want to create a data filter to determine if a go-live date field on the Accounts object is in a certain period. Combine this with the deal’s ClosedInPeriod data filter to give an extra bonus for deals closed in the same month as the client’s go-live date from the accounts object.

Remember, DealToAccount is the bridge between the two objects, and then the GoLiveDate is the name of the field that you want to use from the Accounts object.

One-to-Many Data Relationships

There are many common one-to-many relationships in Spiff. Examples include OpportunityToOpportunityProducts, OpportunityToOpportunitySplits, AccountToOpportunities, and InvoiceToInvoiceLineItems.

When using a one-to-many relationship, such as Account to Opportunities, Spiff returns multiple opportunity records for a single account. You direct Spiff what to do with all those records. There are several functions that you can use with one-to-many relationships to sift through the returned records and get the information you need. Learn more about these functions next.

sum()

A calculation made on the original object table that gets values from the second object must be aggregated in some way. And the most common aggregation is adding values to the many records associated with the main record. If you use sum(AccountToOpportunities, ARR), Spiff takes the opportunity records that are returned for each account and sums the annual recurring Revenue (ARR) for all of them. In other words, you have a total ARR closed for each account across all its opportunities.

filter()

Use the filter() function with one-to-many relationships to access information from another object. The output returns multiple records. For example, to fetch a list of line items where the product type is Widget, use filter(OppToLineItems, Product = “Widget”).

sort()

The sort() function orders dataset records or a list by date, string, or number. You can use this function in conjunction with filter(). For example, to fetch a list of line items sorted by CloseDate, where the product type is Widget, use filter(sort(OppToLineItems, CloseDate, 1), Product = “Widget”).

transform()

When you need a list of values for a certain field, use transform_list(). For example, to retrieve the ID for each line item in an invoice, use transform_list(InvoiceToInvoiceLineItems, Id).

choose()

The choose function returns a value from a list based on its index number. The first item in a list has an index of 1. You can also use this function in conjunction with functions such as transform_list() and filter(). When you use choose(1, TransformedList), the transformed list isn’t in any particular order, so you don’t know exactly which result you receive. To fix this, sort, filter, and transform values in a list so that you know which records appear first or last. An index of 1 returns the first item in the list, and an index of -1 returns the last.

If the list you are using in the choose() function is empty, the calculation errors out.

Here’s an example showing how not to use the choose() function.

choose(1, transform_list(OppToOppProducts,ARR))

The output gives you the first listed ARR from one of the opportunity products associated with the opportunity, but you have no way of knowing which one it is.

Here’s an example demonstrating the recommended way of using the choose() function.

choose(-1, transform_list(sort(OppToOppProducts, ARR), ARR)

The output is the largest ARR value of all the opportunity products associated with the opportunity because they are sorted in ascending order and you are fetching the last one in the list by using the index of -1.

Fetching Data in Multiple One-to-Many Relationships

There are times when it’s necessary to fetch data two objects away. Learn how to do this using an example. Starting with the Account object, get data from the OpportunityProduct object using this path.

Account > (one-to-many) > Opportunity > (one-to-many) > OpportunityProduct

Sum All Opportunity Products per Account

It’s easier to conceptualize if you start in the middle. So first, sum up all opportunity products per opportunity.

• Create a one-to-many relationship named OppToProducts from Opportunity to OpportunityProduct.
  
• On the Opportunity object, create a datasheet calculation “OppTotal” = sum(OppToProducts, ProductPrice)that sums all related OpportunityProduct amounts per Opportunity.
  

Sum All Opportunity Amounts per Account

Now that you have the total amount per opportunity, move to the Account object and sum up all opportunity amounts per account.

• Create a one-to-many relationship named AcctToOpps from Account to Opportunity.
  
• On the Account object, create a datasheet calculation “AcctTotal” = sum(AcctToOpps, OppTotal)that sums all related OppTotal amounts.
  

Here’s what the final solution looks like.

You can apply the same principle for any datatype, not just summing amounts, but sometimes this requires additional data customization. While one-to-one relationships are more straightforward, you know how to fetch data from one-to-many relationships as well.

In this module, you learned how to create and use data relationships to connect isolated tables. You explored one-to-one and one-to-many relationships and how they help you retrieve relevant information. With these concepts, you can structure data more effectively and enhance your analysis in Salesforce Spiff.