Filter ComboBox with Collection (for DefaultSelectedItems)

[iwonder]

Thank you for taking the time to read my question.

I have a ComboBox for which I want to set the DefaultSelectedItems. My understanding is that this value must be a recordset not a table or a list or a single text value. Therefore I think I have to use Filter() to generate the recordset. I have a collection that has all the values I want to use as the filter criteria but I can't seem to piece this together.

ComboBox.Items = '[dbo].[FacilitiesActive]'

ComboBox Field = ["Facility"]

Collection = EditUserFacilities; Field = UserFacility

Here is what I tried:

ForAll(EditUserFacilities.UserFacility,Filter('[dbo].[FacilitiesActive]',Facility = UserFacility)) but that doesn't work.

I have this but I get a delegation warning on the equals sign:

Filter('[dbo].[FacilitiesActive]',Facility in EditUserFacilities.UserFacility)

Any direction would be very appreciated.

Thanks

[RandyHayes]

@iwonder 

I just double checked to make sure I'm not going crazy (been there done that).

I have a combobox that is set to a datasource (no other formula) : PowerAppsSamples

The DefaultSelectedItems property is set to : 

   ForAll(colSomeRecords As recs, LookUp(PowerAppsSamples, ID=recs.ID))

I have a quick collection (colSomeRecords) that has 9 records in it.

End result is that I have a combobox with all of the items from PowerAppsSamples and 9 items selected.

What could be different?

[iwonder]

...very strange. I've triple checked and I'm the same way. I've even File - Close and then exited my browser then opened it in editor again and I'm only getting the first value in the collection, not the second.

I have only one field for the DisplayFields property ["Facility"]

IsSearchable = false

Items = '[dbo].[FacilitiesActive]'

MultiValueDelimiter = ","

SearchFields = ["Region","Facility"]

SelectMultiple = true

Collection = EditUserFacilities

UserFacility
Rosenort
Otterburne

When I preview I select the user from the gallery which fills the collection (I only have one user in the list to test) and takes me to the screen with the form. The ComboBox has the very first record selected (which is NOT Rosenort) BUT I have a Label above the ComboBox that shows what is selected in the ComboBox

Label.Text = Concat(cboFacility.SelectedItems,Facility,", ")

It shows only Rosenort.

When I expand the ComboBox, The first item is selected but when I scroll down neither Rosenort or Otterburne are selected.

Should I just go with the "in" formula?

[RandyHayes]

@iwonder 

Ah, so what you are seeing is the ghost select.  This is when you see something listed as selected, but you scroll down and find that same value and it is NOT selected.

This means the record did not match.

Here is what I would try:

Insert a blank screen and put a button on the screen and set the OnSelect action to:

Set(CompareSelected, JSON(cboFacility.SelectedItems, IndentFour))
Set(CompareItems, JSON(First('[dbo].[FacilitiesActive]'), IndentFour))

Put two Labels on the screen and set their Overflow property to Scroll 

Make them big and side-by-side then set the Text properties to CompareSelected and the other to CompareItems.

Now, Alt-Click on the Button and look at the JSON record structure of the two label results.

First we are checking to make sure that they match column by column (i.e. the schema).

If ALL of that looks good, then we need to consider the column type in your database.  If you are using CHAR(x) or any other "set" size, then your results will have spaces Padding the results.  This will impact the match.

SO, change the second line of the formula above in the OnSelect of the button to:

Set(CompareItems, JSON(Filter('[dbo].[FacilitiesActive]', Facility="Rosenort" || Facility="Otterburne"), IndentFour))

Alt-Click the button again and look at the selected JSON and the CompareItems JSON.  The records MUST match to be selected properly.  Check EVERYTHING in the JSON - number of columns, column names, values of the columns (including spaces).

Let's see if we can identify the cause.  You say the In operator works, so I am highly suspect of the spacing.

[iwonder]

The results are different... time for pictures 🙂

You have me thinking about the spacing... The way I store the Facilities the user has access to is a comma separated string of values. "Rosenort,Otterburne"

This is how I get the values into the collection EditUserFacilities

Gallery OnSelect:  Set(SelectedUser,ThisItem);ClearCollect(EditUserFacilities,ForAll(Split(SelectedUser.FacilityAccess,","),{UserFacility:Result}));Navigate(EditUserScreen,ScreenTransition.None)

Does Split cause trailing spaces?

Here is the result of the second formula:

Thanks again for you help....amazing. I really really appreciate it.

[RandyHayes]

@iwonder 

What I am concerned with is this:

In the first Compare Items we took the first record from FacilitiesActive and it shows two columns Facility and Region.

Yet in the second where we filtered the records for the two facilities, we got a record with only one column - Facility...why are we not seeing the Region in that?  It should be there since it is coming from the same FacilitiesActive datasource!!

Is FacilitiesActive a view or a table?

No problem on the help...I love a good mystery!  

I am about to wrap up for the day though, but please fire back anything you find and I will try to pop in later and over the weekend.

[iwonder]

Hi @RandyHayes ,

I hope you had a great weekend.

FacilitiesActive is a SQL View:

ALTER View [dbo].[FacilitiesActive] AS
SELECT *
FROM [MyDataBaseName].[dbo].[Facilities]
WHERE Active = 1
GO

[iwonder]

Hi @RandyHayes 

By way of update, I remade my screen from scratch and I'm still seeing the same error.

Thanks,