Sorting and avoiding delegation

[PowerApps11]

Hi All,

My current code is working fine but I having issue with delegations my code, how can I filter and avoid delegations?
Thanks 

Here is my currant code

If(
    First(StoreInventory).Value = "All",
    Filter(
        ProductServiceList,
        ProductSearchBox.Text in Sales_x0020_Description
    ),
    If(
        First(StoreInventory).Value = "Stock",
        Sort(
            Filter(
                ProductServiceList,
                ProductSearchBox.Text in Sales_x0020_Description && Quantity_x0020_On_x0020_Hand > 0
            ),
            If(
                Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 > 70,
                "a",
                Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 >= 31 && Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 <= 70,
                "b",
                Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 >= 1 && Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 <= 30,
                "c",
                Quantity_x0020_On_x0020_Hand = 0,
                "d"
            ),
            Descending
        )
    )
)

[PowerApps11]

Thanks in advance 

[winch]

Hello @PowerApps11,

Instead of filtering the list, get the list to be fetched into a collection.

Select the screen where your control (assuming it's a combo box and the code above is in the Items property) and in the OnVisible property, create a collection from you list:

Collect(colProductServiceList, ProductServiceList)

Then in the Items of the combo box, put:

Filter(colProductServiceList, ProductSearchBox.Text in Sales_x0020_Description && Quantity_x0020_On_x0020_Hand > 0)

Best,

Winch

[PowerApps11]

Hi @winch 

This will not respect delegation as you will get all the value in one go not as a optimized manner, I do know how to do a collection but I am avoiding collection at all costs for performance reasons, so I am looking a way around to modify my currant formula like StartsWith etc.

Thanks for trying 

[PowerApps11]

Hi @winch 

Below code will work and will respect delegations and bring back data in optimized manners  in patches of 100 each time and no need collection, direct from Data Source, but the problem comes in when I add Sort 

Works with no errors direct from data source

If(
    First(StoreInventory).Value = "All",
    Filter(
        ProductServiceList,StartsWith(
       SKU, ProductSearchBox.Text
    )),
    If(
        First(StoreInventory).Value = "Stock",
       
            Filter(
                ProductServiceList,
               StartsWith(
       Sales_x0020_Description, ProductSearchBox.Text
    ) && Quantity_x0020_On_x0020_Hand > 0
            )))

When I add sort I get the delegations 

            
            If(
                Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 > 70,
                "a",
                Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 >= 31 && Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 <= 70,
                "b",
                Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 >= 1 && Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 <= 30,
                "c",
                Quantity_x0020_On_x0020_Hand = 0,
                "d"
            ),
            Descending
        )
    )
)

Always is better to use direct from data source, collections is good for temporary operations, that's my opinion

Can you help please @StalinPonnusamy  thanks 

[StalinPonnusamy · Accepted solution]

Hi @PowerApps11 

Please try this. I assume only the issue with sorting.

If(
    First(StoreInventory).Value = "All",
    Filter(
        ProductServiceList,
        ProductSearchBox.Text in Sales_x0020_Description
    ),
    If(
        First(StoreInventory).Value = "Stock",
        With({_Item:  Filter(
                ProductServiceList,
                ProductSearchBox.Text in Sales_x0020_Description && Quantity_x0020_On_x0020_Hand > 0
            )},
        Sort(_Item,
            If(
                Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 > 70,
                "a",
                Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 >= 31 && Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 <= 70,
                "b",
                Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 >= 1 && Quantity_x0020_On_x0020_Hand / Value(Stock) * 100 <= 30,
                "c",
                Quantity_x0020_On_x0020_Hand = 0,
                "d"
            ),
            Descending
        )
      )
    )
)

[PowerApps11]

Hi @StalinPonnusamy 

I have tried but this just eliminates the blue = delegations and still I get all the data in one go not in batches is only sort part is causing the delegation issue and if i take the sort off i get my data in Batches of 100 each scroll.

maybe I need to use switch ?

Thanks for answering my tag

[StalinPonnusamy]

Hi @PowerApps11 

When we use Sort, This formula is evaluated for each record of the table and the results are used to sort the table. So when we use Sort batching is not possible. The system needs to pull all data to evaluate to sort.

On the other hand, SortByColumns which systems know ahead so batching works fine. If not defined anything it means it uses SortbyColumns.

I hope this answer helps to understand how this works.

[StalinPonnusamy]

Hi @PowerApps11

Please let us know if anything needs on your post. We can help with this.

Please do not forget to give kudos if you find the suggestion helpful or Accept it as a solution if works fine to help other users to find it useful.

[PowerApps11]

Hi @StalinPonnusamy 

Sorry for delay I was away and not near a pc Thank you so much for you advice and help