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.
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:
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.
When field value on sheet Sales Order: Item
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.
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.
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 "2017 Calendar" in our inventory is 5, and in this sales order, the customer wants to order 2 copies.
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 "2017 Calendar" in our inventory will now be updated, and there are only 3 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.
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.
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.
There are some additional settings you can configure for action buttons, you may refer to this article for the details.
If you want to remove the action button, you may refer to this article.
Apart from the example of inventory in this article, you probably would like to know how to calculate a running balance on Ragic.