In this article, you'll discover the different functions in formulas, how to set them up and how to use them in Tabsters.
Tabsters custom fields let you define calculation formulas to obtain the information you need.
For example, you can add two fields together to obtain the result of their addition in your custom field, transform values into percentages by dividing your value by 100, or transform data from euros to millions of euros by dividing by one million.
However, all these calculations are based on simple operators such as addition, subtraction, division and multiplication.
To give you even greater freedom in the creation of your formulas, Tabsters provides a number of functions to give you a wider choice of parameters for the formulas in your custom fields.
Description :
Parameterization: Select the MinOf() / MaxOf() formula from the list of available functions. Then position yourself inside the function's parentheses, and select the required field from the list of available fields.
Once the field has been selected, separate the field from the next with a semicolon, then add the next field.
Repeat the operation as many times as you wish to compare fields in your function.
Please note: Depending on the type of custom field created (Number or Date), remember to only fill in fields of the same type as the created field in your function, or your settings will not work.
Examples of settings :
Description :
Parameterization: Select the YearOf() / QuarterOf() / MonthOf() formula from the list of available functions. Then position yourself inside the function's parentheses, and select the date field whose year you want to retrieve from the list of available fields.
Examples of settings :
Description :
Parameterization: Select the formula NbDays() / NbOpenDays() from the list of available functions. Then position yourself inside the function's parentheses, and select the desired date field from the list of available fields.
Once the field has been selected, separate the field from the next with a semicolon, then add your second date field.
Examples of settings :
Description : The DateAdd function adds a given number of days/months/years to the day/month/year of an element's date field.
Setup: Select the DateAdd formula from the list of available functions. Position yourself inside the function's parentheses, and select the date field you wish to increment.
Once the field has been selected, separate it from the next field with a semicolon, then enter the date element you wish to increment between quotation marks &(e.g."Year" / "Month" / "Day")."Year" / "Month" / "Day").
Once the period has been entered, separate it from the next field with a semicolon, then enter the value with which you wish to increment the previously entered period.
Example of settings :
Description : The Today function is used to return the current date.
This function in itself has no particular use apart from returning the current date, but can be integrated into other functions for conditions or other checks.
Settings : - NA -
Example setting :
Description :
Setup: Select the ChildMin() / ChildMax() formula from the list of available functions. Then position yourself inside the function's parentheses, and enter the item type of the elements to be compared between quotation marks.
Once the item type has been entered, separate it from the next field with a semicolon, then select the field to be considered from the list of available fields.
Once the field has been selected, you can choose to validate your formula, or add optional parameters if you wish.
To add optional parameters :
Please note You can only set parameter n°4 if a value has also been entered for parameter n°3.
Examples of settings :
Description :
Parameterization: Select the ChildSum() / ChildAverage() formula from the list of available functions. Then position yourself inside the function's parentheses, and enter the item type of the elements to be compared between quotation marks.
Once the item type has been entered, separate it from the next field with a semicolon, then select the field to be considered from the list of available fields.
Once the field has been selected, you can choose to validate your formula, or add optional parameters if you wish.
To add optional parameters :
Please note You can only set parameter n°4 if a value has also been entered for parameter n°3.
Examples of settings :
Description :
Setup: Select the ChildCount() / ChildDistinctCount() formula from the list of available functions. Then position yourself inside the function's parentheses, and enter the item type of the elements to be compared between quotation marks.
Once the item type has been entered, separate it from the next field with a semicolon, then select the field to be considered from the list of available fields.
Once the field has been selected, you can choose to validate your formula, or add optional parameters if you wish.
To add optional parameters :
Please note You can only set parameter n°4 if a value has also been entered for parameter n°3.
Examples of settings :
Description :
Please note AND & OR functions can either be used on their own, or to specify conditions within other formulas
Paramétrage : Sélectionnez la formule AND() / OR() dans la liste des fonctions disponibles. Positionnez vous ensuite à l'intérieur des parenthèses de la fonction, et sélectionnez le champ sur lequel vous souhaitez appliquer une condition, dans la liste des champs disponibles.
Une fois le champ sélectionné, positionnez votre condition de vérification avec l'opérateurs de votre choix tel que égal à, supérieur/inférieur à, supérieur/inférieur ou égale à, différent de (Opérateurs : "=", ">", "<", ">=", "<=", "!=").
Une fois votre première condition renseignée, séparez la du champ suivant avec un point-virgule, puis sélectionnez le champ suivant pour la condition suivante sur le même principe que la première.
Une fois votre deuxième condition renseignée, vous pouvez choisir de valider votre formule, ou de rajouter d'autres conditions dans votre fonction.
Examples ofsettings :
Description :
The NOT function is used to check a condition, and sets the Boolean to true if all the specified conditions are not met.
Note The NOT function can either be used on its own, or to specify conditions within other formulas
Paramétrage : Sélectionnez la formule NOT() dans la liste des fonctions disponibles. Positionnez vous ensuite à l'intérieur des parenthèses de la fonction, et sélectionnez le champ sur lequel vous souhaitez appliquer une condition, dans la liste des champs disponibles.
Une fois le champ sélectionné, positionnez votre condition de vérification avec l'opérateur de votre choix, tel que égal à, supérieur/inférieur à, supérieur/inférieur ou égale à, différent de (Opérateurs : "=", ">", "<", ">=", "<=", "!=").
Examples ofsettings :
Description :
The IIF function checks a condition, and returns a desired value if the condition is true, and another value if the condition is false.
Please note The IIF function can either be used on its own, or to specify conditions within other formulas
Paramétrage : Sélectionnez la formule IIF() dans la liste des fonctions disponibles. Positionnez vous ensuite à l'intérieur des parenthèses de la fonction, et sélectionnez le champ sur lequel vous souhaitez appliquer une condition, dans la liste des champs disponibles.
Une fois le champ sélectionné, positionnez votre condition de vérification avec l'opérateur de votre choix, tel que égal à, supérieur/inférieur à, supérieur/inférieur ou égale à, différent de (Opérateurs : "=", ">", "<", ">=", "<=", "!=").
Une fois votre condition renseignée, renseignez un point-virgule pour la séparez de la valeur suivante, puis renseignez la valeur à renvoyer si la condition précédente est vraie (soit sous forme de valeur fixe, renseignez entre guillemets, soit sous forme de valeur variable, sous forme de champ sélectionné via les champs disponibles).
Une fois la valeur renseignée pour le cas où la condition est vraie, renseignez un point-virgule pour la séparez de la valeur suivante, puis renseignez la valeur à renvoyer si la condition précédente est fausse (soit sous forme de valeur fixe, renseignez entre guillemets, soit sous forme de valeur variable, sous forme de champ sélectionné via les champs disponibles).
Please note If you don't want to return anything in either case (true or false), you can simply enter an empty value by placing two quotation marks "".
Examples ofsettings :
Description :
Parameterization: Select the Decode() formula from the list of available functions. Then position yourself inside the function's parentheses, and select the desired field from the list of available fields.
Once the field has been selected, separate it from the following value with a semicolon, then enclose the desired field value in quotation marks for comparison.
Once you've done this, add a semicolon, then enclose in quotation marks the value to be displayed in the field if the comparison field has the indicated value.
Repeat the operation as many times as you wish to compare field values in your function.
Examples of settings :
Description :
Parameterization: Select the GetJoinedValue() formula from the list of available functions. Then position yourself inside the function's parentheses, and enter, between quotation marks, the item ID of the element whose data you wish to retrieve.
Once the key has been entered, separate it from the next value with a semicolon, then select the field whose data you wish to retrieve from the list of available fields.
Examples of settings :
Description :
Parameterization: Select the Concat() formula from the list of available functions. Then position yourself inside the function's parentheses, and select the field whose value you wish to retrieve, from the list of available fields.
Once the field has been selected, separate it from the next with a semicolon, then enter the separator you wish to apply between your two fields, between quotation marks.
Once the separator has been added, enter a semicolon and select your second field from the list of available fields.
Repeat the operation as many times as you wish to concatenate fields in your function.
Examples of settings :
Description :
Parameterization: Select the Left() / Right () formula from the list of available functions. Then position yourself inside the function's parentheses, and select the field on which you wish to apply a condition, from the list of available fields.
Once the field has been selected, separate the field from the next with a semicolon, then enter the number of characters from which you wish to take into account the content of the selected field
Examples of settings :
