cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerApps11
Helper II
Helper II

Sorting and avoiding delegation

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

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
StalinPonnusamy
Community Champion
Community Champion

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

 

View solution in original post

9 REPLIES 9
PowerApps11
Helper II
Helper II

 

 

Thanks in advance 

winch
Helper I
Helper I

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

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 

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
Community Champion
Community Champion

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

 

View solution in original post

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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.

Hi @StalinPonnusamy 

 

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

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 (2,965)