cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helphelp91
Level: Power Up

Filter Gallery by Current User Details

Hello Everyone, 

 

I have seen some similar posts regarding this topic but couldn't get one matching my scenario. I know there are many talented people here and would like to get some suggestion regarding a problem. 

 

Objective - Filter gallery by checking current user's email ID against permissions table. 

Background - 
I have a set of tables in SQL. The first table is 'User Groups Table' and details as shown below,

Capture1.PNGUser Group Table





Second table is 'Case Details Table' as shown below, 

Capture2.PNGCase Details Table
Third table is 'Admin Table' as shown below,

Capture3.PNGAdmin Table


App details - 

I have inserted a gallery in the app along with search text box. This gallery is connected to 'Case Details Table'. Now I want to filter this gallery by following logic, 
Condition 1 - If current user is  in 'Admin Table' then show him all cases where 'Resolved Date' = Null.
Condition 2 - If current user is in 'User Group Table' and 'Can Escalate' = 0(refer 'Group Name' C, User3) then show him all cases where 'Resolved Date' is Null and 'Is Escalated' = 1 only(refer 'Case ID' 4 where case is escalated and resolved date is null)
Condition 3 -  If the current user is in 'User Group Table' then get 'Order Type' for current user from 'User Group Table'(Eg current user is in group A where 'Order Type' = PO) and retrieve cases matching the 'Order Type' from 'Case Details Table' where 'Resolved Date' is Null and 'Is Escalated' = Null

Condition 4 - If the current user is in 'User Group Table' then get 'Order Type' for current user from 'User Group Table'(In this condition current user is in Group B where 'Order Type' = Manufactured) and retrieve cases matching the 'Order Type' from 'Case Details Table' where 'Resolved Date' is Null and 'Is Escalated' = Null

To make this logic work I have written the following code in 'Items' section of the gallery, 

Filter(
    '[dbo].[Case Details Table]',
    If(
        LookUp('[dbo].[Admin Table]', UserEmail in User().Email, ElevatedSecurity) = true, IsBlank(ResolvedDate),

        LookUp('[dbo].[User Group Table]', 'User Email' in User().Email, 'Can Escalate') = false, IsBlank(ResolvedDate) && 'Is Escalate' = true,

        LookUp('[dbo].[User Group Table]', 'User Email' in User().Email, OrderType) = "Manufactured", 
        OrderType = "Manufactured" && IsBlank(ResolvedDate) && 'Is Escalate' = false,  
        
        LookUp('[dbo].[User Group Table]', 'User Email' in User().Email, OrderType) = "PO", 
        OrderType = "PO" && IsBlank(ResolvedDate) && 'Is Escalate' = false
        ),
    StartsWith(ItemID, TextSearchBox1_1.Text)
) 


Obstacle - 
The above filter against 'Case Details Table' works fine for Condition 1 and Condition 2 but it fails in Condition 3 and 4 where current user is able to see all the cases when I tested against user 1 & 2 for groups A & B respectively.

I would greatly appreciate if someone can help me in overcoming this obstacle. Thanks in advance!!!


4 REPLIES 4
Super User
Super User

Re: Filter Gallery by Current User Details

Off the top of my head, I would probably approach this problem in the following way using a context variable to create an alias for the Items property of the gallery. (say var as the Items property for the gallery) and use the If() function to define the text of var.  Something like 

If(condition1, UpdateContext({var:Filter('[dbo].[Admin Table]', UserEmail in User().Email, ElevatedSecurity = true)}),
condition2, UpdateContext({var:Filter('[dbo].[User Group Table]'..etc

until you reach the situation where none of the conditions is met. That would be the "false" element of the If() function. 

 

 

Super User
Super User

Re: Filter Gallery by Current User Details

@Helphelp91

A couple questions on the conditions...first, are the mutually exclusive?  In other words, if a user meets condition 1, will "cases where 'Resolved Date'=Null" be the ONLY records they would see, or does the logic continue from there - meaning, if they also meet condition 3, they should see all of the records of condition 1 and condition 3?

And also, when you say "all" in your conditions are you stating they should see all of them, or by the further filter of the TextSearchBox1_1 criteria? 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Helphelp91
Level: Power Up

Re: Filter Gallery by Current User Details

Hi Drrickryp, 

Thanks for the input! I tried your solution for using UpdateContext but it's giving me following error, 

Capture4.png

So I tried it in a different way as seen below,

If(
    LookUp('[dbo].[Admin Table]', UserEmail in User().Email, ElevatedSecurity) = true, 
    Filter('[dbo].[Case Details Table]', IsBlank(ResolvedDate)),

    LookUp('[dbo].[User Group Table]', User_x0020_Email in User().Email, Escalate) = false,
    Filter('[dbo].[Case Details Table]',  IsBlank(ResolvedDate) && Escalate = true),

    LookUp('[dbo].[User Group Table]', User_x0020_Email in User().Email, OrderType) = "Manufactured",
    Filter('[dbo].[Case Details Table]', IsBlank(ResolvedDate) && IsBlank(Is Escalate) && OrderType = "Manufactured"),

    LookUp('[dbo].[User Group Table]', User_x0020_Email in User().Email, OrderType) = "PO",
    Filter('[dbo].[Case Details Table]', IsBlank(ResolvedDate) && IsBlank(Is Escalate) && OrderType = "PO"),
    
    !IsBlank(TextSearchBox1_1.Text),
    Filter('[dbo].[Case Details Table]', StartsWith(ItemID, TextSearchBox1_1.Text))
    ) 


Now this logic is working for all conditions but I am not able to make it work for TextInput box(Refer last logic and true results in above formula) which I am using as search box for gallery.

This is the last peice of puzzle that I am trying to put together. Any suggestion on that?

Thanks!


Helphelp91
Level: Power Up

Re: Filter Gallery by Current User Details

@RandyHayes 

So about your first question, 
Condition 1 is for Admin and the rule for that condition is he should be able to see all unresolved cases(categorized by 'Resolved Date' = Null). Further if the current user is not in 'Admin Table' the 'If' statement looks for second condition and so on... Here, I tested first 2 conditions where I am an admin and I am a part of 'User Group Table' meeting condition 2 as well. After testing against me and my coworker in my team where he is not an admin, he is only able to see cases that meet condition 2. So long story short, I could get the condition logic working yesterday. 

About second question, 
My logic for this gallery is that initially it should be filtered by above 4 conditions.  On successfully retrieving unresolved cases by current user meeting any of the condition, user should have ability to search among those retrieved cases using the text search box.  

Now as I mentioned in my previous reply to Drrickryp, I was able to retrieve cases as per the conditions. My next obstacle is not being able to search gallery by text search box against ItemID column. Can you help me with search box part?  Below is the formula I am using, 

If(
    LookUp('[dbo].[Admin Table]', UserEmail in User().Email, ElevatedSecurity) = true, 
    Filter('[dbo].[Case Details Table]', IsBlank(ResolvedDate)),

    LookUp('[dbo].[User Group Table]', User_x0020_Email in User().Email, Escalate) = false,
    Filter('[dbo].[Case Details Table]',  IsBlank(ResolvedDate) && Escalate = true),

    LookUp('[dbo].[User Group Table]', User_x0020_Email in User().Email, OrderType) = "Manufactured",
    Filter('[dbo].[Case Details Table]', IsBlank(ResolvedDate) && IsBlank(Is Escalate) && OrderType = "Manufactured"),

    LookUp('[dbo].[User Group Table]', User_x0020_Email in User().Email, OrderType) = "PO",
    Filter('[dbo].[Case Details Table]', IsBlank(ResolvedDate) && IsBlank(Is Escalate) && OrderType = "PO"),
    
    !IsBlank(TextSearchBox1_1.Text),
    Filter('[dbo].[Case Details Table]', StartsWith(ItemID, TextSearchBox1_1.Text))
    ) 



Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,418)