web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Gallery Multiple If ba...
Power Apps
Answered

Gallery Multiple If based on User Email

(0) ShareShare
ReportReport
Posted on by Phineas 5,331 Moderator

I was being assisted by a User. They provided the following to my original parameters, which were:

Gallery content 'defaults' to the User's data ONLY - based on the User's Microsoft Outlook email - where the text in the 'Status' SPList column is "Pending" (or, even better, shall show ALL content based on User email with all items with Status of "Pending" sorted to be first in Gallery.

 

If(IsBlank(LookUp('Test List', User.Email=varUserName, Status="Pending")), Filter('Test List',User.Email=varUserName),Filter('Test List',User.Email=varUserName, Status="Pending"))

 

Here is my existing Gallery filter; I need this added to the above:

If(ReviewScreenEventDateRangeCheckbox,
Filter(
'Test List',
DateOfEvent>DateAdd(ReviewScreenEventStartDatePicker.SelectedDate,-1,Days)
&&
DateOfEvent<DateAdd(ReviewScreenEventEndDatePicker.SelectedDate,1,Days)

),Sort(
Filter(
'Test List',
Len(ReviewScreenSearchComboBox.Selected.Value) = 0 ||
StartsWith(
1CategoryType & 2MCategoryType,
ReviewScreenSearchComboBox.Selected.Value)
),
varSortGallery
))

Categories:
Building Power Apps
I have the same question (0)
  • WarrenBelz Profile Picture
    WarrenBelz 155,955 Most Valuable Professional on at

    @Phineas ,

    I have seen some of this code before and still wonder (as per my previous posts) why you are testing for after the previous day and before the next day, when you could simply test for the days equalling, however the below is provided for syntax guidance (it is free-typed, so check brackets and commas etc). I am also not entirely sure on your required exact logic here as well.

    With(
 {
 wLast: DateAdd(
 ReviewScreenEventStartDatePicker.SelectedDate,
 -1,
 Days
 ),
 wNext: DateAdd(
 ReviewScreenEventStartDatePicker.SelectedDate,
 1,
 Days
 ),
 wPending:
 !IsBlank(
 LookUp(
 'Test List', 
 User.Email=varUserName && Status="Pending"
 ).Status
 )
 },
 Sort(
 Filter(
 'Test List',
 (
 !ReviewScreenEventDateRangeCheckbox ||
 (DateOfEvent > wLast && DateOfEvent < wNext)
 ) && 
 ( 
 ReviewScreenEventDateRangeCheckbox ||
 (
 Len(ReviewScreenSearchComboBox.Selected.Value) = 0 ||
 StartsWith(
 1CategoryType & 2MCategoryType,
 ReviewScreenSearchComboBox.Selected.Value
 )
 )
 ) &&
 (
 !wPending || User.Email=varUserName
 ) &&
 (
 wPending || (User.Email=varUserName && Status = "Pending")
 ) 				
 ),
 If(
 ReviewScreenEventDateRangeCheckbox,
 varSortGallery
 )
 )
)

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

    Visit my blog Practical Power Apps

  • Phineas Profile Picture
    Phineas 5,331 Moderator on at

    Power Apps took most of it.

     

    PA errored on User.Email. I changed it to User().Email, which did not error but now has a delegation warning at the equal.

     

    !IsBlank(
    LookUp(
    'Test List',
    User().Email=varUserName && ContactStatus="Pending"
    ).ContactStatus
    )

     

    Also, the entire StartsWith and the && and || highlighted below are all part of a delegation warning:

     

             ) &&

             (         

                ReviewScreenPSMEventDateRangeCheckbox ||

                (

                   Len(ReviewScreenPSMSearchComboBox.Selected.Value) = 0 ||

                   StartsWith(

                      1CategoryType & 2MCategoryType,

                      ReviewScreenPSMSearchComboBox.Selected.Value

                   )

                )

             ) &&

             (

                !wPending || User.Email=varUserName

             ) &&

     

  • WarrenBelz Profile Picture
    WarrenBelz 155,955 Most Valuable Professional on at

    @Phineas ,

    I simply copied your code with User.Email assuming you had a Person field called User.

    I can get rid of the warning for you, but the last filter will still not be Delegable if the top filter (wList) below returns more than your Delegation limit

    With(
 {
 wLast: DateAdd(
 ReviewScreenEventStartDatePicker.SelectedDate,
 -1,
 Days
 ),
 wNext: DateAdd(
 ReviewScreenEventStartDatePicker.SelectedDate,
 1,
 Days
 ),
 wPending:
 !IsBlank(
 LookUp(
 'Test List', 
 User.Email=varUserName && Status="Pending"
 ).Status
 )
 },
 With(
 {
 wList:
 Sort(
 Filter(
 'Test List',
 (
 !ReviewScreenEventDateRangeCheckbox ||
 (DateOfEvent > wLast && DateOfEvent < wNext)
 ) && 
 (
 !wPending || User.Email=varUserName
 ) &&
 (
 wPending || (User.Email=varUserName && Status = "Pending")
 ) 				
 ),
 If(
 ReviewScreenEventDateRangeCheckbox,
 varSortGallery
 )
 )
 },
	 Filter(
 wList, 
 ReviewScreenEventDateRangeCheckbox ||
 (
 Len(ReviewScreenSearchComboBox.Selected.Value) = 0 ||
 StartsWith(
 1CategoryType & 2MCategoryType,
 ReviewScreenSearchComboBox.Selected.Value
 )
 )
 )
 )
)

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

    Visit my blog Practical Power Apps

  • Phineas Profile Picture
    Phineas 5,331 Moderator on at

    Okay now, take it easy on me; I'm trying to follow up and to provide this as understandable way as I can.

    1. You can see the Checkbox is 'true' which means the DatePickers should be active and I should only being seeing "Pending" items. I can see "Pending", 'Sent to Buyer" and others.

    2. The filter by CollectorsName=varUserName is working - as only my name is showing.

    3. The DatePickers component DOES work; if I change to the Start Date to 4/1/2021 the first option shown in the example drops off.

     

    Phineas_2-1650062281312.png

     

    With(
    {
    wLast: DateAdd(
    ReviewScreenEventStartDatePicker.SelectedDate,
    -1,
    Days
    ),
    wNext: DateAdd(
    ReviewScreenEventEndDatePicker.SelectedDate,
    1,
    Days
    ),
    wPending:
    !IsBlank(
    LookUp(
    'Test List',
    CollectorName = varUserName && ContactStatus="Pending"
    ).ContactStatus
    )
    },
    Sort(
    Filter(
    'Test List',
    (
    !ReviewScreenEventDateRangeCheckbox ||
    (DateOfEvent > wLast && DateOfEvent < wNext)
    ) &&
    (
    ReviewScreenEventDateRangeCheckbox ||
    (
    Len(ReviewScreenSearchComboBox.Selected.Value) = 0 ||
    StartsWith(
    Commodity1 Or Commodity2 Or Commodity3,
    ReviewScreenPSMSearchComboBox.Selected.Value
    )
    )
    ) &&
    (
    !wPending || CollectorName=varUserName
    ) &&
    (
    wPending || (CollectorName=varUserName && ContactStatus = "Pending")
    )
    ),
    If(
    ReviewScreenEventDateRangeCheckbox,
    varSortGallery
    )
    )
    )

     

    If I UnCheck the Checkbox nothing appears in the Gallery. With the Checkbox empty the Gallery should default to All "Pending" items associated with the CollectorsName. Click the Checkbox should show you everything and allow you to filter by Date range and the Content of the Combo Box.

    Phineas_1-1650062117263.png

     

  • WarrenBelz Profile Picture
    WarrenBelz 155,955 Most Valuable Professional on at

    @Phineas ,

    I am not trying to be hard on you - I just find that communicating the issue as clearly as I can saves a lot of unnecessary thread interactions. When you have a piece of code as complex as this, it is quite easy to give you valid code that produces a result from what I understand you might want, but is not actually what you expect. I am also free-typing this (without the benefit of any "red or blue lines"), so I tend to focus a bit on syntax.

    In the below, I am assuming the following:

    • The ReviewScreenEventDateRangeCheckbox only affects whether the date pickers are considered in the filter
    • If there are records with the Status of Pending, show only those records and if not show all records.
    • You want the end result sorted by varSortGallery (I assume this contains a field name, but this would normally only work on SortByColumns)
    • A bit more confusion now as User().Email = varUserName as this does nothing to filter the list (neither are part of it)

    Nevertheless, some guidance below using your code (and I still am confused why you are using the date filters the way you are as you could simply compare the DateOfEvent with the current date).

    With(
 {
 wLast: DateAdd(
 ReviewScreenEventStartDatePicker.SelectedDate,
 -1,
 Days
 ),
 wNext: DateAdd(
 ReviewScreenEventStartDatePicker.SelectedDate,
 1,
 Days
 ),
 wPending:
 !IsBlank(
 LookUp(
 'Test List', 
 User.Email=varUserName && Status="Pending"
 ).Status
 )
 },
 With(
 {
 wList:
 Sort(
 Filter(
 'Test List',
 (
 !ReviewScreenEventDateRangeCheckbox ||
 (DateOfEvent > wLast && DateOfEvent < wNext)
 ) && 
 User.Email=varUserName &&
 (!wPending || Status = "Pending") 				
 ),
 varSortGallery
 )
 },
	 Filter(
 wList, 
 Len(ReviewScreenSearchComboBox.Selected.Value) = 0 ||
 StartsWith(
 1CategoryType & 2MCategoryType,
 ReviewScreenSearchComboBox.Selected.Value
 )
 )
 )
)

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

    Visit my blog Practical Power Apps

     

  • Phineas Profile Picture
    Phineas 5,331 Moderator on at

    I apologize for the any frustration I may cause. Is there a preferred method by-which to post the formula so you can see it the way you'd like (with 'color')?


    Also, feel free to update the formula to better deliver the date range option; if ONLY Today is required rather than a second date picker please show me how to achieve, thank you.

    Below is my latest edit with your latest updates. It returns no data, with or without the checkbox.

    Phineas_0-1650127173614.png

    Yes, I understand the User.Email issue and have changed it to CollectorName ( a column in the SPLIst)

                        CollectorName = varUserName

    I've added full updated formula at the bottom. Below is an excerpt that kicking back and error (all in red is the error). Error =  The requested operation is invalid. Server response: Test List failed: The query is not valid...."

     

    With(
    {
    wList:
    Sort(
    Filter(
    'Test List',
    (
    !ReviewScreenEventDateRangeCheckbox ||
    (DateOfEvent > wLast && DateOfEvent < wNext)
    ) &&
    CollectorName=varUserName &&
    (!wPending || ContactStatus = "Pending")
    ),
    varSortGallery
    )
    },

     

    Updated formula (with above included):

    With(
    {
    wLast: DateAdd(
    ReviewScreenPSMEventStartDatePicker.SelectedDate,
    -1,
    Days
    ),
    wNext: DateAdd(
    ReviewScreenPSMEventStartDatePicker.SelectedDate,
    1,
    Days
    ),
    wPending:
    !IsBlank(
    LookUp(
    'Test List',
    CollectorName=varUserName
    )
    )
    },
    With(
    {
    wList:
    Sort(
    Filter(
    'Test List',
    (
    !ReviewScreenEventDateRangeCheckbox ||
    (DateOfEvent > wLast && DateOfEvent < wNext)
    ) &&
    CollectorName=varUserName &&
    (!wPending || ContactStatus = "Pending")
    ),
    varSortGallery
    )
    },
    Filter(
    wList,
    Len(ReviewScreenSearchComboBox.Selected.Value) = 0 ||
    StartsWith(
    PSMCCCategoryCommodity & SSMCCCategoryCommodity,
    ReviewScreenSearchComboBox.Selected.Value
    )
    )
    )
    )

  • Verified answer
    WarrenBelz Profile Picture
    WarrenBelz 155,955 Most Valuable Professional on at

    @Phineas ,

    It is not frustration, just an expectation when dealing with questions on code of this complexity that structure guidance will go a long way to solving it (I cannot always write valid and effective code of this size for you without being able to see your data or app).

    If you use Format Text and post in a Text box (the </> icon at the top), your code looks like the below and considerably easier to read and saves a lot of time formatting (which I have to do manually).

    Firstly, you had an error in there (User.Email) I had corrected on the other post, however as I mentioned, this line is doing nothing as neither value is in the list you are filtering

    You also needed .Value after your checkbox. I have also added the current day logic and this has added complexity due to your requirement for it to be Delegable (it is almost that - you need to be under your Delegation limit when the code hits the bottom filter)

    With(
 {
 wDay: Today(),
 wPending: !IsBlank(
 LookUp(
 'Test List', 
 User.Email=varUserName && Status="Pending"
 ).Status
 )
 },
 With(
 {
 wList:
 Sort(
 Filter(
 'Test List',
 (!ReviewScreenEventDateRangeCheckbox.Value || DateOfEvent = wDay) && 
 User().Email = varUserName && // this is doing nothing !!
 (!wPending || Status = "Pending") 				
 ),
 varSortGallery
 )
 },
 Filter(
 wList, 
 Len(ReviewScreenSearchComboBox.Selected.Value) = 0 ||
 StartsWith(
 1CategoryType & 2MCategoryType,
 ReviewScreenSearchComboBox.Selected.Value
 )
 )
 )
)

    The above should be valid.

     

    Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

    Visit my blog Practical Power Apps

     

     

     

  • Phineas Profile Picture
    Phineas 5,331 Moderator on at

    Yep, I changed the User.blah, blah, to CollectorName=varUserName; CollectorName being a column in the List. That should solve that.

    I'll make a note of the Format Text recommendation.

    To the value with the checkbox, I figured as much, and tried several ways but couldn't get anything to stick. So, I had the right though but couldn't execute.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 294 Most Valuable Professional

#2
11manish Profile Picture

11manish 210

#3
Valantis Profile Picture

Valantis 169

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans