ComboBox - Alternative way to set them up

[KroonOfficeSol]

This Blog is about the Combo Box and an alternative way to set them up. We will build or solution from inside to outside and I will guide you step by step through the process.

Step 1 - Filter()
The first step is to use the Filter() formula to be able to filter out data source. Here we use a trick which we use a lot in T-SQL to get a first true condition. The formula is like this:

Filter(_Persons
	, 1 = 1 // This part is always true
)

The 1 = 1 is equal to true, which helps us to make a wrapper formula without declaring existing conditions and makes it easy in the future to preform test (for example comment out parts of the filter conditions)

Step 2 - AddColumns()
For the second part of our wrapper we take a closer look at the options of the ComboBox. As a option we can pick Single, Double and Person ComboBox, we will build our wrapper on the option with the most features but in a way that it is suitable for all the options. We will use AddColumns() formula to do so. Nice about the AddColumns() formula is that it makes it possible to manipulate the display and output values of our ComboBox, for example we can do a concatenation on the FirstName and SurName columns from the persons in our wrapper function. The formula become like this:

AddColumns(
	Filter(_Persons
		, 1 = 1
	)
	,"Display1"
	,FirstName & " " & SurName
	,"Display2"
	,City
	,"Img"
	,Picture
	,"Output"
	,ID
	,"Output1"
	,DateOfBirth
)

Know that the AddColumns() formula does create a virtual table, so our original data source is not changed.

Step 3 - SortByColumns()
In this step, we will sort our dataset with the SortByColumns() formula. The formula will be something like this:

SortByColumns(
	AddColumns(
		Filter(_Persons
			, 1 = 1
		)
		,"Display1"
		,FirstName & " " & SurName
		,"Display2"
		,City
		,"Img"
		,Picture
		,"Output"
		,ID
		,"Output1"
		,DateOfBirth
	)
	,"Display1"
	,Ascending
)

Nine out of then times the display1 will be our sort column, but we can also pick all the other fields of our data source.

Step 4 ShowColumns()
We want to set the columns we output with the ShowColumns() formula. This makes it easier later to find the column we want to use in our formulas. The final function becomes:

ShowColumns(
	SortByColumns(
		AddColumns(
			Filter(_Persons
				, 1 = 1
			)
			,"Display1"
			,FirstName & " " & SurName
			,"Display2"
			,City
			,"Img"
			,Picture
			,"Output"
			,ID
			,"Output1"
			,DateOfBirth
		)
		,"Display1"
		,Ascending
	)
	,"Display1"
	,"Display2"
	,"Img"
	,"Output"
	,"Output1"
)

Step 5 – DefaultSelectedItems

To show the DefaultSelectedItems we surround the Step 4 formula with a Filter() formula. So Like:

Filter(

// Items

Step 4 - Formula

// End Items

,Output = _SelectedRecord.ID)

Here just copy the .Items formula and past it between // Items and // End Items.
 
It would really help here if we could reference the combobox.Items instead of having to copy-paste the combobox.Items formula. I posted an Idea on this, maybe you could vote and support this idea. Link: 
DefaultSelectedItems - easier and better approach for setting defaultselecteditems off ComboBox

I find that using this wrapper in my apps makes it easier to set up my ComboBoxes and it always works as I expect.

I hope this Blog helps you improving your apps.

Paul