Dynamic Choice Field

David Gyenes, Director of IT

One of my favorite topics is Dynamic choice fields. These are really great features to display value based on different conditions. How do we do that? Let’s dig in…
So normally we use the choice field to display records from another table. So we create a Dynamic Choice Field (DCF) or a Dynamic Multi Choice Field (DMCF). Then we define the Dynamic Value selecting a Table (ex: select Clients). Then we select the field we would like to display in the selection. This is the simple set up. But this is not all we can do.
On the table where we create the DCF/DMCF we can have some fields that acts as condition for the records being displayed in the DCF/DMCF. A simple example, let’s say or DCF displays our clients. On the clients table we have a field to determine gender. If I want to display only women in the DCF, I can set up a field next to it. This field will be a Yes/No switch. If it’s turned on, then I want to see only women, otherwise I want to see all clients. Into the Dynamic Value property I can write this code:
				
					if ‘Yes / No’ then
	(select Clients)[Sex = 1]
else
	select Clients
end
This way I would get different values in the field. Or I could have a different order for my results:
if ‘Yes / No’ then
	select Clients order by ‘Last Name’
else
	select Clients order by ‘First Name’
end
				
			

With a little trick actually we can change the value being displayed in the DCF/DMCF as well. We can set a switch (Yes/No field – can be hidden) on the Clients table as well. When we flip the Yes/No switch on our main table that would set all record value as well to the same. Now we can build the condition what field to display. In the Dynamic Value Name we can write a code. As example we will display First name if it’s on else display the last name:

				
					if ‘Yes / No’ then 
	‘First Name’ 
else 
	‘Last Name’
end
				
			

So now we know how to filter records, organize them by different order or show different values. Of course, we can combine these 3 example codes above.
Setting values for DCF/DMCF:

Now we are able to set values by code as well to these fields. For DMCF we can either use the
‘Multiple choice (dynamic)’ := select Clients
(this would select all) or we can use filters on this as well
‘Multiple choice (dynamic)’ := select Clients[‘Yes / No’ = 1]
Also we can just simply assign an array of IDs to it
‘Multiple choice (dynamic)’ := [1,2,3]
For DCF it would be easier by just assigning a simple record
‘Choice (dynamic)’ := first(select Client)
Or assigning a number value
‘Choice (dynamic)’ := 1
With these features you can make your database very versatile.

en_USEN