ComboBoxes with large item lists

[nealkal]

I'm building an invoice submission form, where users will be picking information from comboboxes.  The user then clicks SUBMIT and it emails a team with the submission entries and invoice attachment.

The data is linked to a SharePoint List (I'm under the impression I have to use SharePoint in order to use the Attachment feature).  Some of the columns contain 10k and 20k+ items. I'm using Power Automate to update the SharePoint list with new data.

The issue is that the fields are not pulling in the full list for each.  There seems to only be a list of 100-300 or so in each field, when there should be 10s of thousands.  I'm honestly not sure how it's deciding what data to pull because it appears to be a random sample of each list.  Additionally, changing the Power Apps Setting "Data row limit" to a small number like 5, has no impact on each field's list.

I've played with the StartsWith() function. It accepts the function but has no impact and gives me the "delegation" warning.  I don't think I can use StartsWith anyway because I need the comboboxes to allow for searching of any part of the string.  

This is an example of the function from one of my fields:

Filter(Choices([@InvoiceIntake].'Spend Category:'),DataCardValue13.SearchText in Value)

Screenshot of App:

Any help is greatly appreciated!

Neal

[WarrenBelz]

Hi @nealkal ,

The search function will only work on record numbers up to your Data Row Limit (which is standard 500 and a maximum of 2,000). There is a workaround of sorts using StartsWith, which also gets Distinct values as per this blog of mine but (as you have discovered) you cannot use the in Filter in a Delegable manner.

With(
   {
      _Start: 
      Filter(
         Choices([@InvoiceIntake].'Spend Category:',
         StartsWith(
            Value,
            Self.SearchText
         )
      )
   },
   Sort(
      Distinct(
         _Start,
         Value
      ),
      Value
   )
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[nealkal]

Thank you, @WarrenBelz 

The code below is now allowing for my "Row Limit" rule in settings to work (other fields ignore this rule for some reason).  But still does not allow the list to reach beyond 2000. 

With(
{
_Start:
Filter([@InvoiceIntake].'Invoice Approver:',
StartsWith(
'Invoice Approver:'.Value,
Self.SearchText
)
)
},
Sort(
Distinct(
_Start,
'Invoice Approver:'.Value
),
Value
)
)

[WarrenBelz]

@nealkal ,

Ahh yes - you have a Lookup field, which is not Delegable using StartsWith. You actually need to use the field being looked up in the other list (which I assume is Text)