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

Conditional Filtering - Simplifying Query

Hello,

I'd like to simply this formula on a gallery.

If(
    Dropdown7_1.Selected.Value = Blank(),
    Filter(
        MarketingFlyerList,
        FlyerPeriod = varFlyerTime,
        EditFlag = Dropdown2_2.Selected.Value || Dropdown2_2.Selected.Value = "All Items",
        PurchasingOwner = Dropdown5_2.Selected.Result || Dropdown5_2.Selected.Result = "All Items",
        SupplierName = Dropdown3_2.Selected.Result || Dropdown3_2.Selected.Result = Blank(),
        StartsWith(
            ProductCode,
            TextInput7_2.Text
        )
    ),
    If(
        Dropdown7_1.Selected.Value = "Qc",
        Filter(
            MarketingFlyerList,
            FlyerPeriod = varFlyerTime,
            EditFlag = Dropdown2_2.Selected.Value || Dropdown2_2.Selected.Value = "All Items",
            PurchasingOwner = Dropdown5_2.Selected.Result || Dropdown5_2.Selected.Result = "All Items",
            SupplierName = Dropdown3_2.Selected.Result || Dropdown3_2.Selected.Result = Blank(),
            StartsWith(
                ProductCode,
                TextInput7_2.Text
            ),
            QC = "true"
        ),
        If(
            Dropdown7_1.Selected.Value = "On",
            Filter(
                MarketingFlyerList,
                FlyerPeriod = varFlyerTime,
                EditFlag = Dropdown2_2.Selected.Value || Dropdown2_2.Selected.Value = "All Items",
                PurchasingOwner = Dropdown5_2.Selected.Result || Dropdown5_2.Selected.Result = "All Items",
                SupplierName = Dropdown3_2.Selected.Result || Dropdown3_2.Selected.Result = Blank(),
                StartsWith(
                    ProductCode,
                    TextInput7_2.Text
                ),
                ON = "true"
            ),
            If(
                Dropdown7_1.Selected.Value = "Atl",
                Filter(
                    MarketingFlyerList,
                    FlyerPeriod = varFlyerTime,
                    EditFlag = Dropdown2_2.Selected.Value || Dropdown2_2.Selected.Value = "All Items",
                    PurchasingOwner = Dropdown5_2.Selected.Result || Dropdown5_2.Selected.Result = "All Items",
                    SupplierName = Dropdown3_2.Selected.Result || Dropdown3_2.Selected.Result = Blank(),
                    StartsWith(
                        ProductCode,
                        TextInput7_2.Text
                    ),
                    ATL = "true"
                )
            )
        )
    )
)

 

Dropdown7_1 contains 3 value (Qc, On, Atl) or can be blank.

The associated SharePoint list contains 3 columns for each choice (QC, ON, ATL), which are Single Lines of Text than contain the words "true" or "false". They are not booleans, just text.

 

The query under each condition is the same. The only thing that varies is the QC/ON/ATL columns.

I'd like to convert this formula in 1 block where I would not have to repeat everything 4 times because of the 4 choices in the dropdown.

 

Ideally, it would look like the one below but slightly longer so I can specify each possible choice in the dropdown (blank, Qc, On, Atl).

Filter(
    MarketingFlyerList,
    FlyerPeriod = varFlyerTime,
    EditFlag = Dropdown2_2.Selected.Value || Dropdown2_2.Selected.Value = "All Items",
    PurchasingOwner = Dropdown5_2.Selected.Result || Dropdown5_2.Selected.Result = "All Items",
    SupplierName = Dropdown3_2.Selected.Result || Dropdown3_2.Selected.Result = Blank(),
    StartsWith(
        ProductCode,
        TextInput7_2.Text
    ),
    If(
        Dropdown7_1.Selected.Value = "Qc",
        QC = "true"
    )
)

The issue here is that it's giving me a delegation warning so I'm wondering if I'm doing something wrong.

Thanks!

8 REPLIES 8
WarrenBelz
Super User
Super User

Hi @sam_sonepar ,

I think you need something like this (free-typed, so watch commas and brackets etc)

Filter(
   MarketingFlyerList,
   FlyerPeriod = varFlyerTime &&
   (
      Dropdown2_2.Selected.Value = "All Items" || 
      EditFlag = Dropdown2_2.Selected.Value
   ) && 
   (   
      Dropdown5_2.Selected.Result = "All Items" || 
      PurchasingOwner = Dropdown5_2.Selected.Result
   ) && 
      Dropdown3_2.Selected.Result = Blank() || 
      SupplierName = Dropdown3_2.Selected.Result
   ) && 
   Switch(
      Dropdown7_1.Selected.Value
      Blank(),
      StartsWith(
         ProductCode,
         TextInput7_2.Text
      ),
      "Qc",
      QC = "true",
      "On",
      ON = "true"
      "Atl",
      ATL = "true"
   )
)

 

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.

@WarrenBelz 

I tried your formula, written like this.

 

Filter(
    MarketingFlyerList,
    FlyerPeriod = varFlyerTime,
    EditFlag = Dropdown2_2.Selected.Value || Dropdown2_2.Selected.Value = "All Items",
    PurchasingOwner = Dropdown5_2.Selected.Result || Dropdown5_2.Selected.Result = "All Items",
    SupplierName = Dropdown3_2.Selected.Result || Dropdown3_2.Selected.Result = Blank(),
    StartsWith(
        ProductCode,
        TextInput7_2.Text
    ),
    Switch(
        Dropdown7_1.Selected.Value,
        Blank(),
        true,
        "Qc",
        QC = "true",
        "On",
        ON = "true",
        "Atl",
        ATL = "true"
    )
)

 

 

It seems to be working fine but I'm still getting the yellow triangle and blue line saying "Delegation Warning. The filter part of the formula might not work on large dataset."

I tried to change the Data Row Limit to 1, just to see if it would limit the query but I'm still able to get all the items I have.

Would you know why I'm getting a delegation warning but no delegation "symptoms"?

Hi @sam_sonepar ,

If the top filter is a date, that may be your issue. If you start removing items until the warning goes away, you will find it, although the rest looks OK.

The top is not a date. It's actually free text like this: "NOV/DEC 2020" 

The delegation warning is on the switch itself. But...it's working despite changing the Date Row Limit from 500 to 1.

Hi @sam_sonepar ,

I think it is the true in the Switch - try this (note also your top filters would normally show the item requiring true - no filter - first)

Filter(
    MarketingFlyerList,
    FlyerPeriod = varFlyerTime,
    Dropdown2_2.Selected.Value = "All Items" || EditFlag = Dropdown2_2.Selected.Value,
    Dropdown5_2.Selected.Result = "All Items" || PurchasingOwner = Dropdown5_2.Selected.Result,
    Dropdown3_2.Selected.Result = Blank() || SupplierName = Dropdown3_2.Selected.Result,
    StartsWith(
        ProductCode,
        TextInput7_2.Text
    ),
   (
      Dropdown7_1.Selected.Value = Blank() ||
         Switch( 
           Dropdown7_1.Selected.Value,
           "Qc",
           QC = "true",
           "On",
           ON = "true",
           "Atl",
           ATL = "true"
      )
   )
)

 

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.

 

The latest formula doesn't seem to work.

(I also took the chance the adjust the previous terrible naming scheme to something easier to work with.)

 

sam_sonepar_0-1622504932868.png

 

I'm wondering if having a condition (IF or SWITCH) is just not possible inside of the Filter function.

 

@sam_sonepar ,

Interesting - I cannot test this - can you please try the structure below

Filter(
   MarketingFlyerList,
   FlyerPeriod = varFlyerTime,
   (
      Dropdown2_2.Selected.Value = "All Items" || 
      EditFlag = Dropdown2_2.Selected.Value
   ) &&
   (
      Dropdown5_2.Selected.Result = "All Items" || 
      PurchasingOwner = Dropdown5_2.Selected.Result
   ) && 
   (
      Dropdown3_2.Selected.Result = Blank() || 
      SupplierName = Dropdown3_2.Selected.Result
   ) &&
   StartsWith(
      ProductCode,
      TextInput7_2.Text
   ) && 
   (
      Dropdown7_1.Selected.Value = Blank() ||
      If(
         Dropdown7_1.Selected.Value = "Qc",
         QC = "true",
         Dropdown7_1.Selected.Value = "On",
         ON = "true",
         Dropdown7_1.Selected.Value = "Atl",
         ATL = "true"
      )
   )
)

 

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.

 

FYI, I renamed some control and changed QC/ON/ATL from string to Boolean. It's still working fine with the previous formula.

 

When I put your formula, I get this with nothing showing up.

sam_sonepar_1-1622736486628.png

 

I tried to remove the condition about the region and the delegation warning goes away and results were showing up in the gallery.

 

sam_sonepar_0-1622736431376.png

 

Should I assume that IF conditions nested in a Filter function simply don't work?

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,425)