Node - Get Multiple Data
Node Function: This node can retrieve multiple rows of data (multiple records from a worksheet or multiple rows from an array), enabling batch modifications to these records, batch addition of these rows to other worksheets, or passing them to sub-processes. When querying data, it supports fetching only the first N rows or randomly selecting N rows.
Seven methods to get multiple data
1. Query and batch update
Query multiple records from a worksheet and perform batch updates on these records. Supports updating up to 1,000 records; if queried more than 1,000 records, only the first 1,000 (sorted) will be updated.
After updating, these data can still be used by subsequent nodes. If used by subsequent nodes, this node supports a maximum of 100 records.
2. Query and batch delete
Query multiple records from a worksheet and perform batch deletion on the retrieved records. Supports deleting up to 1,000 records; if queried more than 1,000 records, only the first 1,000 (sorted) will be deleted.
When deleting records, you can choose to permanently delete them without moving them to the Recycle Bin (if this, the deleted records cannot be recovered).
3. Query worksheet
Retrieve multiple records from a worksheet, which can then be used by subsequent nodes.
-
If the queried records are used by "Add Record" node, "Update Record" node, "Delete Record" node, or "Code Block" node, up to 100 records can be processed. If it exceeds 100, the workflow will be terminated.
-
If passed to a subprocess, process up to 10,000 rows.
4. Get associated records
Retrieve multiple records associated with a single record. This method can obtain up to 1,000 associated records. To retrieve more than 1,000 associated records, please use the "Query Worksheet" option.
5. Get new data in bulk
The "Add Record" node supports batch addition, allowing users to retrieve multiple rows of data again from the batch-added records.
6. Get operation details
Retrieve operation details of an approval node or fill-in node, including operator, time, comments, signature, etc.
7. Get object array
Nodes such as "Code Block", "Send API Request", and "Packaged Business Process" can output an object array. This method can receive the object array and convert it into an internal multi-row data structure.
The input parameters in a packaged business process (PBP) may also be object arrays. In PBP, this method can also receive and convert them into an internal multi-row data structure.
Randomly retrieve multiple records
When querying data, results can be returned either:
-
In order according to specified sorting rules
-
Randomly by ignoring sorting
Limit number of records retrieved
Based on sorting rules and query conditions, supports retrieving only the first N records.
If retrieved data is processed in batches by "Add Record" node, "Update Record" node,"Delete Record" node, maximum processing capacity is 100 records (recommended to set limit at 100). For over 100 records, use sub-processes. The workflow will terminate when exceeding maximum processing limits.
Access mode - Whether to save retrieved records
1. Direct access
When the workflow reaches this node, it retrieves the data and saves the current data for use by subsequent nodes. All subsequent nodes will use the same retrieval results.
2. Dynamic retrieval during each use
The workflow does not retrieve data when first reaching this node. Instead, data is retrieved when executing subsequent nodes that require the retrieved data. Different subsequent nodes may use different retrieval results.
Use Case
The project worksheet is related with the task worksheet, where one project can be related with multiple tasks. When a project is completed and marked as "Finished", batch retrieve all tasks related with that project and update them to "Finished".
1. Create a worksheet event-triggered workflow
Worksheet: Select the project worksheet
Trigger method: Choose "Only when updating records" to trigger the workflow, and select the project "Status" field as the trigger field
Filter: "Project Status" = "Finished"
2. Add "Get Multiple Data" node
Add a "Get Multiple Data" node and select "Get associated records".
3. Configure "Get Multiple Data" node
-
A: Select node object
- Choose which record to get its related records from. Here, select the record that triggered the workflow.
-
B: Select a relationship field
- Choose a relationship field (multiple row). Here, select the "Task" relationship field related with the project worksheet.
-
C: Filter
- Set filter to retrieve only needed related records. For example, if a project is related with 3 tasks but some are already completed, use the filter to get only the related incomplete tasks.
-
D: Sorting rules
- Set sorting rules to display retrieved records accordingly.
-
E: Limit number of retrieved records
-
Set the number of records to retrieve, either as a fixed number or by selecting a numeric field. For example, if a worksheet query returns 200 matching records but only 60 need batch deletion, set the maximum to 60. without setting a limit， the "Get Multiple Data" node can retrieve up to 100 records by default; if passing records to a sub-process, up to 10,000 records can be retrieved.
-
Recommendation: When passing records to a sub-process, set the maximum to 10,000 records.
-
4. Batch update records
Updated object: Select the multiple records obtained from the "Get Multiple Records" node
Updated field: Set task status to "Finished".