Update Value on Another Sheet
About Update Value On Another Sheet
Sometimes you might want to update field values in sheet A's records according to the values in a record from sheet B, for example, issuing goods from a Sales order to change the inventory of the products, or running balances of an account according to your transaction records. To achieve this, you can create an update value on another sheet action button that will run an update for your records.
How to set up an Action Button to Update Values On Another Sheet
Let's say we have two sheets "Sales Order" and "Inventory". We can take issuing goods as our example here.
To change the inventory of the products based on a sales order, we can create an action button on sheet "Sales Order". Navigate to the design mode on your form page by clicking on the "Change Design" button, and click Form Tools. You'll see Update Value on Another Sheet listed under the Custom Buttons category.
Choose the sheet to be updated from the pop out window. In our case, we want to update our "Inventory".
You will be able to configure the conditions for ways to update Inventory records as explained further below:
Updating value on other sheet
Use this field value on sheet Sales Order: Qty (this is a subtable field)
Update to: "-" (To deduct)
This field value on sheet Inventory: Total Quantity
You can update more than one field value by clicking the "+" button.
Note: The execution sequence would be from top to bottom.
The conditions of updating value on another sheet
When field value on sheet Sales Order: Item
Matches
This field value on sheet Inventory: Product Name
You can add as many conditions as you'd like by clicking the "+" button.
(If there are multiple conditions, the action can be executed successfully only if all conditions are met.)
Note: if your conditions are not reasonable, you will not be able to save your configuration. To check what common mistakes are while setting conditions, please see here.
Advanced Setting
Clicking on "Advanced Setting", you will be able to configure these advanced settings:
Recalculate formulas in sheet Inventory: If you have any fields that have formulas referring to the field values that will be updated by the "Update Value On Another Sheet" action button, you should check this box to recalculate formulas once the field value is updated.
Create reverse action button: This will create a new action button, which will allow you to reverse the update that was submitted with the "Update Value On Another Sheet" action button.
Automatically execute when saving: The update value on another sheet action will be triggered automatically when saving the entries after creating new entries, or after modifying existing entries. The updated value will always be based on the latest value. Only one set of action button can be configured as automatically executed on a sheet.
After your configurations are complete, click "create update value on another sheet button".
You will be prompted to name the button, which will be added to your "Actions", visible in every records' form page on the lower-right corner.
You may notice there are new date & time fields created by the system in order to record the time you execute an update value on another sheet action. Please do not delete this field.
If your action button was created after 2017/06/01, with reverse action button, it's recommended to configure this field as read only to prevent users from modifying the field value manually, which might cause the action to execute a reverse action multiple times.
If your action button was created after 2023/03/21, the "Executed Time Field" will have an undeletable attribute. You will need to delete the action button before deleting this field.
Now that your update value on another sheet action button is created, don't forget to save your design changes.
We can create a new sales order and try out the new action to update value on sheet "Inventory".
As you can see from the link & load pop-out list, the quantity of the item "Chair" in our inventory is 35, and in this sales order, the customer wants to order 15.
After saving this record, you can click on update records action button "Good issue".
The system will prompt a message saying that the update is finished, and will automatically fill the date & time field.
The item quantity of the item "Chair" in our inventory will now be updated, and there are only 20 left.
To reverse the action "Goods issue", you can click on the action button "Reverse Goods issue". The reverse action will be executed and the value in date & time field will be cleared.
(You will have this action button only if you have checked the box Create reverse action button while setting your update records action.)
If your action button was created before 2017/06/01, you may need to refer to this section for information regarding using reverse action button.
Reverse Action Button
Note: The "Reverse action button" that reverses the action for the update value on another sheet button has been updated on June 2017.
If your update value on another sheet action button was created or edited before June 2017, to reverse the action, please clear the value in the system-generated date & time field first, then click on the "Reverse action button".
The following outlines the update of the "reverse action button".
Previously, users would check the last executed date & time field. If the field is not null, the action button will be executed, and update the executed date & time.
The current behavior has changed to directly executing the action, and clearing the executed date & time field automatically.
Previously, there was no prevention mechanism for repetitively executing reverse actions.
You can now execute the reverse action button only if the last modified time field has a value. The system will determine whether the update value on another sheet action button has been executed or not, based on the recorded executed date & time.
Note that Ragic still supports the previous versions' action button, following the previous rules.
Trigger the button more than once
You can only trigger the update values on another sheet action button once in each entry. The system will check the system-generated date & time field to prevent double execution. There are scenarios that you may need to trigger the button again:
You would like to update the sheet content and run the button for newly added info
It usually happens when the button is set up with "+ to add" or "- to deduct." If you clear the value of the system-generated date & time field, existing rows might be double added or deducted. Hence, please run the reverse action button first, update the entry, and re-trigger the action button. For example, you have a purchase order containing 1 item and have deducted the inventory already via the action button. Now, if you would like to add an extra item, the process would be below:
Step 1. Run the reverse action button
This step will revert the deduction resulting from the existing line item.
Let's say you've deducted three inventories for the existing line item, the reverse action button will help you add back those inventories and clear the system-generated date & time field. This step is pretty important to ensure there won't be double deductions.
Step 2. Add an extra line in the subtable and trigger the update values on another sheet action button.
You'll modify the values constantly and would like the values to be updated to the destination sheet always
It usually happens when the button is set up with "= equal." For example, there are two sheets that have no linking relationship directly. However, you would like modifications in sheet A to be updated to sheet B always. You may follow the step below:
Step 1: When creating the action button, check "Automatically run when saving" option in advanced settings.
Step 2: Apply the formula: " " to the system-generated execution time field.
Common Mistakes and Tips While Setting Conditions
1. If you are going to add or deduct the value, the field types of both source and field to be updated have to be Numeric. (To fill in a string, please use "=".)
2. You need at least one set of condition.
3. You are not allowed to set different conditions on the same field of the target sheet.
4. Your update record action button will become invalid if you remove the date & time field that is created by the system. (You can rename the field if necessary, or hide the field if you do not wish to create a reverse action.)
5. You cannot click on the update records action button twice or click on Reverse action button without clearing the value from the date & time field created by the system.
Other configurations for action buttons
Additional Settings
There are some additional settings you can configure for action buttons, you may refer to this article for detailed information.
Remove the action button
If you want to remove the action button, you may refer to this article.
Other use cases
Apart from the example of inventory in this article, you probably would like to know how to calculate a running balance on Ragic.