cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

filtering gallery using dropdown list and current user

Hello,

 

I would like to filter a Gallery based on dropdown lists, search text box and according to some other parameters (if the item is confidential and the user of the powerapps is equal to the creator of the item or the current user is one of the users that I have defined).

 

I did firstly the following formula which worked:

On Visible of the screen I set up the following:

If(
IsBlank(
LookUp(
SPListofusers,user_email=appuser || CreatedBy.Text=appuser,ID
)
),UpdateContext({itemprop: Filter('SPList',Confidential ="No")}),
UpdateContext({itemprop:'SP'})

On items in Gallery set up : SortByColumns( Filter( itemprop,StartsWith( EnquiryStatus, ViewReqsSearchText_1.Text)),"DateReceived", Descending ) 

 

so then Confidential record were visible only to someone who is included in the list of the approved users or the user of the powerapps is the creator of the specific item.

 

But I had to filter also my Gallery based on some dropdown lists.

SortByColumns(
Search(
If(
dd_Status.Selected.Result = "All" And dd_Outstanding.Selected.Result="-",'SPList',
dd_Status.Selected.Result ="All" And dd_Outstanding.Selected.Result <> "-",
(Filter('SPList',Outstanding= dd_Outstanding.Selected.Result)),
dd_Status.Selected.Result <> "All" And dd_Outstanding.Selected.Result = "-",
Filter('SPList', EnquiryStatus=dd_Status.Selected.Result),
dd_Status.Selected.Result <> "All" And dd_Outstanding.Selected.Result <> "-",
Filter('SPList',Status=dd_Status.Selected.Result And Outstanding=dd_Outstanding.Selected.Result)
),
ViewReqsSearchText_1.Text, "Title", "Description"),"DateReceived",Descending)

So in the last formula I would like to add if the record is Confidential and the user of the app is the creator of the the item OR the user of the app is one of the approved users. Like to embed the itemprop from this formula  SortByColumns( Filter( itemprop,StartsWith( Status, ViewReqsSearchText_1.Text)),"DateReceived", Descending ) to the formula above.

 

Or even because next to each record I have an arrow which navigates you to another gallery which shows more details, this arrow to be visible only if the record is Confidential and the user of the app is the creator of the the item OR the user of the app is one of the approved users. 

 

Can anyone help me with this, please?

 

Many thanks

1 ACCEPTED SOLUTION

Accepted Solutions

@anna2109 

Perfectly fine!  We're here to help...so no need to worry over the level of understanding or simplicity or complexity.

 

If based off of 'Created By' then change to the following formula:

With({isAdmin: !IsBlank(LookUp(SPListofusers, user_email=appuser, ID))},

    SortByColumns(
        Search(
            Filter('SPList',
                (dd_Status.Selected.Result = "All" || EnquiryStatus=dd_Status.Selected.Result) &&
                (dd_Outstanding.Selected.Result = "-" || Outstanding= dd_Outstanding.Selected.Result) &&
                (isAdmin || Confidential = "No" || 'Created By'.Email=appuser)
            ),
            ViewReqsSearchText_1.Text, "Title", "Description"
        ),
        "DateReceived",
        Descending
    )    
)    

 

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too!

View solution in original post

7 REPLIES 7
Super User III
Super User III

@anna2109 

Consider the following change to your formula:

With({isAdmin: !IsBlank(LookUp(SPListofusers, user_email=appuser || CreatedBy.Text=appuser, ID))},

    SortByColumns(
        Search(
            Filter('SPList',
                (dd_Status.Selected.Result = "All" || EnquiryStatus=dd_Status.Selected.Result) &&
                (dd_Outstanding.Selected.Result = "-" || Outstanding= dd_Outstanding.Selected.Result) &&
                (isAdmin || Confidential = "No")
            ),
            ViewReqsSearchText_1.Text, "Title", "Description"
        ),
        "DateReceived",
        Descending
    )    
)    

 

You can get rid of the context variable as well.

 

I hope this is helpful for you.

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too!

Thank you for the response it seems the formula you suggested returns only the No Confidential records.

 

What I would like to succeed is to show all the records Confidential and Not Confidential but to show for the confidential only if the current user of the App is the same with the created by or if the current user of the App is one of the SPListofusers

 

Do you have any suggestions.

 

Apologies if I am a pain.

Thank you for the response it seems the formula you suggested returns only the No Confidential records.

 

What I would like to succeed is to show all the records Confidential and Not Confidential but to show for the confidential only if the current user of the App is the same with the created by or if the current user of the App is one of the SPListofusers

 

Do you have any suggestions.

 

Apologies if I am a pain.

@anna2109 

No pain at all.  I misunderstood your logic a little at first.

Change your formula to the following:

With({isAdmin: !IsBlank(LookUp(SPListofusers, user_email=appuser, ID))},

    SortByColumns(
        Search(
            Filter('SPList',
                (dd_Status.Selected.Result = "All" || EnquiryStatus=dd_Status.Selected.Result) &&
                (dd_Outstanding.Selected.Result = "-" || Outstanding= dd_Outstanding.Selected.Result) &&
                (isAdmin || Confidential = "No" || CreatedBy.Text=appuser)
            ),
            ViewReqsSearchText_1.Text, "Title", "Description"
        ),
        "DateReceived",
        Descending
    )    
)    

 

I have some issues though with your CreatedBy column...can you explain that further?  You originally had this in a different filter.  I am assuming from your description now that you want to use that as part of the record filter above.  That bit of the formula most certainly needs to be replaced.  But, I need to know what is CreatedBy - Is that a text column you created in your list or are you trying to refer to the 'Created By' column that is automatically in SharePoint?  If the SharePoint column, then I just need to know what/how you defined your appuser variable.

 

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too!

Hello @RandyHayes 

 

thank you so much for your quick response it is the 'Created By' column that is automatically in SharePoint and for the appuser I set up on start of the App Set(appuser,User().Email);Set(appname, User().FullName).

 

What I want to succeed is if the current user of the App is one of these that are included in my SPListofusers then gallery should show all the items (Confidential and not confidential), if the current user of the App is not included in the SPListofusers then the gallery should so all the items as not confidential and the items as confidential that have been submitted from the same current user.

I am so sorry the lack of experience drives me to ask very simple questions and maybe how I approached the formula is unnecessarily complex.

 

Does it make more s

@anna2109 

Perfectly fine!  We're here to help...so no need to worry over the level of understanding or simplicity or complexity.

 

If based off of 'Created By' then change to the following formula:

With({isAdmin: !IsBlank(LookUp(SPListofusers, user_email=appuser, ID))},

    SortByColumns(
        Search(
            Filter('SPList',
                (dd_Status.Selected.Result = "All" || EnquiryStatus=dd_Status.Selected.Result) &&
                (dd_Outstanding.Selected.Result = "-" || Outstanding= dd_Outstanding.Selected.Result) &&
                (isAdmin || Confidential = "No" || 'Created By'.Email=appuser)
            ),
            ViewReqsSearchText_1.Text, "Title", "Description"
        ),
        "DateReceived",
        Descending
    )    
)    

 

_____________________________________________________________________________________
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.
Check out my PowerApps Videos too!

View solution in original post

Hi @RandyHayes 

 

Thank you so much. It worked!!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Users online (7,197)