Gallery Multiple If based on User Email

[Phineas]

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
))

[WarrenBelz]

@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]

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]

@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]

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.

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.

[WarrenBelz]

@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]

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.

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
)
)
)
)

[WarrenBelz · Accepted solution]

@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]

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.