Power Platform Community Forum Thread Details

Hi Everyone,

I'm stumped and hoping you can help!

I have a power app that uses on prem sql database. I have a lookup table for locations in a table the app points to which is not changeable since it is shared with a legacy app. Because of the legacy system some of the data has a lot of extra spaces, here is an example:

"123 STREET STREET " --As you can see there is an extra space in the middle and trailing spaces at the end.

When I create a combobox to point to this table it seems the combobox removes these spaces innately because its displayed normal upon selection. My items code for the box is:

Distinct(Filter('HR.NA_LOCATION', NA_LOC_STATUS = "A"), NA_LOC_ADDRESS1)

But when I select the problem address in the combobox it removes the spaces and looks normal. Anyways, i have a secondary combobox that should show unit options based on the address selected in the first. This looks like this:

Distinct(Filter('HR.NA_LOCATION', NA_LOC_ADDRESS1 = ComboBox8.Selected.Value && NA_LOC_STATUS = "A"),NA_LOC_UNIT_NAME & " (" & NA_LOC_CODE & ")")

My issue right now is when I select the problematic (with spaces) address, the unit combobox is completely blank because its not finding a match. I assume its because power apps is fixing all the trailing and extra spaces in the first combobox.

I did get it working with the following code for the second combobox as below:

Distinct(Filter('HR.NA_LOCATION', Trim(NA_LOC_ADDRESS1) = ComboBox8.Selected.Value && NA_LOC_STATUS = "A"),NA_LOC_UNIT_NAME & " (" & NA_LOC_CODE & ")")

However now because I'm trimming in the middle of filtering I of course got the annoying delegation warning which wont do since there is much more than 2k records to sift through.

Please help!

Categories:

General Questions