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 in the record templates

• In the Formula Expression for a field - calculates the value of the field based on the formula expression. The result must be of the same type as the field for which we write the expression;
• In the Visibility Expression for a field or a group - calculates the visibility of the field or a group based on the visibility expression. The result must be of the boolean type.
• In the Filter Criteria for a process, record, or user reference field, or for a repeatable group determined by record reference or 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.
• In the Predefined Values Expression for a text or number field with predefined values - define conditions when to show/hide some of the predefined values for a field, see examples here - the Conditional Predefined Values section.
• In the Action Expression under Actions for a field -  calls functions, calculates the value of fields, or updates fields parameters based on the action expression, see examples here - the Field Actions - On Change section.
• In the Action Expression for a button field -  calls functions, calculates the value of fields, or updates fields parameters based on the action expression, see examples here.
• In the Privileges Expressions (Can Create, Can Edit, Can View, Can Share) - defines the set of privileges for a list of users. The result must be a list of users.

Alchemy Script Expressions can be used in the process templates

• In the Privileges Expressions on the process and stage level - defines the set of privileges for a list of users. The result must be a list of users, see examples here and here.
• In the When expression for a stage privileges - defines the condition when a certain privilege set will be active. The result must be a boolean.
• In the Record Naming Pattern for a record on a stage - defines the record name based on the naming expression. The result must be a string, see the example here.
• In the Record Minimum and Maximum Records for a record on a stage - 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.
• In the When expression for a stage Actions - defines the condition when a certain action will be performed. The result must be a boolean.
• In the Assign To expression for a stage Create Task action - defines the list of users to be assigned to the task. The result must be a list of users.
• In the Task Subject expression for a stage Create Task action - defines the subject of the task. The result must be a string.
• In the Task Description expression for a stage Create Task action - defines the description of the task. The result must be a string.
• In the Number of Records expression for a stage Create Record action - defines the number of records to create on the action. The result must be a number.
• In the Notification Recipients expression for a stage Create Notification action - defines the list of users to be sent the notification. The result must be a list of users.
• In the Notification Subject expression for a stage Create Notification action - defines the subject of the notification. The result must be a string.
• In the Notification Description expression for a stage Create Notification action - defines the description of the notification. The result must be a string.
• In the Recipients expression for a stage Send Email action - defines the list of users to receive the email. The result must be a list of users.
• In the Subject expression for a stage Send Email action - defines the subject of the email. The result must be a string.
• In the Description expression for a stage Send Email action - defines the description of the email. The result must be a string.
• In the Action Expression for a stage Execute Script action -  calls functions, calculates the value of fields, or updates fields parameters based on the action expression.

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 number is saved in Alchemy with as many decimals entered in the field by the user or calculated, and it will be used in calculations as such (e.g. 12.3, 5+6.097)
• Boolean - Logical values, true and false (written 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 (Zoned DateTime). The different time and date formats are defined in the Configuration Portal Manual (Chapter Date & Time).
• User reference - A link to a User of 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. They can be used as connections to access configuration and system fields for the records they are referenced to. These are record reference objects that can either be accessed using expressions or configured record fields. 
• Process References -  A link to the specific process. They can be 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 

Possible elements of the formula expressions are graphically represented in Figure 2.1.

Constants are values (not fields) that are entered directly into the Alchemy Script Expression and do not change when the context is changed.
They can be:

• String - use single quotes (' ') to indicate the string constant. Example: 'abc' 'a10' '154.3$'
• Number - use numbers with or without decimal places. Example: 10, 7.5, -4.0000
• Boolean - use boolean terms. Example: true, false

Note that other data types (mainly references) cannot be used as constants, as such data always depends on the context in the user portal.

As variables in Alchemy Script Expressions we can use field identifiers from record templates or system data fields. The system evaluates variables every time the calculation is triggered. 

Each field type value results in already defined data types: 

• Field type Alphanumeric, RichText, Link, field values are treated as a string. 
• Field type Number field values are treated like a number
• Field type Yes/No, Checkbox field values are treated as a boolean
• Field type Date and Time field values are treated as a DateTime
• Field type Record field values are treated as Record reference
• Field type Process field values are treated as Process reference
• Field type User field values are treated as User reference

To use fields from a record in the same record:

• If a variable is used in the expression placed in the same record where the expression is written, then you can define that variable in the expression using only the field identifier.  Example of the identifier of a field: 

VariableFieldIdentifier

When you want to use a variable (a field value), from one record, in an expression placed in a different record, you will need to access them through record references. In general, you can access the field using the following expression:

Record References from the same Process

You can access all records created in the scope of the current process template using  Record[RecordTemplateIdentifier] in an expression:

• Record[RecordTemplateIdentifier].VariableFieldIdentifier

Record - Keyword representing which concrete record should be accessed. Records are sorted by the time of their creation. By using  [RecordTemplateIdentifier] in expression, Alchemy will look for and take the value of the last created record in the same process.

If the field is in the record template that is used in multiple stages, you need to specify which stage you want to pull the data from by adding the Stage[StageName] part to your expression

Stage[StageName].Record[RecordTemplateIdentifier ].VariableFieldIdentifier

Record References from other Processes

To access and perform calculations with fields located in a record in another process you will need to configure a field where the users can select either the record reference directly (record type field) or a process reference from where you want to access the record (process type field). 

In case you configure the record field, its identifier will be used as the record reference in your expressions:

RecordFieldIdentifier.VariableFieldIdentifier

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

ProcessField.Record[RecordTemplateIdentifier].VariableFieldIdentifier

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.

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:

RecordVariable.Process

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

RecordVariable.Process.Status

will return the string representing the status of the process in which this record is created. This could be useful filter criteria, i.e. 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.

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 next chapters.

Process Data

You can get some data about the process from the system fields listed below using the keyword Process or the process template identifier. In order to get process data you need to create a field (alphanumeric, number, datetime or user field, based on the type of data from the system field that you want)  in which you will write the expression in the Formula Expression field. The process data that you want can be related to  the current process or some other process in the system:

1. If you want the system data from the current process, you will write Process.SystemFieldIdentifier in the Formula Expression of the created field, where Process is the keyword for the current process instance.

2. If you want the system data from another process, you need to create a process field type where the User on the User Portal will choose the exact process. Then you need to create the field (alphanumeric, number, datetime or user field, based on the type of data from the system field that you want) in which you will write ProcessFieldIdentifier.SystemFieldIdentifier in Formula Expression, where ProcessFieldIdentifier is the identifier of the process type field.

System fields that could be placed in the Formula Expression of the alphanumeric field - their output is a string:

• Name: the name of the process
• Template: name of the process template of the target process
• Status: current status of the process, can be one of these four possible values: In Progress, Voided, On Hold, Completed
• CurrentStage: currently active stages in the target process, formed as a comma-separated list of active stage names (i.e. Requirements, Lab Test, Sample Request). If the process is completed, on hold or voided, it will return the status.

System fields that could be placed in the Formula Expression of the number field - their output is a number

• Priority: current priority of the target process; if the process has no priority, it is set to 0. 
• FinalDuration: Return process duration in days, hours, minutes and seconds after the process is identified as status Completed or Voided. If the process is not completed, return an empty value. Duration is a number representing the number of milliseconds. 
• TotalEffortSpent: total effort spent on the process, in hours. It is the sum of all the effort logged on all the tasks in the process. Every time the effort spent is updated on the task linked to the process, this number is updated.

System fields that could be placed in the Formula Expression of the datetime field - their output is date and time:

• CreatedOn: date and time when the process was created 
• CompletedOn: date and time when the process was completed; the default value is empty.

System fields that could be placed in the Formula Expression of the user field - their output is the user's first and last name:

• CreatedBy: the user that has created the process

Stage Run Data

You can get any of the data about the stage run from the system fields listed below using the stage identifier. You need to create a field (alphanumeric, number, date/time or user field, based on the type of data from the system field that you want) in which you will write the expression in the Formula Expression field. 

1. To get the information for the stages in the current process, the expression is:  Stage[StageIdentifier].SystemFieldIdentifier. If you don't specify the stage run after the StageIdentifier, the system will pull back information from the last run. Stage[StageIdentifier, 1].SystemFieldIdentifier will access the first run, Stage[StageIdentifier, 2].SystemFieldIdentifier will access the second run, etc.
2. To get the information from another process, similar to the process data, first you need to select the process from which you would like to pull information (from a process reference field type), then create an appropriate field for the data and write in the calculation formula the expression: ProcessFieldIdentifier.Stage[StageIdentifier].SystemFieldIdentifier

System fields that could be placed in the Formula Expression of the alphanumeric field - their output is a string:

• Name: name of the stage

System fields that could be placed in the Formula Expression of the yes/no and checkbox fields, their output is a boolean:

• Completed: current status of the stage run, returns true if completed

System fields that could be placed in the Formula Expression of the number field - their output is a number:

• Run: stage run number
• TotalEffortSpent: total effort spent for the stage run, in hours. It is the sum of all the effort logged on the tasks linked to the stage run. Every time the effort spent is updated on the task linked to the stage run, this number is updated.

System fields that could be placed in the Formula Expression of the datetime field - their output is date and time:

• CreatedOn: date when the stage was created 
• CompletedOn: date when the stage was completed, datetime data type

System fields that could be placed in the Formula Expression of the user field - their output is user's first and last name:

CompletedBy : user that has completed the stage run

Record Data

You can get some data about the record from the system fields listed below using the record field identifier or record template identifier. You need to create a field (alphanumeric, number, date/time or user field, based on the type of the data from the system field that you want) in which you will write the expression in the Formula Expression field. You can access the record data that you want if the record is in the current process or if the record is from another process:

• If you want the system data about the record from the current process, you will write Record[RecordTemplateIdentifier].SystemFieldIdentifier in the Formula Expression of your field. You can use this same syntax for any other field, not just the system field. The system will pull back information from the last created record. 
• If you want the system data about the record from another process, you need to create a record field type where the User will choose the exact record in the User Portal. In another field where you want to pull the data (alphanumeric, number, datetime or user field, based on the type of data from the system field that you want) you will write RecordTypeFieldIdentifier.SystemFieldIdentifier in Formula Expression, where the  RecordTypeFieldIdentifier is the identifier of the record field type.

System fields that could be placed in the Formula Expression of the alphanumeric field - their output is a string:

• Name: name of the record 
• Template: name of the record template of the target record
• Status: record status, string data type, values can be Valid or Invalid

System field that could be placed in the Formula Expression of the datetime field - its output is date and time:

• CreatedOn: date and time when the record was created

System field that could be placed in the Formula Expression of the user field - its output is the user's first and last name:

• CreatedBy: the user that created the record

System field that could be placed in the Formula Expression of the process field - its output is the process reference:

• Process: Reference to a process the record is linked to
• Stage: Reference to a stage run the record is linked to

System field that could be placed in the Formula Expression of the number field - its output is the number:

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

User Data

You can get some of the data about the users from the system fields listed below using the user field type identifier. First, create a user field type. In order to get user data, you need to create an alphanumeric field in which you will write in the Formula Expression field: UserFieldTypeIdentifier.SystemFieldIdentifier

System fields that could be placed in the Formula Expression of the alphanumeric field type:

• 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

Accessing System Data - Summary

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. 

To summarize - accessing system data can be done by following the logic below: 

• Process data can be accessed as:

• Process.CreatedBy - Used to access the “created by” property of the current process
• ProcessReferenceField.CreatedBy - Used to access the “created by” property of the process referenced in the process reference field

This approach can be used for all the Process data.

• Stage run data can be accessed as:

• Stage[RequestReview].CreatedOn  - will access the last run of the request review stage in the current process. In this example, the CreatedOn value of the last stage run is accessed. 
• Stage[RequestReview, last].CreatedOn - can also be used

To access a specific stage run:

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

• Record data can be accessed as:

• Stage[RequestReview].Record[Approval].CreatedOn - will access the last record based on the Approval record template from the last stage Request Review 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 - will access the first record based on the Approval record template from the last stage Request Review run. In this example, the CreatedOn value of the first record created in the last stage run is accessed.
• Record[Approval, 1].CreatedOn - will access the first record of type Approval in the whole process

Record[Approval, last].CreatedOn - will access the last record of type Approval in the whole process

AND - operator checks two Boolean expressions and only returns true if both of the expressions are true.

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

Examples: 

• (6 > 3) AND (2 < 5) - The output is true because both of the expressions are true.
• (‘text’ == ‘tekst’) AND (3 < 5) - The output is false because only the second expression is true.

OR  - operator combines two Boolean expressions and returns true if at least one of the expressions is true.

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

Examples:

• (ProductPrice < ProductRecommendedPrice / 2) OR (ProductPrice > 0)
  - 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 (the second one). Note that the expression would still return true if both statements had been true.
• ('text' == 'nottext') OR ('ciao' == 'stillnotciao') - The expression will return false, because both expressions are false.

‍

NOT - operator negates the specified Boolean expression. Returns true if the operand is false, false otherwise.

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

Example:

• NOT('text' == 'hallo') - ​​The expression will return true.
• NOT(true) - The expression will return false.

IN - Checks whether all the values of the left operand are all contained in the right operand. 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, 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:

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

NOT IN - Negation of IN. 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, returns false
• If the right operand is empty, returns true

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

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

•  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:

• ModificationsApplied IS SET - ​​The expression will return true if the value for the field ModificationsApplied is set.

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

• 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

Examples:

• FieldIdentifier NOT SET

Example:

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

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

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

Examples:

• FieldIdentifier IS VALID

NOT VALID - Checks if there is any value error at the field template. Returns true if there is a value error, false otherwise.

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

Examples:

• FieldIdentifier NOT VALID

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, false otherwise (null value is treated as 0).

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

- Examples:

• 5 > 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

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

• 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, false otherwise.

• 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 ( <= ) - 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, false otherwise.

• 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, false otherwise.

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

• 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.

• Output type: boolean 

- Example:

• a == b

Is Not Equal To ( != ) - Determines whether the two values are not equal Works like the exact opposite of is equal to operator.

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

- Example:

• a != b

Assign operator ( = ) - it 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 chapter 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 ( + ) - Adds two numbers or concatenates two strings, depending on operand type.

• Keyword: +
• Supported operand 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.

• Output type: string, number

- Examples:

• NumberFieldA + NumberFieldB - returns a number;
• TextFieldA + ' : ' + TextFieldB - returns a string;
• RecordIdentifier.NumberFieldIdentifier + 3.5 - returns a number;

Subtraction ( - ) - Subtracts the second operand from the first.. 

• If two numbers are subtracted, the resulting number is the result of subtraction of the second operand from the first operand. 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.

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

- Examples: 

• NumberFieldA - NumberFieldB - returns a number;
• NumberFieldA - 1 - returns a number;
• EndDate-StartDate (the subtraction must happen in a number type field) - returns a number;

Multiplication ( * ) - Multiplies two numbers.

• If two numbers are multiplied, the resulting number is the result of the multiplication of the two operands. 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.

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

- Examples: 

• NumberFieldA * NumberFieldB - returns a number;
• NumberFieldA * 3 - returns a number;

Division ( / ) - Divides two numbers.

• If two numbers are divided, the resulting number is the result of dividing the left operand with the right operand. 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.

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

- Examples:

• NumberFieldA / NumberFieldB  - returns a number;
• NumberFieldA / 3  - returns a number;

Negation ( # ) - Changes the sign of the number. Transfers it from negative to positive or vice versa. 

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

- Examples: 

• #(-2) - the result is 2;
• #NumberFieldA - returns a number;

‍

MOD - 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: MOD(a%b)
• Supported operand type: number
• Output type: number

Example:

• MOD(23%5) - the result is 3;

Please note: all the 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 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...)- Checks whether one or more conditions are met and returns the value that corresponds to the first true condition. Accepts parameters in pairs, the first parameter in pair 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 we are using the IFS function. The first parameter is considered an expression before the comma, and the second one is after the comma.

• If the first parameter in a pair evaluates to true, 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.

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

- Examples:

• IFS ( a < b, a, a> b, b) - returns a if a<b is true, if a<b is false it will check if a>b, and if it is true and it will return b;
• IFS ( f <=5, d * 3, (f +2 < c), IFS (f < c, 4, e>=d, d), a = b, c, f >= 250, d + 1) - example of nested IFS functions, if the first condition (f<=5) is false and the second condition (f +2 < c) is true then it will start evaluating second IFS function conditions.

‍

In the Configuration Portal Administrator Manual chapter 4.6.3 Formulas in Calculations, there is an example of function IFS.

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

• If the operand is negative, it returns an error. 
• If performed on an empty value, returns 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.

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

- Examples:

• SQRT(NumberField) - returns a number;
• SQRT(NumberFieldA + NumberFieldB) - returns a number;
• SQRT(144) - the result is 12;

‍

POW ()- A function that calculates the result of a number raised to a power. 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:

• POW(NumberFieldA, 2) - returns a number;
• POW(3*NumberFieldA, 3) - returns a number;
• POW(10, 2) - the result is 100;

ABS () - Returns the absolute value of a number or the operand (value without the sign).

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

• Examples:

• ABS(NumberFieldA) - returns a number;
• ABS(-15) - the result is 15;

SUM () - Returns the sum of all the values of a multiple value field or all the values in a column. Adds all the values of the specified field or constant.

• 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, 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.

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

- Examples:

• SUM(15) - the result is 15;
• SUM(NumberFieldA) - returns a number;

‍

LOG () - calculates the logarithm (base 10) of the value in the argument.

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

- Examples:

• LOG(100) - the result is 2;
• LOG(NumberFieldA) - returns a number;
• LOG(NumberFieldA/NumberFieldB) - returns a number;

‍

COUNT () - 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.

• 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.

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

- Examples:

• COUNT(15) - the result is 1;
• COUNT(FieldIdentifier) - returns a number;

‍

ROW_COUNT () - Counts rows or columns of a certain field. 

-  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.

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

- Examples:

• ROW_COUNT(FieldIdentifier)  - returns a number;

‍

AVERAGE ()- 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. Calculates the average of the specified number field or constant.

• 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).

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

- Examples:

• AVERAGE(15) - the result is 15;
• AVERAGE(NumberField)  - returns a number;
  
  

TODAY () - returns the current date, in UTC, can be parameterized optionally.

• 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.

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

- Examples:

• TODAY ()- returns current UTC date and time, i.e. 2022-04-06T20:40:50Z;
• TODAY(2) - returns a day after tomorrow's UTC date and time, i.e 2022-04-08T20:40:50Z;
• TODAY(0, -1, 0) - returns a UTC date and time that is one hour in the past, i.e. 2022-04-06T19:40:50Z

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

• 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.

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

- Examples:

• DATE_FORMAT(ProjectStartDate, 'YY-MM-DD') - the result is a text representation of the date value of ProjectStartDate field, i.e. 22-04-06
• DATE_FORMAT(Process.CreatedOn, 'EEEE, MMMM d, y')  - the result is a text representation of the date value of Process.CreatedOn system data, i.e. Wednesday, April 6, 2022;

‍

ROUND () - 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).

• 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.

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

- Examples:

• ROUND(12, 2) - the result is 12.00;
• ROUND(12.5, 0) - the result is 13;
• ROUND(NumberField, 4) - returns a number with 4 decimal places;
• ROUND(NumberFieldA, NumberFieldB) - returns a number with the number of decimals determined by the value of NumberFieldB; 

‍

TRANSITION_FROM () - Checks whether the activation path is coming from the selected stage. Returns true if stage activation is coming from the specified stage, false otherwise. 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:

• TRANSITION_FROM(Stage[Requirements]) - the result is true if the stage is activated from the stage Requirements, otherwise it will return false;

HAS_ANY_TAG () - Returns a list of users that have any of the supplied tags.

• If no tags are supplied a warning is generated.
• If the same tag is passed multiple times a warning is generated.

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

- Example:

• HAS_ANY_TAG(Tag[SalesDirector], Tag[President]) - returns a list of users who have the tag SalesDirector or the tag President;
  ‍

HAS_ALL_TAGS () - Returns a list of users that have all of the supplied tags.

• If no tags are supplied a warning is generated.
• If the same tag is passed multiple times a warning is generated.

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

- Example:

• HAS_ALL_TAGS(Tag[SalesDirector], Tag[VPMarketing]) - returns a list of users who have the tag SalesDirector and the tag VPMarketing;

‍

INTERSECT () - 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

- Examples: 

• INTERSECT(HAS_ALL_TAGS(Tag[Thermosets], Tag[TechnicalService]), HAS_ANY_TAG(Tag[Manager], Tag[Representative])) - returns a list of users who have the tags Thermosets and TechnicalService, and have the tag Manager or Representative;

‍

UNION () - 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

- Examples: 

• UNION(HAS_ANY_TAG(Tag[Chemist], Tag[SynthesisLab]), HAS_ALL_TAGS(Tag[LabManager])) - returns a list of users who have the tag Chemist or SynthesisLab or LabManager;

‍

CREATED_BY () - Determines if the process has been created by supplied users. Returns true if one of the supplied users has created the process, false otherwise.

• Keyword: CREATED_BY
• Supported operand type: list of users
• Output type: boolean

- Example:

• CREATED_BY(Tag[SalesDirector]) OR CREATED_BY(Tag[LabManager]) - returns true if the process was created by a user with tag SalesDirector or LabManager, otherwise it returns false;

‍

PATTERN () - 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
• Pattern:

• N - for a single digit
• 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 '-', '/', '.', '_', whitespaces,  and ':' 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. 

• Supported operand type: string
• Output type: string

- Examples:

• 'Sample ' + PATTERN('ANN') - the first instance returns text ‘Sample A01’;
• 'Shipment ' + PATTERN('YYYY-MM-DD-NN') - the first instance returns text, i.e. 'Shipment 2022-04-06-01’;

KEY_PATTERN () - Returns an auto-incremented pattern based on the supplied pattern in the operand

• The first parameter defines the prefix (in further text KEY) for the pattern, and the prefix can be 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 constant or field value.

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

- Examples: 

• TextField + KEY_PATTERN(Textfield, ’NNN’) - returns the pattern value together with the KEY
• KEY_PATTERN(TextField, ’N’) - returns only the pattern value
• KEY_PATTERN('Text', 'NN', '23') - returns pattern value starting from 23

The value of KEY_PATTERN will reset with the change of KEY. If the configurator decides to return the previous KEY in the calculation, the pattern will continue to calculate from the last value that KEY returned before the change is made.

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 () - Determines if the stage has been completed by supplied users. 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:

• STAGE_COMPLETED_BY(Tag[SalesDirector]) - returns true if the stage was completed by a user with tag SalesDirector;

‍

RUN_COUNT () - Returns the number of runs the given Stage has

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

- Examples:

• RUN_COUNT(Stage[Review]) - returns the number of runs for the stage Review;

CONVERT UNIT - Convert 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: 
  ‍
  var a = { “unitType”: { “value”: unitTypeValueVariable, “unit”: 
  unitTypeUnitVariable } } 
  CONVERT_UNIT (ValueToConvertField, UnitField, UnitFieldToConvert, a);

- Examples: 

• CONVERT_UNIT(ValueToConvertField, UnitField, UnitFieldToConvert); 
  var a = { "density": { "value": DensityValueVariable, "unit": DensityUnitVariable } } 
  CONVERT_UNIT(100, ‘g’,’ml’, a);

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 (in buttons, in stage execute script and in On Change action scripts), not in formula or visibility expressions:

SIGNED_IN_USER () - Returns the REFERENCED_USER which is signed in.

• Function only available to use in action expression of the button field or on the field action

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

- Examples:

• UserField = SIGNED_IN_USER() - returns the user who is signed in;

CREATE_PROCESS () - Function that 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

- Examples:

• CREATE_PROCESS('LabWork',1) - creates a new process from the Lab Work process template and returns the created process;

‍

CREATE_RECORD () - Function that 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.  

• 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

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

- Examples:

• CREATE_RECORD(‘Formulation’, ‘LabWork’, Process,1) - creates a new Formulation record in the Lab Work stage of the current process and returns the created record;
• CREATE_RECORD(Material, 1) - creates a new Material solo record and returns the created record;

ADD_ROW () - Function for adding a row in a group. 

• 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

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

- Examples:

• ADD_ROW(GroupIdentifier, 1) - adds one row to the group determined by the GroupIdentifier;
• ADD_ROW(GroupIdentifier, Group1.RowCount) - adds the number of rows determined by the number of rows in Group1 to the group determined by the GroupIdentifier;

GET_RECORD () - Function returns referenced record value, depending on passed record ID

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

- Examples:

• GET_RECORD(IdOfRecord) - returns the record with ID determined by the field IdOfRecord;
• GET_RECORD(5) - returns the record with ID 5;

CREATE_TASK () - Creates a new task on the defined stage. Function 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/user s)

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)

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

- Examples:

• CREATE_TASK(Process.Stage[LabWork], 'Title for the Task From a Script', 'This is a description for the task from a script', TODAY(), DateTimeField, 1, HAS_ANY_TAG(Tag[SalesDirector])) - returns a new Task;

CREATE_COMPARE () - Creates a new Compare Records. Function 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

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

- Examples:

• CREATE_COMPARE(Process.Stage[LabWork], ‘LabRecord’, [‘NumberField’, ‘TextField’], [‘TextField’], Record[LabRecord]) - returns a Compare Records;
• CREATE_COMPARE(Process.Stage[LabWork], ‘LabRecord’, [‘NumberField’, ‘TextField’], [‘TextField’], RecordField) - returns a Compare Records;

CREATE_VIEW () - Creates a new View in a stage, from the View Template with predefined fields and filters. Function 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.

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

- 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.

- param1 is the email recipient. Can be email address as string, or any expression returning user reference

- param2 is the subject of email (string)

- param3 is the body of email (string)

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

- Examples:

• SEND_EMAIL(‘const1@mail.com’, ‘mail subject’, ‘mail body’);
• SEND_EMAIL(‘const1@mail.com, const2@mail.com’, ‘mail subject2’, ‘mail body2’);
• SEND_EMAIL([‘const3@mail.com, const4@mail.com’], ‘mail subject3’, ‘mail body3’);
• SEND_EMAIL(UserField.Email, ‘mail subject4’, ‘mail body4’);
• SEND_EMAIL(UserField, RecordName + ‘ send as user 5’, RecordName + ‘ 5’);
• SEND_EMAIL(UserFieldInGroup, RecordName + ‘ send as a user in group 6’, RecordName + ‘ 6’);
• SEND_EMAIL(HAS_ANY_TAG(Tag[SalesDirector]), ‘mail subject 7’, ‘mail body7’);

‍

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:

• if(CustomerType == ‘Prospect’){ NewCallDate = TODAY (4); } - only if the value of the CustomerType field is Prospect, then it will set the value of the NewCallDate to the 4 days from now;
• if(NumberA > 3){ CalcNumber = NumberA*3.14; }
  else { CalcNumber = NumberA/3.14; } - if the value of the NumberA field is bigger than 3, then the value of the CalcNumber will be calculated from the value NumberA * 3.14, else  the value of the CalcNumber will be calculated from the value NumberA / 3.14

‍

for() {} - A For loop is used to define number of executions (iterations) of defined  code block (calculations/commands). It is used to access values of fields with multiple values or fields in repeatable groups.
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

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

- Example:

• for(var i=NumberField; i<=5;i=i+2){code bloc}
• for(var i=1; i<=Group1.RowCount;i=i+2){code bloc}

RECALCULATE_PRIVILEGES() - this function can be used in action script 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: 
• Output type: none

- Examples:

• RECALCULATE_PRIVILEGES(Process) - initiates access rights recalculation for the active process;

CREATE_LINK() -  Function is used in calculation of the link type field to generate a new 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. 

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

- Examples:

• CREATE_LINK('https://www.alchemy.cloud/alchemy-knowledge-center/home', 'Alchemy Knowledge Center')

CREATE_LINK('https://pubchem.ncbi.nlm.nih.gov/#query=' + CasNumber , 'PubChem Link') - where CASNumber is an identifier of an alphanumeric field in the same record template where the link field is. The output link is a dynamic value that depends on the CASNumber entry.

SIN() - calculates the SINE of the given angle

• 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

- Examples:

• SIN(FieldIdentifier) - returns a number;

COS() - calculates the COSINE of the given angle

• 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:

• COS(1.047) - the result is 0.500171;
• COS(RADIANS(60)) - the result is 0.5

TAN() - calculates the TANGENT of the given angle

• 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

- Example:

• TAN(0.785) - the result is 0.99920;
• TAN(RADIANS(45)) - the result is 1;

COT() - calculates the COTANGENT of the given angle

• 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:

• COT(30) - the result is -0.156119952161659;

ASIN() - 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

- Example:

• ASIN(-0.5) - the result is -0.523598776;
• DEGREES(ASIN(-0.5)) - the result is -30;

ACOS() - 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

- Example:

• ACOS(-0.5) - the result is 2.094395102;
• DEGREES(ACOS(-0.5)) - the result is 120;

ATAN() - 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

- Example:

• ATAN(1) - the result is 0.785398163;
• DEGREES(ATAN(1)) - the result is 45;

ATAN2() from X- and Y-coordinates - calculates the ARCTANGENT of the specified x- and y-coordinates (inverse TANGENT).

• 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:

• ATAN2(1, 1)
• DEGREES(ATAN2(-1, -1))

ACOT() - 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:

• ACOT(2)

DEGREES()  - converts angle given in radians to angle in degrees

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

- Example:

• DEGREES(0.785398163) - the result is 45;

‍

RADIANS() - converts angle given in degrees to angle in radians

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

- Example:

• RADIANS(270) - the result is 4.712389;

LN() - 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.

• Given number must be a positive real number.

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

- Example:

• LN(86)

EXP() - calculates 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:

• EXP(1)

CEILING() - rounds a number up, to the nearest integer

• parameter is a number or a value of number field identifier which you want to round up

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

FLOOR() - rounds a number down, to the nearest integer

• parameter is a number or value of number field identifier which you want to round down

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

IS_DIVISIBLE() - checks if two numbers are divisible without the remainder

• Number1 is dividend, number2 is divisor

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

Example:

• IS_DIVISIBLE(8,2) - returns true;

GEOMEAN() - calculates 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() - Transforms given string in URL code

• This function will replace non-alphanumeric characters with the percentage symbol and a hexadecimal number

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

- Example:

• ENCODEURL(‘string’)

LEFT() - Gives the leftmost characters from a text value

• 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

• Keyword: LEFT(Text, numberOfCharacters)
• Supported operand type: text
• Output type: 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 Work Plan, there is also a button that creates a Formulation record and gives it a name - fills 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 calculation: LEFT(FormulationName, 12). This way, the VL Number field will get the value ‘VL1012C0001’.

‍

RIGHT() - Gives the rightmost characters from a text value

• 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

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

- Example:

• RIGHT(FieldIdentifier,4)

‍

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

• Not case-sensitive.
• 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

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

- Example:

• SEARCH(AlphanumericField, AlphanumericSerchField). AlphanumericField has the value "Lorem ipsum dolor sit amet, consectetur adipiscing elit" and AlphanumericSearchField has the value "dol". The result is 13 which is the position of “d” from “dolor”

REPLACE() - Replaces characters within the 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.

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

‍

SUBSTITUTE() - Substitutes new text for old text in a string

• 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. NumberOfOccurences is optional and defines how many occurrences to replace. If not specified, the function will substitute all the occurrences.

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

‍

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

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

- Example:

• LOWER(FieldIdentifier) - returns a text all in lower case;
• LOWER(‘Yield’) - the result is ‘yield’;

UPPER() - converts a text string to uppercase

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

- Example:

• UPPER(FieldIdentifier)  - returns a text all in lower case;
• UPPER(‘total amount’) - the result is ‘TOTAL AMOUNT’;

FORMAT_STRING() - Gives a formatted string made from a string with placeholders and values passed to fit in the placeholders. 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)

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

• STRING_FORMAT(Instruction, Ingredient, Phase) - This calculation is placed on an alphanumeric field with identifier Procedure. Instruction, Ingredient, and Phase are alphanumeric fields. User must enter a value for field Instruction in the following format: ‘Mix {1} than add {2}’. Number 1 in the curly brackets is where the value of field  Ingredient will be placed in the text. Number 2 in the curly brackets is where the value of field Phase will be placed in the text. There should be a single number in the curly brackets without any other characters.

MID() - Trims the given number of characters from the original string from the left and from the right side

• 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

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

RANDOMIZE - function generates random string according to the provided string length, it returns array of characters

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.

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

RANDOMIZE(length, isNumber)

P_CORREL() - 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.

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

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

‍

S_CORREL() - calculates the Spearman correlation coefficient.

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

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

‍

MAX() - Gives the largest value in a set of values

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

‍

MIN() - Gives the smallest value in a set of values

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

‍

MEDIAN() - 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() - Gives the most frequently occurring single value in a data set 

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

‍

MODE_M() - 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()  - Calculates the k-th percentile of values in a data set; where k is in the range from 0 to 1, excluded

• percentileNumber is k

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

‍

STDEV() - Calculates standard deviation based on the sample of population

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

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

• 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

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

‍

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

• 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

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

- Example:

• DATE_ADD(DateTimeFieldIdentifier, 3, 'MONTH') - returns a date & time 3 months from the date provided in the field DateTimeFieldIdentifier;

‍

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

• dateValue is the given date for which you want to calculate the year
• dateSegmentKeyword is 'YEAR', 'MONTH', 'MONTHDAY' or 'DAY'

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

- Example:

• DATE_GET(DateFieldIdentifier, 'YEAR') - returns a year for the date provided in the field DateFieldIdentifier;
• DATE_GET(CreatedOn, 'MONTH') - if the value of the field Created On is 25th September 2021, the result will be 9.
• DATE_GET(CreatedOn, 'MONTHDAY') - if the value of the field Created On is 25th September 2021, the result will be 25;
• DATE_GET(CreatedOn, 'DAY') - if the value of the field Created On is 25th September 2021, the result will be 268;

‍

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

• dateValue is the given date for which you want to calculate the day of the week
• dateSegmentKeyword is 'WEEKDAY'

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

‍

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

• dateValue is the given date&time for which you want to calculate the hour
• dateSegmentKeyword is 'HOUR'

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

‍

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

• dateValue is the given date&time for which you want to calculate the minute
• dateSegmentKeyword is 'MINUTE'

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

‍

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

• dateValue is the given date&time for which you want to calculate the second
• dateSegmentKeyword is 'SECOND'

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

‍

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

• dateValue is the given date&time for which you want to calculate the millisecond
• dateSegmentKeyword is 'MILLISECOND'

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

‍

NUMBER OF THE WEEK - returns a weak 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...

• dateValue is the given date&time for which you want to calculate the number of the week
• dateSegmentKeyword is 'WEEK'

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

‍

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

• dateValue is the given date&time for which you want to calculate the quarter of the year
• dateSegmentKeyword is 'QUARTER'

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

‍

END_OF() - 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.

• dateValue is the given date&time
• TimeUnit can be 'MONTH', 'WEEK', 'QUARTER' or 'YEAR'

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

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() - 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. 

- param1 is the local variable of the type array

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

• 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.

- Example:

• var a = [1, 2, 3, 4];

APPEND(a, 5); - the result is array a = [1, 2, 3, 4, 5]

• var a = [1, 2, 3, 4];

APPEND(a, [5, 6]); - the result is array a = [1, 2, 3, 4, [5, 6]]

‍

APPEND_ALL() - Adds element(s) 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.

- param1 is the local variable of the type array

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

• 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.

- Example:

• var a = [1, 2, 3, 4];

APPEND_ALL(a, 5); - the result is array a = [1, 2, 3, 4, 5]

• var a = [1, 2, 3, 4];

APPEND_ALL(a, [5, 6]); - the result is array a = [1, 2, 3, 4, 5, 6]

‍

INSERT() - 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. Array size will be increased by one.

- 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

• 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. 

- Example:

• var a = [1, 2, 3, 4];

INSERT(a, 2, 11]); - the result is array a = [1, 11, 2, 3, 4]

• var a = [1, 2, 3, 4];

INSERT(a, 2, [5, 6]); - the result is array a = [1, [5, 6], 2, 3, 4]

‍

INSERT_ALL() - Inserts element(s) to the exact position in the 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

• 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. 

- Example:

• var a = [1, 2, 3, 4];

INSERT_ALL(a, 2, 13]); - the result is array a = [1, 13, 2, 3, 4]

• var a = [1, 2, 3, 4];

INSERT_ALL(a, 2, [5, 6]); - the result is array a = [1, 5, 6, 2, 3, 4]

‍

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

- 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

• 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. 

- Example:

• var a = [1, 2, 3, 4];

REMOVE(a, 2); - the result is array a = [1, 3, 4]

• var a = [1, 2, 3, 4];

REMOVE(a, [2, 4]); - the result is array a = [1, 3]

‍

RESIZE() - Resizes the array to the defined size.

- 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)

• 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.

- Example:

• var a = [1, 2, 3, 4];

RESIZE(a, 3]); - the result is array a = [1, 2, 3]

• var a = [1, 2, 3, 4];

RESIZE(a, 6); - the result is array a = [1, 2, 3, 4, null, null]

‍

SUBARRAY() - 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, returned subarray will be between from position and the last element of the main 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)

• 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)

- Example:

• var a = [1, 2, 3, 4];

var b = SUBARRAY(a, 2, 3); - the result is array b = [2, 3]

• var a = [1, 2, 3, 4];

var b = SUBARRAY(a, 2, 8); - the result is array b = [2, 3, 4]

‍

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

- param1 is 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

•  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) 

- Example:

• var a = [1, 2, 3, 4];

CONTAINS(a, 2); // returns true

• var a = [1, 2, 3, 4];

CONTAINS(a, 7); // returns false

• var a = [1, 2, 3, 4];

CONTAINS(a, [1, 2]); // returns false

• var a = [1, 2, 3, 4];

a[3] = [4, 5, 6]; // array a now has on position 3 array [4, 5, 6] 

CONTAINS(a, [4, 5, 6]]); // returns true

• var s = {'key1': 'description1', 'key2': 'description2'};

CONTAINS(s, 'key1'); // returns true

CONTAINS(s, 'key5'); // returns false

• var s = {'key1': 'description1', 'key2': 'description2'};

s[‘key3’] = ‘new description’; // struct is now updated {'key1': 'description1', 'key2': 'description2', 'key3': 'new description'}

• var s = {'key1': 'description1', 'key2': 'description2'};

s[‘key1’] = ‘demo’; // struct is now updated {'key1': demo, 'key2': 'description2'}

‍

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

- param1 is the local variable of type array

- param2 specifies elements to be checked, 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)

- Example:

• var a = [1, 2, 3, 4];

CONTAINS_ALL(a, [1, 2]); // returns true

• var a = [1, 2, 3, 4];

CONTAINS_ALL(a, [4, 5, 6]); // returns false

‍

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

- param1 is local variable of type array

- param2 specifies elements to be checked, 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)

- Example:

• var a = [1, 2, 3, 4];

CONTAINS_ANY(a, [1, 2]); // returns true

• var a = [1, 2, 3, 4];

CONTAINS_ANY(a, [4, 5, 6]); // returns true

‍

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

- param1 is local variable of type array

- param2 specifies elements to be checked, defined in another 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

- Example:

• var a = [1, 2, 3, 4];

COUNT_OF(a, 2); // returns 1

• var a = [1, 2, 3, 4];

COUNT_OF(a, 5); // returns 0

• var a = [1, 2, 3, 2, 4, 2];

COUNT_OF(a, 2); // returns 3

• var a = [1, 2, [3, 4], 3, 4];

COUNT_OF(a, [3, 4]); // returns 1

FIND_AND_REMOVE - find and remove the first (or all) equal item(s) in the array. The function won't return any value, what is forwarded is changed. 

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

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

var a = [1, 2, 3, 4];

FIND_AND_REMOVE(a, 2); 

NumberField=a; //NumberField = [1, 3, 4]

var a = [1, [1, 2], 2, 3];

FIND_AND_REMOVE(a, [1, 2]); 

NumberField = a; //NumberField = [1, 2, 3]

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

param1 is local variable of type array

param2 specifies value which will be found in param1

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

var a = [10, 20, 30, 40];

INDEX_OF(a, 20); //returns [2] 

var a = [10, 20, 30, 40, 30];

INDEX_OF(a, 30); // returns [3,5]

AS_ARRAY - 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: 

var a = 20;

var index = AS_ARRAY(a); // index = [20] 

• Example 2: 

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

var index = AS_ARRAY(someField); // index = [10]

• Example 3: 

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

var index = AS_ARRAY(someField); // index = [10, 20]

• Example 4: 

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

var index = AS_ARRAY(someField); // index = [10, 20, 30]

• Example 5: 

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

var index = AS_ARRAY(someField); // index = [[10, 20], [20],  [30, 10, 20]]

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 Chapter Repeatable Options.

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 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 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 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 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 formula expression for a user type field. Returns a user who created a certain record of the table

As already described in chapter 4.4.2 Alphanumeric, 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. Here’s an example.

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 value “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 value “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 value “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 4.63 where the Values Tab of the field Test is shown with the Expression entered.

Correct Expression is the following:

var a=[]; 

if(TestType=='Characterization')

{a=['pH','Viscosity', 'Density'];} 

else if(TestType=='Application')

{a=['Scrub Resistance', 'Adhesion', 'Tensile Elongation'];} 

else if(TestType=='Microbiology')

{a=['Fungal', 'Bacterial'];}

a;

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 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. Here’s an example.

In chapter 4.64. Button, there is an Example 1 - Button recalculates all rows in the table. In this example, you learned how the user can compose the formulation, and how (s)he 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”:

AddedMaterialg = AddedMaterialL / TotalVolume * TargetBatchSizeL * MaterialDensity

See how it’s done in Figure 4.64.

It is important where are you 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, action will happen.

A field in a specific row

As already explained, 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 3 - Button with Helper Field will be upgraded to explain this functionality.

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 (Figure 4.61). The Formulation Record created on the button will have a name defined in an alphanumeric field Experimental Formula Name (identifier ExperimentalFormulaName). Let’s say you want to configure that the user can 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 3 - 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. See Figure 2.62.

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:

‍

for(var i = 1; i <= ExperimentalFormulaRRGroup.RowCount; i = i + 1)

{

 if(FormulaCreated[i] == false)

  {

        FormulaHelper[i] = CREATE_RECORD('FormulationDesign','LabWork',Process,1);

FormulaHelper[i].FormulationName = LabWorkNumber+' ' + ExperimentalFormulaName[i];

        FormulaHelper[i].TargetpHfrom=TargetpHfrom[i];

        FormulaHelper[i].TargetpHto=TargetpHto[i];

        FormulaHelper[i].TargetViscosityFrom=TargetViscosityFrom[i];

        FormulaHelper[i].TargetViscosityTo=TargetViscosityTo[i];

        FormulaHelper[i].TargetDensityFrom=TargetDensityFrom[i];

        FormulaHelper[i].TargetDensityTo=TargetDensityTo[i];

  }

  FormulaCreated[i] = true;

}

‍

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:

‍

if(FormulaHelper[CurrentRow] IS SET)

{

FormulaHelper[CurrentRow].FormulationName = LabWorkNumber + ' ' + ExperimentalFormulaName[CurrentRow];

}

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

This powerful feature enables you to change the property of the field using the appropriate Alchemy script expression. It can be used on all field types except for Label, Image, and Button.

The general form of Conditional Formatting Expression is

FieldIdentifier.FieldProperty = PropertyValue;

Where FieldProperty is the keyword to access a specific property of the field defined by the FieldIdentifier. 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 backpropagate - field property will not be affected. It will remain as defined by the previous Conditional Formatting Expression.

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 Appendix F - 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)

Field property Hidden should be used only for a field that is not inside a repeatable row or repeatable column. 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:

FieldIdentifier[CurrentRow].FieldProperty = PropertyValue;

‍

Examples

Let’s build on an already existing example - IFS conditions. In this example we already have 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 alphanumeric field (identifier pHRange) there should be this expression:

var a = ' ';

if(pH>=TargetpHfrom AND pH<=TargetpHto)

{pHRange.InputBackgroundColor='#93C480';

a='IN RANGE';}

else

{pHRange.InputBackgroundColor='#DF6969';

a='OUT OF RANGE';}

a;

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, 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, field property will be red ('#DF6969') and the field will get the value 'OUT OF RANGE'.