PowerApps SharePoint Delegation

[gauravsharma74]

Hello

I have Text, Choice, Number column in SharePoint list. I am using this list in PowerApps gallery filter items. It is giving delegation warning that it wont work for large number of records. How to fix this?

Filter(
    PropertyInventory,
    TextInput1.Text in ClientName,
    BHK in cmbBHK.SelectedItems,
    ClientCategory in cmbClientCat.SelectedItems,
    Furnishing in cmbFurnishing.SelectedItems,
    SaleOrRent in cmbPropCategory.SelectedItems
)

[FernandoTC]

Hi @gauravsharma74 

The problem here is that the 'in' operator is not delegable to Sharepoint for neither text, integers such as id's or full records.

Based on your example. The best option is to use '=' in the clauses that refer to dropdown/combobox/lisbox which one single selection. I.E, lets say you can only pick one ClientCategory, then in the filter clause, change: 

• ClientCategory in cmbClientCat.SelectedItems for: ClientCategory=cmbClientCat.Selected.Value (This is assuming this combobox items refer to a choice column. If not, just specify which field you need to match)

For text, you can use the function StartsWith(). This can be delegated only for text and the filter condition is that the field STARTS with the specified string.

If some of those filters you want to apply can have more than one selection in your comboboxes/listbox I recommend you tu use two filter functions, the inner one with the delegable clauses and the outer one with the non-delegable clauses:

I.E, lets say I want to filter by any ClientCategory, by client name,and by if it is a Rent or a Sale.

If first I filter by RentOrSale field, this function will be delegated and thus, the ammount of data retrieved will be less significant (the target here is to reduce this data to less than 2000 records by using delegable functions, and then, using the non-delegable ones).

Filter(
        Filter(
                 PropertyInventory,
                 StartsWith(ClientName,TextInput1.Text),
                 SaleOrRent = cmbPropCategory.Selected.Value
              )
    ClientCategory in cmbClientCat.SelectedItems,
)

 
The filter function in the inside is fully delegable. The outside one it is not. This way you can filter with non-delegable functions. Just make the delegable functions filter the data source first enough to make it return less than 2000 records.

More info about SP delegation here: https://docs.microsoft.com/en-us/connectors/sharepointonline/

Hope it helps you

[gauravsharma74]

Hello @FernandoTC 

Let me explain exactly what I am looking for and then you can advise. I have a SharePoint list that has multiple columns. Please find image attached. Choice columns is single select, does not allow multiple options. But on Powerapps for filtering gallery I have allowed multiple select for choice columns. So either or is used for finding items within each choice column or filter.

The way I want to filter data is using And on Filters or multiple columns, but Or on within each choice column allowing multi select on using Combo box. Please advise.

Also I could find anything on documentation or internet on inner and outer filters as explained by you. Do you have any references? Also when I try to use multiple filters it is giving error. When I type my collection name, intellisense is not showing options, so that means it is not identifying. Please see image attached.

[WarrenBelz]

Hi @gauravsharma74 ,

@FernandoTC 's comprehensive explanation sets it our well. You simply cannot use the in statement on data sets over (a maximum of) 2,000 records. The "Inner" and "outer" filter explanation can be viewed another way - you need to get your data set down to under 2,000 records for the in filter to work. I will use a With() statement below, which is only really setting a Variable for the filtered set that needs to be under the limit.

With(
   {
      colInventory:
      Filter(
         PropertyInventory,
         StartsWith(ClientName, TextInput1.Text)
      )
   },
   Filter(
      colInventory,
      BHK in cmbBHK.SelectedItems &&
      ClientCategory in cmbClientCat.SelectedItems &&
      Furnishing in cmbFurnishing.SelectedItems &&
      SaleOrRent in cmbPropCategory.SelectedItems
   )
)

So if your client filter using StartsWith produced less than 2,000 records, you would get all relevant records to filter the rest.

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.

[gauravsharma74]

Thank you @WarrenBelz . I have been searching for this solution tirelessly over the net but couldn't find it. Answer was in bits and pieces and could not connect the dots to get the complete solution. Your solution worked. I am left with only one thing i.e. to test it out on large no of records. Right now it is only around 20.

Actually the way it is supposed to work is that first time when screen loads, gallery is supposed to load all records with delegation support. So I am fine with multiple of 100s. But then user could search by client name - text or pick one or more filters - combo multi select. So in that scenario, with lets say more than 5000 records, what should be the gallery items formula strategy?

[gauravsharma74]

Hello @WarrenBelz 

I have 5 filters. If I select just one or less than 5 filters, it is not returning any records. It is working only when all 5 filters conditions are met. Not working in case any filter is not selected. What should I do?

I am creating a collection using Choices function and then using the collection to populate combobox items.

[gauravsharma74]

I tried adding blanks while populating collections for filters and then updated my query, but still it doesn't work. Please advise @WarrenBelz 

With(
    {
        colInventory: Filter(
            PropertyInventory,
            StartsWith(
                ClientName,
                TextInput1.Text
            )
        )
    },
    Filter(
        colInventory,
        (PropertyProject in cmbProjects.SelectedItems || If(
            IsBlank(cmbProjects.SelectedItems),
            true,
            false
        )) && (BHK in cmbBHK_1.SelectedItems || If(
            IsBlank(cmbBHK_1.SelectedItems),
            true,
            false
        )) && (Furnishing in cmbFur_1.SelectedItems || If(
            IsBlank(cmbFur_1.SelectedItems),
            true,
            false
        )) && (ClientCategory in cmbClCat_1.SelectedItems || If(
            IsBlank(cmbClCat_1.SelectedItems),
            true,
            false
        )) && (SaleOrRent in cmbPropCat_1.SelectedItems || If(
            IsBlank(cmbBHK_1.SelectedItems),
            true,
            false
        ))
    )
)

[gauravsharma74]

Finally, this works. Thanks @WarrenBelz for all your help and advise.

With(
    {
        colInventory: Filter(
            PropertyInventory,
            StartsWith(
                ClientName,
                TextInput1.Text
            )
        )
    },
    Filter(
        colInventory,
        (PropertyProject in cmbProjects.SelectedItems || If(
            IsBlank(cmbProjects.Selected),
            true,
            false
        )) && (BHK in cmbBHK_1.SelectedItems || If(
            IsBlank(cmbBHK_1.Selected),
            true,
            false
        )) && (Furnishing in cmbFur_1.SelectedItems || If(
            IsBlank(cmbFur_1.Selected),
            true,
            false
        )) && (ClientCategory in cmbClCat_1.SelectedItems || If(
            IsBlank(cmbClCat_1.Selected),
            true,
            false
        )) && (SaleOrRent in cmbPropCat_1.SelectedItems || If(
            IsBlank(cmbPropCat_1.Selected),
            true,
            false
        ))
    )
)

[WarrenBelz]

@gauravsharma74 ,

Yes - if you want to allow for blanks, you need that statement. I am in a different timezone so morning here.

You have also marked your own post as the solution - please consider what helped you get there also.

[gauravsharma74]

Hello @WarrenBelz  @FernandoTC . Thanks for all your help so far.

When I used CDS for the same, delegation worked perfectly and used to return records in the gallery in multiple of 100s, after scrolling down it used to give me next 100 and so on. But now with this it returns me more than 100, i.e. 327 in one go, which is all records as of now. What will happen if app reaches more than 10K or a million? I think it will slow down the data load, right? How can I make this work like CDS with Delegation? What is the best way to solve this from design perspective for records close to 100k or millions?