Delegation - Filter Gallery on basis of Distinct items dropdown

[Pershad]

I have one dataverse Table "T" which has 3 columns - "Enter Date", "Category" and "Cost".

I Added one gallery "glryT" and connected it to datasource table "T".

I added 3 dropdowns above outside this gallery- "ddYear", "ddMonth" and "ddCategory"

I also created one collection "colT" using table "T" as datasource for OnVisible property of screen1 - 

ClearCollect(colT, Filter(T, 'Created By'.'Primary Email' = User().Email)

Items property of all Dropdowns are as follows:

ddYear - Distinct(colT, Text('Enter Date',"yyyy")

ddMonth - Distinct(colT, Text('Enter Date',"mmm")

ddCategory - Distinct(colT, Category)

Now I am filtering out the gallery glryT using different dropdown options so items property of gallery is:

Sort(
Filter(T, 'Created By'.'Primary Email' = User().Email,
IsBlank(ddYear)||IsEmpty(ddYear) || Text('Enter Date',"yyyy")=ddYear.Selected.Result,
IsBlank(ddMonth)||IsEmpty(ddMonth)|| Text('Enter Date',"mmm")=ddMonth.Selected.Result,
IsBlank(ddCategory)|| IsEmpty(ddCategory)|| Category = ddCategory.Selected.Result),
'Modified On',Descending)

I am getting delegation error on  " || Text('Enter Date',"yyyy")=" and 
"|| Text('Enter Date',"mmm")=" section of above formula

I am doing one more calculation outside this gallery on same screen1 inside Label control using:

Sum(glryT.AllItems,Value(Cost))

Result of this calculation is used in another screen2. 

I could have used the collection ColT as the datasource for gallery but issue is the calculation part will only work once we come to this gallery screen1 otherwise the calculation part will not show on other screen2. 

Is there any way we can get rid of these delegation errors? Appreciate help from anyone please.

[RandyHayes · Accepted solution]

@Pershad 

Your formula is not delegable.  Also, the use of random IsBlank and IsEmpty functions are not going to give you the results you want as they are based on a Control...not a value.

Please consider changing your Formula to the following:

With({_items: Filter(T, 'Created By'.'Primary Email' = User().Email)},

    Sort(
        Filter(_items,
            (IsBlank(ddYear.Selected.Result) || Text('Enter Date',"yyyy") = ddYear.Selected.Result) &&
            (IsBlank(ddMonth.Selected.Result) || Text('Enter Date',"mmm") = ddMonth.Selected.Result),
            (IsBlank(ddCategory.Selected.Result) || Category = ddCategory.Selected.Result)
        ),
        'Modified On',
        Descending
    )
)    

I hope this is helpful for you.

[Pershad]

@RandyHayes 

Thank you so much. It works. I have been trying since last many hours but could not able to get the right formula. 

I am using IsBlank and IsEmpty because gallery would otherwise looks empty and will show results only if I select any item from dropdowns. 

Thanks again for your reply. Much appreciated 🙂

[RandyHayes]

@Pershad 

IsBlank and IsEmpty are two entirely different functions and, you were applying them to the Control - which will never be Blank or Empty.

In the formula I provided, the answer is to look at the Selected Item of the control to determine if it is blank, that is all you need.

Happy to help!

[Pershad]

@RandyHayes 

Agreed but in my case I am using "AllowEmptySelection" property of dropdown control as true.