I am trying to figure out how to make a list that contains a list of a set of items but I want to turn certain items off for varying conditions. What I have is a boat booking application. People book a certain type of boat say B1, B2, B3, B4 for a time during the day. There is a limit of each type of boat ( eg 4 boats of type B1, 5 of B2, 3 of B3, 10 of B4). The time slots are fixed but can overlap with each other. As boats get booked I can figure out the number of boats left for each time slot already but when a boat count goes to zero I can not figure out how to take the boat out of the list. For example, if there are no bookings the list would show B1, B2, B3, B4. Once bookings started happening then say there were no boat type B3 left I want the list of boats to be B1, B2, B4.
You can filter the drop down list. You should be able to add a filter when boats available < 1.
On the list, go to Features. And filter should be there for you.
Yes that works if I have a column of boat names beside a column of boat counts but there are several booking times in a day and so the boat count is varies (or can vary) for each time slot depending on what boats are booked for when.
I’ve got something that sounds similar and may have a solution. Can you post a screen shot of the list or share a Sheet?
Will try with some screenshots first.
In one tab (Book) in my sheets file I have this (some columns hidden)
In another tab (Data) I have
In the app I can book a boat off this screen - corresponding to a row in the Data tab
Which currently gives me a boat choice from the Data tab
What I want to do is… based on the value in Column C-Lion thru Private if the value is zero I want to remove it from the list
Your first two screen shots are the same. I think you meant to upload a different image for the DATA tab.
Data tab
BTW sorry about that and thanks for the interest
This would be trivial if I could get a choice list to be gebnerated from a cell that contained a delimited list of values (comma separated)
Really, you could sort of generate a choice list from a delimited list. You can always filter a choice list, but since you have a pretty static list of choice values, and if you already have a way to build a delimited list, then you can take the delimited list, create a Split Text column, which will turn it into an array, then create a relation column to link that array to the sheet that contains all your possible choices. Finally, you can point your choice component to the relation column. In that way, you can ultimately drive list of possible choices from a delimited list in a single cell. As long as final outcome is a list of choices that are contained in rows, then it will work.
That was my problem I did not know how to get the data into rows (an array) from columns.
So if I had separate columns with the names in them on each row (and I name them properly) can I merge that into an array using an array column and then point the choice list at that array column
Not exactly. A choice component can only be built from rows, but if you follow the steps I highlighted above, that array can drive a relation to a sheet that has the data in rows. That relation would directly drive the choices, but indirectly, the array would drive the relation.
Yes, I tried it and the array column does not show up in the choice list options but if it had worked it would’ve been trivial. I will work a bit on the suggestion you gave me and if I can not figure it out will ask for more details.
Well I tried for a couple of hours to figure out what you meant Jeff. Here is what I did in the spreadsheet.
In the spread sheet each row is several bookings and on each row the type of boat available can change. the TEST column is the comma separated list to display in the Choice list
I can get this into the Glide data table where I split the delimited list
What I can not figure out is what you meant by “then create a relation column to link that array to the sheet that contains all your possible choices.”
I assume you have a sheet that lists each boat type? Let’s assume the sheet is called Boats. Create a new column type of Relation in the glide data editor. Set it up to link the boat types column you created to the boat type column in the Boats sheet. Then use that relation column for your choice component.
Okay. I got done what you described (I think). Here is what I did. The Boats tab is
To the glide sheet that tracks the bookings I added the relation column xxx
Where sheet12 is the Boat sheet above
Now when I go to the choice widget the column xxx does not show up in the Choice List > Values selection.
But I know the relation is done correctly because if I use a Inline List or a Relation List and pick xxx the proper data is displayed. The issue is that choice list does not allow us to use a relation to “load” its values.
You shouldn’t be looking for the relation under Values. You should look for it under Sheet. You want to set the relation as the source for you choice component. The values will come from the Boats column in Sheet12, but through the relation.
Marvelous. Jeff thank you so much.
I did find out that I can use a Rollup column (at least I think that is what it is called when you name column all the same except for a digit at the end) in the glide sheet to generate the boat names to use in the relation which makes it a lot easier than generating a Comma separated list. The expression in the spreadsheet are much simpler.
Something I never foresaw was that using a choice widget if you take the value out of the choice widget values then it can not display the one you took out. So in the case where I run out a boat type - for the people that have booked that type of boat the choice widget no longer displays the value and they think oh no I do not have a boat. So now I have to have a component that displays what they currently booked and put the choice widget below it. If I find a better solution I will come back here and let you know.