Power Platform Community Forum Thread Details

You helped me to get this code working before, hoping you can help me again.

Here is what I need to do.

I have changed the connection to data to a SQL Table called Vendors, I need to add search to the original code you provided so it will search either based on the VendorId or Name fields from the SQL table.

I am struggling to make this happen.

Here is the code you helped me with and my attempt

With({_searchText: SQLSearchTextInput.Text,
 _items: 
 Ungroup(
 Table(
 {_src:"CR", Items:
 AddColumns(colContractDataRecords, 
 "ContractNo_ID", If(!IsBlank(GrantContractNumber), GrantContractNumber & " - ") & Counterparty,
 "_altSort", If(IsBlank(GrantContractNumber), 1, 0)
 )
 },
 {_src:"VL", Items:Vendors}
 ),
 "Items"
 ),
 
 _use: If((DataCardValue2_2.Selected.Value="Legal" && Radio2.Selected.Value = "Direct") || DataCardValue2_2.Selected.Value="Direct", "VL", "CR")
 },
 Filter(
 ShowColumns(
 SortByColumns(
 ForAll(
 Filter(
 _items, 
 _src=_use && (!IsBlank(_searchText) && 
 (
 VendorId in _searchText ||
 Name in _searchText
 ))
 ),
 Switch(
 DataCardValue2_2.Selected.Value, 
 "Direct", {Value: VendorId & " - " & Name, _sort: 0}, 
 "Contract", {Value: ContractNo_ID, _sort: _altSort},
 "Legal", 
 Switch(
 Radio2.Selected.Value, 
 "Direct", {Value: VendorId & " - " & Name, _sort: 0},
 "Contract", {Value: ContractNo_ID, _sort: _altSort}
 )
 )
 ),
 "_sort", SortOrder.Ascending, 
 "Value", SortOrder.Ascending
 ),
 "Value"
 ),
 !IsBlank(Value)
 )
)

I do not know if this is even possible. If I have to put in a text lookup then change it from a combobox to a label I am fine with that just seems like I should be able to do it all at once.

The issue is that the SQL table has 5900 items in it so I want it to filter as the person types, Yes I know there is a concat in this code, that is why I broke it apart, still not working to bring in all of the list or filtering to what is being type.

Thank you in advance.

Categories:

Building Power Apps

@RandyHayes helped me get this resolved and works like a charm!! Thanks Randy for rescuing me again!!

With({_items: 
 Ungroup(
 Table(
 {_src:"CR", Items:
 AddColumns(colContractDataRecords, 
 "ContractNo_ID", If(!IsBlank(GrantContractNumber), GrantContractNumber & " - ") & Counterparty,
 "_altSort", If(IsBlank(GrantContractNumber), 1, 0)
 )
 },
 {_src:"VL", Items: Filter(Vendors, Self.SearchText in VendorId || Self.SearchText in Name) }
 ),
 "Items"
 ),
 
 _use: If((DataCardValue2_2.Selected.Value="Legal" && Radio2.Selected.Value = "Direct") || DataCardValue2_2.Selected.Value="Direct", "VL", "CR")
 },
 Filter(
 ShowColumns(
 SortByColumns(
 ForAll(Filter(_items, _src=_use), 
 Switch(DataCardValue2_2.Selected.Value, 
 "Direct", {Value: VendorId & " - " & Name, _sort: 0}, 
 "Contract", {Value: ContractNo_ID, _sort: _altSort},
 "Legal", 
 Switch(Radio2.Selected.Value, 
 "Direct", {Value: VendorId & " - " & Name, _sort: 0},
 "Contract", {Value: ContractNo_ID, _sort: _altSort}
 )
 )
 ),
 "_sort", SortOrder.Ascending, 
 "Value", SortOrder.Ascending
 ),
 "Value"
 ),
 !IsBlank(Value)
 )
)