0

Dynamic Choice Field reference in a function

Hi all,

I want to make a function out of a formula I often use in my Database.

The function involves the following fields:

FieldA: text

FieldB: text

FieldC: date

FieldD: choice

FieldE: dynamic choice

so it's syntax is like this

function CF (FieldA: text, FieldB: text, FieldC: date, FieldD: number, FieldE: ???)

I can't find a way to reference the correct field type for FieldE and it's the only one that keeps giving me an error, no matter what field type I set for it. Any idea on how it can be done?

As additional information, FieldE is populated with one value taken a list of roughly 9000 values from a text field in a static table not related to any others in the database.

Thank you!

19 replies

null
    • Lars
    • 1 yr ago
    • Reported - view

    Hi Gianluca,

    I'm not sure if you can pass a dynamic choice as a function parameter, because dynamic choices refer to other tables, so in the best case, the type would be a record of an unknown table type. 

    I propose to not pass FieldE, but the selection of FieldE (i.e. the id of the referenced object) and that is of type number. Example:

    function CF(... FieldE: number) do
        let tempFieldEElement := record(<type of selection), FieldE);
    end
    
    ...
    
    CF(..., number(FieldE))
    
    ...
    

    You understand what I mean?

      • Gianluca
      • 1 yr ago
      • Reported - view

      Lars hi and thank you for the explanation about why dchoice can’t be passed in a function.

      i don’t think I completely get your code though…

      could you please explain to me what the code you wrote is supposed to do? Especially the last part where the function is recalled 😃

      • Lars
      • 1 yr ago
      • Reported - view

      Gianluca the main message is, that you (in my opinion) can't pass the dynamic choice field. That means, you have to pass the selection of the dynamic choice. And the selection of the dynamic choice is a number ... it's the id of the selected record. You can get the id of the selected record with 

      idOfSelectedElement := number(FieldE)

      Inside the function, you have to get the corresponding record to get any data, because you only pass the id and not the real record. I'm not sure if the ids are numbered globally unique, but that doesn't matter. To get the record you have to use the record command. And record needs to know which table is used (<table of selection>)

      selectedRecord := record(<table of selection>, idOfSelectedElement);
      ... do stuff with selectedRecord, which is actually the object you need ...
      

      If not clear, please tell me

      • Gianluca
      • 1 yr ago
      • Reported - view

      Lars thank you for your patience, very much appreciated! I'm a beginner with ninox and so many things are different from Filemaker, so sometimes (often) i get lost.

      Now...I did as you suggested, but I still miss a piece to make things work and I think the problem is about the instructions order in the function, which is like this

      function CF (FieldA: text, FieldB: text, FieldC: date, FieldD: number, FieldE: number) do
          let ID := number(FieldE)
          let selectedrecord := record(tableofselection, ID);
          CompleteFormula *the actual formula that uses FieldE as data*;
      end

      I get no errors from Ninox, but the function doesn't return any result when used (while the "completeformula" only did.

      Probably I'm either making a mess out of your instructions or messing up the structure of the function.

      • Lars
      • 1 yr ago
      • Reported - view

      Gianluca it's a bit difficult if I don't know have enough knowledge about your structure and the names of your tables, but I try.

      First of all, you call the function already with the result of the dynamic choice selection. So in the main script, where you call CF, it should look something like this:

      ... stuff being done ...
      ... I don't know if CF returns any value, but let's assume it does ...
      ... and let's assume, you already have all parameters to pass ...
      
      result := CF(first, second, third, fourth, number(nameOfYourDynamicSelection));
      
      ... and the rest ...

      And "nameOfYourDynamicSelection" is the name of the field that contains the dynamic selection. Now, the id of the selected record is passed to the function CF.

      Inside the function it should now look like this (and I assume, that the dynamic selection is pointing to a table named "tableofselection")

      function CF (FieldA: text, FieldB: text, FieldC: date, FieldD: number, FieldE: number) do
          let selectedrecord := record(tableofselection, FieldE);
          CompleteFormula *the actual formula that uses FieldA to FieldD and selectedrecord*;
      end
      

      --> You pass the id of the selected record to the function CF. Inside the function, you get the record that contains the data and only then, you can use the content.

      Actually, there's a second way to solve the issue (there's always a second way) - maybe it's simpler. You extract the necessary data before calling the function CF. What type of data do you exactly need from the dynamic selection? Is it a number, a string, ...? Then you just define FieldE with that data type. And before calling CF, you extract that data from the requested record. 

      If you're still unsure, you have to tell me, what kind of table is used for the dynamic choice and which field you like to use.

      • Gianluca
      • 1 yr ago
      • Reported - view

      Lars your patience with me earned you a few drinks, if there will ever be a chance 😄

      The second way is definitely easier and works perfectly, but it forces me to create an additional  field and if I could I would avoid it.

      About the first way: I have attached here a sample database  with the two main tables involved. In "People" table there's a formula field named "CF" which contains the script that I would like to turn into a global function to use it all over the database.

      The dynamic choice field is the one named "BirthPlace" and it gets its values from the field named "Comune" in the table named "Comuni"

      What I seem not to be able to understand is how to pass that value into the function. It is clear to me that I have to extract the ID of that record and then use it to identify the value that I actually need, but I'm too dumb to put things in a row and make it work as it should :9

      • Lars
      • 1 yr ago
      • Reported - view

      Gianluca It's all fine 🙂

      However, you're one step ahead of me, when it comes to exporting ... I can't export the database I modified - don't know how ... So I'll copy everything into this answer.

      You say the second way works, but forces you to create an extra field. I don't have an extra field, just a temporary variable for the search result. Here's the global function:

      function CF(LastName : text,FirstName : text,Sex : number,BirthDate : date,CodiceComune : text) do
          if LastName = null or FirstName = null or Sex = null or CodiceComune = null or BirthDate = null then
              ""
          else
              let xPCog := replace(upper(LastName), " ", "");
              let xConCog := replacex(xPCog, "[AEIOU]", "g", "");
              let xVocCog := replacex(xPCog, "[BCDFGHJKLMNPQRSTVWXYZ]", "g", "");
              let xCog := substr(xConCog + xVocCog + "XXX", 0, 3);
              let xPNom := replace(upper(FirstName), " ", "");
              let xConNom := replacex(xPNom, "[AEIOU]", "g", "");
              let xNom := if length(xConNom) > 3 then
                      extractx(xConNom, "^(.).(..)", "$1$2")
                  else
                      let xVocNom := replacex(xPNom, "[BCDFGHJKLMNPQRSTVWXYZ]", "g", "");
                      substr(xConNom + xVocNom + "XXX", 0, 3)
                  end;
              let cfparz := xCog + xNom + substring(text(year(BirthDate)), 2, 4) + item("ABCDEHLMPRST", month(BirthDate) - 1) + switch Sex do
                  case 1:
                      format(day(BirthDate), "00")
                  case 2:
                      text(day(BirthDate) + 40)
                  default:
                      ""
                  end + CodiceComune;
              let xCarList := "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
              let xParFact := [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9];
              let xDisFact := [1, 0, 5, 7, 9, 13, 15, 17, 19, 21, 2, 4, 18, 20, 11, 3, 6, 8, 12, 14, 16, 10, 22, 25, 24, 23, 1, 0, 5, 7, 9, 13, 15, 17, 19, 21];
              let sumPD := sum(for c in replacex(cfparz, ".(.)?", "g", "$1") do
                          item(xParFact, index(xCarList, c))
                      end) + sum(for c in replacex(cfparz, "(.).?", "g", "$1") do
                          item(xDisFact, index(xCarList, c))
                      end);
              let finalC := item("ABCDEFGHIJKLMNOPQRSTUVWXYZ", sumPD % 26);
              cfparz + finalC
          end
      end
      

      And here's the code for the CF field

      let xComune := text(BirthPlace);
      CF(LastName, FirstName, number(Sex), BirthDate, first(select Comuni where Comune = xComune).CodiceComune)
      

       

      Now, if you directly want to enter the BirthPlace into the CF call, that would like like this (change in the parameter list and the Comune-part):

      function CF(LastName : text,FirstName : text,Sex : number,BirthDate : date,BirthPlace : text) do
          if LastName = null or FirstName = null or Sex = null or BirthPlace = null or BirthDate = null then
              ""
          else
              let xPCog := replace(upper(LastName), " ", "");
              let xConCog := replacex(xPCog, "[AEIOU]", "g", "");
              let xVocCog := replacex(xPCog, "[BCDFGHJKLMNPQRSTVWXYZ]", "g", "");
              let xCog := substr(xConCog + xVocCog + "XXX", 0, 3);
              let xPNom := replace(upper(FirstName), " ", "");
              let xConNom := replacex(xPNom, "[AEIOU]", "g", "");
              let xNom := if length(xConNom) > 3 then
                      extractx(xConNom, "^(.).(..)", "$1$2")
                  else
                      let xVocNom := replacex(xPNom, "[BCDFGHJKLMNPQRSTVWXYZ]", "g", "");
                      substr(xConNom + xVocNom + "XXX", 0, 3)
                  end;
              let cfparz := xCog + xNom + substring(text(year(BirthDate)), 2, 4) + item("ABCDEHLMPRST", month(BirthDate) - 1) + switch Sex do
                  case 1:
                      format(day(BirthDate), "00")
                  case 2:
                      text(day(BirthDate) + 40)
                  default:
                      ""
                  end + (
                      let xComune := text(BirthPlace);
                      first(select Comuni where Comune = xComune).CodiceComune
                  );
              let xCarList := "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
              let xParFact := [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9];
              let xDisFact := [1, 0, 5, 7, 9, 13, 15, 17, 19, 21, 2, 4, 18, 20, 11, 3, 6, 8, 12, 14, 16, 10, 22, 25, 24, 23, 1, 0, 5, 7, 9, 13, 15, 17, 19, 21];
              let sumPD := sum(for c in replacex(cfparz, ".(.)?", "g", "$1") do
                          item(xParFact, index(xCarList, c))
                      end) + sum(for c in replacex(cfparz, "(.).?", "g", "$1") do
                          item(xDisFact, index(xCarList, c))
                      end);
              let finalC := item("ABCDEFGHIJKLMNOPQRSTUVWXYZ", sumPD % 26);
              cfparz + finalC
          end
      end
      

      And the function call looks like this:

      CF(LastName, FirstName, number(Sex), BirthDate, text(BirthPlace))
      

      I tried it and it works:

      I think the second way (in this reply) is nicer, because the function call is clean

      • Gianluca
      • 1 yr ago
      • Reported - view

      Lars Yes! It's perfect! And I agree: the second way (here) it's nicer.

      Btw: to export the database I simply used "Save the archive as" from My Database list in my Mac App.

      Are you using Mac App or Web App? maybe this is why it didn't work for you.

      Thank you so much for the help!

      • Gianluca
      • 1 yr ago
      • Reported - view

      Lars oh...and one last thing just for the love of learning new things.

      Basically, it seems that the only difference with the original script is that "first"  in the comune part.

      How comes this one change allows BirthPlace to be used as a text parameter in the function?

      • Lars
      • 1 yr ago
      • Reported - view

      Gianluca "first" changes a lot. The select command always returns an array of records. In your case, this array always has only one single record. But it still remains an array. By using first (and that's safe, because your array only has one element), you get the record and from that you can get the string (i.e. the field). Otherwise it wouldn't be a single string.

      Great that it works.

    • Alain_Fontaine
    • 1 yr ago
    • Reported - view

    Everything Lars said about "select" always returning an array is true. But did you notice that your original formula did work without specifying "first(…)"? Sometimes, Ninox tries to be helpful by "automagically" extracting the only element of an array when needed. In the case of your original function, Ninox knew that a string is needed to be added to string beeing build, and it took the initiative to extract that string from the array. But one cannot count on this to happen consistently, so it is certainly safer to always specify an explicit extraction.

    Now, why use a "select" statement when not really needed? At several places in the discussion, the "record" function was mentioned. You can indeed get the desired result with the expression:

    record(Comuni,number(ComuneNacita)).CodiceComune
    

    This expression is certainly less resource intensive than a "select", and it does natively return a single element.

    And a last, philosophical consideration: when defining a function, it is preferable to let the result of the function depend on the arguments only, and not on some other information that the function must gather from some other place. In this respect, the style:

    function CF(LastName : text,FirstName : text,Sex : number,BirthDate : date,CodiceComune : text)
    

    is, IMO preferable. You may call it with:

    CF(LastName, FirstName, number(Sex), BirthDate, record(Comuni,number(ComuneNacita)).CodiceComune)
    

    And, laster than last 8-): if a N:1 reference to the "Comuni" table was used instead of a Choice (dynamic) field, the value of the code would be simply: ComuneNascita.CodiceComune, which is a clean as one can ask for.

      • Gianluca
      • 1 yr ago
      • Reported - view

      Alain Fontaine thank you for all these wise suggestions.

      About the "laster than last" 😂: as I said, I moved to Ninox from Filemaker 18, which I used to build fully working database for a few of activities I take care of (one is a private school finances management system, one is a system for medical surveillance of workers and another one is a system with personal data that allows me to produce precompiled contracts, privacy agreements and other annoying papers with lots of data that change all the time ).

      I felt the need the build my own database systems because usually the ready solutions one can buy never fully meet the needs OR are good for big companies with lots of needs, but make easy task complicated and slow when used in smaller companies.

      One of the features I think was great in Filemaker was the ability to build solutions where mouse clicks were almost not needed while populating the database with data: I hate to move hands from the keyboard to the mouse every 5 second to add new informations.

      At the moment, the best way I've found in Ninox to "minimize" the use of mouse is to use dynamic choice fields where otherwise I should click to open a related table and choose a value from the related table.

      I'd be happy to change my approach, if others allow me to keep my hands on the keyboard as long as possible: any suggestion in that direction will be more than very welcome 😊

      • Gianluca
      • 1 yr ago
      • Reported - view

      Lars  and Alain Fontaine

      You both made me curious about the different approaches with 

      record(Comuni, number(Birthplace)).CodiceComune

      instead of 

      let xComune := text(BirthPlace);
      first(select Comuni where Comune = xComune).CodiceComune);

      I thought that If the ID of BirthPlace is the same as the choice# everything would work fine, but I expected formulas to give different results if choice# and ID were different. So I tried using both formulas in two different fields and...the result is the same. What am I missing here? 

      • Fred
      • 1 yr ago
      • Reported - view

      Gianluca You should add that request to be able to use a keyboard to access a reference field to the Christmas Wish List post. I too wish that was possible.

      I would consider Dynamic choice fields sort of a Ninox "soft link" between records. While you can follow the path it is not very direct as a reference field. So it depends if you need the link or not.

      • Gianluca
      • 1 yr ago
      • Reported - view

      Fred yes… i would if I knew how to explain it the way you did 😂😂😂

       can I copy and paste your explanation? 

      • Fred
      • 1 yr ago
      • Reported - view

      Gianluca I think your reasoning for keyboard access to reference field is better.

      • Gianluca
      • 1 yr ago
      • Reported - view

      Fred ok… I’ll take your word on it and add that part to the Christmas list 😃

    • Alain_Fontaine
    • 1 yr ago
    • Reported - view

    For a dynamic choice field, the number returned is always the reference number (aka "Id") of the record in the "source" table providing the chosen item. It is not the row number of the choice as shown in the field. For example, if you define four choices in the source table, then delete the second one, you will have three rows in the choice field, but the number returned will be 1, 3 or 4. Which means that the "record()" function is the best choice to access the corresponding record. Does this make sense?

      • Gianluca
      • 1 yr ago
      • Reported - view

      Alain Fontaine clear as crystal water. Thank you!

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 19Replies
  • 721Views
  • 4 Following