Array Column As A Choice Input

I have several columns Hub 1, Hub 2, Hub 3 in my user profile sheet. Those columns are being combined into a single array column in the glide data editor as Hubs. I want to a choice component in a form to pull from this array column. But the choice component only allows me to pull from Hub 1, Hub 2 or Hub 3. What am I doing wrong? How can I get a choice component to allow me to show all the choices that are listed in Hub 1 through Hub 3?

can anyone help me on this one? thanks in advance for helping.

Hola @Food2Soil,

Do you want the choice component to show the 3 elements in your 3 columns? A choice component will always get info from one column.

This is been a feature request for over a year. Choice components can’t be populated from array columns. They can only be populated from relations or individual rows.

1 Like

oh shucks! Why oh why!
Thanks so much for clearing this up. I’ll look at a workaround for now.

Actually, I might as well ask for help in figuring out a workaround. Here’s my use case.

I am building a backend supply order system where stores can order from a catalog of supplies.
I have one sheet for STORES, another sheet for STORE MANAGERS and a third sheet for SUPPLY ORDERS.

One individual can be in charge of several stores. So the user profile (Store Manager sheet) has columns Store 1, Store 2, Store 3 which is used to build an array column Stores. User profiles is enabled in the app and linked to the STORE MANAGER sheet so user details can be captured automatically.

To place an order for supplies, the manager goes to the store screen and selects the catalog from within the store screen. This takes them to a screen where they can use a button Add+1 to add one item to the order list. This Add+1 button is using the add row action to capture the signed in user’s information and the row id of the supply item. Its all working perfectly except for the part where I need to also capture the store for which this supply is being requested at.

Since I’m using the ‘add row’ action it needs to populate a value for the Store field. I am forced to populate using whatever value is in the Store 1 column of the user profile sheet. But the correct way to do this would be to give the user a choice from the Stores array field.

I realize that the way I’ve set this up I won’t be able to use the ‘add row’ action since there is no way to integrate a choice component in that action. Is there a way to capture the store through which the manager accessed the catalog and use that to populate the store field in the order sheet?

Is there another way you would do this? I really like the Add+1 action so don’t want to get rid of that.

Thanks so much!

You should be able to create a multiple relation column from the store array column to the stores sheet. Then, use that relation to populate the choice component.

2 Likes

yeah, I’d do this a little differently. What you have doesn’t really scale, and (as you’ve already discovered) will have you jumping through hoops to make things work.

Assuming that a store will only ever have one manager at any given time, then I’d simply make “Manager” a column in your Stores table. Then you can drop those “Store 1”, “Store 2”, “Store 3” columns and just create a relation between the two tables based on user.

And your choice component becomes trivial, as you can just list the stores and filter by manager.

1 Like

Thankyou very much. I’m going to give this a shot.

How would you do this if a store had two managers?

The simplest way would be to create a 3rd table, call it Store Managers.
This table would just need two columns:

  • Store ID: a reference to the Row ID in the Stores table
  • Manager ID: a reference to the Row ID in the Managers table

The table will contain one row, per store, per manager.
So in the case where a store has two managers, there will be two rows in the Store Managers table - one row for each manager.

  • To get a list of all managers for any given store, create a multi-relation from the Stores table to the Store Managers table, linking Stores->Row ID to Store Managers->Store ID
  • To get a list of Stores assigned to any given manager, create a multi-relation from the Managers table to the Store Managers table, linking Managers->Row ID to Store Managers->Manager ID
2 Likes

Okay. that will work and streamline the app for scaling. Thankyou.
With this setup how do I use the ‘add row’ action so the row that gets added remembers the store name from the previous screen. I see that there is a way to copy the store name to the clipboard. Is there an action that will allow me to paste that value also? Any other way in which I can save the store name from the previous screen. Just so its clear the workflow is as follows:
Store name tiles - Go to Supply Catalog tiles - Select Supply - Add one item by clicking on a flash button.

There are a few ways to do this, but what I would probably do is write the Store name (or Store ID) to a User Specific column as part of the navigation action.

Just a note on this.
The copy to clipboard action is for the benefit of the user, not the developer. What I mean is, it’s a one way action. It’s a bit like the send webhook or send email action. Once you’ve done it, the data has gone off into user land (or wherever), and there’s no way (that I’m aware of) to get it back.

Hello Darren, can you walk me through the steps on how to do this. I haven’t used user specific columns before and don’t know how to store a value using the navigation action. Thankyou very much for your time.

Hello Darren,
I am sorry for bugging you on this topic. But I’m stuck and I think it might be a simple tweak which is eluding me. I’ve figured out how to use User Specific columns and write the Store Name to this column in the data editor. But am not able to figure out how to clear out previous values from this user specific column. Here are the details of the spreadsheet setup and where I am getting stuck.

Opening screen shows different stores in tile format. Note that Manager A manages Store 1,2,3. Manager A selects Store 1 and clicks on the catalog button. This selection of the catalog button triggers a multi-step action which
Step 1 - the store name is stored in a user specific column titled Store_Active in the stores sheet and Step 2 - takes the user to the catalog of items.

I’ve created another column in the user profiles sheet which is a joint list of the Stores_Active column. When the user clicks the Add+1 button it adds a row in the Orders sheet for that specific item. The Store Name is retrieved from the Store_Aactive joined list via the set column action from the user profiles sheet. The issue is that because Manage A manages Stores 1, 2,3 the joined list shows Store 1, Store 2, store 3 for this order.

I think the solution is to not use the joint list column. If so, what column type do I use to retrieve the user specific value. Or perhaps the solution is that the multi-step action needs to first clear all values in the user-specific column before writing the Store Name through which the user clicked the catalog. But I cant figure out how to clear an entire column.

Thanks so much for your time and guidance.

More than happy to help with this, but to save time would you allow me to make a copy of your app?
If I can see it in front of me it will be much easier to visualise the challenge and come up with a solution.
You can send me a private message with the link if you want.

Ofcourse! The app can be copied from this link https://profuse-magic-8651.glideapp.io/
Thanks so much!

You might need to remove all excess rows from the GSheets.

please try now
https://hideous-cap-4871.glideapp.io/
I’ve removed all extra sheets and features. Its just showing you the stuff related to the store and the catalog.
-Sarah