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.
Solved! Go to 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!
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โฆ
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
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])
)
)
)
)
Steve,
You are the master of expressions. Wish you could check every expression in my app,
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!
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.
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.
@Steve You are a scholar and a gentleman! How can I buy you a beer?!
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!
@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โ)
In what way doesnโt it work? Screenshots?
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!
User | Count |
---|---|
39 | |
35 | |
29 | |
23 | |
18 |