Replace Startswith with Contains or Substring?

(0) Share

Report

Posted on by Australia

387

Hi all,

Is there a way that I can replace my Startswith function with 'Contains' or 'Substring' in PowerApps?
Report Owner Name: Joe Smith

Report Number: R2021/07

So that when I search in 'Smith' or '2021' or '07' the Gallery can return the desired results.

Thank you.

If(

!IsBlank(
LookUp(
'Report List',
Title = varUserName
).Title),
Sort(Filter('Report List', varUserName = 'Report Owner Name' || 'Report Status'.Value = "Complete"),
StartsWith('Report Owner Name', InpSearch.Text)|| StartsWith('Report Number', InpSearch.Text)
),
else statement

)

Categories:

Building Power Apps

I have the same question (0)

All responses (18)

Answers (1)

Australia 387 on at

Like (0)

Report
Copy link

Link copied!

Or can I just use 'Search' function?

Was this reply helpful? Yes No
WarrenBelz 154,755 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @Australia ,

Firstly the top filter needs to return less than 2,000 records (and your delegation setting at this) for this to work as the in filter is not Delegable

If( !IsBlank( LookUp( 'Report List', Title = varUserName ).Title ), With( { wList: Filter( 'Report List', 'Report Owner Name' = varUserName || 'Report Status'.Value = "Complete" ) }, Sort( Filter( wList, InpSearch.Text in 'Report Owner Name' || InpSearch.Text in 'Report Number' ) ), YourSortFieldHere ), else statement )

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.

Was this reply helpful? Yes No
Australia 387 on at

Like (0)

Report
Copy link

Link copied!

Fantastic, thanks @WarrenBelz. Could you tell me what the 'With' function does in this incidence?

Was this reply helpful? Yes No
Australia 387 on at

Like (0)

Report
Copy link

Link copied!

And correct, my list only has about 500 rows of data (less than 2000).
What is the advantage of using 'With' over 'Search' function highlighted in blue below?
If(
!IsBlank(
LookUp(
'Report List',
Title = varUserName
).Title),
),
Sort(
Search('Report List', InpSearch.Text,"Report Owner Name", "Report Number", "Report Date")
,
'Report Date',
Descending
), else statement

Was this reply helpful? Yes No
WarrenBelz 154,755 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @Australia ,

I have explained it in this section of one of my blogs, but it creates a (very) temporary table which must be under 2,000 items (With() is a "hidden" Delegation limitation) which you can then filter in the rest of the statement without any Delegation issues as it is no longer querying the data source directly.

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.

Was this reply helpful? Yes No
Australia 387 on at

Like (0)

Report
Copy link

Link copied!

Thanks @WarrenBelz A little bit advanced for me to comprehend. 🤔
So I just applied your formula and it has no red lines. But the gallery did not return any results though. Not sure what happened here?

Was this reply helpful? Yes No
WarrenBelz 154,755 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Australia ,

Can you please post the exact code (in Text) you have used and also how many items are in the list. I assume you have records in your list where the Title field is your user name?

Was this reply helpful? Yes No
Australia 387 on at

Like (0)

Report
Copy link

Link copied!

Thanks @WarrenBelz.
Here's the full syntax:

If(
!IsBlank(
LookUp(
'Report List',
Title = varUserName
).Title
),
With(
{
wList:
Filter(
'Report List',
'Report Owner Name' = varUserName || 'Report Status'.Value = "Complete"
)
},
Sort(
Filter(
wList,
InpSearch.Text in 'Report Owner Name' ||
InpSearch.Text in 'Report Number'
)
),
'Report Date',
Descending
),
Sort(Filter('Report List', varUserName = 'Report Owner Name' || 'Report Status'.Value = "Pending"),
StartsWith('Report Owner Name', InpSearch.Text)|| StartsWith('Report Number', InpSearch.Text)
)
);

Was this reply helpful? Yes No
WarrenBelz 154,755 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @Australia ,

I assume you had something in the Search box? Anyway try this

With( { wList: Filter( 'Report List', Report Owner Name' = varUserName || (Title = varUserName && 'Report Status'.Value = "Complete") || 'Report Status'.Value = "Pending" ) }, Sort( Filter( wList, IsBlank(InpSearch.Text) || ( InpSearch.Text in 'Report Owner Name' || InpSearch.Text in 'Report Number' ) ), 'Report Date', Descending ) )

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.

Was this reply helpful? Yes No
Australia 387 on at

Like (0)

Report
Copy link

Link copied!

Fantastic, thanks very much @WarrenBelz !
By the way can I have two 'With' functions in the syntax?

For the else statement at the last of the syntax, can I use another With
where I want to filter the list varUserName = 'Report Auditor Name'

Was this reply helpful? Yes No