Set Table Cell
The Set Table Cell node is only available in GlobalCapture.
The Set Table Cell node is available for download from the Solutions Delivery Network.
Version 1.1 and higher may not be backwards compatible with version 1.0. If you were using version 1.0 of the node, open the node settings in the workflow and save, and the node definition will be correctly updated.
Using automation to modify data residing in a table can streamline a document capture processes. The Set Table Cell node allows for adding and modifying data in a specific cell or deleting an entire row of data based on the row number or the data in a specific cell within the row.
Set Table Cell Node
Node Properties
Set Table Cell Node Settings
Title
Add a title for this node. Titles are useful when reading the history in the history of a workflow for easier understanding of the overall process.
Description
Provide a synopsis of what this node is doing, or make note of any important details. This is useful for providing additional information such as workflow details and use case information. A good description is helpful when returning to modify the workflow in the future.
Add/Change/Delete
Set Value - Add a row to the end of the table with data or add/modify data in an existing cell.
Delete Table Rows - Delete an entire row of data from the table.
Row
This value is “1” based meaning the first row of the table has a Row ID of 1, the second row has a Row ID of 2, and so on.
Enter the Row ID you wish to work in or delete.
If Set Value is selected and
the Row ID value is ONE greater than the number of rows in the table, a new row is added and the data in the Value field will be added to the cell in the new row under the Field Name column.
the Row ID value is equal to that of an existing row, the data in the Field Name for that row will be replaced.
If the value for the Row is left blank or the Row ID entered is 2 or more greater than the number of rows in the table, the workflow will error.
Ex. A data table called AP Invoice Data contains the columns: Quantity, Item Number, Description, Unit Cost, and Total Cost.
Row ID/Field Name | Quantity | Item Number | Description | Unit Cost | Total Cost |
|---|---|---|---|---|---|
1 | 2 | R2D2 | Astromech starship mechanic | $15,899.99 | $31,799.98 |
2 | 1 | C3PO | Third-class protocol droid | $7,499.99 | $7,499.99 |
3 | 4 | BB8 | BB Series astromech | $4,999.99 | $19,999.96 |
This would add a 4th row to the data table and populate the Item Number column with the value stored in the New Item Number process field.
This would replace the value in the Item Number in row 2 with ME-8D9.
This would error because the table only has 3 rows and the indicated row to add is 5.
If Delete Table Rows is selected and
the Row value is left blank, any row that contains the value in the Value field for the selected Field Name column, will be deleted. If the Value field is left blank, any row with no data in the selected Field Name column, will be deleted.
the Row value contains a value, the row matching the value will be deleted regardless of any values in the Value field for the indicated Field Name.
If the Row ID entered is greater than the number of rows in the table, the workflow will error.
Ex. A data table called AP Invoice Data contains the columns: Quantity, Item Number, Description, Unit Cost, and Total Cost.
Row ID/Field Name | Quantity | Item Number | Description | Unit Cost | Total Cost |
|---|---|---|---|---|---|
1 | 4 | R2D2 | Astromech starship mechanic | $15,899.99 | $31,799.98 |
2 | 2 | C3PO | Third-class protocol droid | $7,499.99 | $7,499.99 |
3 | 4 | BB8 | BB Series astromech | $4,999.99 | $19,999.96 |
This would delete rows 1 and 3 because the Quantity field contains the value 4.
This would delete the 2nd row only. Row takes priority over value.
This would error because the Row value is greater than the number of rows in the table.
Field Name
The exact name of the field in the table to work in. When combined with the Row, this identifies the specific cell of interest.
Table Name
The exact name of the table field.
Value
If Set Value is selected, the value entered here will be:
Added to the new row in the cell under the Field Name, or
Replace the value in the cell indicated by the Row and Field Name values.
To clear the value, leave this blank.
If Delete Table Rows is selected:
If the Row and Value are left blank, all rows that contain no data in the column indicated by the Field Name will be deleted.
If the Row has a value and the Value is blank, the row indicated is deleted regardless of the any data within the row.
If the Row is blank and the Value has a value, all rows that contain matching data in the column indicated by the Field Name will be deleted. This is case sensitive and therefore must be an exact match.
If the Row and Value both have values, the row indicated is deleted regardless of the any data within the row.
Data Validation
Data Validation checks to ensure data being added to an index field conforms to the field’s properties such as length and data type.
If enabled, a process error will occur if there is a mismatch.
If disabled, data will populate the field even if there is a mismatch.
When disabling, there should be a validation step as documents with invalid data formats will error on release to GlobalSearch.
Ex. If the imported data is $3.25, and the field is a decimal type, this will fail validation due to the $ and ..
Uses
Adding a new Row when the Number of Rows in the Table is not Constant - When using GlobalCapture to extract line item data from a document, the number of rows in the table will likely vary from document to document. If you need to add one or more rows to the end of the table, you can do this using a Set Process Field and Square 9 Notation.
Create a Numeric Field to store the number of rows in the table and add it to the Process Fields Pane along with the table field to add to and any other process fields needed for the workflow.
Our Table Field (AP Table) contains Quantity (Field ID: 8), Item Number (Field ID: 15), Description (Field ID:18), Item Cost (Field ID: 14), and Extended Cost (Field ID: 20).
Process Fields Pane
Before the Set Table Cell node, use a Set Process Field node and set the created field to: {p_Table Name[Field Id of value being added to by the Set Table Cell node]_length} + 1
Workflow Configuration
Set Process Field Node Settings
In the Row field of the Set Table Cell node, enter the variable you set in Set Process Field node using Square 9 Notation.
In the Value field, enter the value of the Quantity you want to populate the field. This can be a hard coded value or, using Square 9 Notation, a value stored in a variable.
Set Table Cell Settings
Since the value is set prior to adding the new row to the table, you can reuse this value to add other values to the same row using additional Set Table Cell nodes.
Set multiple values in the same row of the table using multiple Set Table Cell nodes.
Remove data from a table data set that doesn’t belong - Sometimes when capturing data in a table, additional data that does not belong in the table is captured. The can often be attributed to the structure of the data table in relation to other information on the document such as the shipping added within the table.
This can result in line times that have no quantities, product codes, etc. Often information like this, when needed, is stored in other index fields and should be removed from the table.
You can use the Set Table Cell node to remove any line items from the data table that don’t contain Product Codes. This is achieved by leaving the Row and Value blank.
Invoice Example
Configuration to Remove Any Row with no Product Code
Remaining Configuration to Remove any Row with no Product Code