Filter Table using Enumerated List

bellapotpot15 · 01-13-2020 11:51 PM

Hi Appsheet,

I need to filter a table using enumerated list. Is this possible with appsheet?

Kirk_Masden

Yes, that is definitely possible. There may be a number of ways to do it, depending on what you are trying to achieve. Are you familiar with “slices”?

bellapotpot15

Hi Kirk,

Yes, I’m currently working on it. I have 4 tables: Accounts, Clients, Sites and Filter.

In Sites Table, I have the Client ID from Clients.

In Accounts Table, I have the Sites ID from Sites.

Now in Filter Table, I have columns of Email Address, and the rest is the available columns to filter (Slice based on user input).

In example, in Filter Table, column name “Client”, EnumList.

If I select 2 values, I need it to filter Accounts Table with Clients with Value 1 and Value 2.

I don’t have the correct expression for this because I’m using ANY().

I’ve tried difference List Expression and it all just didn’t work.

Kirk_Masden

I see. I’m having trouble understanding the structure of the table you call “Filter.” Is it a “slice”? To make things simpler, could you share a screenshot of the spreadsheet table that has the data you want to use to do the filtering and an image of the spreadsheet table you want to filter? Also, please share the expression you are using now, even if it isn’t working yet. I think that seeing the expression you are using now will help us understand what you are trying to do.

bellapotpot15

Steve

Steve

What it appears you’re trying to do is pretty advanced. Your expression has a number of problems, but the biggest potential problem I see is your Filter table itself. How will the app user add their filter criteria to the filter table? Will the table have only a single row that the user changes each time they want a different list? Or will a new row be added with each new set of filter criteria? Will the app have only a single user, or will it have multiple users?

bellapotpot15

Hi Steve,

The app has multiple users and can have additional users in the future.

On my Filter Table I have single row for each user that is identified via useremail().

What I do is I have a Slice that function as a filter for the Filter Table to a single row assigned for the current useremail().

Digging in to other tables that is related to Filter.

Client Table: Contains Client ID, Client Name.

Sites Table: Contains Site ID, Client ID and Site Name.

Accounts Table: Contains Account ID, Site ID and Account Name.

Filter Table: Contains Filter ID, Email Address, Client ID, Site ID.

To elaborate about my main problem,

I have a column ‘Client ID’ in my Filter Table that has the column type of EnumList, base type Ref, and the ref table is Client Table.

From the app user view:

A dropdown of Client Names available for multiple selection.

Client Name 1
Client Name 2
Client Name 3

–

What I need the app to do is filter out the Accounts Table with single or multiple Client Names.

Sorry if this is quite long and maybe a little bit confusing.

Steve

Thank you for the detailed response. Your approach to handling the filter table for multiple users sounds good.

It appears the Accounts Table doesn’t have a Client ID column, so in order to filter Accounts Table by client, the selected clients must be converted to their corresponding sites first, then accounts filtered by those sites. Given that, the following should give you a list of site IDs for the chosen clients:

SELECT(
  Sites Table[Site ID],
  IN([Client ID], [_THISROW].[Client ID])
)

You can then add this list to that from the filter table’s Site ID column (presumably also an EnumList😞

(
  [Site ID]
  +
  SELECT(
    Sites Table[Site ID],
    IN([Client ID], [_THISROW].[Client ID])
  )
)

This gets you the complete list of site IDs both chosen explicitly by the user and those implied by the user’s chosen client IDs. You would then need to store this result in another column of the filter table so you could reference it in the row filter expressions of other slices.

Sudheer_Kumar_K

Hi bellapotpot15 & Steve

can u please elaborate, how you have done the below part. I need the exact same thing in my app also. Thanks in advance

On my Filter Table I have single row for each user that is identified via useremail().

What I do is I have a Slice that function as a filter for the Filter Table to a single row assigned for the current useremail().

Steve

Kirk_Masden

I see. Are you using the IF() expressions as your condition(s) for a slice?

bellapotpot15

Yes.

Kirk_Masden

If() doesn’t work as a condition in a filter. For example, if you want all of the record in which [Age] > 19 to be included, the expression would be

[Age] > 19

and you wouldn’t use an if expression.

P.S. Your expression is complicated so forgive me if I have misunderstood what you are trying to do, but I suspect that you’ve misused IF().

Steve

Sure it does! Your example, [Age] > 19 can be expressed with an IF() expression as IF([Age] > 19, TRUE, FALSE).

In @bellapotpot15’s case, they have some complex logic, and IF() is a reasonable approach. It may be easier to see with the expression reformatted (typos are mine):

IF(
  ANY(Filter Page[Location])="City",
  IF(
    OR(
      Filter Page[City]={"All"},
      ISBLANK(Filter Page[City])
    ),
    ISNOTBLANK([City]),
    [City]=ANY(Filter Page[City])
  ),
  IF(
    OR(
      Filter Page[Client]={"All"},
      ISBLANK(Filter Page[Client])
    ),
    ISNOTBLANK([Client ID]),
    AND(
      IN(
        [Client ID],
        ANY(Filter Page[Client])
      ),
      IF(
        OR(
          Filter Page[Site]={"All"},
          ISBLANK(Filter Page[Site])
        ),
        ISNOTBLANK([Site ID]),
        [Site ID]=ANY(Filter Page[Site])
      )
    )
  )
)

Koichi_Tsuji

Hi Steve,
sorry, this is not question direct to this post subject, but you use curly blacket within the formula. Is there any specific meaning and function affecting the works of expression?

Steve

Curly braces ({ and }) denote a literal-value list.

This…	Is equivalent to…
`{"a", "b", "c"}`	`LIST("a", "b", "c")`
`{1, 2, 3}`	`LIST(1, 2, 3)`

But important to know, curly-braces can only be used to construct a list from literal values; you cannot include expressions within curly-brace lists, and the interpretation of expressions within curly braces is undefined. For instance:

This…	Is equivalent to…
`{2 * 3}`	`LIST(2, 3)`
`{[name]}`	`LIST("[name]")`

Koichi_Tsuji

Thank you Steve! I got it.

Kirk_Masden

Thanks for coming to the rescue, @Steve! Sorry for the misinformation @bellapotpot15! Good luck!