Matching data using Query and filter

My Column data: [Strategy, Creative]

I want to use query to filter data that matches another row with the data: [User Experience, Creative] and another row: [Creative].

I should appear in the query to match this 2 rows but currently by using split text into array.
I compare Array 1 contains Array 2 it will show if the data has the 2 text [Strategy, Creative] or only 1 text [Creative]

Can this just be a relation between the arrays or do you need to do more with the query?

Trying to find matches for users with multiples filters.

But isn’t a relation doing what you want, or do you mean more filters outside of the “list” above?

Yeah using relation it match 1 array to another array. But I need to do another match based on another set of data. Definitely more than 3 filters. e.g Hobbies, Gender, Location.

My current set up using the query column, what i encounter was data set A [Apple, Orange] matching to data set B [Apple, Orange, Banana] the result is false.

but if data set A [Apple, Orange] matching to data set B [Orange, Apple] the result is true a match.

You should definitely use the array for that, not the “is included” part.

I would do a relation and return the matching IDs for each filter.

Then feed all of them into a JS function perhaps, and find the ones that appear in all filter results.

2 Likes

I reference some related thread/topic.
which has the following JS. But i need help with adjusting to my desired outcome.

var arr = p1.split(‘,’);
var re = new RegExp(p2,‘g’);
var matches = ;
arr.forEach(function (item){
if (item.match(re)) {
matches.push(item);
}
});
return matches.join(‘,’);

Here a sample data which i want to filter A and B to find user that match them. I showed the result if its 1 data match but ideally i can have more data to filter to funnel down the match result.

Hi @Trustin

According to your screenshot and if your data are structured like this, you can achieve the desired result by following these steps:

  1. Create a “Split Text” column for each User Data column (this way, you’ll get an array of values)
  2. Create a “JSON Template” column to store all the User Data for each row
{
"username": $Username,
"numbers": [$Numbers],
"colors": [$Colors]
}
- Username → Username in another table
- Numbers → User Data A (split)
- Colors → User Data B (split)
  1. Create a Lookup column of the previously created JSON Template column (you’ll store the JSON of each row this way)
  2. Create a Joined List column of the Lookup (to get a comma separated list the JS column will be able to read)
  3. Create a JavaScript column to filter your data and get the expected output
let data = JSON.parse("[" + p1 + "]"); // to read and store the All joined JSON User Data

let filterNumbers = String(p2).split(","); // I had to force the String type because numbers are regarded as numbers... but it's not mandatory if you deal only with strings
let filterColors = p3.split(",");

let result = data
  .filter(user => 
    user.colors.some(color => filterColors.includes(color)) && // the .some means at least one element
    user.numbers.some(number => filterNumbers.includes(number))
  )
  .map(user => user.username) // you retrieve the username value
  .join(', '); // all matched usernames are joined

return result;

Here is my result with your sample data (the Gordon row is different because in my opinion, you should also get Gordon, he has a matching on “blue”):

And the previous JS code let you also play with a second filter, as you can see (it’s an at least “one of the colours” AND at least “one of the numbers” filter type):

If you have more filters to apply, just ajust the result variable by adding and ajusting another filter like that: "&& user.numbers.some(number => filterNumbers.includes(number))

Last observation: some steps I mentionned could have been done directly in JavaScript. However, I find it more easy to explain the logic this way. Feel free to improve it :stuck_out_tongue:

Hope it was what you wanted :+1:

3 Likes

Thank you Nicolas! Yes it works!
The javascript column we use, limits to P1 P2 and P3, which means it only accept up to 2 data to filter.
I am wondering, let says i have 5 data(or even more) to filter and find the suitable match. Can I join list for all 5 data as a string. Then filter this 1 string. Will it work this way, or are there alternatives.

Yes, you can do that. So you could pass something like the following:

AAAAA||BBBBBB||CCCCC||CDDDDD||EEEEE

Then p1.split('||'), and you have 5 parameters :slight_smile:

2 Likes

Or do a JSON of all your inputs and then parse them out from a JSON string. Both will work.

2 Likes

And if you need another option, in additon to the relevant ones already mentioned by @Darren_Murphy and @ThinhDinh, you can go for a Template column where you write your code and replace as many parameters as you want.

Here, it’s just 5 “Text” columns, but you get this idea.

let value1 = "p1";
let value2 = "p2";
let value3 = "p3";
let value4 = "p4";
let value5 = "p5";

let concat = value1 + " " + value2 + " " + value3 + " " + value4 + " " + value5

return concat;

And then… use this Template column as the source of your JS column.

It’s not my idea of course, I’ve seen it in many Glide tutorials/posts :relieved_face:

3 Likes

That’s a nice idea. Thanks for sharing!

Well, my approach for this was using template to collect all data.

Pass it to Make JSON, and pass it as p1.

Its showing results but not sure if it is100% functionally returning the correct matching results.
Will test some more.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.