Sharepoint ID search in PowerApps

[ebm1001]

Hi!
I have one List Screen in which my items have the ID in Sharepoint as a Subtitle. I want to search by all: Title, Subtitle and Body. The problem is that when I try to search by the ID everything goes blank.I attach the formulas I used in case any of you can help me figure out how to solve it.

Search(my_SP_List, TextSearchBox1.Text, "a", "b", "ID")

The thing is that when I have the formula like this Search(my_SP_List, TextSearchBox1.Text, "a", "b") it works, but not with the ID from Sharepoint:

Thank you very much for your time.

[Drrickryp]

Hi @ebm1001 

You would get more responses if you posted your question in the Building PowerApps forum.  This forum is meant for Common Data Service issues.  

To your point, Search() is not delegatable in SharePoint.  The correct syntax for your formula would be something like

Filter(my_SP_List, StartsWith( ID, SearchInput.Text ) )

Ref. https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-startswith 

You may need to chain the StartsWith() functions search several columns. 

An alternative would be to create a collection form your SP list and then Search() will work on it as collections are not subject to delegation limits and all PowerApps functions will work on them. 

[v-yutliu-msft · Accepted solution]

Hi @ebm1001 ,

Do you mean that you could not use ID field in search function?

Two reasons:

1)search function only works for text type field, ID field is number type

2)Textinput.Text represents text , not number

To solve this problem, you need to add a text type column with the value of ID and search based on this field.

Try this function:

Search(
       AddColumns(my_SP_List,"id2",Text(ID)), 
       TextSearchBox1.Text,
       "a", "b", "id2")

What's more, the delegation problem that @Drrickryp said may affect your issue too, but not the key problem.

Could you tell me how many records are in your sharepoint list?

If it is less then 2000, you just need to change the delegation limit to 2000 to avoid the effect.

 If it is larger then 2000, I suggest you save data to collection then filter based on this collection.

I do not suggest you use startswith function to avoid the delegation limit because startswith function and search function are different feather. Number type(ID field) is not delegate for startswith function neither.

Just need to do this: 

set the app's OnStart:

ClearCollect(collection1,my_SP_List)

set the gallery's Items:

Search(
       AddColumns(collection1,"id2",Text(ID)), 
       TextSearchBox1.Text,
       "a", "b", "id2")

Best regards,

[MShelnutt]

@v-yutliu-msft  What is the "a" and "b" of the search formula?  Thanks.

[AdrianQ9]

There actually is a way to search the Sharepoint ID.

I found it on another form (source at the very bottom), you basically convert the ID value to text. 

Text (ID)

• I changed my ID to be called Reference ID
• The bolded is the formula you need
• Edit the orange sections
• I have other filters if you don't need drop downs for filters remove those 

Filter( 'Preclearance Form',
('Approval Status'.Value = Dropdown1.Selected.Value || If(IsBlank(Dropdown1.Selected.Value),true,false)) &&
('Full Name With Title'.Value = Dropdown2.Selected.Value || If(IsBlank(Dropdown2.Selected.Value),true,false)) &&
(StartsWith(Text('Reference ID'),SearchBox.Text) || If(IsBlank(SearchBox.Text),true,false))
)

Source I got the Text (ID) from:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Can-PowerApps-search-the-Default-ID-field-in...