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
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.
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:
Recalculation 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 trigger automatically when saving the entires for both creating new or modifying existing entries. (Which the updated value will always be based on the latest value.) And only one set of action button can be configured as automatically execute 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.
And if your action button is created after 2017/06/01, with reverse action button, it's recommended to configure this field as read only to prevent users modifying the field value manually, which might cause the action execute revers 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 you with a message 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 is created before 2017/06/01, you may need to refer to this section for the information regarding using reverse action button.
The "Reverse action button" of update value on another sheet has been updated on 2017/06/01.
If your update value another sheet action button was created or edited before 2017/06/01, to reverse the action, please clear the value in system generated date & time field first, then click on the "Reverse action button".
The followings are the update of "reverse action button".
(Previous) Checking the last executed date & time field. If it's not null, the action button will be executed and update the executed date & time.
(Now) Directly executing the action and cleaning the executed date & time field automatically.
(Previous) No prevention mechanism for repeating reverse actions (You can click the reverse action button whenever the last modified time field is null. It might cause repeating reverses)
(Now) You can execute the reverse action button only if the last modified time field has value. (System will determine whether the update value on another sheet action button has been executed or not based on the recorded executed date & time.)
PS. We still support previous version's action button(Following the old rules)
1. The field types of both source and field to be updated have to be Numeric.
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 detailed information.
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.