Slice, filter or some sort of ref row?

[Mike_T]

I’ve got a dashboard with 2 views: Map of Displays and Campaigns (interactive). I’m trying to limit which rows the user can see based on their state. I’ve got a table with a all the users and a column designating their state plus another column tagging them as either a “M” manager or “A” all.

I want the users tagged “A” to see all of the rows on the map. I want the users tagged “M” to only see the rows in the state they are listed in. What’s the best way to accomplish this? I have about 7 states so not keen on having 7 slices.

[Grant_Stead]

There’s a few options… If you don’t want the “M” users to ever see the other states, then I would put it into a security filter…

[Mike_T]

I think I found a very ugly solution so I’m hoping someone easily smarter than me will graciously reply with a better solution.

I created a slice with the following Row Filter Condition:

IF(LOOKUP(USEREMAIL(),“QRManager Map”,“Email”,“View Type”)=“A”,TRUE,IF([State]=LOOKUP(USEREMAIL(), “QRManager Map”, “Email”, “State”),TRUE,FALSE))

Essentially checks for an “A” in the “View Type” column based on the Useremail matching my QR Manager Table. If this fails then it limits the rows to just the state of the USEREMAIL.

I’m embarrassed to admit how many hours I’ve been working on this

[Steve]

Here’s your expression reformatted to my preference:

IF(
  (
    LOOKUP(
      USEREMAIL(),
      “QRManager Map”,
      “Email”,
      “View Type”
    )
    =“A”
  ),
  TRUE,
  IF(
    (
      [State]
      =LOOKUP(
        USEREMAIL(),
        “QRManager Map”,
        “Email”,
        “State”
      )
    ),
    TRUE,
    FALSE
  )
)

Here it is simplified a bit:

OR(
  (
    LOOKUP(
      USEREMAIL(),
      “QRManager Map”,
      “Email”,
      “View Type”
    )
    =“A”
  ),
  (
    [State]
    =LOOKUP(
      USEREMAIL(),
      “QRManager Map”,
      “Email”,
      “State”
    )
  )
)

Simplified even further (and more efficient!):

ISNOTBLANK(
  FILTER(
    “QRManager Map”,
    AND(
      ([EMail] = USEREMAIL()),
      OR(
        ([View Type] = "A"),
        ([State] = [_THISROW].[State])
      )
    )
  )
)

[Lucinda_Mason]

Steve,

You are the master of expressions. Wish you could check every expression in my app,

[Mike_T]

I have new wrinkle. I’ve recently added some users that cover multiple states. I’m not sure how to structure the table. If a single user has a row for each state, I’m pretty sure the Lookup function will find the first instance of USEREMAIL and only slice the corresponding State data. Here is my current working formula:
IF(LOOKUP(USEREMAIL(),“QRManager Map”,“Email”,“View Type”)=“A”,TRUE,IF([State]=LOOKUP(USEREMAIL(), “QRManager Map”, “Email”, “State”),TRUE,FALSE))

The multi-state users will still be designated “M” users to restrict their views to only their markets, while “A” users will have access to all. Thanks in advance for your help!

[Steve]

Your expression, reformatted:

IF(
  (
    LOOKUP(
      USEREMAIL(),
      “QRManager Map”,
      “Email”,
      “View Type”
    )
    = “A”
  ),
  TRUE,
  IF(
    (
      [State]
      = LOOKUP(
        USEREMAIL(),
        “QRManager Map”,
        “Email”,
        “State”
      )
    ),
    TRUE,
    FALSE
  )
)

Simplified:

OR(
  (
    LOOKUP(
      USEREMAIL(),
      “QRManager Map”,
      “Email”,
      “View Type”
    )
    = “A”
  ),
  (
    [State]
    = LOOKUP(
      USEREMAIL(),
      “QRManager Map”,
      “Email”,
      “State”
    )
  )
)

Simplified even further (and more efficient!):

ISNOTBLANK(
  FILTER(
    “QRManager Map”,
    AND(
      ([Email] = USEREMAIL()),
      OR(
        ([View Type] = "A"),
        ([State] = [_THISROW].[State])
      )
    )
  )
)

Which is exactly what I gave you previously.

[Mike_T]

will this last formula handle my multi-state issue? To confirm, I’ll need to create a row per state for the same user?

i.e.

Bob M bob@email.com CO
Bob M bob@email.com UT
etc.

[Mike_T]

@Steve You are a scholar and a gentleman! How can I buy you a beer?!

[Mike_T]

The last one ISNOTBLANK doesn’t work for me. I’ll try the other ones and get back to you. Thanks again for helping out here!

[Mike_T]

@Steve I’m back with a slightly different twist on the Ref_Row Interactive dashboard.

Dashboard has 2 views: 1. Map of Displays from Display Data Table. 2. Table View from Campaign List Table.

I’m not able to change the Campaign column in the Display Data Table to Ref, because I’m using a formula in there to select the campaign based on a QRScan in another Form View.

How do I do both?
One view is a dashboard and I’m trying to use the Campaign column as an interactive filter for a map. Another part of the app in a form view, selects the Campaign based on a Scan column.
Formula in the Display Data Table [Campaign] = LOOKUP([_THISROW].[QR_Code],“QR Codes”,“QR_Code”,“Campaign”)

[Steve]

In what way doesn’t it work? Screenshots?

[Mike_T · Accepted solution]

I guess I thought that having a formula in a Ref type column would for some reason conflict. You know what they say about “assuming”.

I switched the column to Ref and the dashboard is working just fine!

Thanks Steve for busting my myth!