Guide on Using Update Record

Introduction

In this passage, you will learn about the configuration and the execution logic of the Update Record node, and understand how to use it better.

What is Data Source?
The data source is, literally, the source of data. In an assignment equation, there on the left is the field to be assigned value to (the target field), and on the left is the source field (i.e., with its value assigned to the target field).
In Automations Pro, a node that provides data for other nodes to use is considered a data source; it can be used at subsequent nodes for value assignment. For details, see Beginner Guide (Automations Pro) > Terms.

Steps to Configure

Usually, before updating records in a form, you should decide which row, which column, and what values to assign. The same goes for updating records at nodes.

At a Update Record node, you should finish 3 steps to configure:

Step 1: Filter and select records to be updated (the row), by adding filtering conditions;
Step 2: Set field values (the column, the values): select target fields or subfields, and assign to them the new values;
Step 3 (optional): Decide the matching relationship between the target and the source. When subfields or multiple records are selected to update, you should set the one-to-one or one-to-many matching relationship between the target data and the data source. In this way, source records match the corresponding target records, and update them.

Why set the matching relationship?
After you finish Step 1, the relationship between the target and the source is still unclear.
When the Update Record node tries assining the data source to the target data:
If there is only one target record, and the source also has only one record, the value assignment will naturally succeed. But, if the target data include multiple records, and the source has one or more, they do not match without a condition, so the value assignment may fail.
Therefore, to clarify for the value assignment, you should decide an one-to-one or one-to-many matching relationship between the target data and the data source.

Notes:

1. A subform can include one or multiple subform records. At the Update Record node, the SubForm field is considered multi-value.

2. Step 3 is optional. If the item in Step 3 (Match the to-be-updated...) does not show up, the configuration is finished.

Execution Logic

Now, the Update Record node is ready. It follows the execution logic that varies between the following two ways of update.

Updating Existing Records

1. Illustrations

Step 1: After source records are retrieved, the node filters and selects the target records;

If the node successfully selects some target records, the execution continues;
If no, ends.

Step 2: The node fetches a source record, and matches it with one or multiple records or subform records in the target form.

If the record is matched, the update is executed;
If not, not executed.

Next, Step 2 is repeated, until all source records are traversed, the execution ends:

2. Demonstration

Note: This section only demonstrates the execution logic. For form designing and detailed configuration at nodes, see Using SubForm Records to Update SubForm Records.

Example:

Step 1: The node filters and selects the complete record whose Order Number is “Jodoo-001”.

Step 2: The node fetches a record from the data source. If it matches one or more target record, the update is executed:

The record in which the Product Name is “Coat” matches the record in which the Product Name is also “Coat” in the target form, and the update succeeds.

The record in which the Product Name is “Sweater” does not match any record in the target form. Here, the record is skipped, and no update is executed.

Updating or Adding Records

When no record is selected or matched, other than immediately ending the execution, the Update Record node can continue it by adding new records to the target form.

1. Illustrations

Step 1: After source records are retrieved, the node filters and selects the target records;

If the node successfully selects some target records, the execution continues;
If no, adds new records to the target form, and ends the execution.

Step 2: The node fetches a record from the data source, and matches it with one or multiple records or subform records in the target form.

If the record is matched, the update is executed;
If not, add the new record to the target form.

Next, the Step 2 is repeated, until all source records are traversed, the execution ends:

2. Demonstration

Notes:

1. This section only demonstrates the execution logic. For form building and detailed configuration at nodes, see Using SubForm Records to Update or Add SubForm Records .

2. This section only explains the execution logic in a record adding scenario, where no record is selected or matched for update. For the logic in the record updating scenario, see the Updating Existing Records section.

Example:

When you submit a new order to, or update an existing order in the Order form, the update will be synchronized automatically to the Collection Follow-up form.

However, if there is only one record in the Collection Follow-up form, with the Order Number, “Jodoo-001”, the update will have different results based on the chosen data source.

Scenario One

The automation is triggered after the record titled “Jodoo-002” is updated, but there is no corresponding record in the Collection Follow-up form.

Thus, the new record will be added to the target form:

Scenario Two

The automation is triggered after the record titled "Jodoo-001" is updated, and there in the target data is a "Jodoo-001" record; they match.

The “SJ-001” and “PJ-001” subform records match with corresponding subform records in the target form, and the update is executed;

The “SJ-002” subform record does not match with a corresponding record, and the new subform record is added to the target form.

Finally, two subform records are updated, and one subform record is added:

Matching Relationship

Logic

When updating multiple target records, and the data source also has multiple records, you should set rules to match the records, establishing a one-to-one (1:1) or one-to-many (1:N) relationship between them.

How to set matching rules? According to actual needs, you can combine one or more fields in the target form to create a composite index. In this way, the valuer source can use this index to find the corresponding target records.

For example, when updating a batch of user contact information, you can set the matching rule as Target Data.User ID equals Source Data.User ID, establishing a 1:1 relationship between the source data and the target data. This ensures that each record in the new form can find and modify the corresponding user data in the base form through User ID.

4.2 Scenarios

Note: Before continuing the reading, refer to Beginner Guide (Automations Pro) > Terms to understand the definitions of single-value and multi-value sources/fields.

What to Update	(Main) Fields Updated to	SubFields Updated To
Record in the form	Multi-value fields: Single-value source's subfields Multi-value source's fields	Unupdatable
Record at the node (single-value source)	No need to set matching rules
Record at the node (multi-value source)	Same as Record in the form.

Take the highlighted part as an example:

When you select to update Record in the form and the target record's fields are assigned values to by single-value source's subfields, a many-to-many relationship is created, and matching rules are needed, because:

When form records are selected for update, 0~N records can be retrieved from the target form, so the target data is considered multiple-value
When you use subfields from the data source for assignment, since there can be one or more entries in a subform, the data source is considered multi-value.

Conditions for Update

Logic

In the following scenarios, conditions for update need to be set, establishing a one-to-one (1:1) or one-to-many (1:N) relationship between the source data and the target data, so that the node knows which sub-data rows to filter out for modification.

Updating subfields to single-value fields;
Updating subfields, and there is a many-to-many relationship between the data source and the target subform records.

Here is an example: When udpating a product's Order Details.Quantity , you can set the update condition as Order Details.Product Name equals Order Details (updated).Product Name, so that only the matched records get updated.

Scenarios

Note: Before continuing the reading, refer to Beginner Guide (Automations Pro) > Terms to understand the definitions of single-value and multi-value sources/fields.

What to Update	(Main) Fields Updated to	SubFields Updated To	Why?
Record in the form	Single-value fields or unconfigured single-value source's fields fields with Custom values Empty fields	Single-value fields single-value source's fields fields with Custom values Empty fields
Record in the form		Multi-value fields: Single-value source's subfields Multi-value source's fields	Assignment data source and subform data have a many-to-many relationship
Record at the node (single-value source)	Single-value fields or unconfigured single-value source's fields fields with Custom values Empty fields	Single-value fields single-value source's fields fields with Custom values Empty fields
Record at the node (single-value source)		Multi-value fields: Single-value source's subfields Multi-value source's fields	Assignment data source and subform data have a many-to-many relationship
Record at the node (multi-value source)	Same as Record in the form.

Let's explain the highlighted part in detail:

When you select to update Record in the form and the target record's subfields are assigned values to by single-value source's fields, a one-to-many relationship is created, and matching rules are needed, because

When subfields are to be updated, there can be one or more sub-records; the target data is considered multi-value.
The single-value source only has a single record, and its field is single-value.