Ragic Logo
<< Update Values Table of Contents Send Custom E-mail >>

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

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.

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:

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 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.

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.

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 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.

Common Mistakes and Tips While Setting Conditions

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.

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.

Top of Page
<< Update ValuesTable of ContentsSend Custom E-mail >>
Learn more about Ragic: Why we created Ragic | Webinars | User Guide.
Keep in touch: Blog | Email | Facebook | Twitter.
Ragic, Inc. | +1 888-666-8037 | Terms | Privacy