web

You’re offline. This is a read only version of the page.

News and Announcements icon

Power Platform CommunityForums Power Apps Delegation warning usi...

Power Apps

Answered

Delegation warning using Search for sql server

(0) Share

Report

Report

Posted on by Community Power Pla... Microsoft Employee

I need to Search a table in sql server containing more than 2000 records.

I'am Using this formula:

If(rdo_SearchChoice.Selected.Value = "Track Code", Search('[dbo].[v_nsc_trackcode_assigned]',cbo_ComboSearch.SearchText,"Track_code"),
rdo_SearchChoice.Selected.Value ="Seller", Search('[dbo].[v_nsc_trackcode_assigned]',cbo_ComboSearch.SearchText,"Aligned_x0020_CSM_x002f_MAM"),
rdo_SearchChoice.Selected.Value ="Track Name", Search('[dbo].[v_nsc_trackcode_assigned]',cbo_ComboSearch.SearchText,"Track_Name"),
rdo_SearchChoice.Selected.Value ="Director", Search('[dbo].[v_nsc_trackcode_assigned]',cbo_ComboSearch.SearchText,
"Director_x0020_Name")
)

Is there a way to get around this delegation issue using another Function?

Thanks

Dave

Categories:

Building Power Apps

All responses Img

All responses (2)

Answers Img

Answers (1)

Sort by

Verified answer

CU-18081211-6 9,272 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous ,

It is possible that one of your column: "Track_code", "Aligned_x0020_CSM_x002f_MAM", "Track_Name" or "Director_x0020_Name" from sql table [dbo].[v_nsc_trackcode_assigned] to be a number type (bigint, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real) ? If so, please note that Search function is not delegable to SQL server if you use something different to text type (char, nchar, varchar, nvarchar, text, ntext).
The only way I see to solve your problem is to change the type of your column for SQL table into text type.

Also, I think for your formula is suitable to use Switch() function:

Switch(rdo_SearchChoice.Selected.Value, "Track Code", Search('[dbo].[v_nsc_trackcode_assigned]',cbo_ComboSearch.SearchText,"Track_code"), "Seller", Search('[dbo].[v_nsc_trackcode_assigned]',cbo_ComboSearch.SearchText,"Aligned_x0020_CSM_x002f_MAM"), "Track Name", Search('[dbo].[v_nsc_trackcode_assigned]',cbo_ComboSearch.SearchText,"Track_Name"), "Director", Search('[dbo].[v_nsc_trackcode_assigned]',cbo_ComboSearch.SearchText,"Director_x0020_Name") )

Hope it helps !
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

First, ensure that all of the columns you are trying to search are text columns.

Then, change your Formula to the following:

With({_column: Switch(rdo_SearchChoice.Selected.Value, "Track Code", "Track_code", "Seller", "Aligned_x0020_CSM_x002f_MAM", "Track Name", "Track_Name", "Director", "Director_x0020_Name" )}, Search('[dbo].[v_nsc_trackcode_assigned]',cbo_ComboSearch.SearchText,_column) )

I am not sure of your design as to why you would use a Combobox as the search source for this though. But if that is working for you and need it to be that way, then the above should either work or highlight the issue a little better for you.

I hope this is helpful for you.

Join the conversation