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
)
)
)
Thanks for helping me out again. my app is working a lot better now.
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
)
)
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.
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.
@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.