0

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?

13 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    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.

    • Fred
    • 2 yrs ago
    • Reported - view

    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.

      • Gianluca
      • 2 yrs ago
      • Reported - view

      Fred Here's a sample. 

      The selections table is to define every kind of possible job that a certain contract offers.

      The contracts table defines all the data of a contract and it's linked to People table.

      When i define a contract, I select a person and then define his job through Area, livello and Mansione.

      Since italian has different names for the same job depending if it's done by a man or a woman, i wanted to be able to get the correct Selection according to the gender of the hired person.

      Sample.ninox
      • Gianluca
      • 2 yrs ago
      • Reported - view

      Fred  forgot to mention that the field that needs to change according to person's gender is "Mansione"

    • Fred
    • 2 yrs ago
    • Reported - view

    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.

      • Gianluca
      • 2 yrs ago
      • Reported - view

      Fred I’ll try this and let you know! Thanks!

    • Ninox partner
    • RoSoft_Steven.1
    • 2 yrs ago
    • Reported - view

    Maybe this gives you idea's... :

    https://forum.ninox.com/t/g9hrzlf/filling-lines-of-ordersarticles-with-dynamic-multiple-choice-fields-and-cascading-dynamic-choice-fields

    Steven

      • Gianluca
      • 2 yrs ago
      • Reported - view

      RoSoft_Steven thank you sir!

      i saw this post while looking for a solution, but i can’t find  the database attached. 😅😅😅

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    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.

    Sample
      • Gianluca
      • 2 yrs ago
      • Reported - view

      Alain Fontaine Brilliant as usual! In my search to keep the workflows fast, I often forget how useful can be the hide/show options...even if sometimes it means to double the fields.

      • Alain_Fontaine
      • 2 yrs ago
      • Reported - view

      Gianluca This solution is only possible because there are very few - actually two - possibilities. While working with the choices, I noticed that gigantic switch statement triggered by "Mansione". I would propose to add a field "ObbligoAbilitazione" in the "Selections" table, and to replace the switch by:

      ObbligoAbilitazione := record(Selections,number(Mansione)).ObbligoAbilitazione

      This would make the database easier to maintain, since all the informations about a job are now in the same place. In passing, I also added some triggered code in "Area" and "Livello" to clear the downstream levels, in order to avoid inconsistent states. 

      Sample
      • Gianluca
      • 2 yrs ago
      • Reported - view

      Alain Fontaine thank you for this! Great suggestion to move ObbligoAbilitazione to the selections table.

      The selections table was something I added on the way, but it makes very much sense to put obbligoabilitazione there. I'll have a closer look at all of the triggers you set up to learn a bit more about the inconsistent states!

      • Gianluca
      • 2 yrs ago
      • Reported - view

      Alain Fontaine everything perfect! 

Login to reply

Content aside

Categories

View all topics
  • 2 yrs agoLast active
  • 13Replies
  • 319Views
  • 4 Following