Multiple criteria within a REF column

[jsbt]

Hi,

Apologies for the basic question. I’ve been searching for a solution but don’t know if i’m using the right terminology.

The app is basic list of online resources, data has a different resource each row ( columns with name, link, description, image).

Then each resource is organised into subject (English, math, science etc.)

I’m using references to allow the user to view filtered data by subject (so to be able to view all the science resources etc) but the issue is each subcategory might have multiple relevant categories it falls into. Eg. a resource might fall into math, English and science under subject, and another resource might just fall into math and English. I want the resource to appear in multiple reference views without having to create a new column for each criteria, if that makes sense.

What topic/concept should i be researching to help me with this?

[WillowMobileSys]

The easiest way to accomplish this is to define your Subject column as an EnumList column on your resource row. This is a way to allow selection of multiple items.

The selections would be saved as a comma separated list.

To then show that resource in the different views you would simply check if the selected list contains the desired subject.

For example, if you are creating the Math view, a part of your expression to pick the resource rows might be:

’ …IN(“Math”, [Subject])…’

if “Math” was one of the items in the list [Subject] then that row would be selected for the view.

I hope this makes sense!

[Steve]

For reference;

help.appsheet.com

IN()

Is item in list?

[jsbt]

Thanks!

As data is never actually entered using the app, only viewed (all data is just added directly to the spreadsheet), would would be the advantage of doing this rather than just simply going through and manually entering with the commas?

I’m still a bit confused as to how an enumlist would fit into a static app (one that doesn’t ever require User input)

[WillowMobileSys]

Yes, you can simply enter the subject list into the sheet manually (e.g. “Math, Science”).

Even though the app is static, I would still define the column as EnumList. I don’t think it harms anything.

[jsbt]

Gotcha. I’m still struggling on creating the views to sort the data into multiple ways. Do you suggest using slices or is there a more efficient way of doing this?

[jsbt]

and would that then involve creating a slice for every view based on the criteria? Eg. a maths slice, science slice etc?

[WillowMobileSys]

There are multiple ways to display the data.

A Slice and view for each Subject is one way. If there are not many subjects this might be a good way.

You could also have a main view in the app that shows a set of buttons, one for each Subject - “All”, “Science”, “Math”, “English”. Tap the button to go to a view showing only those resources for that subject. You would only need a single view fo this and would use the LINKTOFILTEREDVIEW() function to specify the filter criteria by subject (that IN() function from above).

Do you have an idea in mind of what you would like?

[jsbt]

Yes link to filtered view sounds like the way to go!

So essentially create a gallery from a table with 3 buttons.

1. View by age
2. View by subject
3. View full list

Then each of the first two buttons directs to a new table, eg. “view by subject” directs to a table containing all subject areas and view by age directs to a table containing all the age levels. These are then viewed in a gallery view, with each directing to a filtered view essentially saying “if the column key for this row is listed in the “subject” column of the resource list, then show all those resources”

Is that right?

[jsbt]

How do I define the criteria for the filter to be coming from the source table?

It is scanning column in Resource List called “Subject”, looking for if it says “maths”, but I want the term “maths” to be defined by the row key in the subject area table?

Like how i would use a linktoview defined by the item in the gallery.

Does that make sense?

[WillowMobileSys]

jsbt:

It is scanning column in Resource List called “Subject”, looking for if it says “maths”, but I want the term “maths” to be defined by the row key in the subject area table?

I am not clear what this means.

But…if you are asking how would you create the filters, you would do this directly in the LINKTOFILTEREDVIEW() function.

The buttons would be Actions to navigate to another view in the app. The target view in those actions would be something like these examples:

For all:

LINKTOFILTERDVIEW("My Filtered View" , true)

For age:

LINKTOFILTERDVIEW("My Filtered View" , [Age] = "5")
or
LINKTOFILTERDVIEW("My Filtered View" , [Date] <= "12/01/2020")

For Subject:

LINKTOFILTERDVIEW("My Filtered View" , IN("Math", [Subject])

help.appsheet.com

LINKTOFILTEREDVIEW()

Constructed deep link to filtered view

[WillowMobileSys]

jsbt:

So essentially create a gallery from a table with 3 buttons.

1. View by age
2. View by subject
3. View full list

Yes this sounds like a very good way to go!!

[jsbt]

Sorry - i’m totally behind on the right terminology with what i’m trying to say.

If I had a gallery populated by all the subject areas from a table with column 1: subject area and column b: image and applied an event action to the gallery based on the below expression:

LINKTOFILTERDVIEW(“My Filtered View” , IN(“Math”, [Subject])

Each button, regardless of subject area, would go to a filtered view where its only showing math resources. What I’m wanting to do is have the “maths” defined by the “subject area” of each button.

Sorry if that again is highly confusing

[jsbt]

I’m getting the “Parameter 2 of function IN is of the wrong type” error with the following expression:

LINKTOFILTEREDVIEW(“All Resources”, IN(“Maths”,[Learning Areas]))

Any ideas?

[WillowMobileSys]

jsbt:

I’m getting the “Parameter 2 of function IN is of the wrong type” error with the following expression:

LINKTOFILTEREDVIEW(“All Resources”, IN(“Maths”,[Learning Areas]))

Any ideas?

[Learning Areas] is most likely not be defined as a LIST type. This is only one of the reasons I recommended to still define the column as EnumList.

[jsbt]

Okay I managed to get the following expression functioning:

LINKTOFILTEREDVIEW(“All Resources”,IN([Learning Areas], LIST(Maths)))

What I now need is the “maths” to be determined by the current views main key.
Eg. depending on which button i press, it will send me to a filtered view of just THAT resource type.

[WillowMobileSys]

jsbt:

LINKTOFILTEREDVIEW(“All Resources”,IN([Learning Areas], LIST(Maths)))

This will only work if [Learning Areas] = “Maths”. If [Learning Areas] is set to “Maths”,“English” for a row, the IN() will not select that row for the results. As I understand it, you would want the row to be selected

[WillowMobileSys · Accepted solution]

jsbt:

What I now need is the “maths” to be determined by the current views main key.
Eg. depending on which button i press, it will send me to a filtered view of just THAT resource type.

The words “main key” is throwing me off here.

If you are asking how you would setup the navigation from the main view to the filtered view, this is what I would do:

0. I’ll assume you already have a table for the Resources. Make sure [Learning Areas] is a LIST type column.
1. Create a table the defines each subject - named maybe “Subjects”. Include in this table at least: Main Subject, Image to use as the button, text to use as a caption (if desired).
2. If you haven’t yet, create a view to act as the filtered view and set the data source to be the Resources table. This could be a Deck or Table view.
3. If you haven’t already, create an Action with datasource set to Subjects table.
4. In the action’s target, I would then adjust the expression to that below:
   NOTE: I used “Main Subject” to avoid confusion with the name Subject from earlier.
   [Learning Areas] replaces what I suggested as [Subject] above.

IF([Main Subject] = "ALL",
   LINKTOFILTEREDVIEW(“My Filtered View” , true),
   LINKTOFILTEREDVIEW(“My Filtered View” , IN([_THISROW].[Main Subject], [Learning Areas]))
)

5. For the main view create a Gallery of the subject rows displaying the images and optional caption.
6. Set the “Row Selected” Behavior in the Gallery view to your action that navigates to the filtered view.

I have not implemented this before so it’s not tested. I will build a quick sample app to test this out. The only uncertainty I have is if the IN() function will properly identify the data from the Subjects row and fill in the parameters to filter the Resources table.

---

The main gist:

Gallery view shows a list of images representing the different Subjects as “buttons” (they only mimic buttons). Tap a “button” which activates the Row Selected action. The action pulls the [Main Subject] from the Subjects row and uses that value in the IN() clause of the LINKTOFILTERDVIEW() function to filter the rows in the filtered view. The action then navigates to the filtered view listing only the rows indicated by the Subjects “button” selected.

I hope this all makes sense. I’ll build a quick test app to prove the concept (though i know others have done this). Be sure to ask questions!!

[WillowMobileSys]

So I implemented a quick test app and all works as I suspected. I did need to make some syntax corrections in the expression from previous post. I have edited that post to correct.

Main View:

Tapping on the English "button"

Tapping on the Science "button"

Tapping the ALL "button"

[jsbt]

@WillowMobileSystems Yes!! it works!! I was missing the [_THISROW] function.

Just as a general question, why do i need to define the row in LINKTOFILTEREDVIEW and not in LINKTOVIEW? I have another page that links to views and use defining the column has it functioning.

[WillowMobileSys]

jsbt:

why do i need to define the row

In LINKTOFILTEREDVIEW(), you are not defining a row but rather filter criteria for a set of rows. This allows you to use the same view for many different sets of rows.

LINKTOVIEW() will just use ALL rows as defined by the datasource attached to the view

If LINKTOFILTEREDVIEW() DIDN’T exist, you can achieve the same thing by creating several Slices to filter the rows (e.g. Slice for Math rows, Slice for English rows, etc) and then a dedicated View for each of those Slices. Then you would use LINKTOVIEW() to go to the desired View.

BUT each of these views would be the same in structure. In other words, if you had 10 subjects, you would need 10 Slices, 11 Views and 11 different LINKTOVIEW() calls (11 to account for ALL).

Much more tedious than a couple LINKTOFILTEREDVIEW() calls.

I hope this explains it.

---

By the way:

LINKTOFILTEREDVIEW(“My Filtered View” , true)

is the same as

LINKTOVIEW(“My Filtered View”)