Setting DefaultSelectedItems in a hard-coded ComboBox

I WAS PUTTING TOGETHER A SIMPLE FORM for our company's branch offices to report on what Business services they have in place. Over the years, I've learned ways to keep forms as simple as possible, and the degree of simplicity will inform whether I can use a Customised SharePoint form or have to go full Canvas App. In this case it was just eight fields, so a customised SharePoint formset should do the job.

The next step on my simplification journey is, wherever possible in my datasource List, use Single line of text fields, as Lookup and Choice fields complicate things unnecessarily. I can let PowerApps DropDowns and ComboBoxes do the heavy lifting.

For simple forms, the embedded SharePoint "Customise forms" method will work just fine.

So, the first two fields are Country and Office. In most European countries, we just have the one office, but in a couple we have three or more offices. So a simple cascade was the way to go.

Pretty sure I've outlined this elsewhere in the blog, but here's the short version. Set up a separate SharePoint List (Locations) with a column for Country and a column for Office. I don't like to mess with the Title column, so I keep that as is, but set the default value to "Edit this item", so it automatically populates if I add any further locations.

I prefer to give my lookup columns headers that make sense, so if I have to make changes later, I don't waste time wondering what function the "Title" column feeds.

Like I say, the main SharePoint List uses Single Line of text where possible, so these first two fields are populated using DropDowns that cascade. The set up for the Country DropDown looks like this:

ddCountry.Items = Distinct(Locations,Country)

Country_DataCard1.Update = ddCountry.SelectedText.Value

Country_DataCard1.Default = ThisItem.Country

And the Office DropDown is configured this way:

ddOffice.Items = Distinct(Filter(Locations,Country = ddCountry.SelectedText.Value),Office)

Office_DataCard1.Update = ddOffice.SelectedText.Value

Office_DataCard1.Default = ThisItem.Office

If you set it up this way, then the Default for both DropDowns can be:

Parent.Default

All pretty easy so far, but the next bit was a challenge. I didn't really want to set up another SharePoint List with just a single column for the Services used by each office, and tacking it on as a third column in the Locations list would have meant ballooning it up from 15 rows to 135 rows ... which didn't seem sensible. I'm not keen on Choice columns with more than four or five items, and they can be a bit of a pain in PowerApps forms, so I thought I'd try a slightly different approach and see how it worked out. I was going to hard-code the list of services into a ComboBox.

So, the set up of the Service ComboBox, looks like this (number of Items shortened for the sake of brevity):

cbService.Items = ["Reception","Switchboard","Catering" ...]

Service_DataCard1.Update = cbService.Selected.Value

Service_DataCard1.Default = ThisItem.Service

The tricky part for me was how to set the "Default" of the ComboBox for when the Form was in Edit mode.

If I'd been using a SharePoint Lookup list I could just set the ComboBox DefaultSelectedItems to:

cbService.DefaultSelectedItems = ThisItem.Service

But with a hard-coded datasource, that doesn't work. So off I trekked to Google. Incredibly, I couldn't find a definitive answer ... but after a couple of hours of searching, I was able to piece together from various sources the principle of how this should work. Essentially, it's this:

Filter([Datasource], Value = ThisItem.Service)

So what I had to put in was this:

cbService.DefaultSelectedItems = Filter(["Reception","Switchboard","Catering" ...], Value = ThisItem.Service)

Now when I edit an item, the stored value is displayed in the Service ComboBox.

I hope that helps someone.