Answered

Replace startswith

(0) Share

Report

Posted on by Chrisguff12

229

I have a datatable in two of my apps the formulas are different but but use the StartsWith function. I use them to filter out the requests and I would like to replace startswith so that it will search any word contained in the designated fields. Can you please help me to edit these formulas, while keeping them Delegable?

The first Formula

Sort(
 Filter(
 'Expense App List New',IsBlank(Filter_By_Item_TextInput.Text)||
 StartsWith(Description,Filter_By_Item_TextInput.Text),
 IsBlank(FIlter_by_Common_Supplies_Dropdown1.Selected.Supplies)||
 CommonSupplies = FIlter_by_Common_Supplies_Dropdown1.Selected.Supplies,
 IsBlank(Filter_By_Supplier_TextInput4.Text)||
 StartsWith(Suppliers,Filter_By_Supplier_TextInput4.Text),
 IsBlank(LookUp_Category_Dropdown2.Selected.Category)||
 StartsWith(Category,LookUp_Category_Dropdown2.Selected.Category)
 ),
 ID,Descending
)

here is the second Formula.

With(
 {
 wActive: Filter(
 'Mis-Etched Details',
 CurrentStatus = "Can't Replace" || 
 CurrentStatus = "Checking on Replacement Parts" || 
 CurrentStatus = "Complete" || 
 CurrentStatus = "Configs Purchased" || 
 CurrentStatus = "Dropped off to Repairs" || 
 CurrentStatus = "Given to B-Stock" || 
 CurrentStatus = "Liquidated" || 
 CurrentStatus = "Repaired and Restocked" || 
 CurrentStatus = "Repairing Unit" || 
 CurrentStatus = "VRMA'ed" || 
 CurrentStatus = "Waiting For Approval"
 )
 },
 Sort(
 Filter(
 wActive,
 IsBlank(Tech_Name_Search_TextInput1_2.Text) || 
 StartsWith(Mis_EtchedBy,Tech_Name_Search_TextInput1_2.Text),
 IsBlank(Filter_by_EDC_TextInput.Text) || 
 StartsWith(EDC,Filter_by_EDC_TextInput.Text),
 IsBlank(Filter_by_Serail_TextInput.Text) || 
 StartsWith(SerialNumber,Filter_by_Serail_TextInput.Text),
 IsBlank(Filter_by_Manufacturer_TextInput.Text) || 
 StartsWith(Description,Filter_by_Manufacturer_TextInput.Text),
 IsBlank(Filter_Current_Status_Dropdown1_2.Selected.Status) || 
 CurrentStatus = Filter_Current_Status_Dropdown1_2.Selected.Status),
 MisEtchedDate,
 If(SortDescending1,Ascending, Descending
 )
 )
)

Categories:

General Questions

I have the same question (0)

All responses (5)

Answers (1)

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

Re: Replace startswith

@Chrisguff12
Search() and the "in" operator are not delegatable in SharePoint. See @WarrenBelz 's blog Practical Powerapps for ways around delegation limits in SharePoint. However, if you have a large list, ie. >4k items, they all come with a penalty that they take a lot of time to execute.

Was this reply helpful? Yes No
Chrisguff12 229 on at

Like (0)

Report

Re: Replace startswith

@Drrickryp
I have found WarrenBelz BLog before but I should probably go over it again. Both of my apps are still under 700 records so maybe Delegation is not going to be an issue. Even if the records eventually get over 4k and I would only get 2,000 records back I think that would be fine. I am using these formula to find past equipment and items that we have ordered. I was hoping that the multiple filters would cut down amount of records that come back anyways.

I was just hoping that I could get the on filter to check the certain field if it contains the info in the textbox.

Was this reply helpful? Yes No
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like (1)

Report
Re: Replace startswith

@Chrisguff12

As long as your filtered list does not exceed 2k

With( { wActive: Filter( 'Mis-Etched Details', CurrentStatus = "Can't Replace" || CurrentStatus = "Checking on Replacement Parts" || CurrentStatus = "Complete" || CurrentStatus = "Configs Purchased" || CurrentStatus = "Dropped off to Repairs" || CurrentStatus = "Given to B-Stock" || CurrentStatus = "Liquidated" || CurrentStatus = "Repaired and Restocked" || CurrentStatus = "Repairing Unit" || CurrentStatus = "VRMA'ed" || CurrentStatus = "Waiting For Approval" ) }, Sort( Filter( wActive, IsBlank(Tech_Name_Search_TextInput1_2.Text) || Name_Search_TextInput1_2.Text in Mis_EtchedBy, IsBlank(Filter_by_EDC_TextInput.Text) || Filter_by_EDC_TextInput.Text in EDC, IsBlank(Filter_by_Serail_TextInput.Text) || Filter_by_Serail_TextInput.Text in SerialNumber, IsBlank(Filter_by_Manufacturer_TextInput.Text) || Filter_by_Manufacturer_TextInput.Text in Description, IsBlank(Filter_Current_Status_Dropdown1_2.Selected.Status) || CurrentStatus = Filter_Current_Status_Dropdown1_2.Selected.Status ), MisEtchedDate, If(SortDescending1,Ascending, Descending ) )

should work. Please proofread my text to make sure the textbox names are correct.

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

Like (0)

Report
Re: Replace startswith

@Chrisguff12

Another option would be to use one textinput box for the search

With({wactive:If(IsBlank(Filter_Current_Status_Dropdown1_2.Selected.Status), 'Mis-Etched Details', Filter('Mis-Etched Details',CurrentStatus= Filter_Current_Status_Dropdown1_2.Selected.Status ) )}, Sort( Search( wActive, TextInput1.Text, "SerialNumber","EDC","Description" ), MisEtchedDate, If(SortDescending1,Ascending, Descending ) )

Was this reply helpful? Yes No
Chrisguff12 229 on at

Like (0)

Report

Re: Replace startswith

@Drrickryp
Thanks for helping me out again. my app is working a lot better now.

Was this reply helpful? Yes No