Throughout the Configuration Portal manual, you had the chance to see different alchemy script expressions in examples and explanations. 

Alchemy Script Expressions can be used throughout record templates:

• Formula Expression: Calculates the value of the field based on the formula expression. The result must be of the same type as the field for which you write the expression.
• Visibility Expression: Calculates the visibility of the field or group based on the visibility expression. The result must be a boolean.
• Filter Criteria: Filters down the result set for the fields or groups based on the filter expression. Must use the Result keyword when writing filter expressions. See examples here.
• Predefined Values Expression: Defines the conditions for when to show/hide some of the predefined values of a text or number field. See examples here under Conditional Predefined Values.
• Action Expression for fields: Calls functions, calculates the value of fields, or updates fields parameters based on the action expression. See examples here under Actions - On Change.
• Action Expression for buttons: Calls functions, calculates the value of fields, or updates fields parameters based on the action expression. See examples here.
• Privileges Expressions: Defines the set of privileges for a list of users. The result must be a list of users.

Alchemy Script Expressions can be used throughout process templates:

• Privileges Expressions: Defines the set of privileges for a list of users at the process and stage level. The result must be a list of users. See examples here and here.
• When expression: Defines the condition when a certain privilege set will be active for stage privileges. The result must be a boolean.
• Record Naming Pattern: Defines the record name on a stage based on the naming expression. The result must be a string. See an example here.
• Minimum and Maximum Records: Defines the minimum and the maximum number of records created on the stage based on the expression. The result must be a number. See examples here.
• When expression: Defines the condition for stage Actions when a certain action will be performed. The result must be a boolean.
• Assign To expression: Defines the list of users to be assigned to the task for a stage Create Task action. The result must be a list of users.
• Task Subject expression: Defines the subject of the task for a stage Create Task action. The result must be a string.
• Task Description expression: Defines the description of the task for a stage Create Task action. The result must be a string.
• Number of Records expression: Defines the number of records to create on the stage Create Record action. The result must be a number.
• Notification Recipients expression: Defines the list of users to be sent the notification on a stage Create Notification action. The result must be a list of users.
• Notification Subject expression: Defines the subject of the notification on a stage Create Notification action. The result must be a string.
• Notification Description expression: Defines the description of the notification on a stage Create Notification action. The result must be a string.
• Recipients expression: Defines the list of users to receive the email on a stage Send Email action. The result must be a list of users.
• Subject expression: Defines the subject of the email on a stage Send Email action. The result must be a string.
• Description expression: Defines the description of the email on a stage Send Email action. The result must be a string.
• Action Expression: Calls functions, calculates the value of fields, or updates fields parameters based on the action expression on a stage Execute Script action.

Before we see how to write expressions, we need to define the types of data to be used in expressions: 

• String (Text): Consists of letters, numbers, and characters of any kind or alphabet. It is represented with text inside single quotation marks (e.g. 'Alchemy', 'New Product Development #12-2022'). 
• Number: Real, decimal numbers with the decimal dot "." for precision. Numbers are shown in the User portal with the number of decimal places that are defined while creating the number field in a record template. However, the full number is saved in Alchemy with as many decimals entered in a field and will be used in calculations.
• Boolean: Logical values, true and false, written in all lowercase. Yes/No and Checkbox fields are saved in the system as booleans.
• DateTime: The Date and Time are saved in one format type in Alchemy. Available formats can be found in the Configuration Portal manual - Date & Time.
• User reference: A link to a User in the Alchemy User Portal. These are user reference objects that can either be accessed using expressions or configured user fields.
• Record References: A link to the specific record used as connections to access configuration and system fields for the records they are referencing. These are record reference objects that can either be accessed using expressions or configured record fields. 
• Process References: A link to the specific process used as connections to access process system fields or records. These are process reference objects that can either be accessed using expressions for the current process or selected by the user in the User Portal.

Elements of alchemy script expressions can be

1. Variables
2. Constants
3. Operators
4. Functions 

These elements are represented in Figures 2.1 and 2.2.

Constants are values that are entered directly into a formula expression and do not change when the context is changed. They can be:

• String: Single quotes (' ') are used to indicate the string constant 
  
  • Example: 'abc',  'a10',  '154.3$'
• Number: Numbers can be with or without decimal places. 
  
  • Example: 10, 7.5, -4.0000
• Boolean: Specific terms used
  
  • Example: true, false

Additional data types cannot be used as constants since the data will always depend on context from the user portal.

Variables can be system fields or different fields inside record templates. Field identifiers are used to identify variables in formulas and the system evaluates variables every time a calculation is triggered. 

Defined data types correspond to specific field types in the system: 

• String: Alphanumeric, RichText, Link 
• Number: Number
• Boolean: Yes/No, Checkbox
• DateTime: Date and Time
• Record Reference: Record
• Process Reference: Process‍
• User Reference: User

Variables used to reference a field within the same record can be defined using only the field identifier, such as:

VariableFieldIdentifier

Variables from different records can be accessed and used in expressions by using the record reference field when configuring a record template. The expression field would be defined using:

RecordReferenceField.VariableFieldIdentifier

2.2.1.1 Record References within a Process

All records created in the scope of a single current process template can be accessed using the expression:

To better understand what this expression means, we can break down the first element:

Record:  The keyword representing which record should be accessed.

[RecordTemplateIdentifier]: Records are sorted by the time of their creation. By using this component in the expression Alchemy will look for, and take the value of, the last created record within the process.

If the field referenced is part of a record template that is used in more than one stage of the process, you need to specify within the expression which stage you want to pull the data by adding Stage[StageName].

The full expression would be then be:

2.2.1.2 Record References from other Processes

To access and perform calculations with fields located in a record from another process you will need to configure a field in the record template where users can select either the: 

• Record reference
  
  • Field Type: Record 
• Process reference
  
  • Field Type: Process 

If the record template is configured using the Record field, its identifier will be used as the record reference in your expressions:

In case you use a process field in your configuration, you must add its identifier in the expression:

This expression behaves the same as explained earlier, with the difference that the records will be pulled from the process selected in the ProcessField field. See the examples below.

In case there may be multiple records of the same template in the process you are referencing, it is advisable to create a new record field where the user can choose which record to pull the data from.

2.2.1.3 Multiple Reference Connections

As you have seen already, it is possible to "connect" multiple record and process references in one expression. For example, it is possible to access the system information about the process where the record was created using the expression:

Where RecordVariable is the identifier of your record field. This can be expanded further to access the process system fields. For example:

will return the string representing the status of the process in which this record is created. This could be useful filter criteria, such as when we only want to be able to select formulation records in ongoing lab work processes.

While the multiple "dot expressions" are useful, they should be used sparingly, as more complex expressions may impact performance. Using intermediate, or hidden, fields is recommended when possible.

2.2.1.4 Field Self Reference

ThisField makes it possible to reference a field in its own calculation by enabling calculations to check the state of the field before any recalculation is done, giving the user the possibility to calculate values only for newly created rows in tables.

Data generated by the system operation is available to be used in expressions. System data can provide more information about a process, record, stage run or a user. They can be accessed by using specific keywords listed in the following sections.

2.2.2.1 Process Data

Process data, from the same or different process, can be accessed through the system fields listed below using the keyword Process or the process template identifier. To access this data you need to create a field in the record template with the appropriate formula expression. The field type must be: alphanumeric, number, datetime, or user.

If data is pulled from the current process, the formula expression should be:

Process.SystemFieldIdentifier‍

In this example, Process is the keyword for the current process instance while SystemFieldIdentifier is the variable field identifier that is housing the data being pulled.

If data is pulled from a different process, the record template needs to include an additional field. Prior to the variable field, a process field type needs to be added, allowing the user to select the exact process the data will be pulled from.

The calculation formula in the variable field should then be:

ProcessFieldIdentifier.SystemFieldIdentifier‍

In this example, ProcessFieldIdentifier is the identifier of the process field type while SystemFieldIdentifier is the variable field identifier that is housing the data being pulled.

System field identifiers that can be used in the Formula Expression of an alphanumeric field to return a string output include:

• Name: Process name
• Template: Process template name
• CurrentStage: Currently active stages of the target process. Displays as a comma-separated list of active stage names.
  
  • The status of the process will be returned, instead of the stage, if the process is complete, on hold, or voided. 

System fields identifiers that can be used in the Formula Expression of a number field to return a number output include:

• Priority: Current priority number of the target process
  
  • If the process has no priority assigned, the returned value is 0.
• FinalDuration: Processes with a status of Completed or Void return the duration in days, hours, minutes and seconds. The duration displayed is based on the number of milliseconds captured by the system. 
  
  • If the process is not completed or void, an empty value is returned. 
• TotalEffortSpent: Displayed in hours, this value is the sum of all effort logged by users on tasks in the process. 
  
  • Each time a new value is added or updated on a task within the process, this value is updated.

System field identifiers that can be used in the Formula Expression of a datetime field to return a date and time output include:

• CreatedOn: Date and time when the process was created in the system
• CompletedOn: Date and time when the process was completed in the system 
  
  • The default value displays as empty

System field identifiers that can be used in the Formula Expression of a user field to return an output of the user's first and last name include:

• CreatedBy: The user that created the process in the system

2.2.2.2 Stage Run Data

Stage run data can be accessed through the system fields listed below using the stage identifier. To access this data you need to create a field in the record template with the appropriate formula expression. The field type must be: alphanumeric, number, datatime, or user. 

If data is pulled from the stages of the current process, the formula expression should be:

Stage[StageIdentifier].SystemFieldIdentifier‍

In this example, no stage run is added after StageIdentifier in the expression so the system will pull back information from the latest run.

To pull data from a specific run, add a numerical value to the expression:

Stage[StageIdentifier,1].SystemFieldIdentifier‍

In this example, the expression will access the data from the first stage run.

If data is pulled from a different process, the record template needs to include an additional field. Prior to the variable field, a process field type needs to be added, allowing the user to select the exact process the data will be pulled from.

The calculation formula in the variable field should then be:

ProcessFieldIdentifier.Stage[StageIdentifier].SystemFieldIdentifier‍

In this example, ProcessFieldIdentifier is the identifier of the process field type, Stage[StageIdentifier] is the identifier of the latest stage run, and SystemFieldIdentifier is the variable field identifier that is housing the data being pulled.

System field identifiers that can be used in the Formula Expression of an alphanumeric field to return a string output include:

• Name: Stage name

System field identifiers that can be used in the Formula Expression of a yes/no or checkbox field to return a boolean output include:

• Completed: Current status of the stage run
  
  • Will return as true if the stage has been completed

System field identifiers that can be used in the Formula Expression of a number field to return a number output include:

• Run: Stage run number
• TotalEffortSpent: Displayed in hours, this value is the sum of all effort logged by users on tasks in the process. 
  
  • Each time a new value is added or updated on a task within the process, this value is updated. 

System field identifiers that can be used in the Formula Expression of a datetime field to return a date and time output include:

• CreatedOn: Date and time when the stage was created in the system
• CompletedOn: Date and time when the stage was completed in the system

System field identifiers that can be used in the Formula Expression of a user field to return an output of the user's first and last name include:

• CompletedBy: The user that completed the stage run

2.2.2.3 Record Data

Record data can be accessed through the system fields listed below using the record field or record template identifier. To access this data a field needs to be created in the record template with the appropriate formula expression. The field type must be:  alphanumeric, number, datatime, or user. 

If the data is pulled from the current process, the formula expression should be:

Record[RecordTemplateIdentifier].SystemFieldIdentifier

This expression will pull back data from the last record created in the system.

If data is pulled from a different process, the record template needs to include an additional field. Prior to the variable field, a record field type needs to be added, allowing the user to select the exact process the data will be pulled from.

The formula expression in the variable field should then be:

RecordTypeFieldIdentifier.SystemFieldIdentifier

System field identifiers that can be used in the Formula Expression of an alphanumeric field to return a string output include:

• Name: Record name
• Template: Record template name
• Status: Record status
  
  • Output is a string data type, with possible values of Valid or Invalid

System fields identifiers that can be used in the Formula Expression of a number field to return a number output include:

• Id: Unique identification number of the record
  
  • This number is also visible in the URL

System field identifiers that can be used in the Formula Expression of a datetime field to return a date and time output include:

• CreatedOn: Date and time when the record was created in the system

System field identifiers that can be used in the Formula Expression of a user field to return an output of the user's first and last name include:

• CreatedBy: The user that created the record in the system

System field identifiers that can be used in the Formula Expression of a process field to return a process reference output include:

• Process: Process the record is linked to‍
• Stage: Stage run the record is linked to

Id: unique identification number of the record (visible in the URL also)

2.2.2.4 User Data

User data can be accessed through the system fields listed below using the user field type identifier. To access this data an alphanumeric field needs to be created in the record template with the appropriate formula expression:

‍UserFieldTypeIdentifier.SystemFieldIdentifier

System field identifiers that can be used in the Formula Expression of an alphanumeric field to return a string output include:

• FirstName: First name of the user 
• LastName: Last name of the user 
• Email: User's email
• UserTags:  Returns a list of tags the user is assigned to

2.2.2.5 Recap: Accessing System Data

Accessing system data can be achieved at the Process, Stage, or Record level using the expressions listed below: 

Any process data can be accessed using:

• ‍Process.CreatedBy - Accesses the CreatedBy value of the current process.
• ‍ProcessReferenceField.CreatedBy - Accesses the CreatedBy value of the process, referenced in the process reference field.

Stage run data can be accessed using:

• Stage[RequestReview].CreatedOn  - In this example, the CreatedOn value of the last stage run is accessed.
  • Accesses the last run of the request review stage in the current process. 

• Stage[RequestReview, last].CreatedOn - Same as above.

Specific stage run data can be accessed using:

• Stage[RequestReview, 1].CreatedOn - Accesses the first run of the request review stage in the current process.
• Stage[RequestReview, last].CreatedOn - Accesses the last run of the request review stage in the current process.

Record data can be accessed using:

• Stage[RequestReview].Record[Approval].CreatedOn - Accesses the last record, based on the record template, from the last stage run.
  • In this example, the CreatedOn value of the last record created, in the last stage run, is accessed.
• Stage[RequestReview].Record[Approval, 1].CreatedOn - Accesses the first record, based on the record template, from the last stage run.
  • In this example, the CreatedOn value of the first record created, in the last stage run, is accessed.
• Record[Approval, 1].CreatedOn - Accesses the first record of the specified type in the process.
• ‍Record[Approval, last].CreatedOn - Access the last record of the specified type in the process.

‍

Tips and Tricks:

• Use square brackets when you want to access the record from the current process the user is working in. 
• When you want a user to have a dropdown list in the User Portal and to choose a record that they need from the process where they are at that moment, you need to make a link using the record reference field. 
• Complex expressions are possible by "dotting" record and process variables together, allowing you to access data from different processes and records.

AND - An operator used to check two Boolean expressions. It only returns true if both of the expressions are true.

• Keyword: AND
• Supported operand types: boolean 
• ‍Output type: boolean

Examples: 

The output is true because both of the expressions are true.

The output is false because only the second expression is true.

OR  - An operator used to combine two Boolean expressions. It returns true if at least one of the expressions is true.

• Keyword: OR
• Supported operand types: boolean ‍
• Output type: boolean

Examples:

The value of field ProductPrice equals 3 and you have another field called ProductRecommendedPrice whose value equals 2. The expression will return true because at least one of the expressions is true, in this case the second one. Note that the expression would still return true if both statements had been true.

The expression will return false because both expressions are false.

NOT - An operator that negates the specified Boolean expression. It returns true if the operand is false, otherwise false.

• Keyword: NOT
• Supported operand types: boolean ‍
• Output type: boolean

Examples:

The expression will return true.

The expression will return false.

IN - An operator that checks whether all values of the left operand are contained in the right operand. It returns true if all the values present in the left operand are contained (are a subset) of all the values in the right operand. Otherwise, false is returned. 

The left and right operand can be a single constant, enumerated constants in brackets, a single field, a single field with multiple values (multiple, column, or multiple columns), or an enumerated field in brackets.  

If the left and right operands are of different types, a warning is generated.

If the left operand is empty, it returns true. If the right operand is empty, it returns false.

• Keyword: IN
• Supported operand type: boolean, number, string, datetime, process reference, record reference, user reference, stage reference
• Output type: boolean

Example:

​​The expression will return true if one of the values for the field TypeOfSample is 'Liquid'

‍

NOT IN - An operator that is a negation of IN. It returns true if all the values present in the left operand are not contained (are not a subset) of all the values in the right operand. Otherwise, false is returned. 

Left and right operands can be a single constant, enumerated constants in brackets, a single field, a single field with multiple values (multiple, column, or multiple columns), or an enumerated field in brackets.

If left and right operands are of different types, a warning is generated.

If the left operand is empty, it returns false. If the right operand is empty, it returns true.

• Keyword: NOT IN
• Supported operand type: boolean, number, string, datetime, process reference, user reference
• Output type: boolean

IS SET - An operator that checks whether a value of a field is set. Returns true if the supplied operand contains at least a single value, otherwise false.

If defined to be used on a constant it always returns true and generates a warning during configuration.

• Keyword: IS SET
• Supported operand type: All
• Output type: boolean

Example:

​​The expression will return true if the value for the field ModificationsApplied is set.

NOT SET - An operator that checks whether a value of a field is set. Returns false if the supplied operand contains at least a single value, otherwise true. 

If defined to be used on a constant it always returns false and generates a warning during configuration.

• Keyword: NOT SET
• Supported operand type: All
• Output type: boolean

Example:

​​The expression will return true if the value for the field ModificationsApplied is not set.

IS VALID - An operator that checks if there is any value error at the field template. Returns true if there is no value error, otherwise false.

• Keyword: IS VALID
• Supported operand type: All
• Output type: boolean

Example:

‍NOT VALID - An operator that checks if there is any value error at the field template. Returns true if there is a value error, otherwise false.

• Keyword: NOT VALID
• Supported operand type: All
• Output type: boolean

Example:

Greater Than ( > ) - This operator determines whether the first value is greater than the second value. Returns true if the left operand is greater than the right operand, otherwise false (null value is treated as 0).

• Keyword: >
• Supported operand type: number
• Output type: boolean

Examples:

Returns false if the value of the pH1 field is 2.3 and the value for the pH2 field is 2.4.

Greater or Equal ( >= ) - This operator checks if the left operand is greater than or equal to the right operand. Returns true if the left operand is greater than or equal to the right operand, otherwise false.

• Keyword: >=
• Supported operand type: number
• Output type: boolean

Examples:

5 >= 3

Returns true.

3 >= 3

Returns true.

pH1 >= pH2

Returns false if the value of pH1 field is 2.3 and the value for pH2 field is 2.4.

Less Than ( < ) - This operator determines whether the first value is less than the second value. Returns true if the left operand is less than the right operand, otherwise false.

• Keyword: <
• Supported operand type: number
• Output type: boolean

Examples: 

5 < 6‍

Returns true.

pH1 < pH2

Returns false if the value of pH1 field is 2.4 and the value for pH2 field is 2.3.

Less Than or Equal To ( <= ) - This operator checks if the left operand is less than or equal to the right operand. Returns true if the left operand is less than or equal to the right operand, otherwise false.

• Keyword: <=
• Supported operand type: number
• Output type: boolean

Examples:

5 <= 6

Returns true.

5 <= 5

Returns true.

pH1 <= pH2

Returns false if the value of pH1 field is 2.4 and the value for pH2 field is 2.3.

Is Equal To ( == ) - This operator determines whether the two values are equal. Returns true if the left operand is equal to the right operand, otherwise false.

• Keyword: ==
• Supported operand type: string, number, boolean, datetime, user reference, record reference, process reference, file
• Output type: boolean

Text values are equal if all the characters are the same (comparison is case sensitive) and the text is of the same length. If the value of both operands is null value or an empty string, equals returns true.

Numbers are equal if the numeric value is the same, regardless of the precision. (i.e. 1 == 1.000 will return true).

Boolean is equal if both operands have the same value (true and true, false and false).

Date comparison returns true if every part of the date is the same. Namely, equals returns true if the day, month and year part of the date is the same for both operands, false otherwise (comparison is done in UTC - Coordinated Universal Time).

Link comparison works the same as a text comparison.

File comparison (when attachment field types are compared) returns true if both operands contain the same file path, false otherwise.

Process reference comparison returns true if both operands contain the same process ID, false otherwise.

Record reference comparison returns true if both operands contain the same record ID, false otherwise.

Stage reference comparison returns true if both operands contain the same stage name, false otherwise.

User reference comparison returns true if both operands contain the same user ID, false otherwise.

If empty fields are compared (Null values - no value present) comparison returns true.

Comparison between two operands of different types always returns false, a validation error is generated.

Example:

Is Not Equal To ( != ) - This operator determines whether the two values are not equal. It works like the exact opposite of the Is Equal To operator.

• Keyword: !=
• Supported operand type: string, number, boolean, datetime, process reference, record, reference, user reference, file, link
• Output type: boolean 

Example:

Assign operator ( = ) - This operator is used to assign the value from the expression on the right side to the variable on the left side.

• Keyword: =
• Supported operand type: any
• Left operand can be: field, local variable and element of array
• Right operand: any type

Examples: 

• See the Configuration manual in section 4.6.4 Button, where it is used in Example 1 and Example 2.
• array[index] = element (gets the value of element on i position)
• struct[key] = text (put element for exact key or change it if already exist)

‍

Addition/Concatenation ( + ) - This operator adds two numbers or concatenates two strings, depending on the operand type.

• Keyword: +
• Supported operand type: string, number
• Output type: string, number

If two numbers are added together, the resulting number is the sum of the operands with the precision of the number with the highest precision.

If two string values are added together, the result is a string value formed by concatenating the two operands.

If a number and string value are added together, the resulting value is a string that is made by concatenating the string and the string representation of the number (decimal places format is conserved in string conversion).

If the addition results in a number that is higher/lower than the target field defined max/min, an error will be shown by the frontend validator (i.e. if you adjust min and max for the pH field as 1 and 14. An error will be shown in case that pH1 + pH2 = 14.2 since it is greater than 14) 

If the addition results in a number greater than 2,147,483,647 an out-of-range error is shown.  

If the addition result is a number smaller than -2,147,483,647 an out-of-range error is shown.  

Same principle for strings; error is shown if a string is too long, otherwise, if it is longer than the string it is shortened with “...” appended.

If any of the other operands supported types is used with this operator, the values are converted to their string representation and concatenation of string values is performed.

Examples:

Subtraction ( - ) This operator subtracts the second operand from the first.

• Keyword: -
• Supported operand type: number
• Output type: number

If two numbers are subtracted, the resulting number is the result of subtraction of the second operand from the first operand. The result is a number with the precision of the operand with the highest precision.

If subtraction results in a number that is higher/lower than the target field defined max/min, an error will be shown by the frontend validator.

If subtraction results in a number greater than 2,147,483,647, an out-of-range error is shown.  

If subtraction results in a number smaller than -2,147,483,648, an out-of-range error is shown.

Examples: 

Multiplication ( * ) - This operator multiplies two numbers.

• Keyword: *
• Supported operand type: number
• Output type: number

If two numbers are multiplied, the resulting number is the result of the multiplication of the two operands. The result is a number with the precision of the operand with the highest precision.

If multiplication results in a number that is higher/lower than the target field defined max/min, an error will be shown.

If multiplication results in a number greater than 2,147,483,647, an out-of-range error is shown.

If multiplication results in a number smaller than -2,147,483,648, an out-of-range error is shown.

Examples: 

Division ( / ) - This operator divides two numbers.

• Keyword: /
• Supported operand type: number
• Output type: number

If two numbers are divided, the resulting number is the result of dividing the left operand with the right operand. The result is a number with the precision of the operand with the highest precision. 

If division results in a number that is higher/lower than the target field defined max/min, an error is shown.

If division results in a number greater than 2,147,483,647, an out-of-range error is shown.  

If division results in a number smaller than -2,147,483,648, an out-of-range error is shown.

If the right operand is 0, a divide by zero result is returned.

Examples:

‍Negation ( # ) - This operator changes the sign of the number, transferring it from negative to positive or vice versa. 

• Keyword: #
• Supported operand type: number
• Output type: number

Examples: 

‍MOD - This operator calculates the remainder of the division of one number by another. The result has the same sign as the divisor. Number a is the dividend, and number b is the divisor.

• Keyword: a%b
• Supported operand type: number
• Output type: number

Example:

Note: All Alchemy functions that will be listed in the following pages are available, but not applicable in all cases. Some of the functions are generated in order to help solve specific problems and use cases, and their application at the moment is not system-wide.

Functions are expressions in Alchemy that can use one or more inputs, given in brackets (), and perform complex calculations before returning the output.

IFS (condition, value...) - This function checks whether one or more conditions are met and returns the value that corresponds to the first true condition. It accepts parameters in pairs. The first parameter is a condition and must evaluate to a boolean or an error is generated. The second parameter can be a constant, variable, or another expression that must result in the same type as the field for which you are using the IFS function. The first parameter is considered an expression before the comma, and the second one is after the comma.

• Keyword: IFS
• Supported operand type: all
• Output type: any

If the first parameter in a pair evaluates to true, the function returns the second parameter of the pair.

If IFS is defined with an odd number of parameters, an error is generated.

If IFS is defined without parameters, an error is generated.

Examples:

See an example of the IFS function in the Configuration Portal Administrator Manual, section 4.6.3 - Formulas in Calculations.

SQRT ()- A function that calculates the square root of the number. When performed on an operand, it finds the square root of the number. 

• Keyword: SQRT
• Supported operand type: number
• Output type: number

If the operand is negative, it returns an error. 

If performed on an empty value, it returns a 0. 

If the square root is used on a negative constant, a warning is shown. 

If the square root is used on a number whose minimum is less than 0, a warning is shown.

Examples:

‍POW ()- A function that calculates the result of a number raised to a power. It returns the value of the first operand (before the comma) being raised to the power of the second operand (after the comma).

• Keyword: POW
• Supported operand type: number
• Output type: number

Examples:

ABS () - A function that returns the absolute value of a number or the operand (value without the sign).

• Keyword: ABS
• Supported operand type: number
• Output type: number

Examples:

‍SUM () - A function that returns the sum of all the values of a multiple value field, or all the values in a column. It adds all the values of the specified field or constant.

• Keyword: SUM
• Supported operand type: number
• Output type: number

If used on a constant number, the result is that number. If used on a single value field that belongs to a simple type group, a warning is issued during configuration. In that case, the SUM will return the value of that field. If used on a constant, a warning is issued.

If used on a number field that is marked as multiple, all the values are added together and returned.

If used on a single value number field in a table (repeatable row or repeatable column group) all the values in all the rows are added together.

If used on a multiple value number field in a table, all the values in all the rows and all the multiple values within a single cell are added.

Examples:

‍LOG () - A function that calculates the logarithm (base 10) of the value in the argument.

• Keyword: LOG
• Supported operand type: Number
• Output type: Number

Examples:

‍COUNT () - A function that counts all the values of a multiple value field or all the values in a column. This function counts all the values of the specified field or constant.

• Keyword: COUNT
• Supported operand type: all
• Output type: number

If used on a single value field that belongs to a simple type group, a warning is issued during configuration. It will return 1 if there is a value, 0 otherwise. If used on a constant, a warning is issued.

If used on a field that is marked as multiple, all the values are counted.

If used on a single value field in a table (repeatable row or repeatable column group) all the values in all the rows are counted.

If used on a multiple value field in a table, all the values in all the rows and all the multiple values within a single cell are counted together.

Examples:

‍ROW_COUNT () - A function that counts rows or columns of a certain field. 

• Keyword: ROW_COUNT
• Supported operand type: all
• Output type: number

If it is used on a constant, the result is 1. If it is used on a single value field (multiple=false) that belongs to a group that is a single type, a warning is issued during configuration. It will return 1 if there is a value, 0 otherwise. 

If it is used on a field that is marked as multiple (multiple=true, groupType=SINGLE) the result is 1.

If it is used on a single value field in a table (multiple=false, group type != SINGLE) the result is the number of rows or columns.

If used on a multiple value field in a table (multiple=true, group type != SINGLE) the result is the number of rows or columns.

Examples:

‍AVERAGE () - A function that calculates the average (arithmetic mean) of all the values of a multiple value property or all the values in a column. This function works by adding together all the number values and then dividing them by the number of values. It calculates the average of the specified number field or constant.

• Keyword: AVERAGE
• Supported operand type: number
• Output type: number

If used on a constant number, the result is that number. If used on a single value field that belongs to a simple type group, a warning is issued during configuration. It will return that number as a result. If used on a constant, a warning is issued.

If used on a field that is marked as multiple, all the values are added together and divided.

If used on a single value field in a table (repeatable row or repeatable column group) all the values in all the rows are added together and divided.

If used on a multiple value field in a table, all the values in all the rows and all the multiple values within a single cell are added together and divided by the total count of all the values.

It is identical to SUM(NumberField)/COUNT(NumberField).

Examples:

‍TODAY () - A function that returns the current date, in UTC, and can be parameterized optionally.

• Keyword: TODAY
• Supported operand type: number
• Output type: datetime

When defined without parameters, it returns the current timestamp without seconds.

When defined with parameters, the first parameter represents offset in days, the second represents hours and the third represents minutes (i.e. TODAY(2, 0, 0 ) will return a UTC date that is two days from now. TODAY(0, -1, 0) will return a UTC date that is one hour in the past.).

If a parameter is not supplied, it is assumed to be 0.

When a non-integer number is supplied as a parameter, a warning is generated.

Examples:

DATE FORMAT () - A function that returns a text representation of a date input based on the supplied format.

• Keyword: DATE_FORMAT(Date, Format)
• Supported operand type: Date and Time as the first operand, string as the second operand
• Output type: string

The first parameter is the Date and Time variable.

The second parameter is the format, provided as a string. You can use any of the formats available as explained in Section 4.4.4. - Field Types, Date &Time Field of the Configuration Portal Administrator Manual.

Examples:

‍ROUND () - This function rounds the number to a given precision. It is rounded using half-up mode (i.e. everything above 12.5 will be shown as 13, and everything below 12.5 will be shown as 12. If the number that is rounded is 12.5, 13 will be shown).

• Keyword: ROUND
• Supported operand type: number
• Output type: number

Rounds the first operand to the number of digits supplied in the second operand.

If the second operand is less than 0, 0 is returned as a result.

When the rounding has more digits than the source number (first operand), the resulting number has its precision increased.

Examples:

‍TRANSITION_FROM () - This function checks whether the activation path is coming from the selected stage. It returns true if stage activation is coming from the specified stage, false otherwise. The function can only be used in process template rules (i.e., in conditions for actions). An error is generated if used in other types of expressions.

• Keyword: TRANSITION_FROM
• Supported operand type: stage reference
• Output type: boolean

Example:

HAS_ANY_TAG () - A function that returns a list of users that have any of the supplied tags.

• Keyword: HAS_ANY_TAG
• Supported operand type: list of tags
• Output type: list of users

If no tags are supplied a warning is generated.

If the same tag is passed multiple times a warning is generated.

Example:

‍HAS_ALL_TAGS () - A function that returns a list of users that have all of the supplied tags.

• Keyword: HAS_ALL_TAGS
• Supported operand type: list of tags
• Output type: list of users

If no tags are supplied a warning is generated.

If the same tag is passed multiple times a warning is generated.

Example:

‍INTERSECT () - A function that finds the intersection between multiple lists of the same type. Only the values that are found in all the supplied lists are returned.

• Keyword: INTERSECT
• Supported operand type: All, but all must be of the same type
• Output type: List of results of the same type as provided parameters

Example: 

‍UNION () - This function calculates the union between multiple lists of the same type. All the values that are found in all the supplied lists are returned. List members are not repeated if found multiple times.

• Keyword: UNION
• Supported operand type: All, but all must be of the same type
• Output type: List of results of the same type as provided parameters

Example: 

‍CREATED_BY () - This function determines if the process has been created by supplied users. It returns true if one of the supplied users has created the process, false otherwise.

• Keyword: CREATED_BY
• Supported operand type: tag
• Output type: boolean

Example:

‍PATTERN () - This function returns an auto-incremented pattern based on the supplied pattern in the operand. A defined pattern supports letters (acbd, etc.), digits (1234, etc.), and date and time. The maximum length of this field is 255 characters. We support single pattern definition; if multiple patterns are defined in a formula, an error is generated. If you need a pattern to start from a specific number you can use the following expression: PATTERN('NN','41'), where 41 can be any two digits number.

• Keyword: PATTERN
• Supported operand type: string
• Output type: string
• Pattern:
  • N - for a single digit
  • n - for an infinite digit (no leading zeros)
  • A - for a single letter uppercase
  • a - for a single letter lowercase
  • YYYY - for the current year represented as 4 digits (values from 2019)
  • MM - for the current month represented as 2 digits (values from 1 -12)
  • MMM - for the current month represented as 3 chars (Jan, Feb, Mar, etc.)
  • DD - for the current date represented as 2 digits (values from 1 - 31)
  • hh - for the current hours represented as 2 digits (values from 00 to 23)
  • mm - for the current minutes represented as 2 digits (values from 1 to 59)
  • ss - for the current seconds represented as 2 digits (values from 1 to 59)
  • Characters '-', '/', '.', '_',  ':', '#', ';', '\' and whitespaces can be used as delimiter characters inside the pattern. They are just there to make the pattern more readable, but they are not breaking the pattern.

Each time you reach the "end" of the pattern, the pattern will restart from the beginning. So if you use N for single digits, when N reaches 9, it will start from the 1 again; the same thing applies for letters.

This does not apply to the time-based pattern values, since their values are dependent on the current date and time, but changes in these values influence the resetting of the values from there (i.e., if you are counting Lab Works per date, the pattern would start from the beginning with the new day). Date values are calculated in the time zone from which the request is coming from.

Also, it does not apply when using n as an infinite digit pattern. Small n will start from 1 (or any other value if it’s specified as the second parameter of the PATTERN function and third parameter of the KEY_PATTERN function) and will be increased by 1 up to max value of 2,147,483,647. It does not have leading zeros. 

Examples:

KEY_PATTERN () - This function returns an auto-incremented pattern based on the supplied pattern in the operand.

• • Keyword: KEY_PATTERN
  • Supported operand type: string
  • Output type: string

The first parameter defines the prefix (in further text KEY) for the pattern, and the prefix can be a constant or field value.

The second parameter is the pattern value and the same rule can be applied to the PATTERN function.

The third parameter is optional. It defines which value is the starting point from which pattern should count and can be a constant or field value.

Examples: 

The KEY_PATTERN function is a powerful tool. You can change the KEY, and reset the counter. If you change the KEY back to the previous value, it will not reset the counter again but continue from the value when it was changed for the first time. See the following example.

STAGE_COMPLETED_BY () - This function determines if the stage has been completed by supplied users. It returns true if one of the supplied users has completed the stage, false otherwise.

• Keyword: STAGE_COMPLETED_BY
• Supported operand type: tag
• Output type: boolean

Example:

‍RUN_COUNT () - This function returns the number of runs the given Stage has.

• Keyword: RUN_COUNT
• Supported operand type: Stage reference
• Output type: number

Example:

CONVERT UNIT - This function converts the provided number value in the desired unit.

• Keyword: CONVERT_UNIT
• Supported operand type: number
• Output type: number

UnitField and UnitFieldToConvert are alphanumeric values, which have to be exact with defined units in the system, otherwise an error will be thrown. 

In case UnitFieldToConvert is not provided, the result of conversion will be the same as a value of ValueToConvertField. 

In case there is conversion from one Unit Type to another additional parameter needs to be defined as JSON object in form like:

The accepted abbreviations for some of the units are given below. The details on the units’ abbreviations were  based on this Source.

Abbreviations: 'mg',' g',' kg', 'gr',' lb', 'dr',' oz', 't', 'ton', 'ml', 'dl', 'cl', 'l', 'm3', 'gal', 'hl', 'tsp', 'tbsp', 'fl oz', 'cup', 'pt', 'qt', 'g/ml', 'g/dl', 'g/cl', 'g/l', 'g/m3', 'kg/ml', 'kg/dl', 'kg/cl', 'kg/l', 'kg/m3', 'lb/gal', 'lb/pt', 'lb/qt', 'oz/gal', 'oz/pt', 'oz/qt'

The following functions are used only in writing Alchemy scripts (buttons, stage execute script and On Change action scripts), not in formula or visibility expressions:

SIGNED_IN_USER () - This function returns the REFERENCED_USER which is signed in. It is only available to use in the action expression of the button field or on the field action.

• Keyword: SIGNED_IN_USER
• Supported operand type: None
• Output type: User reference

Example:

CREATE_PROCESS () - This function creates a new process or processes of the provided process template. The first parameter is the Process Template Identifier. The second parameter is the number of processes to be created.

• Keyword: CREATE_PROCESS
• Supported operand type: Process, Number
• Output type: Process

Example:

‍CREATE_RECORD () - This function creates a new record or records of the provided Record Template. Record Templates configured to generate records outside of the process (solo records) can also be created by this function. In that case, second and third parameters should not be added to the expression.  

• Keyword: CREATE_RECORD
• Supported operand type: Record, Stage, Process, Number
• Output type: Record

The first parameter is Record Template Identifier, from which we want to create a new instance.

The second parameter is Stage Identifier, which defines at which stage we want to create a new record instance. This parameter is optional.

The third parameter is the Process Identifier which defines in which Process we want to create a new record instance. Using the reserved word ‘Process,’ the record instances will be created in the current process. This parameter is optional.

The fourth parameter is the number, which defines how many record instances we want to create.

Examples:

‍ADD_ROW () - A function for adding a row in a group. 

• Keyword: ADD_ROW
• Supported operand type: group reference, number‍
• Output type: group reference

The First Parameter is a Group Identifier, which defines which group we want to add rows to.

The Second Parameter is the number, which defines how many rows we want to create.

Examples:

‍GET_RECORD () - A function that returns referenced record value, depending on passed record ID.

• Keyword: GET_RECORD
• Supported operand type: Number‍
• Output type: Record

Examples:

‍CREATE_TASK () - This function creates a new task on the defined stage.

• Keyword: CREATE_TASK 
• Supported operand type: Stage Reference, String, Date & Time, Number, Users
• Output type: Task

It receives next parameters comma separated:

• Stage reference expression
• Task subject *(String)
• Task description *(String)
• Planned Start *(Date and Time)
• Planned Completion *(Date and Time)
• Score *(Number)
• User (referenced user/users)

The order of parameters defined must be as it is listed above.

The function will be executed only if all requirements are met (Stage is active, assignees have Can Edit privilege, etc.)

- Example:

‍CREATE_COMPARE () - This function creates a new Compare Records.

• Keyword: CREATE_COMPARE
• Supported operand type: Stage reference, string, list of strings, list of strings, record reference
• Output type: Compare Records

It receives next parameters, comma separated:

• Stage reference expression
• Record Template Identifier
• [list of record Field’s Identifiers] - list of Field’s Identifiers. Fields order defined here is the order of fields displayed after creating Compare, and only these fields will be displayed in created Compare. 
• [GroupBy Field’s Identifier] - Data pulled in the created Compare will be Grouped By the field chosen here.
• Record Expression - Expression which evaluates to record reference, this can be any expression which results in record reference and also it can be set just record reference Field

Examples:

CREATE_VIEW () - This function creates a new View in a stage, from the View Template with predefined fields and filters.

• Keyword: CREATE_VIEW
• Supported operand types: Stage reference, View Template identifier, Name of the New view, Filter Parameters
• Output type: View

It receives next parameters, comma separated:

• Stage reference expression (Stage[identifier])
• View Template: string (view template identifier)
• View Name: string (name of the new View)
• Filter Parameters: struct. Here is the Filter parameters struct format:
  • filterParameters : array (of nodes)
  • node : object
    • ‘node’ : integer (order of the node in data composition starting from 1)
    • ‘fields’ : array (of fields)
  • field : object
    • ‘name’ : string, standard field keyword (see below) or field template identifier
    • ‘visible’ : boolean
    • ‘filters’ : array (of filters)
    • ‘precision’ : integer, in case of a number field, custom precision [optional]
    • ‘dateFormat’ : string, in case of a dateTime field, custom date format [optional]
  • filter : object
    • ‘type’ : string, filter comparison type (see below)
    • ‘values’ : array (of filter values) [optional, exclusive with ‘from’ and ‘to’]
      note that you always need either ‘values’ or ‘to’/’from’/both ‘to’ and ‘from’
      if there is no values to be passed, leave the values array empty like this [ ]
    • ‘from’ : number / dateTime [optional, exclusive with ‘values’]
    • ‘to’ : number / dateTime [optional, exclusive with ‘values’]

Note that all parameters can be calculated using an expression. It is not necessary to put an explicit value for numbers, strings, etc. Calculation rules apply.

Example:

• View with root process node, middle stage node and last record node of a certain template. Because it was not mentioned in the parameters, node ‘2’ (stage) will be the same as template.

SEND_EMAIL () - This function sends email to the specified user(s). CAUTION: Usage of this function needs to be carefully tested since sending email cannot be undone.

• Keyword: SEND_EMAIL(param1, param2, param3)
• Supported operand type: string and user reference
• Output type: nothing (email is sent)

param1 is the email recipient. It can be an email address as a string, or any expression returning user reference.

param2 is the subject of email (string).

param3 is the body of email (string).

Example:

‍If(){} else {} - If expressions can be used to define conditional actions in expressions. The correct syntax is the following: If(condition){ block of code } else { block of code}.

• Keyword: if
• Supported operand type: any
• Output type: boolean

Examples:

‍for() {} - A For loop is used to define the number of executions (iterations) of a defined code block (calculations/commands). It is used to access values of fields with multiple values or fields in repeatable groups.

• Keyword: for
• Supported operand type: Number
• Output type: none

The correct syntax is the following: for(var i=n; i<=m; i=i+k){code bloc}

• Code block will be executed in iterations while the condition in for loop is met.
• The first parameter in the for loop is the starting number for the counter of iterations. It must be defined as a local variable, i.e. var i=1.
• The maximum number of iterations can be a constant or can be defined as a value from the Number field, and this is part of the condition in the second parameter. While the condition is true,  the code block will be executed in iterations.
• The third parameter defines the increment for the counter, i.e i=i+1

Example:

‍RECALCULATE_PRIVILEGES() - This function can be used in action scripts when it needs to recalculate privileges when you need to change them (for example, when you need to mark a record as “confidential”).

• Keyword: RECALCULATE_PRIVILEGES
• Supported operand type: Process or Record
• Output type: none

Examples:

‍CREATE_LINK() -  This function is used in the calculation of the link type field to generate a new link.

• Keyword: CREATE_LINK
• Supported operand type: String
• Output type: Link

The first parameter is the target URL, written as a string under the single quotes. Other links can be added (again as a string) using the concatenation function (+ function) if you want the link to have a dynamic value.

The second parameter is the name of the link that will be displayed inside the link field. 

Examples:

CREATE_RECORD_ASYNC -  This function is used to create records asynchronously. Asynchronous creation of records enables better performance. 

• Keyword: CREATE_RECORD_ASYNC
• Output type: Void

Number of parameters: 3 mandatory and 1 optional

• The first parameter: RecordTemplate
• The second parameter: StageIdentifier (optional)
• The third parameter: StructWithConstants - parameters which will be used in callback function
• The fourth parameter: postCreationScript (callback) - actions which will be executed after new record is created

Examples:

Creating solo record:

Creating record in a process:

CREATE_PROCESS_ASYNC -  This function is used to create new processes asynchronously.

• Keyword: CREATE_PROCESS_ASYNC
• Output type: Void

Number of parameters: 3 

• The first parameter: Process template
• The second parameter: StructWithConstants - parameters which will be used in callback function
• The third parameter: postCreationScript (callback) - actions which will be executed after new a process is created

Examples:

CREATE_SAMPLE_RECORD_ FROM_MATERIAL() -  A function that is used to create a standalone Sample from the passed Material record.

• Keyword: CREATE_SAMPLE_RECORD_FROM_MATERIAL
• Output type: record reference (Sample RT)

Number of parameters: 1

• First parameter: Material Record Reference

Example:

CREATE_MATERIAL_RECORD() - A function that is used to create a Material record from the ELN & LIMS Material record template.

• Keyword: CREATE_MATERIAL_ RECORD
• Output type: record reference (Material RT; solo or inside a workspace)

Number of parameters: 1

• Optional parameter: Workspace Record Reference

SIN() - A function that calculates the SINE of the given angle. The angle must be in radians. If you have an angle in degrees, convert it to radians by using the RADIANS function (see below).

• Keyword: SIN(number)
• Supported operand type: number‍
• Output type: number

Example:

‍COS() - A function that calculates the COSINE of the given angle. The angle must be in radians. If you have an angle in degrees, convert it to radians by using the RADIANS function (see below).

• Keyword: COS(number)
• Supported operand type: number
• Output type: number

Examples:

‍TAN() - A function that calculates the TANGENT of the given angle. The angle must be in radians. If you have an angle in degrees, convert it to radians by using the RADIANS function (see below).

• Keyword: TAN(number)
• Supported operand type: number
• Output type: number

Examples:

‍COT() - A function that calculates the COTANGENT of the given angle. The angle must be in radians. If you have an angle in degrees, convert it to radians by using the RADIANS function (see below).

• Keyword: COT(number)
• Supported operand type: number
• Output type: number

Example:

‍ASIN() - A function that calculates the ARCSINE of the given number (inverse SINE). The value of the number must be from -1 to 1. The resulting angle is in radians, in the range from -Pi/2 to +Pi/2. If you want an angle in degrees, convert it from radians to degrees by using the DEGREES function (see below).

• Keyword: ASINE(number)
• Supported operand type: number
• Output type: number

Examples:

‍ACOS() - A function that calculates the ARCCOSINE of the given number (inverse COSINE). The value of the number must be from -1 to 1. The resulting angle is in radians, in the range from 0 to Pi. If you want an angle in degrees, convert it from radians to degrees by using the DEGREES function (see below).

• Keyword: ACOS(number)
• Supported operand type: number
• Output type: number

Examples:

‍ATAN() - This function calculates the ARCTANGENT of the given number (inverse TANGENT). The value of the number must be a real number. The resulting angle is in radians, in the range from -Pi/2 to +Pi/2. If you want an angle in degrees, convert it from radians to degrees by using the DEGREES function (see below).

• Keyword: ATAN(number)
• Supported operand type: number
• Output type: number

Examples:

‍ATAN2() from X- and Y-coordinates - This function calculates the ARCTANGENT of the specified x- and y-coordinates (inverse TANGENT). A line starting from origin (0,0) to the point with specific coordinates  (x, y) makes an angle with the x-axis (counterclockwise and clockwise). This angle is the result of arctangent and it is given in radians, in the range from -Pi to Pi, excluding -Pi.

• Keyword: ATAN2(x,y)
• Supported operand type: number (a pair of coordinates)
• Output type: number (the positive number is a counterclockwise angle from the x-axis, a negative number is a clockwise angle from the x-axis)

Example:

‍ACOT() - This function calculates the principal value of the ARCCOTANGENT of a number (inverse COTANGENT). The value of the number must be a real number. It is a cotangent of an angle you want to calculate with this function. The resulting angle is in radians, in the range from 0 to Pi. If you want an angle in degrees, convert it from radians to degrees by using the DEGREES function (see below).

• Keyword: ACOT(number)
• Supported operand type: number
• Output type: number

Example:

DEGREES()  - This function converts an angle given in radians to an angle in degrees.

• Keyword: DEGREES(angle)
• Supported operand type: number
• Output type: number

Example:

‍RADIANS() - This function converts an angle given in degrees to an angle in radians.

• Keyword: RADIANS(angle)
• Supported operand type: number
• Output type: number

Example:

‍LN() - This function calculates the natural logarithm of a number. The natural logarithm of a number x is the power to which constant e (e=2.718) should be raised to equal x. The given number must be a positive, real number.

• Keyword: LN(number)
• Supported operand type: number
• Output type: number

Example:

‍EXP() - This function calculates the constant e (e=2.718) raised to the power of a given number. It is an inverse function of LN.

• Keyword: EXP(number)
• Supported operand type: number
• Output type: number

- Example:

‍CEILING() - This function rounds a number up, to the nearest integer. A parameter is a number, or a value of a number field identifier, which you want to round up.

• Keyword: CEILING(number)
• Supported operand type: number
• Output type: number

‍FLOOR() - This function rounds a number down, to the nearest integer. A parameter is a number, or value of a number field identifier, which you want to round down.

• Keyword: FLOOR(number)
• Supported operand type: number
• Output type: number
  

IS_DIVISIBLE() - This function checks if two numbers are divisible without the remainder. Number1 is the dividend, while number2 is the divisor.

• Keyword: IS_DIV(number1, number2)
• Supported operand type: number
• Output type: boolean

Example:

GEOMEAN() - This function calculates the geometric mean of the given numbers (not arithmetic mean, for that you can use function AVG).

• Keyword: GEOMEAN(numberMatrix)
• Supported operand type: number
• Output type: number

ENCODEURL() - This function transforms a given string in URL code. It will replace non-alphanumeric characters with the percentage symbol and a hexadecimal number.

• Keyword: ENCODEURL(text)
• Supported operand type: text
• Output type: text

Example:

‍LEFT() - This function gives the leftmost characters from a text value.

• Keyword: LEFT(Text, numberOfCharacters)
• Supported operand type: text
• Output type: text

Text is a string from which you want to extract the left side characters. numberOfCharacters sets how many characters you desire to extract from the left side of the given text.

Example:

• On the record Formulation there is a Formulation Name field (alphanumeric, identifier FormulationName) with the value ‘VL1012C0001 improve density’. This value is composed of different fields on another record Work Plan.
• On the Work Plan record, there is also a button that creates a Formulation record and gives it a name - filling in the Formulation Name field. You want to pull only the VL code on the Formulation record in a separate field.
• Make an alphanumeric field called VL Number and put the following formula expression: LEFT(FormulationName, 12). This way, the VL Number field will get the value ‘VL1012C0001’.

RIGHT() - This function gives the rightmost characters from a text value.

• Keyword: RIGHT(Text, numberOfCharacters)
• Supported operand type: text, number
• Output type: text

Text is a string from which you want to extract the right side characters. numberOfCharacters sets how many characters you desire to extract from the right side of the given text.

Example:

‍SEARCH() - This function finds a location of one text value within another text value and returns a number of the character where it starts the searched part of the text.

• Keyword: SEARCH(searchString, text)
• Supported operand type: text
• Output type: number

‍Text is a string where you want to search a certain part; searchString is the part of the string you want to search; Result is the number at which is the first character of searchString in the text. Not case-sensitive.

Example:

‍REPLACE() - This function replaces characters within the text.

• Keyword: REPLACE(OriginalText, StartingPosition, NumberOfCharactersToChange, NewText) 
• Supported operand type: text
• Output type: text

Originaltext is a string where you want to replace a part. StartingPosition is a place from where you want to replace the text; NumberOfCharactersToChange is a part of the text given in the number of characters you want to replace in the text by NewText. NewText is a string you want to have in the text after the replacement.

SUBSTITUTE() - This function substitutes new text for old text in a string.

• Keyword: SUBSTITUTE(OriginalText, OldString, NewString, [optional] NumberOfOccurrences)
• Supported operand type: text
• Output type: text

OriginalText is a string where you want to substitute a part. This function is searching for a given part of the text - OldString in OriginalText and substitutes it with the given NewString. NumberOfOccurrences is optional and defines how many occurrences to replace. If not specified, the function will substitute all the occurrences.

LOWER() - This function converts each uppercase letter in a text string to a lower case letter.

• Keyword: LOWER(text)
• Supported operand type: text
• Output type: text

Examples:

‍UPPER() - This function converts a text string to uppercase.

• Keyword: UPPER(text)
• Supported operand type: text
• Output type: text

Examples:

‍FORMAT_STRING() - This function gives a formatted string made from a string with placeholders and values passed to fit in the placeholders.

• Keyword: STRING_FORMAT(textWithPlaceholders, param1, param2, ...)
• Supported operand type: text
• Output type: text

The number of placeholders in a string must be equal to the number of values to be placed in the string. param1, param2,  are values to fit the placeholders (can be more than 2).

Example:

‍MID() - This function trims the given number of characters from the original string from the left and from the right side.

• Keyword: MID(text, numberOfCharsLeft, numberOfCharsRight)
• Supported operand type: text
• Output type: text

text can be a string, or an identifier of an alphanumeric field, etc.

numbersOfCharsLeft is the number of characters that you want to trim from the left side of the given string.

numbersOfCharsRight is the number of characters that you want to trim from the right side of the given string.‍

‍
RANDOMIZE - This function generates random string according to the provided string length and returns an array of characters.

• Keyword: RANDOMIZE(length, isNumber)
• Supported operand type: Number (length) and Boolean (isNumber)
• Output type: Array of characters (string)

Input 1 is the length, a number type, which defines how many characters need to be returned. 

Input 2 is isNumber, a boolean value, which defines whether the returned array of characters is only numbers.

Example: 

P_CORREL() - This function calculates the Pearson correlation coefficient, dimensionless index that ranges from -1.0 to 1.0 inclusive. It is a measure of linear correlation between two sets of data.

• Keyword: P_CORREL(numberMatrix1, numberMatrix2)
• Supported operand type: number
• Output type: number

numberMatrix1 is a set of independent values, while numberMatrix2 is the other set of independent values that you want to correlate.

S_CORREL() - This function calculates the Spearman correlation coefficient.

• Keyword: S_CORREL(numberMatrix1, numberMatrix2)
• Supported operand type: number
• Output type: number

numberMatrix1 is a set of independent values, while numberMatrix2 is the other set of independent values that you want to correlate.

MAX() - This function gives the largest value in a set of values.

• Keyword: MAX(numberMatrix)
• Supported operand type: number
• Output type: number

MIN() - This function gives the smallest value in a set of values.

• Keyword: MIN(numberMatrix)
• Supported operand type: number
• Output type: number

MEDIAN() - This function gives the median of the given numbers. The median is in the middle of a set of numbers.

• Keyword: MEDIAN(numberMatrix)
• Supported operand type: number
• Output type: number

MODE() - This function gives the most frequently occurring single value in a data set. 

• Keyword: MODE(valueMatrix)
• Supported operand type: number
• Output type: number

MODE_M() - This function gives the most frequently occurring multiple values in a data set. Only the values that are most frequently repeated and that repeat exactly the same number of times are returned.

• Keyword: MODE_M(valueMatrix)
• Supported operand type: number
• Output type: number
  
  

PERCENTILE()  - This function calculates the k-th percentile of values in a data set; where k is in the range from 0 to 1, excluded.

• Keyword: PERCENTILE(valueMatrix, percentileNumber)
• Supported operand type: number
• Output type: number

percentileNumber is k.

STDEV() - This function calculates standard deviation based on the sample of population.

• Keyword: STDEV(valueMatrix)
• Supported operand type: number
• Output type: number

DATE_DIF() - This function calculates the date difference (or the number of days, months, years, etc.) between two dates.

• Keyword: DATE_DIF(dateValue1, dateValue2, dateDiffKeyword)
• Supported operand type: date
• Output type: number

dateDiffKeyword can be one of the following: 'YEAR', 'MONTH', 'WEEK', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND'

dateValue1 is the date you want to calculate the difference regarding to the dateValue2. The calculated difference will be displayed in units that you set by dateDiffKeyword.

DATE_ADD() - This function calculates the resulting date and time by adding the amount specified to the given date.

• Keyword: DATE_ADD(dateValue, amount, dateDiffKeyword)
• Supported operand type: date and time
• Output type: date and time

dateValue is the date to which you want to add the amount of time.

amount is the amount of time you want to add to dateValue (positive number for adding amount of time in future, negative number for returning date from the past).

dateDiffKeyword can be one of the following: 'YEAR', 'MONTH', 'WEEK', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'MILLISECOND'. By choosing one of these keywords you are setting a unit for the amount to be added to the dateValue

Example:

‍DATE_GET() - This function returns a year, a month, a day of a month, or a day of a year of the given date.

• Keyword: DATE_GET(dateValue, dateSegmentKeyword)
• Supported operand type: date & time
• Output type: number (format YYYY,  M or MM, D or DD)

dateValue is the given date for which you want to calculate the year.

dateSegmentKeyword is 'YEAR', 'MONTH', 'MONTHDAY' or 'DAY'.

Examples:

‍WEEKDAY - This function returns a day of the week as a number from 1 for Sunday  to 7 for Monday.

• Keyword: DATE_GET(dateValue, dateSegmentKeyword)
• Supported operand type: date&time
• Output type: number

dateValue is the given date for which you want to calculate the day of the week.

dateSegmentKeyword is 'WEEKDAY'.

HOUR - This function returns an hour of the given date & time value as a number from 0 (for 12:00 AM) to 23 (for 11:00 PM).

• Keyword: DATE_GET(dateValue, dateSegmentKeyword)
• Supported operand type: date & time
• Output type: number (format hh)

dateValue is the given date & time for which you want to calculate the hour.

dateSegmentKeyword is 'HOUR'.

MINUTE - This function returns a minute of the given date & time value as a number from 0 to 59.

• Keyword: DATE_GET(dateValue, dateSegmentKeyword)
• Supported operand type: date & time
• Output type: number (format mm)

dateValue is the given date & time for which you want to calculate the minute.

dateSegmentKeyword is 'MINUTE'.

SECOND - This function returns a second of the given date & time value as a number from 0 to 59.

• Keyword: DATE_GET(dateValue, dateSegmentKeyword)
• Supported operand type: date & time
• Output type: number

dateValue is the given date & time for which you want to calculate the second.

dateSegmentKeyword is 'SECOND'.

‍MILLISECOND - This function returns a millisecond of the given date & time value as a number from 0 to 999.

• Keyword: DATE_GET(dateValue, dateSegmentKeyword)
• Supported operand type: date & time
• Output type: number

dateValue is the given date & time for which you want to calculate the millisecond.

dateSegmentKeyword is 'MILLISECOND'.

NUMBER OF THE WEEK - This function returns a week number of the given date & time value as a number from 1 to 52. Numbering is determined by aligned-week-of-the-year and depends on the starting day of the year. The first aligned week of the year starts on day-of-year 1, the second aligned week starts on day-of-year 8, and so on.

• Keyword: DATE_GET(dateValue, dateSegmentKeyword)
• Supported operand type: date & time
• Output type: number

dateValue is the given date & time for which you want to calculate the number of the week.

dateSegmentKeyword is 'WEEK'.

QUARTER OF THE YEAR - This function returns a quarter of the year for the given date & time value as a number from 1 to 4.

• Keyword: DATE_GET(dateValue, dateSegmentKeyword)
• Supported operand type: date & time
• Output type: number

dateValue is the given date & time for which you want to calculate the quarter of the year.

dateSegmentKeyword is 'QUARTER'.

END_OF() - This function returns a date value which is at the end of the specified time unit for the given date. As for the DATE_GET function, the week starts on Sunday.

• Keyword: END_OF(dateValue, TimeUnit)
• Supported operand type: date & time
• Output type: date & time

dateValue is the given date & time.

TimeUnit can be 'MONTH', 'WEEK', 'QUARTER' or 'YEAR'.

The functions listed below are to be used with arrays. Elements of the array can be numbers, text, another array, or struct. Different types can be elements of the same array.

APPEND() - This function adds an element at the last place in the array. Array size will be increased by one. If the added element is another array, it will be added at the last place of the main array as a subarray. 

• Keyword: APPEND(param1, param2)
• Supported operand type: param1 must be of array type; param2 can be text, number, struct or another array
• Output type: nothing - element is just added to an already existing array.

param1 is the local variable of the type array.

param2 is an element to be added to the array defined in param1.

Examples:

‍APPEND_ALL() - This function adds elements at the last place in the array. Array size will be increased by one, or more elements, depending on the type of added element. If the element added is another array, APPEND_ALL will merge the main array with the array being added. The size of the main array will be increased by the size of the added array. It is good practice to use this function instead of APPEND in Predefined Values Expressions.

• Keyword: APPEND_ALL(param1, param2)
• Supported operand type: param1 must be of array type; param2 can be text, number, struct or another array
• Output type: nothing - element(s) are just added to an already existing array.

param1 is the local variable of the type array.

param2 is the element to be added to the array defined in param1.

Examples:

‍INSERT() - This function inserts an element to the exact position in the array. The existing element on the position of insertion will be shifted to the next position and the array size will be increased by one.

• Keyword: INSERT(param1, param2, param3)
• Supported operand type: param1 must be of array type; param2 must be number; param3 can be text, number, struct or another array
• Output type: nothing - element(s) are just added to an already existing array. 

param1 is the local variable of the type array.

param2 is the position where a new element will be inserted.

param3 is the element inserted on the position defined by param2 in the array defined by param1.

Examples:

‍INSERT_ALL() - This function inserts element(s) to the exact position in the array.

• Keyword: INSERT_ALL(param1, param2, param3)
• Supported operand type: param1 must be of array type; param2 must be number; param3 can be text, number, struct or another array
• Output type: nothing - element(s) are just added to an already existing array. 

When the element being inserted is not an array, the array size will be increased by one. 

If the element being inserted is another array, the array size will be increased by the number of the elements in the inserted array.

param1 is the local variable of the type array.

param2 is the position where a new element will be inserted.

param3 is the element inserted on the position defined by param2 in the array defined by param1.

Examples:

‍REMOVE() - This function removes the element(s) from the array on the exact position(s). The array size will be decreased by the number of removed elements.

• Keyword: REMOVE(param1, param2)
• Supported operand type: param1 must be of array type; param2 can be number or array of numbers
• Output type: nothing - element(s) are just removed from an existing array. 

param1 is the local variable of type array.

param2 is the position where a single element will be removed or array of positions of elements to be removed.

Examples:

‍RESIZE() - This function resizes the array to the defined size.

• Keyword: RESIZE(param1, param2)
• Supported operand type: param1 must be of array type; param2 must be number
• Output type: nothing - element(s) are just added or removed to/from an existing array.

If the size is decreasing, elements on positions outside of the new size will be removed from the resized array.

If the size is increasing, the number of elements in the new array will increase to the new size. The new elements will be populated by null values.  

param1 is local variable of type array.

param2 defines the new size of array (number of elements in a resized array).

Examples:

‍SUBARRAY() - This function creates a new array that will be the subarray of the main array. The new array contains elements between from and to position of the main array. Elements on from position and to position are included in the subarray. If to position is greater than the size of the main array, the returned subarray will be between from position and the last element of the main array.  

• Keyword: SUBARRAY(param1, param2, param3)
• Supported operand type: param1 must be of array type, param2 and param3 must be number
• Output type: array (subarray created from the array)

param1 is local variable of type array.

param2 defines the start of the subarray (position of element in main array that will be first element in subarray).

param3 defines the end of the subarray (position of element in main array that will be last element in subarray).

Examples:

‍CONTAINS() - This function checks if an array contains a specific element. If struct is used, it checks if struct contains a specific key. 

• Keyword: CONTAINS(struct, key)
• Supported operand type: param1 must be or array type or struct; param2 can be number, struct, another array or text (must be text if param1 is struct)
• Output type: true, false (boolean) 

param1 is a local variable of type array or type struct.

param2 has two options based on type of param1:

1) if the param1 is an array - the param2 can be text, number, struct or another array.

•  Keyword: CONTAINS(array, element)

2) if the param1 is struct - the param2 can be only text

Examples:

‍CONTAINS_ALL() - This function checks if the main array contains all of the specified elements. Elements to check are defined in another array. 

• Keyword: CONTAINS_ALL(param1, param2)
• Supported operand type: param1 must be of array type; param2 can be text, number, struct or another array
• Output type: true, false (boolean)

param1 is the local variable of type array.

param2 specifies elements to be checked, defined in another array.

Examples:

‍CONTAINS_ANY() - This function checks if the main array contains any of the specified elements. Elements to check are defined in another array. 

• Keyword: CONTAINS_ANY(param1, param2)
• Supported operand type: param1 must be of array type; param2 can be text, number, struct or another array
• Output type: true, false (boolean)

param1 is a local variable of type array.

param2 specifies elements to be checked, defined in another array.

Examples:

‍COUNT_OF() - This function counts how many times a specified element (or elements in another array) is repeated in the main array.

• Keyword: COUNT_OF(param1, param2)
• Supported operand type: param1 must be of array type; param2 can be text, number, struct or another array
• Output type: number

param1 is a local variable of type array.

param2 specifies elements to be checked, defined in another array.

Examples:

FIND_AND_REMOVE - This function finds and removes the first (or all) equal item(s) in the array. The function will not return any value; what is forwarded is changed. 

• Keyword: FIND_AND_REMOVE(param1, param2)
• Supported operand type: param1 must be of array type; param2 can be any type
• Output type: no output

It can be used to configure a repeatable row table where already used predefined values need to be removed after previously used.  

param1 is local variable of type array.

param2 specifies value which will be removed from param1.

Examples:

INDEX_OF - This function finds the equal item(s) and returns their index as an array of number(s).

• Keyword: INDEX_OF(param1, param2)
• Supported operand type: param1 must be of array type; param2 can be any type
• Output type: array

param1 is a local variable of type array.

param2 specifies the value which will be found in param1.

Examples:

AS_ARRAY - This function converts input parameters to an array. Since it is returning the array, the field to which the value is returned needs to support multiple values.

• Keyword: AS_ARRAY(param1)
• Supported operand type: param1 can be constant, local variable, field variable
• Output type: array

Example 1: 

Example 2: 

someField is single number field out of group with predefined values 10, 20, 30 and value 10 is set.

Example 3: 

someField is multi number field out of group with predefined values 10, 20, 30 and values 10 and 20 are set.

Example 4: 

someField is single number field in a group with predefined values 10, 20, 30 and values are set:

Example 5: 

someField is multi number field in a group with predefined values 10, 20, 30 and values are set:

RANDOMIZE - This function generates random string according to the provided string length and it returns an array of characters.

• Keyword: RANDOMIZE(length, isNumber)
• Supported operand type: Number (length) and Boolean (isNumber)
• Output type: Array of characters (string)

Input 1 is the length, number type, which defines how many characters need to be returned. 

Input 2 is isNumber, boolean value, which defines whether the returned array of characters is only numbers.

Example: 

The Item keyword is used to access the specific item from the resultset used in expressions in repeatable rows or columns determined by record reference or determined by filter (Item.FieldIdentifier). Namely, they are enabling the creation of bound tables, as explained in the section Repeatable Options of the Configuration Portal Administrator Manual.

Item keyword is used as a record reference variable, to access a specific item from the result set of field values in repeatable rows or columns. Item is only used in the Formula Expression for fields in repeatable rows or columns where the repeatable group option is set to Determined by Record Reference or Determined by Filter.  

In our example in the Configuration Portal Administrator Manual on how to make a bound table in Alchemy we configured a bound table in the record Work Plan that is determined by the Literature Search record reference. In this case, the Item keyword will act as a record reference variable, for the Literature Search record.

Since the Item is a record variable, all other options explained in the Chapter Variables are available, as long as they are used inside the repeatable row group. Some use cases are

• Item - must use in a formula expression for a record type of field. Each row in the table (repeatable row group) will have a different value i.e. different record.
• Item.Process - must use in a formula expression for a process type of field. It will serve as a link to access the process in which a certain record is located.
• Item.Process.Status - must use in a formula expression for an alphanumeric field (returns a string variable). It will show  the status of the process where a certain record is located.

Item.CreatedBy - must use in a formula expression for a user type field. Returns a user who created a certain record of the table.

As already described in chapter Alphanumeric of the Configuration Portal Administrator Manual, alphanumeric fields can have predefined values. In some cases, you will need to configure the first alphanumeric field with predefined values. Then, depending on the predefined value selected in this field, a specific set of predefined values should be available for selection in the second alphanumeric field. This is known as Conditional Predefined Values and it can be realized by writing a proper script. See the example below:

You need to configure the Test Instruction record template. This template will be used for the creation of different records - a variety of test instructions in your organization.

First, you need to set the type of your Test Instruction. Make an alphanumeric field (name Test Type, identifier TestType) and give it three predefined values: Characterization, Application, Microbiology.

Make the second alphanumeric field (name: Test, identifier Test), where different tests will appear in a dropdown, depending on the predefined value selected in the Test Type field. Let’s say these are the values you need to configure:

• When Characterization is selected in the field Test Type, you want pH, Viscosity and Density tests to appear in the dropdown of the field Test.
• When Application is selected in the field Test Type, you want Scrub Resistance, Adhesion and Tensile Elongation tests to appear in the dropdown of the field Test.
• When Microbiology is selected in the field Test Type, you want Fungal and Bacterial tests to appear in the dropdown of the field Test.

First, in the VALUES tab of the field Test, add all predefined values to the field Test (pH, Viscosity, Density, Scrub Resistance, Adhesion, Tensile Elongation, Fungal, Bacterial).

Second, write the Expression for conditional predefined values like in Figure 2.3 where the Values tab of the field Test is shown with the Expression entered.

The correct Expression is the following:

First line var a=[];  defines and initializes a variable in the code. Variable a is initialized as an empty array [].

When the first if condition if(TestType=='Characterization') is satisfied (i.e., field with identifier TestType has a value 'Characterization'), variable a can have only these values  {a=['pH','Viscosity', 'Density'];}. This means that when the user in the User Portal selects 'Characterization' in the Test Type, in the dropdown of the field Type will appear only ‘pH’, ‘Viscosity’, and ‘Density’. 

It goes the same for the second if condition if(TestType==’Application’), when variable a can have only one of these values {a=['Scrub Resistance', 'Adhesion', 'Tensile Elongation'];}. The same principle is for the third if condition.

Finally, there is the last part of the expression: a; that will return the value of variable a to the field Test Type and filter down the list of predefined values based on the satisfied condition.

Single Field

Another important and powerful functionality that you can use in every field type is Action - On Change. Each time the value of the field is changed, this functionality will execute a specific action. The action is added to the field which will be changed.

 In the Menu of the field, under tab ACTIONS, you can add a new action by clicking on the button.

Upon adding a new action you have to set On* to “Changed”. Under the Term you  write an Expression of action that should be executed when a user changes the value of the field. See the example below:

In section Button of the Configuration Portal Administrator Manual, there is an Example - Button recalculates all rows in the table. In this example, you learned how the user can compose the formulation, and how they can change the formulation and easily recalculate all amounts in the table with the press of a button, “Normalize”.

Thanks to the On Change action functionality, you can make this recalculation completely automatic - there is no need for a user to press the button “Normalize”. Each time user changes Target Batch Size L, all rows in the table (all amounts of materials added to the formulation) will be recalculated automatically.

In the Actions tab of the field Target Batch Size L add a New Action. Set the On* to “Changed” and under Term write the same calculation that is in the action script of a button “Normalize”:

It is important to note where you are setting this functionality. Try to take into account all possible scenarios that can happen from the user’s perspective regarding changing the field’s value because each time the value of the field is changed, an action will happen.

2.6.2.2 A Field in a Specific Row

As already explained, an Action can be added to the field. Each time a field's value is changed, an action will be executed.

This action can also be performed on a field in a specific row of the repeatable row. Specifying a row is done by using the keyword CurrentRow. Example - Button with Helper Field in the Configuration Portal Administrator Manual will be upgraded to explain this functionality. See the example below:

In the record template Work Plan, there is a table for defining new formulations: users can add rows, set names, and target properties for pH, Density, and Viscosity. With a click of a button, Formulation Records are created with the names defined in the table. The Formulation Record created with the button will have a name defined in an alphanumeric field, Experimental Formula Name (identifier ExperimentalFormulaName). Let’s say you want to configure an action that allows the user to change the name of the Formulation record by changing the field Experimental Formula Name in the table.

It was already explained that the Create Formulations button requires an additional record reference helper field. In Example - Button with Helper Field, the helper field is located outside of the repeatable row group. Now, in order to establish a link between each row in the table with the formulation created, you need to place a helper field inside a repeatable row.

Given the fact that the helper field is now inside the repeatable row, each line of the button script should be updated to access value for each row using  FormulaHelper[i], therefore the button script needs to be modified, like this:

Changing the name of the specific Formulation Record is done  by changing the field Experimental Formula Name in that specific row. 

In the field Experimental Formula Name (identifier ExperimentalFormulaName), in the tab Actions, add New Action - On Change with the following script:

The condition if(FormulaHelper[CurrentRow] IS SET) must be placed in order to apply On Change Action only for the already created Formulations.

Where FieldProperty is the keyword to access a specific property of the field defined by the FieldIdentifier. A specific property is getting the value defined by PropertyValue.

Conditional Formatting Expression can be placed on Formula Expression, Button Action Expression, Field Action - On Change, and Stage Action - Execute Script.

When the Action is triggered, the script will be executed and the field property will be updated. If you change the Conditional Formatting Expression in the Configuration Portal and back propagate - the field property will not be affected. It will remain as defined by the previous Conditional Formatting Expression.

2.6.3.1 Field Properties

Field Dimensions (Width, Height, and Margins) CANNOT be changed using Conditional Formatting.

Here is the list of all Field Properties that CAN be changed using Conditional Formatting. In the list of available field properties below, for each property there is an output type and possible values in parentheses: 

• Required - boolean (true or false)
• Editable - boolean (true or false)
• BorderTop - boolean (true or false)
• BorderBottom - boolean (true or false)
• BorderLeft - boolean (true or false)
• BorderRight - boolean (true or false)
• NameVisible - boolean (true or false)
• NameSize - number (any positive number)
• NameOrientation - text (TOP, BOTTOM, LEFT, RIGHT, CENTER)
• NameHorizontalAlignment - text (LEFT, CENTER, RIGHT)
• NameVerticalAlignment - text (TOP, MIDDLE, BOTTOM)
• NameFont - text (ROBOTO, TIMES_NEW_ROMAN, ARIAL, CONSOLAS)
• NameFontSize - number (any positive number)
• NameFontColor - hex code (refer to Alchemy Color Hex Codes)
• NameBackgroundColor - hex code
• NameItalic - boolean (true or false)
• NameBold - boolean (true or false)
• NameUnderline - boolean (true or false)
• NameStrikethrough - boolean (true or false)
• InputHorizontalAlignment - text (LEFT, CENTER, RIGHT)
• InputVerticalAlignment - text (TOP, MIDDLE, BOTTOM)
• InputFont - text (ROBOTO, TIMES_NEW_ROMAN, ARIAL, CONSOLAS)
• InputFontSize - number (any positive number)
• InputFontColor - hex code
• InputBackgroundColor - hex code
• InputItalic - boolean (true or false)
• InputBold - boolean (true or false)
• InputUnderline - boolean (true or false)
• InputStrikethrough - boolean (true or false)
• Hidden* - boolean (true or false)
• CellUnitSymbol** - text
• CellUnit - text

* Field property Hidden will apply the change for the whole field. If the field is in a repeatable row/column table all rows will be changed. It cannot change the state of individual rows. Other properties can be used if the field is inside a repeatable row/column. If you need to target a specific row inside a table, use the Current Row system data in the form:

** Field property CellUnitSymbol cannot override the unit of the field. It only returns the unit for printing purposes; example field with unit set to kg:

FieldIdentifier.CellUnit returns 'KILOGRAM'

FieldIdentifier.CellUnitSymbol returns 'kg'

‍

Examples

Let’s build on an already existing example - IFS conditions. In this example we have already explained how an alphanumeric field can show text IN RANGE or OUT OF RANGE depending on the entered value for pH, Density or Viscosity and defined target values (i.e. range). To make it more visually appealing, let’s say you need to configure the same alphanumeric field to be green when the entered value is in range and red when it is out of range. In the Formula Expression of the alphanumeric field (identifier pHRange) there should be this expression:

Let’s read and explain this script. In the first condition we are checking if entered number for pH value (in the field with identifier pH) is greater than defined start range limit (value defined in the field with identifier TargetpHfrom) and lower than defined end range limit (value defined in the field with identifier TargetpHto). If that condition is satisfied, the Conditional Formatting Expression will be executed by changing the field property InputBackgroundColor to green ('#93C480'). Also, the field will show the provided text: 'IN RANGE'. In the case that this condition is not met, the field property will be red ('#DF6969') and the field will get the value 'OUT OF RANGE'.