Did you know that... [field types]

Document created by Cécile on Jun 8, 2018Last modified by Cécile on Jun 9, 2018
Version 4Show Document
  • View in full screen mode

I find it an insult to my intelligence when technical documentation lists the various options that I can see on a dialog panel and say the obvious :

"The dialog Method of replication allows you to calculate each 2018-06-07_15-46-01.pngof the replication method ratio. Click the option 'use deoxyribonucleic acid coefficient' to use the deoxyribonucleic acid coefficient."

Instead of what? Or in conjuction? What does that do. Why would I want to use that coefficient. How does that alter my results??? If i click there will all my data disappear or be converted in alien speech???






When you create a field, you can choose from these types (left). The first six types allow you to index their content. That means that they will be searchable and that their value will not update unless triggered to do so by a script and only if the option "Do not replace existing value of field (if any) is not checked.




Often developers will tell you that you should use an unstored calculation. It can be confusing because each field type allows to do calculation (see the Calculated value Specify button in the autoenter panel of the options dialogue from the fields definition Tab of the Manage Database Window).


What they are referring to is not the autoenter "Calculated value" option. They refer to the seventh type of field "Calculation". These fields are calculated (and updated) on the fly.


Why and when then, use the autoenter Calculation option of an indexed field instead of a Calculation type field, and vice-versa?


Depends of the use of the field. If it should be set once then left alone, which is the case for calculating someone's birth year for example, in a person's profile, you would set the field "birth year", select year type and in options, you would select Calculated value and enter the following formula


Year ( Date ( Month ( Get ( CurrentDate ) ) - PeopleTable::AgeField ; Day ( Get ( CurrentDate ) ) ; Year ( Get ( CurrentDate ) ) ) )


You will notice in the special Calculation window that pops up, in the bottom left, the Option "Calculated result must be". You must select "date" because the Year is a date.

Thereafter, when creating a new record, the field will automatically get populated with the birth year, provided that there is an age value specified in the field AgeField. If there wasn't, and it is added later, you will have to trigger an update by script for the field to calculate the value again.


If, instead, you want to know the current age of the person, you would not use the type number, and calculate it in autoenter option because at every birthday, the following years, that figure would be inaccurate or require a programmed update. In this case, it is better to use the field type "calculation" for the field and the following formula would keep the age current because everytime you would access the record, the calculation would update the value in the field.


Year ( Get ( CurrentDate ) ) - Year ( Birthdate ) - ( Get ( CurrentDate ) < Date ( Month ( Birthdate ) ; Day ( Birthdate ) ; Year ( Get ( CurrentDate ) ) ) )


For the option "Calculated result must be:" select number (cuz the age is a number not a date).


Calculations, for text?!

It may be as simple as   If (self = "" ; ""; self) which helps prevent errors due to fields being empty when a script or calculation need a value to work properly. It can set the content of the field based on some other fields' content, for example the concatenation of other field's values. It can set the content of the field based on whether certain conditions are met, for example, a "case" statement which fills the field with some text if some criteria is met or some other text for another value for that criteria.


Or it can be a great way to format text. Users are notorious for being creative with their entry method. Take phone numbers. When you have a field dedicated to phone number, you should select the type Text instead of number. Unless you are working with scientifc algorithms or in numerology, you are not likely to ever use a phone number for its number value. However, you will want to format it in a consistant fashion, regardless of what is your user's flavour of the day. And that is where calculation on indexed fields can be super interesting.


Usually, you will check the box "do not replace existing value, if any" because you do not want the calcuation to preempt user input (e.g. you have set a default boolean value of 1 but want the user to be able to uncheck the box (make it 0)).


However, in our current phone number example, we WANT to replace the user input value. With the user input value, but formatted.


If, for your phone number text field type you select the autoenter option "Calculated value" and put the following calculation:



nx = Lower ( Bureau );

poste = Case ( PatternCount ( nx; "x" ) > 0;

        " poste " & Middle ( nx ; Position ( nx; "x"; 1; 1 )+1 ; Length ( nx ) );


n = Filter(nx;"0123456789");

phone = "(" & Left(n;3) & ") " & Middle(n;4;3) & "-" & Middle(n;7;4)


phone & poste)


Your phone number will look like (555) 555-1212 or (555) 555-1234 poste 4.


In my situation, I chose to limit the users to enter the numbers in this format for simplicity 1234567890x1234 but it is possible with a bit more complex formula to accept any gibberish and turn it into something decent or refuse the data entered.


For this to work, you MUST uncheck the "do not replace existing value" so that when the user exits the field, the calculation replaces the content that was input (in this case with the content that was input but in its formatted state).


In a future post, I will explain how to trigger an update of a stored calculation (the fields types that can be indexed such as text, date, number, etc.) or to format old data to a new standard.



There are still options in these dialogs that I am not familiar with and don't know how and when to use them. I will update in time.


À plus!

2 people found this helpful