I don’t know if this a bug or a limitation but I try to understand why Glide doesn’t allow to write array column’s values created by a Lookup (multiple relation) to any Google sheet directly?
I could create a dynamic list of selected items by a user using relations, templates and put all things together in an array column via Lookup as you can see in my screenshot (the user chose 5 items of 10 possible).
An array column with multiple values can’t be used with a Template, Math or IF-Then-Else tool and this is understandable but why can’t it be written to GS using a Form’s column?
I just to need to write these values to GS and later I might modify it, change its format or something else. I don’t see any problems with it … or am I wrong?
Any idea is welcome and a little modification inside Glide as well.
Because probably Glide is not sure how we want that to be recorded in the Sheet. Do we want to combine them into one cell, or multiple cells from adjacent columns.
Here I put a GIF to show you what I try to get (I know you hate GIFs but a set it to almost slow motion )
The steps are (sorry for Ping Pong effect that sometimes appears, it is like a plague) :
You choose the restaurant (Firenze)
See its menu to select dishes
Check mark the dish and quantity to ask for and with every clic on dish name, its quantity +1 (if you want to change quantity due to error, just uncheck the dish and start over)
While you are selecting dishes and their quantities, the APP shows the total order amount in a green box at bottom
You can see your list of dishes and quantities in the Inline list I put bellow as well (horizontal orientation)
Up to here, everything is normal but using Data Editor you can see:
The Restaurant name colum
The selected Quantity column
The Dish name column
A Template column to join Qty+DishName (TP_CantyPlatoPedido)
The famous Lookup column where I put all dishes selected by user but using only my Template column indicated aboved. Here I have my dynamic list (3 products of 10 possible) ready to send the order and write to GS but, Glide doesn’t allow use a lookup column if this looks like an array with multiple values. It doesn´t make sense!
My Lookup column is an array unidimensional (LK_ListaPlatosPedidos) and could be written to any cell in my GS without problem. With regard to the format and delimiters, I understand the problem but I can live with a simple list separated by a comma, slash or whatever that Glide wants to use (I can configure my Template to separate/split items).
If I copy the Lookup column value from Data Editor manually and past it into a cell, I get a wonderful result as my picture shows…(no spaces, no empty data, etc… only the info/values found by my lookup as a list). It’s what I want!.
If you have a plan C to get it I will appreciate it although I think that a little modification inside Glide could fix it forever.
I could read your posts and the little discussion about the script used in your APP and I understand both points of view. but I think Wiz was refering to use scripts in general to these cases and not to your specific script but anyway, l’m not going to re-open that misunderstanding.
The scripts have these problem with GS when a spreedsheet has many movements or changes and in my opinion, GS loses the control of events queue therefore, the triggers are not fired always. I have seen and dealt with these issues before in other real-time systems including the annoying ping pong effect!
If Glide can modify a little (or eliminate) this restriction and let any lookup (multiple relation) can be written to one cell in a GS, we will save much effort, scripts, columns and the life will be easier.
And now with the new Sheet Edits quota (1000 per month) on Free apps, the classic procedure used by custom Shopping Carts where each item/product is written to GS using one (1) new row now, it wil consume 2 “edits” and any APP will have problems with its capacity and funcionality very fast. It’s not my case because my APP is a Pro but I’m thinking about new users and their no complex APPs.
This improvement or change associated to lookup (multiple relation) can help to many people with medium/big APPs.
May @Mark give us a hand with it in order to have a final solution or hope? Sorry if I bother you my virtual friend.
Mark, the problem we have been facing is a very simple one. We are not able to transfer multiple items created using a multiple relation to a new sheet via a lookup. Below is an example:
Menu Sheet
Customer X selects following items from sheet “Menu”:
Item 1 (on row 1)
Item 2 (on row 2)
Item 3 (on row 3)
Orders Sheet
All 3 items get passed to sheet “Order” in the following order:
Customer X (fetched into sheet “Order” either using Lookup or Column) Item 1 (on row 1)
Customer X (fetched into sheet “Order” either using Lookup or Column)Item 2 (on row 2)
Customer X (fetched into sheet “Order” either using Lookup or Column)Item 3 (on row 3)
Sheet “Confirmation”
This is where it all begins to get complicated. Let me take you through the process item by item:
The normal process/workarounds
When X confirms order, the Confirmation sheet is created but with only 1 item (not all 3, and we will come to why?). If that is so, then how was I able to show all items on the “Confirmation” sheet (without using scripts). This was just a trick many before me used and we all still sometimes over use. We are able to perform this trick by pulling the “Orders” sheet as an inline list, displaying only the items ordered by tying the items to X. All good and well! So what’s the problem>?
The Problem
The problem: If we set the “Confirmation” sheet to edit in- app, and we have to to save rows, what gets deleted is not what’s on the Confirmation" sheet but "what’s on the Orders sheet. When this happens, all we are left with is the Qty and total price, email of X etc. Poor chef does not anymore know what he needs to prepare!
Solutions:
A. Google scripts - my opposition to scripts is not a personal one but a common sense one. We are past the age of google scripts. Our competitors are growing by the day, if not hour, making all sorts of wild claims. Give us the tools mentioned in B below, and we will take the competition to the so called competitors.
B. Glide script: currently, you can paste any single row to any row on any sheet you like via a lookup, provided a single relation has been created between the shits in question Give us the ability to do lookup of multiple items by row/column, whether created by single or multiple relations. This would give us the ability to pass all 3 items (in our example) to the Confirmation sheet.
My request is simple, as you know, a lookup is an array column with multiple values. Any lookup using a simple relation can be only used with a Template, Math, IF-Then-Else tool or into a single value component, this is understandable but what if the lookup uses a multiple relation and its result generates a kind of list (like a unidimensional array column)?
I’d like to write that list (lookup with multiple relation) to one cell in my GS in order to have that info and handle it later. Something like my picture shows bellow:
To get it, the only way is using a Form and associate my lookup colum to one cell of my spreadsheet by choosing a column component but currently, Glide does not allow it, only lookup with a unique value (single relation) can make it. Why? I don’t know
If Glide can generate dynamic lists using multiple relations why we can’t use these lists (working as an array column) and save it to GS? . On APPs like Delivery where the user can select N items from a list, this improvement will save a lot of work and data without creating more columns, templates, math operations, etc.
I hope it can help you to understand this problem which has a simple solution: allow the Form’s Column component to use a lookup (single or multiple relation) and write its data to one cell in any spreadsheet.
This makes me wonder if crossing the bridge between Glide Editor and GS is a difficulty for non-image Array columns in general. My challenge, for weeks, has been going in the opposite direction:
Multiple TEXT columns in GS (heading 1, heading 2, heading 3) can’t be automatically merged as an Array column in the editor and therefore cannot be used as options in a choice component and later “looked up.” Many people here are painfully aware of that limitation.
I want to save my list (array) in GS and use other GS functions to modify if I need it but on my APP is my final goal!
I only want to have and save the items/dishes list that an order has.
Tomorrow, this info could be a list of absent students, list of missing books or a list of red cars (year, model and qty using a template) that a parking had 2 weeks ago!!
To sum it up, I need to log the order’s dishes list every time my APP will receive an order.
I have a similar use case but not necessarily for writing back to the sheet.
I have lessons and for each lesson I have a formula to build a single <tr> row tag for an html table (yeah yeah, I know…html…). In another sheet that is dynamically built from a query, I use a query in one of the columns to go to the lesson table and take all of those preformatted lesson html tr rows and join multiple together while wrapping it in <table> tags. This final html table is what I display in the app. This could easily be done with dynamic markdown tables as well, but right now markdown isn’t functional enough for me. If I could do something with a type of lookup column to pull back multiple rows of column data concatenated as a single string, then wrap it with a template column, then I could accomplish the same thing entirely within Glide without the need of a query in the sheet. This would remove one of the two columns left in my sheet that slows it down considerably (currently use a completely separate helper spreadsheet…not a sheet tab…entire spreadsheet to handle the background calculations of the two heavy columns. Using ImportRange to move data back and forth between the two sheets.).
Maybe a new column type could be considered that functions like a lookup, but concatenates to a single string and where you could specify a delimiter, much like the number column let’s you specify units. I could see character limits being hit pretty quickly, so maybe some limits or a disclaimer would be needed?
I know there have been several questions regarding multi select or attendance type apps that could have a dynamic list of participants, but save the results in a single row. I don’t know what the best method is to achieve this, but I’ve outlined some of my thoughts here:
I also know it comes up a lot with ordering apps where a user may want to customize a food item with different toppings or allergy requests. Stripe doesn’t allow glide only columns, so I suppose people try to create an order record to use for that particular food order. Having to relate that order back to the food item, them back to a list of specific toppings or requests for that food item can get overly complicated when all that’s needed is a single column value that was built from a concatenated list. I don’t, just throwing in my two cents.
I was going to relaunch my Delivery APP (Pro version) using this feature but without this capability nothing will make sense.
I have almost a week stucked at that point and don’t want to continue if Glide can’t improve this detail according to my point of view.
Thanks for your help and I hope I can receive good news.
@Mark I wrote it above but anyway I am going to explain it again, no problem:
Due to the array data could be a list of items (dishes, names, cars, etc) belonging to an order (my case), I need to log that order into GS when a user will send all info via a Form (checkout process).
Glide can’t hold and save this data (all orders) forever in Data Editor or cloud so, I save the order info (Order ID, Date, Customer Name, Address, Payment Type, Order cost, etc) including the list of dishes that a user wants to buy to GS to have a historical orders log.
This list which can have many info/columns together (using a Template) is dynamic and can have many elements as you know by saving the whole info into only one cell. With it there is no need to create extra columns to know how an order was created or its items. I mean, we avoid creating/having columns in GS like: Qty-1 | Dish-1 | Qty-2 | Dish-2 | Qty-3 | Dish-3 | … Qty-N | Dish-N |
What I’d like to do is have a computed column “Join texts” or something, which you can use to combine the individual items into a single text column, which you could then write to the spreadsheet.
Create many columns as items/dishes a menu has (static schema). I mean, if a menu has 8 items we must have 8 columns to make the “join texts” column works using any combination but what if the menu has 12 items tomorrow? … we must create 4 new columns manually to get your idea works again!..
What if the menu has 200 items (it’s a list with 200 rows) and a user can choose 200 items to buy? Must I create 200 columns to save any combination and use them in a Template?
That doesn’t look smart!
With my idea:
The menu (list) is already made and there no need to create new columns to use them in a Template column later.
The user choses the amount of favorite dish from the list and this my flag (>0) to know the selected dish and quantity.
Later, I make a relation/lookup searching those rows with qty>0 (I’m not using columns!) and magically I have my order list created dynamic and automatically.
But that order list is in a Lookup column (multiple relation) and it is what I want to write to GS.
I don’t know if my english is confused to explain this case and makes it clear but maybe @Wiz.Wazeer or @ThinhDinh can help me due to they have faced this trouble before.
You explained it really well. I don’t think I can improve on that. If Mark could give us what you and us all have been requesting, we are onto greater things for sure. But if not, anything that helps will have to do.
)
