Lookup against an array of column

I’m not sure if this is a bug, but I have a sheet with 2 email columns ‘Email 1’ and ‘Email 2’. I am attempting to create a relation column within the same sheet that links the Email array to the same Email array. I know this seems weird, but I’m doing this to pull back a relation of all rows that contain at least one of the two emails. The relation appears to be working just fine. What I want to do is then create a Lookup column against the relation that pulls back every email, from the Email array, of every row that contains one of those emails. The problem I have is that I can only pull back either Email 1 or Email 2 into the Lookup column even though it appears that the email array is one of the options for the lookup. I don’t know if this is a programming limitation where I can’t pull an array of values into an array lookup column or a bug. What I’m trying to do is create a lookup column that can be used for per user data so a user can view all rows where there is at least one common email among all of the rows.

This is my attempt at a simple way to resolve the issue below. I think I could do this with 2 identical sheets and ultimately just the relation column I created. One sheet for per user data, and then the second sheet for the relation to an inline list. I was hoping to come up with a simpler method that didn’t require 2 sheets.

Edit: I forgot to post the thread that I was referring to.

1 Like

Interesting… I’m not sure. I’m going to ask Mark about it though!

1 Like

So you want to do a lookup into a multi-relation, to an array column? I assume you expect the result to be an array that’s a concatenation of all the arrays in the rows in the relation?

Yes, that’s exactly what I’m trying to do. I was hoping to have the email array column (retrieved from the lookup), joined into the result of the lookup column array. So essentially 2 email addresses would be joined at a time, for each iteration, to the lookup column array result for every matching row of the relation. I realize this is a pretty unique scenario, and especially with me creating a relation inside of a sheet to itself, but I think the same could apply when setting up a relation to a different sheet. I was hoping this would work since a Lookup can be an array. I’m assuming this would require some sort of array loop within an array loop, but I don’t know if there would be some limitations on your end.

This may not particularly be a bug and may be a feature request instead. I only marked it as a bug because if you look at my screenshot, you can see that ‘Email 1’ is being shown as ‘Email’ when setting up the Lookup column. The two columns in my sheet are ‘Email 1’ and ‘Email 2’ which creates a virtual array column of ‘Email’. The lookup shows ‘Email’, but I think it’s functioning as if it’s only pulling the ‘Email 1’ column.

In it’s current state, there are some pretty powerful things that can be done with the array functionality in Glide. I have also experimented in the past with creating “Relation to Lookup to Relation” or “Relation to Lookup to Relation to Lookup”. It’s awesome when you can create complex relationships like that.

There is no limitation regarding that, it was just that we weren’t quite sure what the right/intuitive thing to do was in that case, but it seems your intuition and mine assumed the same thing, so it’s probably at least not completely wrong. I’m also not sure what the alternative would be for a multi-relation array lookup.

1 Like

Hi @Jeff_Hager and @Mark

My case a little different but I have an array trapped in a lookup column which I want to write to my GS but I can’t find any way to do it.

My case is:

  1. I have a products list associated to a restaurant (e/o has its own list or menu)
  2. The user selects from list any amount of products (a restaurant menu can have 10 items but the user may select 2, 3 or 5 items to order)
  3. I joined the selected items and quantities into an unique list by using a Relation and Lookup. It is what you can see in my screenshot.
    That “list” of course, is an dynamic array!
  4. Up to here everything is ok but the next step is to write the orden (my list trapped in a lookup column) to my GS but Data Editor doesn’t recognize my LK_ListaPlatosPedidos column as valid to any component able to write to GS.

I can understand the cause of this behavior (it´s an array with several values) but what if I want to use the result in my GS and later handle and modify it using other functions that Glide doesn’t have?

Is there any workaround for it or better… will it be possible make a fast improvement to allow to write a lookup result into GS directly?

I’m stucked at this point on my APP due to this unexpected phenomenon :thinking:

@ThinhDinh btw, have you ever had this problem?

Thanks for your time and help

Feliz día!