Solved: How to edit data on another sheet

juan_walte · 09-03-2019 12:41 PM

Hi. I have an inventory tracking App with 2 sheets. One is a transaction log the other is the inventory count.
I have a column in the inventory sheet where I have a status enum column that can be “ordered, not ordered or received”. I dont know if there is a way to automatically change the status based on the transaction log. So for example if I add a transaction that I received X of part number 123 in the transaction log I want this to trigger an action in the inventory sheet in the line for part number 123 so that it changes its status from “ordered” to “received”. Is there a way to do this? it seems actions can only be performed in the same sheet from what I understand on the appsheet

MultiTech

Exactly, that’s what the Ref column is for.

In the ref action, there’s a place to specify the

In the example below you can see that this is more specific to your scenario.

This action lives on the child table and updates the parent - exactly what you’re wanting to do.

You can see I’ve wrapped the [Log_Phase_Link] in a LIST() formula, this needs to be done because that field, where you’re specifying the rows to update, is expecting a list of rows, not just one; so even if you have one, you still need to convert that single value into a list (that only has the one value, I know… but it’s a matter of data type).

That’s what you should do in your action, enter the column that’s the reference and wrap it with LIST()

View solution in original post

MultiTech

Hello Juan, and welcome!

You can absolutely do what you’re wanting, and you’re right to assume it’s Actions that would do it.

You can create an action to modify the Status column, then create a workflow on the transaction log table so when something is order it triggers the Status changing action.

You can read more about actions here:

And workflows here:

juan_walte

Hi, Thanks for the quick response. I have tried something like this but I have a couple of questions which I can’t seem to connect the dots here:

I created the action for the “Inventory sheet” that will update the status. However I have no way of knowing how it will match the part numbers, I literally just said “update column to received” but I dont know what logic its using to verify that it’s doing it on the appropriate row.
When I go to workflows The “do this” part doesnt list my action since that action is not part of the same table. I dont know how to tell the “do this” part to target a different sheet rather than the one that triggered it.

MultiTech

You’re wanting your parent status to update when a child record is created, right? There’s a ref connection between your two tables, yes?

juan_walte

No, as far as I know there is no reference from one table to another. I do have references in the table but thats within the spreadsheet itself appart from Appsheet. I think that might be the missing link I’m lacking. How to connect and create parent/child relationships

MultiTech

juan_walte

Thank you for your time and patience. It’s been very helpful. I was able to create a reference from one table to another so now the part numbers are shared across both tables. additionally it created a reverse reference that nicely links to the transaction log per part number.
I have 2 sheets:

Inventory sheet
Transaction Log Sheet

I created an action on Inventory sheet to “update column data” on PR status to “Received”
I went to workflows and added a new workflow that triggers when I add a new transaction log, however there is no way for me to select the action on the Inventory sheet if it is being triggered by the Transaction Log. The “Do This” part doesnt let me select actions that are not part of the same table. Additionally the “Formula” selection on the Actions has a drop down to select rows and I cannot use the nested reference [TableName].[ColumnName] = “Received” as an option since it only allows me to set the value not the receiver of the value

MultiTech

There’s three parts to making this work:

Parent update action (which changes the status of the parent record)
This a simple data change action on the table being modified:

Child ref update action (which initiates the parent update from the child table)

and a workflow, on the child table, to initiate the ref update action.

If you look at the picture above, you can see that the action lives on the Line_Items table

but it’s doing an action to a set of records on the Logs table

It’s here that you can then select the action on the table you want to update (the parent table in your case)

You’ll likely need to come up with a condition for your workflow so it only updates when it should, but this is the gist.

juan_walte

Thank you once again. This seems to be working now but how did you link the “rows” that need to be updated?
I just tried a sample action and it updated the entire table to “received” instead of just the line item.
Just for clarification. I have a PART NUMBER column in the “Inventory” sheet and a PART NUMBER REF COLUMN in the transaction log. The goal is to only update the status of the PART NUMBER that matched, as of now it’s updating the entire inventory sheet instead of just a line.
My Referenced Rows say: =Inventory_Sheet[Part Number]

MultiTech

Exactly, that’s what the Ref column is for.

In the ref action, there’s a place to specify the

In the example below you can see that this is more specific to your scenario.

This action lives on the child table and updates the parent - exactly what you’re wanting to do.

You can see I’ve wrapped the [Log_Phase_Link] in a LIST() formula, this needs to be done because that field, where you’re specifying the rows to update, is expecting a list of rows, not just one; so even if you have one, you still need to convert that single value into a list (that only has the one value, I know… but it’s a matter of data type).

That’s what you should do in your action, enter the column that’s the reference and wrap it with LIST()

juan_walte

Thank you very much. Finally got it to work as I wanted!

MultiTech

Sweet! (^_^)