Help! How can I create a view of matching records?

Hi all, I have an app which is a bit like a User Directory where users create a profile adding information about themselves (name, location, email, role, interests). I’d like to set up a view where the user can see other users with the same interests. All of the data is captured in a main table ‘Data’, however the ‘interests’ are selectable from a list in another table called ‘Interests’. I’m stuck on how to create this view. I’ve tried creating a slice using ‘FILTER(“Data”,([Interests] = [_THISROW].[Interests]))’ but I get the error ‘The expression is valid but its result type ‘List’ is not one of the expected types: Yes/No’. Any ideas?

1 Like

Please try slice filter expression as

IN([Date Table’s Key Column], FILTER(“Data”,([Interests] = [_THISROW].[Interests])))

1 Like

Hi @Suvrutt_Gurjar, I’ve just tried this but it only returns a ‘Y’ for my record and not for any of the other records even though they have the same interests. Do you know if it searches within a string or is it looking for exact matches?

Is FILTER(“Data”,([Interests] = [_THISROW].[Interests])) returning multiple key values?

You could test it in a VC in the same table.

It only returns my row, because only my row has a matching key (email address) :frowning:

Got it. Sorry that I missed your latest update post and hence responding a day later. Just in case you are still looking for a solution, please try creating a slice expression something like below

[Interests]=LOOKUP(USEREMAIL(), “Data”, “Email Column Name”, “Interests”)

1 Like

Also since you are creating user based filters, please thoroughly understand the concepts of security filters and slice filters.

Please refer the section “Can slices be used instead of a security filter?” in the article below

1 Like

Thanks for this suggestion @Suvrutt_Gurjar, however I keep getting the error message;
‘Cannot compare List with EnumList in ([Interests] = ANY(SELECT(Data[Interests],([Email] = USEREMAIL()))))’

Any ideas?

1 Like

What is the column type

1 Like

EnumList - because it is referencing another table for it’s values. Basically the user creates a profile and selects interests from a list. This list is held in another table hence EnumList.

1 Like

The EnumList table is also called ‘Interests’.

1 Like

Thanks. What if a user 1 has interests { A, B, C }, user 2 { D, B, C } user 3 { D, G }

Then what user 1, user 2 and user 3 should see in their respective slices?

1 Like

I have a view called ‘Directory’ where the user should see all other people with matching interests to theirs. This is where the [Interests] slice would be.
In your example;
user 1 should only see user 2 in their Directory view
user 2 should see users 1 and 3 in their Directory view
user 3 should only see user 2 in their Directory view

Hope that’s clearer now.

1 Like

Thanks

Can user 1 see himself also along with user 2

Can user 2 see himself also along with users 1 and 3

Can user 3 see himself also along with user 2

1 Like

Ideally the user would not see themselves in the Directory view, this view will be used to connect with other members (send email, sms etc)
There is another view called ‘My Profile’ where the user can see and update their own details.

1 Like

Could you please try an expression something like below in the slice filter

AND(COUNT( INTERSECT( [Interests], SPLIT(SELECT(Data[Interests],[Email] = USEREMAIL()), “,”)))>0 , [Email]<> USEREMAIL())

Have not tested it.

Minor edits in expression done.

2 Likes

“INTERSECT does not accept a list of list.” :frowning:

Do you think it could be getting confused because I have columns with the same name in different tables?
I have my main table called ‘Data’ with a column called [Interests]
‘Data’ references another table '‘Interests’ (a simple list) that also has a column called [Interests]

Can you share the screenprint of the expression entered in the slice filter formula and the error displayed?

1 Like