Does Glide have the ability to compare two multiple relation columns? I need to check if the row values of one multiple relation column are contained in another multiple relation column?
Not as a direct column to column compare, but you could create a lookup column against the first relation, then use that lookup array to create your second relation. It will look for any match, so if one of the items from the first relation lookup is found in any of the items in the second relation, then it will return the row.
4 Likes
I described the task a little incorrectly. I need to compare the values from the first relation lookup with the second relation.
I need to find matches in lookup columns.
Doesn’t change my answer, but if it helps, create a template column in the related sheets that joins whatever value you are using for the relation, with the column value that you have in your existing lookup. Use that template as your lookup against the first relation. Then use that lookup to create your second relation.
1 Like
Thank you. I will try.
1 Like
I do not want to create a new topic, so I will ask in this one.
I have two lookup columns: one contains a list of objects where certain actions need to be performed and another that contains objects where actions have already been performed.
I need to get a list of objects where actions have NOT been performed yet.
Looks like a simple subtraction of the second column from the first, but I haven’t been able to solve this problem for 2 weeks now …
Hello Sir, Did that solution not work for you? surprised becz it’s quite effective.
- Are you using an inline list?
- Columns are on two sheets?
- Filtered by when relation is not empty?
4 Likes
Now everything works!
The problem was with the translation. The meaning was distorted and I did not fully understand what you are talking about.
Thank you for persistently imposing a good idea ))
2 Likes
@Jeff_Hager I don’t seem to understand the solution… perhaps due to my lack of understanding when it comes to relations.
I have the following tabs in my sheet
- ‘Projects’
- Applied ‘Openings’ Per Project
- Applied ‘Phases’ Per Project (All phases will apply to each opening)
- Submitted ‘Records’ per opening
Looking to compare 2 columns
Column 1: In the Openings sheet, a Related Column containing phases for this project
Column 2: In the Openings sheet, a Related Column containing submitted phases for this opening
I’m looking to display the non submitted phases per opening as an inline list in the app (So I guess extract the non submitted phases for this opening, based on the submitted phases from the records sheet)
Hopefully I’m understanding correctly. Each project contains multiple Openings, each Opening contains multiple phases, and each submitted record is for a specific phase. Does that sound correct?
So as I understand it, you want a list of Phases for an Opening that don’t have a submitted Record? I think we can simplify this instead of trying to compare two lookups or relations. I think your original Column 1 relation in your Openings sheet is fine as is. You should be able to use that for your inline list. The column 2 relation can go away. Instead, I think you should be able to create a relation in your Phases sheet linking the phase to the submitted phase sheet. Then you should be able to filter your inline list where the phase to submitted phase relation is empty. That should make your inline list show only the phases that haven’t had a record submitted yet.
1 Like
Yes, that would work if I listed the phases per opening.
However, I only list the applied phases per project and therefore cannot extract whether an applied phase per project has been applied for this specific opening within the project.
I really appreciate your help on this 
Maybe it will help if you explain your sheet structure a little more. So are you saying that all phases in the phase sheet apply to each opening, so each opening has the same number of phases? I’m just trying to picture how everything ties together.
Yes, each opening has the same number of phases -
However, the applied phases per Opening, depends on the applied phases for this project.
e.g.
Project 1 has 6 phases which apply to all its openings
Project 2 has 4 phases which apply to all its openings
Objective: Record submission of each phase per opening
And as mentioned previously, here’s the structure of the google sheet;
In my previous post is the screenshot of the ‘phases’ sheet for reference
I’m trying to think of a good solution, but this is a complicated scenario. The problem with your original idea to compare two relations is that it’s not exactly possible. The original problem in this thread was that the user wanted to find items in one list that were also in the other list. You are trying to do the opposite and find find items in one list are NOT in the other list. Your situation makes it a lot more complicated because a relation looks for matches instead of items that don’t match. I’m still trying to come up with a simple idea.
I do have one idea though, that I know works, but it’s a little more complicated:
- If you have user profiles, then first create a new column to temporarily store an Opening ID or Name
- Then in your openings sheet, create a template column that gets the signed in user’s email from the user profile. Then create a single relation column that links that template email back to the user profile sheet.
- On the list of Openings, create a custom action that will first set the Opening ID or Name, through the relation, to the new column in the user profile sheet. Follow that with a Show Detail Screen action to show the details of that opening.
- In the Phases sheet, then create a single value column that will pull in that saved Opening ID, from the user profile, into the Phases sheet. Then you create a relation column that links that single value Opening ID to the Submitted Records sheet. Then you create a joined list column that will bring back a delimited list of submitted phases. Then create an IF column that checks if the phase is included in the join list. If so, then return ‘true’, else return ‘false’.
- Now you should be able to filter your inline list of phases (that uses your original Column 1 relation) where the IF column value is not true.
So ultimately, what should happen is that whenever you click on a phase, it temporarily stores that Opening ID to the user profile sheet for that specific user. Then that Opening ID is passed to the Phases sheet to get a relation of submitted phases for the Opening. Then the rest of the logic will check to see if that specific phase is included in that related list and give you a boolean value that you can use for your filter. I know it sounds complicated, but that’s all I can think of at the moment. I’ll keep thinking some more to see if I can come up with a better solution. Unfortunately, we can’t compare an array to an array to find items that don’t have a match, like you were hoping to do.
3 Likes
If Eden’s willing to use YC then I have a prototype ready for him.
Direct link: https://glide-yes-code-array-difference.thinhdinh.repl.co
Replit link: https://replit.com/@ThinhDinh/glide-yes-code-array-difference#function.js
4 Likes
Took me a bit to wrap my head around, but this is genius!
Although I typically try to avoid more complex solutions, this one makes sense - it’s rather simple in a way.
I’m assuming it isn’t a heavy calculation either, Even with 1000s of records?
One would think there would be a simpler solution to this problem.
I tried using joins, but it only allows filtering Booleans and single values/matches, which your solution works around.
@ThinhDinh I appreciate it. Although coding is not a domain I’m as comfortable.
Out of curiosity though, how does the load/speed compare to a traditional google sheets formula or glide formula?
And a general question; Based off a post from a while back, I understand that glide calculations occur in app - every time the app is opened And on_edit, while google sheets formulas calculate only on_edit?
It’s called from your device, and in this case it’s not that complex of a coding snippet, so I expect it to be pretty fast. Still, it requires a call to Replit then retrieve the result back, so if I have to rank it I think this is slightly slower than a Glide solution, but faster than Sheets.
Regarding your question in the final paragraph, I think that is true.
1 Like
Yes, but the issue is not so much when the calculations take place, but how long the results take to appear.
To explain: imagine that your user is entering a couple of values, and you need to use those values in a calculation and display the result back to the user.
If the calculations are done in the Google Sheet:
- Data needs to first sync from the user device to Glide servers
- Data needs to then sync from the Glide Servers to your Google Sheet
- Calculations are performed
- Result then syncs back to Glide servers
- Result then syncs from Glide servers back to the user device
- Result is displayed
How long that all takes depends on several things, but can take anywhere from several seconds to several minutes, which doesn’t make for a very nice user experience.
In comparison, if you do your calculations in Glide:
- Calculation is done on the user device and result displayed immediately.
So as a general rule, it is always preferable to do your calculations in Glide wherever possible.
4 Likes
Very nice and short solution @ThinhDinh !!
let result = arrayY_val.filter(x => !arrayX_val.includes(x))
Here you can see it working in a little example:
Sure I will use it a lot 
Saludos
2 Likes