Re: Filters got me again - how do I

tcanelli · 12-24-2019 06:59 AM

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.

Steve

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().

tcanelli

I read the documentation before posting - it doesn’t make sense to me.

Is filter even what I want?

Steve

I don’t know. What are you trying to accomplish?

tcanelli

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

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.

Steve

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

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

tcanelli

Even though it’s two diff tables?

Steve

What are the columns in the Unassigned to Caller slice?

tcanelli

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.

Steve

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

tcanelli

From the caller sign in table.

Steve

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

tcanelli

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.

Steve

What is a “caller”?

tcanelli

Just a user that is signing into the app - that’s the role they have.

Steve

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?

tcanelli

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

Steve

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

tcanelli

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?

Steve

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.