release notes
Release 60.0
Release 59.11
Release 59.10
Release 59.9
Release 59.8
View More...
Release 60.0
Release 59.11
Release 59.10
Release 59.9
Release 59.8
View More...
announcements
What's coming in 2022 Q1?
What's coming in Q4?
What's coming in March?
What's coming in the remainder of Q1?
What's coming in January?
What's coming in December?
What's coming in November?
how to's
MANUALS
Configuration Portal Administrator Manual
Alchemy Script User Manual
Configuration Portal Administrator Manual
ALCHEMY SCRIPT USER MANUAL
DEVELOPERS
Integration API
integration api
BETA

Send Feedback

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
send feedback

Send Feedback

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
sign in

Alchemy Script User Manual

  • Introduction
  • Expressions
    • 2.1 Constants
    • 2.2 Variables
      • 2.2.1 Reference variables
      • 2.2.2 System Data Variables
    • 2.3 Operators
      • 2.3.1 Logical operators
      • 2.3.2 Field Operators
      • 2.3.3 Relational Operators
      • 2.3.4 Arithmetic Operators
    • 2.4 Functions
      • 2.4.1 Commonly used functions
      • 2.4.2 Functions used in Alchemy scripts
      • 2.4.3 Trigonometric functions
      • 2.4.4 Miscellaneous mathematical functions
      • 2.4.5 String functions
      • 2.4.6 Statistical functions
      • 2.4.7 Date and Time functions
      • 2.4.8 Array functions
    • 2.5 Item
      • 2.6 Additional script applications
        • 2.6.1 Conditional Predefined Values
        • 2.6.2 Field Actions - On Change
        • 2.6.3 Conditional Formatting
        • 2.6.4 Alchemy Color Hex Codes

    ** It is not recommended to read Manual on phones and smaller screen tablets, since you may not enjoy full user experience.

    1. Introduction

    BASIC
    ‍
    ⇨Input requirements:

    • Advanced computer knowledge (basic programming skills)
    • Experience in Alchemy as Configuration Administrator (Admin)

    Welcome to the Alchemy Script User Manual!

    This document will be your personal guide through the Alchemy Script language, a tool that enables you to harness the power of Alchemy Script in order to automate parts of processes and data entry, data visibility, or data calculations reflected in the Alchemy User Portal.

    As a Configuration Administrator (Admin), you have the possibility to add Alchemy Script Expressions to the process and record templates to fit the needs of your organization using the Configuration Portal. In the User Portal, configured templates will allow members of your organization (Users) to capture their work in a structured and predetermined way. 

    This manual will help you understand where and how to write  Alchemy Script Expressions, and learn all the best practices.

    2. Expressions

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

    Figure 1. Possible elements of a formula expression
    Figure 2. An example formula expression with different elements

    2.1 Constants

    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.

    2.2 Variables

    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

    Field value used in the expression in the same record

    In the record Request, a requestor fills in the number of samples needed in the Lab Work. This is configured using a field named Number of Samples (type: number, identifier: NumberOfSamples). Each sample needs to be tested three times. In the same record template create a field Number of Tests (type: number, identifier: NumberOfTests). In the Formula Expression of this field write

    NumberOfSamples * 3

    This will pull the number value entered in the field Number of Samples, multiply it by 3 and the resulting value of the field Number of Tests will be automatically calculated.

    2.2.1 Reference variables

    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:

    Accessing a field through a record reference

    In the Request record template there is a field name Number of Samples (type: number, identifier: NumberOfSamples). A person who fills in the Request sets how many samples are needed. In the record template Test Results, a Chemist who performs the tests and enters the results needs to have the same value of the number of samples needed. You need to pull that value from the record Request to the record Test Results. To achieve this you need to:

    - In the record template Test Results, create a record type field (identifier RelatedToRequest), and for record reference choose the record template Request. This field serves as a “door” to enter the record Request data from the record Test Results.

    - Create a number type field where you will pull the data. In the Formula Expression of this field write

    RelatedToRequest.NumberOfSamples

    You can further use this expression in calculation formulas like in the previous example (not just pulling the data, but calculating further with the pulled data). 

    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

    Number of rows determined by the number of samples

    In the Characterization stage you made a field for the number of samples that are needed. The name of the field is Number Of Samples, the identifier is NumberOfSamples. This field is a number type. The number of samples will determine how many rows will be available in the characterization table. To do this, make a repeatable row group and put it on top of input fields as already explained in the chapter How to make a table in Alchemy. In the Repeatable Option field of the group advanced settings, select Determined by Formula. In the Formula Expression write the expression NumberOfSamples.

    The second possible solution is that you defined the number of samples in the stage Request in the record Request Form. In that case in the Formula Expression for the repeatable group Determined by Formula write

     Stage[Request].Record[RequestForm].NumberOfSamples.

    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

    Record field to use data from another process

    To access the value of the pH field(identifier ProductpH) entered in the record QC Profile in the process Register Product (marked with a star in the scheme) you need to configure the following two fields in the record QC Result of the process Quality Control:

    1 - record type field (marked with 1) with identifier FromQCProfile

    2 - number type field (marked with 2) with calculation FromQCProfile.ProductpH

    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.

    Process field to use data from another process

    In the Alchemy Lab, Chemists are using the process template Register Product to input all details about lab products they get from their formulation outcomes. The Lab Manager is using the process template Sample to prepare samples of products, finish the administration work and send them out to customers. Inside a Sample process, the Lab Manager needs to pull some data from a Register Product process. Schemes of both process templates are shown in the figure below. In the process template Register Product there are multiple stages and records, but the field from which the Lab Manager needs to pull data from is called pH (Name: pH, Identifier: pH). This field is located in the stage Product Information in the record Chemical Characteristics. Both, the process template and record template need to be referenced in Lab Manager's Sample in order for the system to recognize from where to pull the data, especially because the record template Chemical Characteristics is used across the organization in multiple process templates (NPD, ARM). 

    Fields that are needed in the Sample process template are

    1. Process field type with referenced process template: Register Product (in the User Portal the Lab Manager chooses the exact process (s)he wants from the dropdown list) Name: Choose Product, Identifier: ChooseProduct
    2. A field that is the same field type as the field from which we are pulling the data. The source field pH is a number type, so this field also needs to be a number field type. The field where the data will be pulled should have the Name: Product pH and identifier: ProductpH. 

    This field needs to have a Formula Expression ChooseProduct.Record[ChemicalCharacteristics].pH

    These fields are in the Sample process template, stage Request, record template Request Form as seen in the figure 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 records of the same record template

    With the same example as above, when there may be multiple Chemical Characterization Records in the Register Product Process, it is advisable to have an intermediate field where the user can select which of the characterization records to pull the data from. 

    Fields that are needed in the Sample process template are

    1. Process field type with referenced process template: Register Product (in the User Portal the Lab Manager chooses the exact process (s)he wants from the dropdown list) Name: Choose Product Identifier: ChooseProduct
    2. Record Field type with referenced record template: Chemical Characteristics (in the User Portal the Lab Manager chooses the exact record (s)he wants from the dropdown list) Name: Characteristic Type Identifier: CharacteristicType. In the Record Filter Criteria field write the expression Result.Process = ChooseProduct. Result is a keyword that is used for filtering, "Process" is a system field used for matching the chosen process above. ChooseProduct is an identifier of the first field where we have chosen a process.

    A field which has the same field type as the field from which we are pulling data from. Field pH is a number field type so this field also needs to be a number field type. The  field where the data will be pulled has the Name: Product pH and identifier: ProductpH. This field also needs to have a Formula Expression: CharacteristicType.pH

    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.

    2.2.2 System Data Variables

    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.

    System data from the current process

    If you make an alphanumeric field and write in the Formula Expression: Process.Status, as an output you will get one of the four available statuses of the current process (In Progress, On Hold, Completed, Voided) as a string.

    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 data from another process

    To build on the previous example, before the alphanumeric field, you would need to make a process field type (identifier ChooseProduct) where you can choose the other process from which you would like system data information. Then, in the alphanumeric field you have made, in the Calculation formula write: ChooseProduct.Status, as a result you will get the status of the process you have chosen in a string.

    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

    Effort spent for Application Testing visible in Lab Work Review

    In the Lab Work Process after the Application Testing the flow is continuing to Lab Work Review. In the Lab Work Review you may want to know the effort spent in the previous stage, the Application Testing (identifier: ApplicationTesting). In the Lab Work Review you can make a field, number type, with the Formula Expression: Stage[ApplicationTesting].TotalEffortSpent. This formula will output the total effort spent on the stage in hours.

    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)

    Record Formulation 2 completion date

    In the stage Formulation, there are multiple records: Formulation 1, Formulation 2, Formulation 3. Their record template identifier is FormulationRecord, and they are created respectively. In the stage Lab Work Review you may want to access system data for the last Formulation record created in the process. Make a datetime field type in the Lab Work Review stage, in the Formula Expression write:Stage[Formulation].Record[FormulationTemplate].CompletedOn, this gives you the information about the date and time when the last Formulation Template record was completed.

    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
    Email of the responsible person for the Technical Call

    You can combine multiple system data information. In the Request Review stage, you can have a field that will give you the information on who completed the stage Technical Call. Make a user field type (identifier: TechnicalCallResponsible) in the Request Review stage (Approval record), in the Formula Expression write: Stage[TechnicalCall].CompletedBy, according to the information from that stage and the field TechnicalCallResponsible, you can get the information about the user's email. Make an alphanumeric field type in the Request Review stage, in the Approval record and in the Formula Expression write: TechnicalCallResponsible.Email.

    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

    Accessing the record data from the current process

    When you are trying to access data from a record that is in the current process you are in, make sure that the record template of that record is added to the process template of the process you are currently in. This is the case when you are using square brackets in the formula expressions.

    2.3 Operators

    Operators are symbols or expressions that use two inputs, one on each (left and right) side of the operator. Spaces are allowed between the inputs and the operators. If an operation is defined in an expression on an unsupported type, an error is generated. Below you can find the lists of operators.

    2.3.1 Logical operators

    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.

    Transition condition with logical operator AND

    The Lab Manager wants to continue from the Lab Work to the Report stage. In the Lab Work Review stage there are two questions at the end. One question is: More formulations needed? (field type Yes/No, identifier: MoreFormulations) and the other is: Is the review finished? (field type Yes/No, identifier: ReviewFinished). The first question needs to be false and the second question true to transition to Report. The transition condition set up in the When field of the Actions for the stage will be:  

    MoreFormulations == false AND ReviewFinished == 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.

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

    ('text' == 'nottext') OR ('ciao' == 'stillnotciao')

    - The expression will return false, because both expressions are false.

    Transition condition with logical operator AND

    When using the logical operator OR, if the left operand is true, the right operand is not evaluated.

    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.

    2.3.2 Field Operators

    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
    Formula Expression based on Status of the Formulation

    In the Formulation stage there is a list of formulations that needs to be made. That list is in the table (repeatable row group). The second column is Status (alphanumeric field with the predefined values, identifier: Status) - status of making a formulation can be ‘Done,’ ‘In Progress’ or ‘Pending’. Below that table, there is a checkbox that says: "All formulations are done". Chemists are able to go to the Characterization stage only when all formulations are done and the checkbox is checked. 

    You will make that possible by writing this expression in the Formula Expression of the checkbox "All formulations are done":

    IFS(('In Progress' NOT IN Status AND 'Pending' NOT IN Status), true, true, false).

    This calculation formula will check the checkbox only when In Progress and Pending are not in the column labeled Status, and leave it unchecked if the opposite.  IFS function will be explained below.

    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

    2.3.3 Relational Operators

    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

    2.3.4 Arithmetic Operators

    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.
    Advice - Use empty string in order to make space between concatenated fields

    When you concatenate two alphanumeric fields as a result you will see values of those fields glued together. In order to avoid that add empty string in the Calculation formula (i.e. FieldIdentifier1 + ' ' + FieldIdentifier2).

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

    2.4 Functions

    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.

    2.4.1 Commonly used functions

    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;

    Today’s date

    Whenever you need a date&time type of field to be filled in by today's date you can use this function. Write TODAY in the Formula Expression of the field type date&time. You should check the Editable checkbox “Editable” in the Properties tab of that field, so if needed, the user in the User Portal can change the date of today to some other date.

    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 of the Configuration Portal Administrator Manual.
    • 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;

    Request Review task subject based on TRANSITION_FROM

    Request Review in the Lab Work can be done after Request stage or after Technical Call stage.  The Lab Manager needs to get the task with a different subject for review. One subject should be when the task is created after the Request stage and the other subject when the task is created after Technical Call. 

    In order to set up those tasks, you need to add two Create Task actions on the Request Review stage. In the first one in the When you will write TRANSITION_FROM(Stage[Request]) and the Task Subject you will fill in with the 'Review the Lab Work Request for ' + Process.Name. In the second one in the When you will write TRANSITION_FROM(Stage[TechnicalCall]) and the Task Subject you will fill in with the 'Review the Lab Work Request for ' + Process.Name + ' - technical call is done'.

    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’;

    You may have only one PATTERN formula in one expression

    You cannot have more than one PATTERN() formula in one expression. i.e. PATTERN('YYYY') + PATTERN('NNN') is not a valid expression. If you need to join two patterns, write the expression in two separate fields, and concatenate the strings in a third.

    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: 

    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.

    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

    Naming Characterization Tests record

    One formulation can be tested multiple times. In order to distinguish between these different test results, you can use the KEY_PATTERN function to name different Characterization Tests records. In the Characterization Tests record template there is a field Related To Formulation (identifier ROwulation), where formulations displayed are filtered only to those created in the current process. You need to add an alphanumeric field, identifier CharacterizationKeyNumber.This field should be hidden because it helps you to name Characterization Tests records in the process and serves only for that. Calculation of this field should be:

    RelatedToFormulation + KEY_PATTERN(RelatedToFormulation + ' ', 'N.N')

    Under Lab Work Stage, under the Records tab, choose the Characterization Tests and in the Naming Pattern use the value of the field CharacterizationKeyNumber. This way, if you have more Characterization Tests records created for the same RelatedToFormulation field value, they will have different names. The difference will be in the number given by the KEY_PATTERN function: 0.1, 0.2, 0.3, etc.

    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.

    Changing KEY will reset the KEY_PATTERN

    In Alchemy Lab final Products receive a number based on the Product Type. Product Number is an alphanumeric field with the calculation ProductType + KEY_PATTERN(ProductType, 'NNN'). However, the new R&D director decided that the Product should receive a number based on the Industry Type where it is applied. In that case, the new calculation in the Product Number field will be IndustryType  + KEY_PATTERN(IndustryType, 'NNN'). The first new product will have the number 001 for a certain Industry Type no matter if it is from the Product Type where the next number would be 029. The reason is that KEY is changed. 

    Getting back the previous KEY in calculation

    After a while the new R&D director realized that the previous product naming convention was more efficient. He asked a configurator to change Product Numbers so they again depend on the Product Type. After a calculation in Product Number is returned to ProductType + KEY_PATTERN(ProductType, 'NNN') numbers will count from the number where they stopped before the change was made (the product from the certain type will have the number 030).

    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',

    2.4.2 Functions used in Alchemy scripts

    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.

    Standard field keyword table:

    Filter comparison type list:

    • 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

    var filterParam = [
     {
       'node' : '1',
       'fields' : [
         {
           'name' : 'PRIORITY',
           'visible' : true,
           'filters' : [
             {
               'type' : 'IS_BETWEEN',
               'from' : 5 , 'to' : 15
             }
           ]
         }
       ]
     } , {
       'node' : '3',
       'fields' : [
         {
           'name' : 'RECORD_ID'
           'visible' : false,
           'filters' : [
             {
               'type' : 'IS_ANY_OF',
               'values' : [Record[LabWork], ReferenceRecordInRR, Record]
             }
           ]
         } , {
           'name' : 'NumberField' ← note that this is an identifier string, so it has ‘ ’
           'visible' : true,
           'precision' : 3,
           'filters' : [
             {
               'type' : 'IS_GREATER_THAN_OR_EQUAL',
               'values' : [SomeOtherNumberField] ← number calculation, no ‘ ’
             }
           ]
         }
       ]
     }
    ];
    CREATE_VIEW(Stage[LabWork], 'View1', 'View From Button', filterParam);

    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.

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

    • Number of parameters: 3 mandatory and 1 optional
    • The first parameter: RecordTemplate
    • The second parameter: StageIdentifier (optional)
    • The third parameter: StructWithConstants - parameters which will be used in callback function
    • The fourth parameter: postCreationScript (callback) - actions which will be executed after new record is created
    • Keyword: CREATE_RECORD_ASYNC
    • Output type: Void

    - Examples:

    Creating solo record:

    var myParams = {
     "newRecordName":"new record",
    "newText":RecordField.AphanumericField
    };
    var myScript = "RecordName = newRecordName;
    AphanumericField = newText;";
    CREATE_RECORD_ASYNC("RecordB", myParams, myScript);

    Creating record in a process:

    CREATE_RECORD_ASYNC(“RecordA”,Process.Stage[Stage_1],{ "a":Record[RecordB].NumberField}, “AlphanumericField = a;”);

    CREATE_PROCESS_ASYNC -  Function is used to create new processes asynchronously.

    • Number of parameters: 3 
    • The first parameter: Process template
    • The second parameter: StructWithConstants - parameters which will be used in callback function
    • The third parameter: postCreationScript (callback) - actions which will be executed after new a process is created
    • Keyword: CREATE_PROCESS_ASYNC
    • Output type: Void
    • Examples:

    var myParams = {
     "recordId": Record.Id
    }
    var myScript = "
    Process.Record[RecordA].RefRecordField = GET_RECORD(recordId);
    Process.Record[RecordA].RefRecordField.ProcessNameField = Process.Name;"
    CREATE_PROCESS_ASYNC("ProcessA", myParams, myScript);

    2.4.3 Trigonometric functions

    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)

    2.4.4 Miscellaneous mathematical functions

    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

    2.4.5 String functions

    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)

    2.4.6 Statistical functions

    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

    2.4.7 Date and Time functions

    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

    2.4.8 Array functions

    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

    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]

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

    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)

    2.5  Item

    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 of the Configuration Portal Administrator Manual.

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

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

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

    • Item - must use in 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

    2.6 Additional script applications

    In this section, you will see some functionalities that can be utilized to write a proper script.

    2.6.1 Conditional Predefined Values

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

    Figure 3. Conditional Predefined Values

    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.

    2.6.2 Field Actions - On Change

    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 of the Configuration Portal Administrator Manual, 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.

    Figure 4. On Changed Action

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

    Figure 5. Helper Field inside Repeatable Row

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

    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.

    2.6.3 Conditional Formatting

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

    2.6.4 Alchemy Color Hex Codes

    The Hex Code can be used as a property value by providing it as a string (under single quotes). The list of Hex Codes for the default Alchemy Colors available in any color Picker is:

    Color applied to
    Hex code (click to copy)
    Text
    #383F45
    Label
    #424B52
    Helper text/icon
    #637482
    Placeholder
    #ADBECC
    Border
    #D1D8DE
    Background
    #f5f7fa
    Link (Blue 500)
    #2196F3
    Blue 300
    #64B5F6
    Blue 100
    #BBDEFB
    Info (Blue 50)
    #E3F1FC
    Red 500
    #F44336
    Red 100
    #FECDD2
    Yellow 500
    #FFEB3B
    Yellow 100
    #FFF9C4
    Green 500
    #4CAF50
    Green 100
    #C8E6C9
    White
    #FFFFFF
    Black
    #000000

    Additional hex codes are available by selecting “Custom” in the Color Picker. Choosing the desired color will display its Hex Code. You can copy and paste it from there in the Conditional Formatting Expression.

    © 2017-2023, Alchemy Cloud, Inc. All Rights Reserved. Restricted. For internal use only and not for redistribution. Legal Terms

    We use cookies to operate this website, improve its usability, personalize your experience, and track visits. By continuing to use this site, you are consenting to the use of cookies. We also share data with our social media and analytics partners. For more information, please read our updated Privacy Policy.

    Accept