Updating Linked Records
This topic discusses two tools for updating records linked to a given record in a one-to-one or one-to-many relationship: Update Fields and Linked Record actions. Automatically updating linked records is a critical part of automating business requirements; in Agiloft, record updates are most commonly controlled by one of these two action types within a business rule.
Changes in process records often require updates to linked records. Sometimes the linked records are interactive process records, such as tasks or support cases. In other cases, they are template records, such as task templates or approval templates, which must be updated to trigger the creation of new process records, such as tasks or approvals.
In both these cases, the records to be updated are linked to the current record where the trigger occurs, through a chain of linked field relationships. The nature of the linked relationship helps determine which action type is required for an implementation. Below are some normal business scenarios that require updating linked records.
Typical Use Cases
- If a contract is canceled, all related pending approvals or tasks are canceled.
- If a customer's support contract expires, all their open support tickets are canceled.
- When a user clicks Generate Approvals, approval templates linked to the contract workflow are updated, triggering the creation of approvals.
- When the Status of an approval record changes to Approved, the next approvals in the queue are updated to a Status of Pending Approval and the approver is emailed.
- When a project's Status advances from Pending to Work in Progress, all required tasks are generated from the task templates linked to the project type.
- If a new contract is added for a vendor, the vendor's linked insurance certificate records can be updated to include the new contract.
Update Fields Actions
The Update Fields action is most commonly used to update one or more fields in the current record. This article focuses on how an Update Fields action can be used to update any records linked to the current record through a chain of relationships. When updating linked records, the Update Fields action is quick to set up, but it has certain limitations.
Selecting Linked Records
An Update Fields action can run on any records linked directly or indirectly to the current record. When creating the action, you select a linked relationship in the wizard. To access the wizard, select Setup [Table] > Actions and click Create Update Fields Action.
Links to a Single Record
On the Fields tab of the wizard, the first section of the drop-down list shows all the fields linked to just one record from the source table. For instance, if you select Client Business Contact links to Person, you update the Person record linked into the project in the Client Business Contact field.
In this example, after selecting Client Business Contact links to Person, the screen refreshes to show the fields in the Person table. The drop-down also refreshes so you can select another record linked to the person record.
In this way, you can move through a chain of linked relationships to get to a record several steps removed from the first. In this case, starting from a project, you can move to the user's Person record, and from there to other links.
Links to Several Referring Records
Near the bottom of the linked record drop-down is a referring tables section break, below which all the tables with linked fields based on the Project table are listed:
Selecting one of the "referring tables" updates all records linked to this record, whether or not they have been set up in a specific related table field. To illustrate this crucial point, consider the following example:
Suppose the Project table contains two related tables displaying the linked tasks from the Tasks table: one called Pending Tasks, and another called Completed Tasks. Both related tables are based on the same linked field set in the Task table containing the Project ID, Project Name, and other fields, but each related table has a different filter based on the task's Status. The two related table fields in a project record therefore display different records. However, because they are based on the same linked field relationship, there is only one entry shown in the drop-down above. If you choose to run an Update Fields action based on the Tasks referring table, it updates all records linked to the current record, meaning any task connected to the project, regardless of its Status value.
In short, the Update Fields action cannot limit updates to only some records in a one-to-many relationship—it always updates all records.
Updating Field Values
By now you've created the linked relationship chain and selected which fields to update in the target records. On the Values tab of the Action wizard, you can set new values for the selected fields. The available options for choosing a new value vary based on the type of field, but at a minimum you can choose Standard text or A formula.
- Select Standard text to use a hard-coded value, or set a linked field to a specific linked value. For example:
- Update the Contact Owner field with the user's Backup Person
- Set a date field to a specific date, such as Jan 01 2019
- Update a choice field or workflow field to a specific value, such as setting the Status to Canceled
- Update the Contact Owner field with the user's Backup Person
- Select A formula to use field variables, global variables, or other generic formulas. For example:
- Set a date field in the target record to today's date using the formula TODAY()
- Set the Contact Owner field with the value in the target record's Manager Name field
- Set a date field in the target record to today's date using the formula TODAY()
- Use the value or values found by a saved search.
- Follow Round Robin Assignment for members of a selected team.
Formula Update Limitations
Update Fields actions only support formulas and variables that are local to the table where the target record is located. This is the primary limitation of Update Fields actions for linked records. For example, you can't use an Update Fields action to copy information from a contract into its approval tasks, or from a project to the user record of its business contact.
This is a crucial limitation of the Update Fields action type. Much of the background automation used to generate process records from templates, such as Approvals or Tasks, requires the ID of the parent record to be mapped into a template so that newly generated records can be linked to the correct process record. The Linked Record action type was developed to address these needs, and that is the main distinction between the action types.
Advantages of the Update Fields Action
- Can simply and directly update fields in related records with a formula or hard-coded value.
- Easy to set up. An Update Fields action is the most common action type, and faster to set up than a Linked Record action.
Disadvantages of the Update Fields Action
- Unable to map values from the parent record into linked records, which is necessary for many automation tasks.
- There is no way to filter which records are updated when multiple records are linked to the current record by the same linked field.
Linked Record Actions
Linked Record actions were developed to overcome the limitations of the Update Fields action. Like If-Then-Else actions, the Linked Record action is a container for other actions. As its name implies, this action is only applied to linked records: it is not used to make updates to the current, local record. Linked Record actions have more extensive capabilities and are required for complex automation. The primary purpose of a Linked Record action is to be able to use field values from the parent record in the nested actions, such as copying field values into the target records with Update Fields actions or sending emails with Email actions from another table that uses field variables from the parent record.
To access the wizard and follow along, Setup [Table] > Actions and click Create Linked Record Action.
Selecting Linked Records
Like the Update Fields action, the Linked Record action can update any record accessible through a chain of linked relationships. However, the layout of the selection screen is a little different. On the Chain tab, use the two drop-down selectors to create the chain of linked records.
The first drop-down shows linked fields in the current table pointing to another table. In this example we'll select Client Business Contact links to Person:
The second drop-down list shows referring links from other tables, meaning it shows linked fields in other tables that point to the current table. This corresponds to the referring tables section of the drop-down in an Update Fields action. In the example below we select Approvals from Approved / Rejected By, Approver / Rejected By Primary Team…
Once you have created the chain to the records that need to be updated, click Next to move to the Actions tab.
The Actions tab is used to define which actions to run on those records and to optionally apply a filter that limits which records are affected. As stated previously, Linked Record actions are containers for other actions, most commonly Update Fields and Email actions. Remember that the Linked Record action wizard is where you select the chain of relationships; the actual field updates are handled by an Update Fields action nested in the Linked Record action.
In this example, we built a chain of relationships starting from Projects, linking to the Person table through the Client Business Contact, and from there to all Approval records linked to that person. We are now ready to update records two steps away from the original table.
When you first navigate to the Actions tab, your relationship chain is displayed but not selected. Independent actions can be defined at each level in the chain. Click on a highlighted table name to begin adding actions at that level.
After selecting the table, you can add a filter to limit which linked records are affected by the actions. Choose from existing filters on the selected table, or define a new saved search to use. This is very useful when updating a related table of records. For instance, suppose a contract is canceled and you want to modify the related approval records, but only those not yet approved. You can add a filter to update just the approvals whose Status is Queued or Pending Approval.
Adding Nested Actions
So far, you have only defined the records to be acted upon. Use the Add Action button to define the actions themselves. Update Fields actions and Email actions are common choices here, where you can set or transfer field values among records and then send an email to users assigned to those records. For instance, you might set an Approval to Canceled and then email its assignees that approval is no longer necessary.
When setting a new value for a field using a formula, you can use values from the parent table, and field variables for each table included in the chain. In this case, you started from a Project and are now updating records two steps away in the Approvals table, through Client Business Contact to the person and from there to all approvals linked to that person. Now you can select the value of a project field on the Parent Fields tab of Formula Help and copy its value into the approval records.
Field variables from the Person table are available farther down the page. You can also insert these variables into the body of an email as part of an email action.
Access to the parent record variables allows us to map the current project ID into a task template record. Then, when we convert that task template into a task, we can map the project ID into a linked field in the new task, thereby linking it to the right project—the one that initiated the action.
As another example, suppose you want to notify all contacts at the customer company whenever a support case is submitted or closed for that company. You could run an email action as part of a Linked Record action to do that by linking from the case to the company, selecting only active contacts, and including the case's priority, summary, and resolution in the email. Note that in this case the email action is actually created and stored in the Person table, but when created from the Linked Record action on the Support Cases table it can include field variables from the linked support case.
Advantages of the Linked Record Action
- It can filter which records are affected in a many-to-one relationship.
- It can map variables from any record in the linked chain into the records being updated or emailed.
- Although usually used for Update Fields or Email actions, it can contain any action type.
Disadvantages of the Linked Record Action
- It requires setting up the Linked Record action AND another action within the Linked Record action, usually an Update Fields action or an Email action.
- Naming can become confusing because of the nested nature of the actions.
Business requirements will often determine whether you can use an Update Fields action or whether you need a Linked Record action:
- Do you need to update only some subset of related records? If so, then you must use a Linked Record action.
- Do you need to map a value in the relationship chain into the target records? If so, then you must use a Linked Record action.
- Do you want to execute more than one action type on the target records? If so, then you must use a Linked Record action.
If you answer No to all of these questions, then an Update Fields action is probably the best choice, as it is simpler to use and maintain.
Update Fields Action Sample Scenarios
Below are some examples where design decisions were made to use an Update Fields action.
Making Contacts Inactive for Inactive Companies
This is the most common use case for an Update Fields action. Suppose a support or helpdesk knowledgebase tracks the companies that are eligible to receive support. The Status field in the Companies table can be set to Active, meaning the company is eligible for support, or Inactive, meaning ineligible. When the company's Status changes to Inactive, all of the company's contacts should be changed to an Inactive status as well.
Since we want to set a choice field to a specific value on all linked records, the answers to all of the Key Questions are No. In this case, then, we can use the simpler Update Fields action.
Updating Next Approvals to Pending Approval
In the Contracts table, there may be multiple sequential approvals linked to each contract. Suppose in a particular approval record, we have a link to multiple approval records called Next Approvals. The link is defined with a saved search to link in the approval records that are next in the sequence for the same contract. When someone changes the approval Status to Approved, we want to update the Status of all of these Next Approvals records from Queued to Pending.
Since we do not need to filter on the related records, and since we are choosing a hard coded Status value, we can use the simpler update field action.
In the action wizard, we can select the linked records to be updated from the drop-down list. Note that the data type link to selected fields with multiple values enabled, such as Next Approvals, is not a "referring table" and therefore appears in the top section of the linked record drop-down.
After selecting Next Approvals links to Approval, we can choose the Status field and set its value to Pending Approval. A separate rule sends email notifications for this Status change.
Refreshing a Linked Field based on a Saved Search
This is another common use case. The default value for linked fields can be set by a saved search or a matching value between a field in another record and the current record. For instance, in the Companies table, there is a set of linked fields to the Contracts table holding information on the "Latest" Contract, meaning the contract with the latest end date. There is also a related table of all contracts. If a new contract is created, the linked field holding the latest contract information should be updated in the Company record.
However, a default value based on a matching value, such as the Latest Contract, only updates with a new value if the record containing the default value is edited—in this case, the company record.
Here, that means that the Latest Contract linked field set will not reflect the new contract unless we somehow cause it to refresh. One way to accomplish the refresh is to run an Update Fields action from the Contract table such that when a contract's Status is set to Active, an Update Fields action runs on the linked Company.
There are two ways to do this from the Contract table:
- Run an update field action on the linked field that populates the Latest Contract field based on a saved search to the latest contract for that vendor/company.
- Run a Linked Record action that populates the current contract's ID into the latest contract ID field.
In this case, the simpler update field action is probably the best solution because there is no guarantee that the new contract is really the latest one, while a saved search can sort by contract end date to find the right one.
Linked Record Action Sample Scenarios
Below is an example where a design decision was made to use a Linked Record action.
Cancel Open Approvals and Tasks When a Contract is Canceled
When a contract is canceled, it makes sense to cancel tasks and approvals associated with that contract. You will typically need two Linked Record actions to accomplish this: one to cancel the tasks and one to set approval Statuses to Not Needed.
To cancel Planned and Pending tasks related to the canceled contract, create a Linked Record action from the Contract table that operates on the related tasks.
Then create an Update Fields action within this Linked Record action that sets the Status of those related tasks from Planned or Pending to Canceled.
The way to cancel Approvals is to change the Status to Not Needed. To do this, create another Linked Record action from the contract table, but this time one that operates on the related approvals. Then create an Update Fields action within this Linked Record action that sets the Status of those related approvals from Planned or Pending to Not Needed.
- Update Fields Actions
- Linked Record Actions
- Key Questions
- Update Fields Action Sample Scenarios
- Linked Record Action Sample Scenarios