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

SQL Delegation with Sort, AddColumns, and Filter

Hello, I'm somewhat new to PowerApps, but I seem to be hitting a wall that I don't fully understand. After reading up, it seems that some functions can put the workload on the source server verses having PowerApps do it directly. And that PowerApps by default can do 500 lines itself and be modified to go up to 2,000. 

 

I'm personally working with a SQL server and am trying to populate a Data Table using this function

 

 

Sort(
    Filter(
        AddColumns(
            mT_SalesAttributeDB_MaterialElements,
            "MatName",
            LookUp(
                mT_SalesAttributeDB_Materials,
                ID = MaterialID
            ).MaterialName,
            "Average",
            (Min + Max) / 2
        ),
        MatName = lblMaterialName_Materials.Text
    ),
    Average,
    Descending
)

 

 

I have a List Box that will change the value of lblMaterialName_Materials, and then that is used to filter what I'm trying to show. This works on most of the results from that List Box, however, there are a few it won't work at all for. The Data Table shows as blank.

 

I went and looked at the SQL table and the results it would be populating in that Data Table are past the 500 count, so I'm thinking that this query isn't getting delegated to the SQL server. Now, I can obviously just change the threshold to 2,000, but it's possible this table will grow past 2,000. And things I have planned later in my application already have 16k lines in it. 

 

My questions are

  1. Why isn't my function being delegated to the SQL server? I've already verified that each of those functions should support delegation with SQL.
  2. Is there something I have to do to enable delegation in PowerApps or the SQL Server?
  3. If for some reason I wanted to put a CountRows around that, which I don't think supports delegation with SQL, would the inside part still be delegated and then the final CountRows wouldn't be or does the whole thing stop being delegated?

Thanks for any help

 

2 REPLIES 2
Jsunnb
Frequent Visitor

I'm not clear why it is different, but if I make a new Label (lblHelper_Materials) that is hidden and set it's value to LookUp(mT_SalesAttributeDB_Materials, MaterialName = lblMaterialName_Materials.Text).ID

 

Then update the Data Table to be 

Sort(
    AddColumns(
        Filter(
            mT_SalesAttributeDB_MaterialElements,
            MaterialID = Value(lblHelper_Materials.Text)
        ),
        "Average",
        (Min + Max) / 2
    ),
    Average,
    Descending
)

That works just fine and seems to respect the Delegation. But when I combine them as a single function it stops working.

Jsunnb
Frequent Visitor

For anyone that might be facing this issue as well, I ended up doing all the AddColumns external to Powerapps and just made views in SQL so that I could maintain that delegation

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,404)