Retrieve Number from Column in Sharepoint List

[Anonymous]

Hi, I have created a form in my PowerApp to record the movement of storage boxes between offices. I want the form to pull in the Storage boxes' unique id (intBoxID) from another SharePoint list (qryBox) and then pull in the relevant data into the form for that record. I have failed at the first hurdle as I can't get the combo box, I have added, to retrieve the numbers. There are 48k records in the SP list so i'm not sure if it is even possible. In the combo box the formula for Items = qryBox.intBoxID and IsSearchable = True and SearchFields=qryBox.intBoxID. Can anyone advise if this is possible and if not is there another workaround? I can't see one as unfortunately the only unique identifier in the list is numeric. Any help would be much appreciated thanks,

Mike

[mdevaney]

@Anonymous 

Wow!  48k records is alot of data.  You are definitely going to need to read up on delegation techniques for SharePoint.  I suggest you start here: https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/

There are two steps to finding a solution here:

#1  Any column you want to perform a FILTER, LOOKUP or SEARCH on must be indexed if its over 5k rows.  Go into your List Settings and add intBoxID to your "Indexed Columns".  Otherwise, it will just stop at 5k rows and not look to 48k.

#2  SEARCH is restricted to 2,000 rows but you can workaround it by using code like this in the Items property of your ComboBox.

Filter(your_datasource_name, StartsWith(intBoxID,ComboBox1.SearchText))

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Anonymous]

Hi,

Thanks for your response, i indexed the boxid column as you said and updated your formula for the Items property to: Filter(qryBox, StartsWith(Text(intBoxID),ComboBox1.SearchText)) which is better but i can still only search up to 20000 records. 

Is there is any other options for searching all records?

thanks

Mike

[mdevaney]

@iAm_ManCat 

Any ideas on why only the 20k records are being retrieved after indexing?

[v-xida-msft]

Hi @Anonymous ,

Actually, the Filter(qryBox, StartsWith(Text(intBoxID),ComboBox1.SearchText)) formula would cause a Delegation warning issue. If you have faced a Delegation issue with a formula in your app, you could only retrieve 2000 records from your data source at most.

As an alternative solution, I think a Calculated field could achieve your needs. You could consider create a corresponding Calculated field (called "intBoxIDCalculated") for the "intBoxID" field in your SP List, and set the Formula box to following expression:

=[intBoxID]

After that, you could use the "intBoxIDCalculated" field as filter field in your Filter formula instead of the original "intBoxID" field. Then modify your Filter formula as below (set the Items property of ComboBox to following) :

Filter(
       qryBox, 
       StartsWith(intBoxIDCalculated, ComboBox1.SearchText)
)

Please check and see if my response within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Delegation-Formula-Help/td-p/393454

Best regards,

[iAm_ManCat]

Using Text() on a column value will not work as it will try and do that to every item in the datasource, however, instead of delegating, as the Text() command is not delegable in that way, it will limit it to 2k records (max delegation limit possible at this point in time).

I agree with @v-xida-msft that you should probably create a calculated column, this would save you from having to run a Flow against the data to create new text values (which would be your alternative method of proceeding).

Cheers,

ManCat

[Anonymous]

Hi,

Thanks for your reply and apologies in delay in responding (it's been a busy week)

I was unable to create a calculated field as this would exceed the List threshold of 5,000 and the SP site administrator does not want to change it.

Any other workarounds ?

thanks

Mike