Filters got me again - how do I

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.

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.

1 Like

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.

1 Like

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.

1 Like

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?