Answered

Please help! Replace startswith with contains

(0) Share

Report

Posted on by teach20

I would like to replace StartsWith with "contains" so that it will search for any character(s) contained in the project and program columns. Can someone please help me to edit these formulas while keeping them delegable? My current code in my galProjects is below:

Switch(varSortColumn, "msdyn_name",
SortByColumns(
Filter(
 'Project Request', Approval = 'Approval (Project Request)'.No && 'Project Request Status'<>'Project Status Choices'.Deleted &&
 And(
 Or(
 IsBlank(cmbBoxHid.SelectedItems),
 IsEmpty(cmbBoxHid.SelectedItems),
 'Project Request Status' = Index (
 cmbBoxHid.SelectedItems,
 1
 ).Value,
 'Project Request Status' = Last(
 FirstN(
 cmbBoxHid.SelectedItems,
 2
 )
 ).Value,
 'Project Request Status' = Last(
 FirstN(
 cmbBoxHid.SelectedItems,
 3
 )
 ).Value
 ),
 StartsWith(
 Name,
 inpSearch.Text
 ) || StartsWith(
 Program,
 inpSearch.Text
 )
 )
),"msdyn_name",If(varSortDirection = Ascending, Descending, Ascending)),
"cr155_program",
SortByColumns(
Filter(
 'Project Request',
 And(
 Or(
 IsBlank(cmbBoxHid.SelectedItems),
 IsEmpty(cmbBoxHid.SelectedItems),
 'Project Request Status' = Index (
 cmbBoxHid.SelectedItems,
 1
 ).Value,
 'Project Request Status' = Last(
 FirstN(
 cmbBoxHid.SelectedItems,
 2
 )
 ).Value,
 'Project Request Status' = Last(
 FirstN(
 cmbBoxHid.SelectedItems,
 3
 )
 ).Value
 ),
 StartsWith(
 Name,
 inpSearch.Text
 ) || StartsWith(
 Program,
 inpSearch.Text
 )
 )
),"cr155_program",If(varSortDirection = Ascending, Descending, Ascending)))

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
@teach20

Search() function and the "in" operator are not delegatable in SharePoint. There are work arounds but there is a performance hit for larger databases. See @WarrenBelz 's blog Practical PowerApps for them. If you must use them consider switching from SharePoint to Dataverse or SQL. In Dataverse,

With({filt1:SortByColumns( Filter( 'Project Request'', Approval = 'Approval (Project Request)'.No && 'Project Request Status'<>'Project Status Choices'.Deleted), Or(Len(cmbBoxHid.Selected.Value)=0 , 'Project Request Status' = Index cmbBoxHid.SelectedItems, 1 ).Value, 'Project Request Status' = Last(FirstN(cmbBoxHid.SelectedItems, 2 )).Value, 'Project Request Status' = Last(FirstN(cmbBoxHid.SelectedItems, 3 )).Value ),"msdyn_name", If(varSortDirection = Ascending, Descending, Ascending) ), filt2:SortByColumns( Filter('Project Request", Or( Len(cmbBoxHid.Selected.Value)=0 , 'Project Request Status' = Index cmbBoxHid.SelectedItems, 1 ).Value, 'Project Request Status' = Last(FirstN(cmbBoxHid.SelectedItems, 2 )).Value, Project Request Status' = Last(FirstN(cmbBoxHid.SelectedItems, 3 )).Value ),"cr155_program", If(varSortDirection = Ascending, Descending, Ascending) ) }, If( varSortColumn = "msdyn_name", Search(filt1,inpSearch.Text,"Program","Name"), varSortColumn = "cr155_program", Search(filt2,inpSearch.Text,"Program","Name") ) )

Was this reply helpful? Yes No
teach20 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Rick! Thanks for the information. My table is currently in a Dataverse database. Is that still possible?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@teach20
Yep, see above

Was this reply helpful? Yes No
Verified answer

WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @teach20 and thanks for the mention @Drrickryp ,

I suspect you are using Dataverse here when you are using this line

'Project Request Status' <> 'Project Status Choices'.Deleted

The code below is based on this premise - if you are using SharePoint, the whole filter is pretty much subject to Delegation, even if you are not getting a warning. I also assume you have set varSortDirection with SortOrder.Ascending / SortOrder.Descending, not the word. I could have also used Search() here but kept it to one Filter.

SortByColumns( Filter( 'Project Request', ( varSortColumn = "cr155_program" || ( Approval = 'Approval (Project Request)'.No && 'Project Request Status' <> 'Project Status Choices'.Deleted ) ) && ( Len(cmbBoxHid.Selected.Value) = 0 || 'Project Request Status' in cmbBoxHid.SelectedItems.Value ) && ( inpSearch.Text in Name || inpSearch.Text in Program ) ), varSortColumn, varSortDirection )

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

Was this reply helpful? Yes No