Modify data in 1 table using data from another table

[Victor_HMG]

So I’m working on an app to help organize and quickly fill out forms and update information on the go from mobile device. So far, I love this app! However, I’m stuck doing the following.

I have a table for:

1. Equipment (laptops, speakers etc.) Saves data related to the equipment and who owns it currently USER_ID.

2. Assignment Forms (A form to fill out when equipment changes ownership) Has unique FORM_ID, USER_ID (max. 1) and date, and a virtual table that contains all equipment that will be assigned on this form.

3. Assignment registries (Created by adding equipment to assignment form) Saves FORM_ID of the Assignment form it belongs to, the id of the equipment it will modify and the reason of the ownership change.

So what I need to do now is create an action(s) triggered by a button, that will update the USER_ID in equipment table using the USER_ID from the Assignment form, and do this for every registry with the same FORM_ID where I clicked the button.

[1minManager]

Hi @Victor_HMG

So what you’re going to need to do is to create a Bot that triggers on a new Assignment Form being created and then runs a "add a new row to another table by using values from this row" type action to update the other table/s

help.appsheet.com

Bots: The Essentials

help.appsheet.com

Actions: The Essentials

Learn how to use actions to modify data and navigation in your app.

Hope this helps

Simon@1minManager.com

[Victor_HMG]

I dont want to add a new row to another table. I just want to update referenced rows in that other table. Does the same action work for that purpose? I saw a demo that updated a table when changes where made in another one, but the update was only a +1 formula. Is there a way to know the values from the row that was updated that triggered the bot to update another table? Like passing the values.

[Steve]

Unfortunately, AppSheet has no built-in way to pass arguments to an action, so you’ll have to put together your own method to do it. But what you want to do is possible, and doesn’t require Automation.

Create two actions (in Behavior >> Actions😞

Action 1

• For a record of this table: (equipment table)

• Do this: Data: set the values of some columns in this row

• Set these columns:

  • USER_ID:

    LOOKUP(
      MAX(assignment-form[_ROWNUMBER]),
      "assignment-form",
      "_ROWNUMBER",
      "USER_ID"
    )
    

  Replace assignment-form (x2) with the name of the table receiving the completed assignment forms.

Action 2

• For a record of this table: (assignment forms table)

• Do this: Data: execute an action on a set of rows

• Referenced Table: (equipment table)

• Referenced Rows: [affected-equipment] (replacing affected-equipment with the name of the column in the assignment form that contains the list of equipment to be updated)

• Referenced Action: (action 1)

Attach action 2 as the Form Saved event action for the assignment form table’s form view. Then, when an assignment form is saved, action 2 will be performed automatically. Action 2 performs action 1 for each piece of equipment identified in the newly-submitted assignment form. Action 1 sets the piece of equipment’s USER_ID column value to the USER_ID column value from the most-recently-added assignment form. The most-recently-added assignment form is the one with the highest _ROWNUMBER column value.

[1minManager]

Victor_HMG:

create an action(s) triggered by a button, that will update the USER_ID in equipment table using the USER_ID from the Assignment form

So here your basically saying your in the equipment table and you want to change something in the assignment table. Am I correct?

[Victor_HMG]

I want to modify equipment table to change user_id(in equipment table) to match the user_id from the assignment form where multiple equipment rows will be referenced.