cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerApps11
Frequent Visitor

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
Frequent Visitor

 

 

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
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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