Unanswered

With() Statement for Distinct Values in a ComboBox

(0) Share

Report

Posted on by SMurphy

105

I have a reference table in excel online that i am connecting to that has aprox 12k rows. I used the With() statement to beable to get the distinct values. I created a concatenated field called agent_code_name_city which is Agent_Code, 'Agent Name', and busns_addr_city. I used the following code originally:

With(
 {
 wStart: 
 Filter(
 tblAgentMasterList,
 StartsWith(
 agent_code_name_city,
 Self.SearchText
 )
 )
 },
 Sort(
 Distinct(
 wStart,
 agent_code_name_city
 ),
 Value
 )
)

This worked though it only allowed me to search by the Agent Code since the concatenated field starts with the agent code. I was hoping to be able to search by agent code, name or city.

I attempted to fix it by this:

With(
 {
 wStart: 
 Filter(
 tblAgentMasterList,
 StartsWith(
 Agent_Code, Self.SearchText
 ) ||
 StartsWith(
 'Agent Name', Self.SearchText
 ) ||
 StartsWith(
 busns_addr_city, Self.SearchText
 ) ||
 StartsWith(
 agent_code_name_city, Self.SearchText
 )
 )
 },
 Sort(
 Distinct(
 wStart,
 agent_code_name_city
 ),
 Value
 )
)

Same issue as before, any help or recommendations would be helpful. Thanks. @WarrenBelz i used your blog for most of this.

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (1)

WarrenBelz 153,042 Most Valuable Professional on at

Like (0)

Report

@SMurphy ,

Sorry, the second one is not going to work - the field searched needs to be the same one as you are using in Distinct.

Was this reply helpful? Yes No
SMurphy 105 on at

Like (0)

Report

@WarrenBelz is there a way i can do this by searching the whole string and not just startswith?

Was this reply helpful? Yes No
WarrenBelz 153,042 Most Valuable Professional on at

Like (0)

Report

@SMurphy ,

Sorry, not possible either - the whole idea of the workaround is to get a list of Distinct items starting with the SearchText - the user has already typed these in, so the Distinct list will only contain items starting with this, so you cannot search other items with the string elsewhere as they will not be in the available list.

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
SMurphy 105 on at

Like (0)

Report
Would it be possible to filter down the main combo box with another combo box that is selecting city?
With( { wStart: Filter( tblAgentMasterList, StartsWith( agent_code_name_city, Self.SearchText ) ) }, Sort( Distinct( wStart, agent_code_name_city ), Value ) )
Thanks.

Was this reply helpful? Yes No
WarrenBelz 153,042 Most Valuable Professional on at

Like (0)

Report
@SMurphy ,

Possibly with (use your output at xxxx)

With( { wStart: Filter( tblAgentMasterList, StartsWith( agent_code_name_city, Self.SearchText ) && City = YourCityDD.Selected.xxxx ) }, Sort( Distinct( wStart, agent_code_name_city ), Value ) )

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
SMurphy 105 on at

Like (0)

Report
Thanks this worked. If i wanted to add another filter would i just add another
&& Agent_Code = cmAgtCode.Selected.Value

As well not sure if its possible to make it so i can still search by agent name after filtering or search by agent if those two filters are blank?

Was this reply helpful? Yes No
WarrenBelz 153,042 Most Valuable Professional on at

Like (0)

Report
Hi @SMurphy ,

It would be something like this

With( { wStart: Filter( tblAgentMasterList, StartsWith( agent_code_name_city, Self.SearchText ) && ( Len(YourCityDD.Selected.xxxx) = 0 || City = YourCityDD.Selected.xxxx ) && ( Len(cmAgtCode.Selected.Value) = 0 || Agent_Code = cmAgtCode.Selected.Value ) ) }, Sort( Distinct( wStart, agent_code_name_city ), Value ) )

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
SMurphy 105 on at

Like (0)

Report

This shows to be working within the Data preview on the formula:

But on the actual combo box it is not filtering. I tried to restart powerapps to see if maybe it was a glitch but no luck.

Was this reply helpful? Yes No
WarrenBelz 153,042 Most Valuable Professional on at

Like (0)

Report

@SMurphy ,

What do you mean by "not filtering"? Are the expected values displaying for selection?

Was this reply helpful? Yes No
SMurphy 105 on at

Like (0)

Report

In my above example the data preview within the formula bar it shows 1 value which is correct, but the actual combo box is not showing the correct values.

Was this reply helpful? Yes No