Dynamic Choice Field reference in a function
I want to make a function out of a formula I often use in my Database.
The function involves the following fields:
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.
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?
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:
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.
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?