How do I change the same field in 2 data tabl...

(Alticroo) #1

How do I change the same field in 2 data tables with 1 form?

I am trying to make an app for inventory management and tracking quantity of items. I have 2 data tables -

  1. a fixed dimension data table which cannot be added to, but can be updated. This is used to show the kind of items we have and their relevant containers, and most importantly last recorded quantity (the part i am struggling with). This is the main view, and the user can identify items they seek through this view. An action is mapped to the navigation to a form which submits a new row in another, second table (below), autocompleting some fields from this view when selecting an item to edit/action.

  2. a data table which can be added to, through a form, and functions as an activity history. The form has some fields autofilled using linktoform(). It shares some field names with the first table, specifically, the container and quantity fields

I have gone as far as to be able to add new entries in the Activity History, but I haven’t found a way to update the quantity set by the user in both the Activity sheet and the first sheet.

Sheet one key is the container, sheet 2 key is row, but still has container field.

Is there anyway to store the latest quantity set through the form (which is adding rows in the activity sheet) in the first sheet, for the particular container/item?

(Aleksi Alkio) #2

Do you need to update that quantity directly or do you want to add that quantity into the existing quantity?

(Alticroo) #3

@Aleksi_Alkio I need the form to update: 1) the added row in the 2 table (done and working) 2) update a value in an existing row in the first sheet. The added row and existing row share a field other than quantity, but in the second sheet that field is not a key.

(Aleksi Alkio) #4

So… you will have two options. #1 - Copy the value from table #1 to table #2 with the DataChangeWorkflow #2 - Instead using the normal column in table #2, you could use virtual column for that value

(Alticroo) #5

@Aleksi_Alkio Thanks for getting back to me on this. Workflows is what I imagined I’d have to use, but I am seriously struggling with Appsheet expression syntax. I used VBA before and it is relatively easy to reference sheets and cells to fetch and insert data.

How do i go about figuring out the workflow structure? I have added a workflow to trigger an event on row add in the activity history sheet. Now I need an action to update the data in first sheet - how do I pass on fields from the added row in the second sheet to a fixed row in the first sheet? I cannot trigger the action because it is tied to a different table than the one where the change event is happening on.

(Aleksi Alkio) #6

When you said “to a fixed row”… do you mean you have only one record on that table?

(Alticroo) #7

@Aleksi_Alkio Sorry I didn’t mean to mislead - no, but the first table does not get rows added to it. It’s just a record of the items we have.

(Aleksi Alkio) #8

I’m not 100% sure if you need to use Data Change/Workflow. If you don’t have big amount of items on your table, why don’t you read values for that table with virtual columns? Then you would not have same data in two different places.