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 related 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 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 a Generate Approvals button, 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 record(s) linked to the current record through a chain of relationships. When updating linked records, the update fields action is quick to set up but 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 will select a linked relationship in the wizard. To access the wizard, select Setup Projects > Rules > [Edit a Rule] > Action, and click Create Update Fields Action. If your knowledgebase does not have a Projects table, edit any other table to access the action wizard.
Links to a Single Record
On the Fields tab of the action 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 will update the Person record linked into the project in the Client Business Contact field.
In this example, after selecting Business Contact Email 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. For instance, starting from a support case, you could move to the customer Company record, and from there to the company's Main Contact to update some field in that user record, building a chain from Support Case > Company > People.
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" will update 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 will update all records linked to the current record, i.e., 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 will always update all records.
Updating Field Values
By now you've created the linked relationship chain and selected which fields to update in the target records. The Values tab of the action wizard is where we can set new values for the selected fields.
What options exist for choosing a new value in an update fields action? The available options vary based on the type of field, but at a minimum you may 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 record;
- Set a date field to a specific date, e.g. Jan 01 2017;
- 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 record;
- 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.
- Linked fields can be set to the value(s) found by a saved search.
- User fields can be set based on Round Robin Assignment from a selected Team.
Formula Update Limitations
The primary limitation of an update fields action is that formula updates can only use field variables and local formulas based on the target record, just as it would do in the local table.
What it cannot do is use a formula to copy a field value from the current or parent record, the one that triggers the update action, into a field in the linked record. 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.
It was this latter requirement that caused the development of the linked record action, which has neither of the limitations just described.
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 record(s) via update fields or sending an email via email action from another table that uses field variables from the parent record.
To access the wizard and follow along, Setup Projects > Rules > [Edit a Rule] > Action, and click Create Linked Record Action. If your knowledgebase does not have a Projects table, edit any other table to access the action wizard.
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, i.e. the linked fields in other tables which 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. You might also define an email action to send an email to some values in those records - for instance, to notify approvers that approval is no longer necessary.
Now you can update records by adding a new update fields action.
You can use any formulas on the Parent Fields tab as well as the field variables for each table used in the chain. Note that this is not an option for update fields actions. In this case we started from a Project and are updating records two steps away - through Client Business Contact to the person and from there to all Approvals that person has linked to them. You can select a project field on the Parent Fields tab 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, let's 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: set up the email action through the linked company to select only active contacts and include all relevant field values in the email: priority, summary description, resolution, etc.
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 (usually an update fields action or email action) within the linked record 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 Examples
Below are some example of cases where design decisions were made to use an Update Fields action.
Modifying a Related Record Must Trigger an Update in a Process Record
Marking Contacts Inactive for an Inactive Company.
This is the most common use case for an update field 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 (eligible for support) or Inactive (not eligible). 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 defined to 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 Examples
Below are some example of cases where design decisions were 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.
Map Helpdesk Case IDs into Task Templates to Generate Real Tasks
The Helpdesk Case table contains a field called New Tasks that is used to select necessary tasks for setting up a new employee. New Tasks is a linked field to the Task Templates table.
When a user creates a helpdesk case and checks off tasks, the case's ID should be inserted into the selected task template records and turned into ready-to-complete task records. To do so, first create a linked record action from the Helpdesk Case table that operates on the linked task templates.
Then create an update field action within the linked record action and update the value of the Helpdesk ID field with a formula for the Last HC ID in the task template.
A rule, triggered when the Last HC ID field changes, is set up on the Task Templates table to run a conversion action to create the actual task records.
If you also create an email action to notify the appropriate assigned team or staff person, they will be now receive the task and can get right to work.