Changing "status" in two separate sheets

If you have an “item” that has a status - for example a product.
When the product is ordered, this information is added to another sheet called “Orders”
and “status” is changed from available to ordered.
But how do I keep two cells synced?
So for example if I change the product back to available, I would like the status in both sheet columns to change as well. Basically keeping the entry synced.

I think what you want is only update the original item’s status, then in the Orders sheet, you make a relation back to the Product table and use a lookup to get the status over, if you want information to appear in both sides.

Assuming you’re using a form to do this, the on submit action of the order form would use the relation back to the Products table to set the status to Ordered.

Then anytime you make a change to the status in the Products table, it will be updated via the lookup as stated in first paragraph.

1 Like

It’s a little tricky because when a user orders that product (using a form), a new line is created in the Orders sheet with details of the product also retrieved automatically from Products page, but I can’t seem to be able to change the status in the first Products sheet…

If you want to sync, you don’t have to write the status in the form submission. In the Orders sheet, create a relation back to the Products and retrieve the status through a lookup instead.

Ah yes… but the status on the Products page is originally Available, and when it’s ordered (using form) then it should change to Ordered in both sheets. That’s what I can’t get my head around.

For the on submit action of the form, can you try using a set column using the relation as I described above, and set the status column to “Ordered”?

Let me try that.

Also, here’s a quick brief of how the app works:

  • User adds a product (it’s labeled as Available in Products sheet)
  • When another user orders that product, change the status in the Products to Ordered instead of Available, and add the order details to Orders with the status now updated (synced) to show Ordered as well
  • Later if product is returned or process doesn’t get through, should be able to change the product back to Available…
1 Like

Let’s try that and let us know. We have other ways to try if the method above does not work.