Get dropdown values from column in another table / Write back to second sheet

[dhdevans]

Hi guys, back again with a simple sounding problem that is flummoxing me.

Background

I have two distinct Google sheets (not tabs) that require to be linked in this app and writeback be performed on both according to input in the app.

Sheet 1: Customer details and orders belonging to Department 1 e.g. John Smith's details and his order for product 1

Sheet 2: Product details belonging to Department 2 e.g. product 1 and its associated properities.

The app is being designed to use Sheet 1 as the primary data driver. For example, if John Smith orders product 1 the app user will mark when it is delivered through the app which will write to Sheet 1. This action needs to then write to Sheet 2 to changed product 1 to delivered.

Both datasets have come under my purview now where they have previously been owned by two separate departments before anyone asks why this wasn't designed as an integrated sheet...

Problem 1

When selecting John Smith's record in Sheet 1 the option for products requires to come from a column in Sheet 2. Products have a unique 3 digit number. I cannot figure out how to make the column go "Right, the values required to populate this dropdown come from Column X in Sheet 2". 

If i try using LIST its gives me a long comma-separated list of all the values which is not helpful. I require individual values to be selectable. Any tips?

Problem 2

Sheet 2 requires to be updated from the outcome of user choices in the app. For example, the default values for the stock column in Sheet 2 is 'At Warehouse' which indicating the product is available. If the user selects the unique product in the app and also changes the app record to 'delivered' we require this to write back to Sheet 2 so that the unique product is marked as 'delivered' and is no longer available in inventory.

How do i get my app to write back to Sheet 2 on the basis of values now written into Sheet 1?

[jyothis_m]

References between tables

execute an action on a set of rows

The above links will help you get started.

Problem 1 : use column type as Ref for Products column in sheet 1 with reference to sheet 2. 

Problem 2 : create a BOT to execute a data action to execute action on a set of rows. Thereby you can update sheet 2 when sheet 1 is updated. Also you can specify condition for BOT trigger if delivered. 

Sorry that I can't help you with any image references for better understanding since i am posting this reply through mobile. 

[Steve · Accepted solution]

---

@dhdevans wrote:

When selecting John Smith's record in Sheet 1 the option for products requires to come from a column in Sheet 2. Products have a unique 3 digit number. I cannot figure out how to make the column go "Right, the values required to populate this dropdown come from Column X in Sheet 2". 

---

The "lookup/dropdown" data pattern - AppSheet Help

Drop-down from Valid_If - AppSheet Help

---

@dhdevans wrote:

Sheet 2 requires to be updated from the outcome of user choices in the app. For example, the default values for the stock column in Sheet 2 is 'At Warehouse' which indicating the product is available. If the user selects the unique product in the app and also changes the app record to 'delivered' we require this to write back to Sheet 2 so that the unique product is marked as 'delivered' and is no longer available in inventory.

---

Try a Form Saved event action:

View events - AppSheet Help

[dhdevans]

Thanks Steve. Due to some quirks with Appsheet i had to rebuild the app, disconnect the interim sheet (Sheet 3 which was a readonly version of Sheet 2). Appsheet seems too clever for its own good, finding values for fields which are connected, but disconnecting these fields is difficult.

The data change aspect is still perplexing me. Sheet 1 has columns 1, 2, 3 and 4 which contain values present in a single column in Sheet 2. How do i go about setting something up that will look at all four columns on Sheet 1 and update the single column in Sheet 2?

[Steve]

So values that occur in any of several columns of sheet 1 determine what values occur in a single column of sheet 2? That seems a little backward... 🤔

[dhdevans]

Tell me about it 😅

Effectively, if a product value occurs in columns A, B, C or D in Sheet 1 then it should update the stock value in Column B in Sheet 2. And yes, I realise we could end up with duplicate values and the data manager in me is raging(!).

Two separate teams developed two separate systems and muggins here has been asked to develop a system to link both with a nice friendly GUI.

Anyway. I am now thinking i'm going to need some sort of nonsense bridging sheet in Sheet 1 that unpivots the columns and acts as a marker to update Sheet 2.

If I solve it that way i'll post an update.