Cascading Selection Fields with a twist
Hi beautiful people,
I've built a cascading selection the wait it is done in this tutorial https://www.youtube.com/watch?v=yWn--YQsoT4
All works perfectly, but I wanted to complicate it a little bit so I put a condition in the 'dynamic value name' in order to use a different field of the 'Selections' table depending on the content of the 'Gender' choice field in 'People' table:
if People.Gender is 1 I want the dynamic value name to be Selection1, if People.Gender is 2 I want the dynamic value name to be Selection2.
The formula I have in the 'dynamic value name' is the following:
let x :=(select People).Gender; if x = 1 then Selection1 else if x = 2 then Selection2 else null end end
All seems to work except that once I write the condition I only get the number in return and not the text. And I'm not lucky also if I use this formula
let x :=(select People).Gender; if x = 1 then text(Selection1) else if x = 2 then text(Selection2) else null end end
If what I want to do it's possibile, any idea of how it can be done?
Just to be sure I'm understanding the question. You have a dynamic choice field that is pointing to the Selections table.
In the dynamic value name part you want to condition it to show a field from Selections depending on the value of the choice field Gender from the People table.
I'm guessing that there is more than 1 record in the People table. So the select statement will return an array of numbers from the Gender field.
I am guessing you are NOT creating this dynamic choice field in the Selections table.
I'm also guessing that there is some relationship between this unknown table and People that causes you to want to change the value shown in the dynamic choice field. You will have use that relationship to modify your select so you can properly choose the field you want.
In my testing if I write (where I'm in Table2 and I create a Dynamic Multi-Choice field to Table1. Then I change the value name formula where Choice is a simple choice field in Table4):
let x := (select Table4).Choice; if x = 1 then Appointment2 else Appointment3 end
I always get the else because there is no data with a simple value of 1.
If I change it to:
let x := first((select Table4).Choice); if x = 1 then Appointment2 else Appointment3 end
Then everything is always true, because the Choice field from the first record from Table4 is 1.
If I change it to:
let t := this; let x := first((select Table4)[Number = t.Num2].Choice); if x = 1 then Appointment2 else Appointment3 end
Now it will show either Appointment2 or Appointment3 depending on the value of Num2 from Table1.
Well that is tricky. Once you are in the Dynamic choice filed you lose all ability to track the current record you are on in the Contracts table (something that many have asked for). Maybe someone has figured out a way to do this?
One way I can think of that could solve this would be to:
1) in the Selections table create a record for each change of job name and put a new field that tracks M or F or whatever you want. To make things easier probably just numbers so you don't have to do anything extra when comparing. So no more selection 1 or 2, just selection.
2) then in your dynamic choice field you can only select the properly gendered name, something like:
let t := this; let xArea := number(Area); let xLiv := number(Livello); (select Selections)[Category = 3 and Area = xArea and Level = xLiv and gender = t.Dipendente.Gender]
Then your dynamic value name would just be selection and it would only show the proper ones since you only show the M or F ones based on the record chosen in the Dipendente field.
Yet another problem that would be easy to solve if one could access the fields of the host record from inside a construct that changes the context.
In this case, one could:
- define a second dynamic choice field;
- display "Selection1" in one, and "Selection2" in the other;
- show either one, depending on the value of "Dipendente.Gender";
- in the "Trigger after update" of each one, set the other one to the same value.