Filters got me again - how do I

tcanelli
Participant V

I was attempting to use usersettings in an app to filter certains views - and it worked - but was very clunky.

Instead I would like to just use some filters but canโ€™t figure them out at all.

I want my evacrecord slice to filter based on the callersignin table - see attached.
I tried to use the expression @Steve and @LeventK helped me with yesterday but couldnโ€™t figure it out.

The dashboard in the pic in just diff slices and I tried to explain how Iโ€™d like to filter each slice.

0 20 666
20 REPLIES 20

Steve
Participant V

FILTER() produces a list, which isnโ€™t suitable for use in an AND() expression. If you want to know if FILTER() produces no results, wrap it with ISBLANK() (ISBLANK(FILTER(...))); if you want to know if it produces at least one result, wrap it with ISNOTBLANK().



I read the documentation before posting - it doesnโ€™t make sense to me.

Is filter even what I want?

I donโ€™t know. What are you trying to accomplish?

The way the app works - users complete a sign in form where they enter a table number they are sitting at - thatโ€™s the first filter for the top left box. Instead of usersettings I want to base that slice on the table number they select.

The middle box from the dashboard I want to filter the records based on the caller name that is assigned.

tcanelli
Participant V

Got the expression to work - but it doesnโ€™t work the way I want it to. Iโ€™m seeing all table numbers not just the one I signed in for.

Ah! I dontโ€™ think you need FILTER() at all. Try this:

AND(
  ISBLANK([CallerName]),
  ISBLANK([OutboundTimestamp]),
  ISNOTBLANK([EOCTbl]),
  ([EOCTbl] = [_THISROW].[EOCTbl])
)

Even though itโ€™s two diff tables?

What are the columns in the Unassigned to Caller slice?

From the evac record table Iโ€™m using [callername], [outboundtimestamp] and [eoctable]

but only want to display rows on the dashboard that match the caller sign table for [eoctbl]

then the next slice would use the above plus [callername] from the sign in table as well.

How does the app know which table the current app user signed in to?

From the caller sign in table.

How does the app know which row of the caller sign in table is for the current app user?

I am not sure I understand that question.

We have about 40 people that sign in to this app when we activate for a hurricane.

1200 records are divided as evenly as possible (SQL stored procedures) to distribute them among 10 tables.

As people sign in, they select the table number. So this unassigned view should show everyone that signed in at table 1 - all the table 1 records.

then someone assigns the records to the callers signed in at that table.

that next view I want to have filtered just for the caller - but not sure how to do that either.

What is a โ€œcallerโ€?

Just a user that is signing into the app - thatโ€™s the role they have.

So a caller is a signed-in user of the app. How can I determine which row of the dbo.CallerSignIn table corresponds to the signed-in user of the app?

by their name? I was using USERNAME() to autofill that column.

Hmmmโ€ฆUSERNAME() is not reliable: some authentication services (like Google) donโ€™t even provide names (anymore). Better to use USEREMAIL().

To get he table assigned to the current caller:

LOOKUP(
  USERNAME(),
  "dbo.CallerSignIn",
  "caller's name column",
  "EOCTbl"
)

Replace caller's name column with the name of the column that contains the callerโ€™s name.

We can plug that into your expression:

AND(
  ISBLANK([CallerName]),
  ISBLANK([OutboundTimestamp]),
  [EOCTbl] = LOOKUP(
    USERNAME(),
    "dbo.CallerSignIn",
    "caller's name column",
    "EOCTbl"
  )
)

How do you know when to use lookup vs filter vs select?

That expression doesnโ€™t give an error but does say โ€œone randomly chosen valueโ€ - if I complete the sign in form and choose table 1 will this display only records from the evac table matching table 1?

LOOKUP() returns a chosen column value from a single row that matches the simple criteria provided. Use LOOKUP() when you need only one value and are confident the row containing the wanted value can be identified easily.

FILTER() returns a list of key column values from rows that match the criteria provided. Use FILTER() when you need a list of rows.

SELECT() returns a list of values from a chosen column from rows that match the criteria provided. Use SELECT() when you need a list of column values.

These three functions are critical for developing AppSheet apps. I strongly encourage you to familiarize yourself with them.

Top Labels in this Space