Array to array relation with AND condition

Help & How To
#1

I want to match 2 array columns in two different sheets with a relation and get only those matches which are a AND condition.

Example:
A sheet with people searching for houses, with an array column showing all of their search criteria.
A second sheet with house offerings with an array column showing all house criteria.
The result should be an array in the people sheet which shows only those houses which match all search criteria.

I searched different forum posts and looked at the video (Multiple Categories - Glide Library), but it seems that the match is always comparable to a OR condition, and showing houses which match only one two criteria and not all.

Has anybody an idea how to solve this?
Thank you

#2

I would consider looking at this thread. It might not be exactly what you are looking for, but might give you some ideas:

We would probably need some clarification on who the audience is for this app. Is it the people searching for homes, or is it a real estate agent looking for matches for several people.

#3

@Jeff_Hager thank you for your suggestion, I’ve seen this video of @Robert_Petitto and are using his approach for other tasks.

The app is mainly for people searching for houses, homes, property etc. In the object table each row has stored the relevant criteria in an array column. Each object might have a different number of criteria.

First the user selects the object type (e.g. house, office, property, …) through a dropdown list.

As for each object type different criteria are relevant, I then show the user a checkbox list (an inline list with checkboxes) which pulls the items from a separate criteria table depending on the object type the user selects upfront.

The items/checkboxes which have been selected then go into a user specific array column into an “input sheet” with only one row.

Finally I want to use this array column with the selected criteria and a relation linking to the criteria array in the object sheet to return all relevant objects.

#4

I generally use a combination of templates and multi-relations for this type of dynamic filtering. The basic idea is:

  • In the table that contains your source data, create a template column that concatenates each of the values that you’ll be filtering on. In your example, it might look something like:
House:5 BR:3 WC
Apartment:1 BR:1 WC
Condo:3 BR:2 WC
etc..
  • Next create an identical template that concatenates the same values from your User Specific Columns
  • Finally, create a multi-relation that joins the two template columns
  • You can then use an inline list to display the results from that relation

This approach has a couple of nice side-effects…

  • You don’t need to apply any filters to your inline list
  • It’s easy to get a dynamic count of filtered records simply by applying a rollup column to the multi-relation

One downside is that if you need to provide a “Select All” option, then it gets a little more complicated. In that case, you need to create multiple templates and rollups to cater for each permutation. So the law of diminishing returns applies. I’ve done it with up to 4 separate filters (3 of them having an “All” option).

4 Likes
In-app filter doesn't require "match multiple"
Bar charts
Tip for how to filter a table then count occurrences, please!
#5

@Darren_Murphy Thank you for your reply.

The use case I’ve in mind is some what different and more generic, as in both tables there is not necessarily the same number of criteria and the criteria are not in the same order.

A relation between two database tables is usually a AND condition, which is also the case in a normal 1:n relation in Glide. But the array to array relation in Glide seems to use a OR condition, which is somehow inconsistent.

In the attached image I try to explain what result I want to achieve.

array to array relation
array to array relation1254Ă—358 81.3 KB

Do you have any idea how to solve this?
Thanks

#6

If I understand this right, you can create two single value columns for the search criteria to populate every row with the same search term.

Then create a template column joining all characteristics of an object.

Create an if then else column. If search term 1 is not included in template info then false, search term 2 is not included in template info then false, else true.

Display only objects with a true value in the if then else.

#7

Thank you, yes, this would be a solution if there are only 2 search criteria.

I want people to select the search criteria from a list with checkboxes, hence the number can and will be changing from case to case. I then want to populate the array with these selected criteria, sure, the array is not limited to 2 columns.

I’m looking for a very generic and dynamic matching of search terms. Think of Google, you are not limited to a max. number of search terms.

1 Like
#8

How are you structuring the Sheet where you store the checkboxes?

If it’s just a column of criteria, then you can add a boolean user-specific column to store the check values.

In that same sheet, create a relation from the criteria name to the array of characteristics in your objects sheet, and a join list on top of that to return all object IDs (I prefer using rowIDs here). Let’s call this “Matching Objects”.

Then in the objects sheet, create a template column with the value “true”. Create a relation from this column to the boolean column in the Criteria sheet, return multiple matches.

Then back to the Objects sheet, create a join list column on top of the relation above, return all “Matching Objects”.

Finally, an If Then Else, if Object ID is included in Matching Objects join list then true, else false.

This would allow you to have unlimited search criteria, I believe.

#9

Forgot to add a case where the user hasn’t added any search criteria. If the “true” relation is empty then I would add an additional If Then Else column, If relation is empty then RowID (to match the row with itself), else Join list of IDs.

The structure:

Criteria sheet:

Criteria name | User-specific boolean | Relation to Objects array column of Descriptions | Join list of object IDs

Objects sheet:

RowID | Object name | Description array 1, 2, 3, 4, 5, 6, etc. | Temp column with value “true” | Relation to User-specific boolean in Criteria sheet | Join list of object IDs from relation | If Then Else column, if Join List is empty then RowID, else Join List | If Then Else column, if RowID is included in previous ITE column then true, else false.

#10

Many thanks for your ideas.
Exactly, I tried to store the selected criteria in a user-specific column.

I’ve created a test app (https://moonlit-rule-3100.glideapp.io/) where you will see that one ends up at the same dilemma, you only get matches for a single criterion as they are derived by the rows of each criterion. Hence the Result column in my example doesn’t give a AND matching of all criteria selected.

This is the reason I tried to make a relation between the two arrays in the first place… which doesn’t work as expected …

#11

Check this sample… in the menu you can filter items by 4 criteria, and for each filter you can select multiple choices , is this what you looking for?

#12

Oh I can see the problem with the AND. Will let you know if I can think of any other ideas.

1 Like
#13

No, I’m using similar filters in my app, but in this case I’m looking for something more generic.
But thanks for your nice example.

#14

Use glide choices and filters based on these choices, in filters use AND logic, there you go…