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

Chetty · 04-21-2021 07:57 AM

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?

Suvrutt_Gurjar

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.

View solution in original post

Suvrutt_Gurjar

Please try slice filter expression as

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

Chetty

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?

Suvrutt_Gurjar

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

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

Chetty

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

Suvrutt_Gurjar

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”)

Chetty

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?

Suvrutt_Gurjar

What is the column type

Chetty

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.

Suvrutt_Gurjar

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

Chetty

The EnumList table is also called ‘Interests’.

Suvrutt_Gurjar

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?

Chetty

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.

Suvrutt_Gurjar

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

Chetty

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.

Suvrutt_Gurjar

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.

Chetty

“INTERSECT does not accept a list of list.”

Chetty

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]

Suvrutt_Gurjar

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

Chetty

Suvrutt_Gurjar

It perfectly works for me.

SPLIT() is used to convert list of lists into a normalized list.

Suvrutt_Gurjar

Hope your [Email] column name is [Email] only and [Interests] is an Enumlist column in “Data” table.

Chetty

Yes @Suvrutt_Gurjar both of those are correct and yet I still get that error?!??

Suvrutt_Gurjar

Please try changing Base type of Enumlist column [Interest] to Text and test

Chetty

@Suvrutt_Gurjar you bloody legend!!! That seems to have done the trick

Thank you so much for all your help! I’ve been pulling my hair out over this. Now to test it with other users…

Suvrutt_Gurjar

Good to know it works. Please thank senior community guide @Steve as well. He asked to post the screenshot of the column setting where the final issue was hiding.

Chetty

Can you just explain why that was the issue? So I know for future use. I have another table ‘LCRN’ which is used in a similar manner although is Type Enum, should I be changing the base type of that column to text as well?

Suvrutt_Gurjar

Yes, base type is applicable for Enum also.

The final issue was Enumlist column is list of some type of items. If it is a list of numbers , the base type of column needs to be Number. In your case base type was text, since [Interests] are list of texts. So a list can be of dates, numbers, texts and so on. Accordingly base type of Enumlist column needs to be selected. The earlier incorrect base type setting was “Enumlist”, so it was creating list of lists and hence the error.

My request will be to go through following articles. AppSheet has rich set of well explained articles with examples. Please search by search words such as “enum”, “COUNT()”, “Reference” and you will get a list of relevant articles.

https://www.appsheet.com/Support

The following post has many references useful for an AppSheet app building

Chetty

Hi @Suvrutt_Gurjar, if I now wanted to sort this list where would I add the SORT expression? I’ve tried adding it at the start and stating True as I want the list in descending order but I just can’t get it to work. It says Sort has invalid inputs.

Steve

Chetty

Thanks @Steve, I have tried this but it doesn’t seem to like it hence my post. I’m trying to sort the following expression;

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

Basically I’d like the list sorted by number of matches (high to low). I’ve tried adding in Sort both before and after the second And statement but in both instances I get the message ‘Sort has invalid inputs’.
Any ideas where I’m going wrong?

Steve

Oh, this is in a view. The order rows are displayed within a view is controlled by the view configuration, not by the slie’s row filter condition.

Chetty

The issue I’ve got is that the View doesn’t allow me to sort by Expression (unless I’m missing something?) I can only sort by Columns. Any way around this?

Steve

There is no way to sort a view by expression.

Chetty

Okay thanks. Possible future feature?

Steve

Please post a screenshot of the configuration screen of the Interests column, down to and including the entire Type Details section.

Chetty

Thanks for this suggestion @Steve! @Suvrutt_Gurjar seems to have identified the issue and has provided a solution

Chetty

My base table called ‘Data’

Chetty

Configuration of the [Interests] column within ‘Data’.