Editing formulas
|
|
|
Prerequisite
- Access to the menus that have the formula editor in the components.
Introduction
The formula editor can be used in different components. Nevertheless, its configuration is identical in the various SoftExpert Suite solutions.
Logic and mathematical functions are available in all components that use this resource, whereas textual and temporal ones are provided in specific contexts. By combining different functions, it is possible to create complex formulas suitable for different scenarios.
See how to edit formulas below:
Editing formulas
Watch the video below to learn how to edit formulas in the system correctly:
Formula editor functions
The formula editor has functions that vary according to the component in which it is used.
Logic and mathematical functions are used in all components that have the formula editor.
See the available functions below:
Logic
| IF | function that checks if a condition has been met and returns a value if it is TRUE. |
| ELSE | function that checks if a condition has been met and returns a value if it is TRUE; if the value is FALSE, it returns another value. |
| AND | function that checks if the arguments are TRUE and returns TRUE. Returns FALSE if at least one of the arguments is FALSE. |
| OR | function that checks if an argument is TRUE and returns TRUE or FALSE. Returns FALSE only if all arguments are FALSE. |
| NO | function that reverses the logic value of an argument, that is, reverses FALSE to TRUE, or TRUE to FALSE. |
| FALSE | function that returns the FALSE logic value. |
| TRUE | function that returns the TRUE logic value. |
| NULL | function that returns the NULL logic value. |
| RETURN | function that specifies the value to be returned for the formula. |
| ISNUM | function that checks the specified value and returns TRUE or FALSE, depending on the result. It will return the TRUE logic value if the argument of the value is a reference to a number. Otherwise, it will return FALSE. |
Math
| RANDOM | function that returns a random number greater than or equal to 0 and less than 1. |
| RANDOM BETWEEN | function that returns a random decimal number in between the specified values. |
| ROUND | function that rounds a number to the closest integer value. |
| FACTORIAL | function that returns the factorial of a number. |
| ABSOLUTE | function that returns the absolute value of a number. |
| PRODUCT | function that multiplies all given numbers. There is no fixed number of parameters. |
| POWER | function that raises a value to a power. |
| QUOTIENT | function that returns the integer portion of a <Numerator> by <Denominator> division. |
| SQUARE ROOT | function that returns the square root of a number. |
| MOD | function that returns the integer remainder of a <Numerator> by <Denominator> division. |
| SUM | function that sums values. There is no fixed number of parameters. |
| SUBTRACTION | function that subtracts values. There is no fixed number of parameters. |
| TRUNCATE | function that rounds a number down. |
| HIGHEST | function that returns the highest number among all given numbers. There is no fixed number of parameters. |
| LOWEST | function that returns the lowest number among all given numbers. There is no fixed number of parameters. |
Date and time
| YEAR | function that extracts the year from a <Date>. |
| BEFORE | function that compares two dates and returns TRUE if <Date 1> comes before <Date 2>. |
| DATE | function that creates a date using day, month, and year values. |
| EQUAL DATE | function that compares two dates and returns TRUE if <Date 1> is equal to <Date 2>. |
| AFTER | function that compares two dates and returns TRUE if <Date 1> comes after <Date 2>. |
| DAY | function that extracts the day from a <Date>. |
| WEEK DAY | function that extracts the week day from a <Date>. |
| DAYS BETWEEN | function that returns the number of days in between the <Date 1> and <Date 2> dates. |
| TODAY | function that shows today's date. |
| MONTH | function that extracts the month from a <Date>. |
| WEEK NUMBER | function that extracts the week number from a <Date>. |
| ADD DAYS | function that returns the date of a working day after summing or subtracting the days of the start date, in accordance with the defined calendar. |
| ADD HOURS | function that returns the date and time of a working day after summing or subtracting the minutes of the start date, in accordance with the defined calendar. |
| SEPARATE DATE | function that returns only the date in the yyyy-mm-dd format, from a date and time value. |
| SEPARATE TIME | function that returns only the time in the hh:mm format, from a date and time value. |
Text
| TRIM | function that removes spaces from the beginning and end of a sequence of characters, except for the single spaces in between words. |
| EXACT | function that compares the <Text 1> and <Text 2> texts and returns TRUE if they are exactly the same, or FALSE if they are different. This function is case-sensitive. |
| FIND | function that finds the exact position of <Searched text> within <Original text>. If the text is not found, position -1 is considered. |
| EXCERPT | function that returns the text of an excerpt beginning in the <Start> position and finishing in the <End> position; positions must be integer numeric values. |
| UPPERCASE | function that changes all text characters to uppercase. |
| LOWERCASE | function that changes all text characters to lowercase. |
| SUBSTITUTE | function that substitutes all <Text to be replaced> occurrences with <New text> within the text contained in <Source text>. |
| CONCATENATE | function that joins texts in a sequence. There is no fixed number of parameters. |
Statistic
| AVERAGE | function that returns the average of values. There is no fixed number of parameters. |
| STANDARD DEVIATION | function that returns the standard deviation of values. There is no fixed number of parameters. |
SoftExpert Process
| Flowchart > SE SUITE ID | function that returns the process instance ID #. |
| Flowchart > ATTRIBUTE VALUE | function that returns the value of an attribute. If the attribute is multivalued, the selected values will be separated by comma. |
| Flowchart > STARTER | function that returns the user who started the process. The <return> may be the user's login, ID, or name. |
| Flowchart > REQUESTER | function that returns the user who requested the process. The <return> may be the user's login, ID, or name. |
| Flowchart > QUANTITY OF EXECUTORS | function that returns the number of executors for the same activity with multiple executors. |
| Flowchart > EXECUTOR |
function that returns the user who is the executor of the activity. The <return> may be the user's login, ID, or name.
|
| Flowchart > TASK ACTUAL DURATION |
function that returns the actual duration of a task, considering the elapsed time (from the date it was enabled until the date it is executed). The return may be in days, hours, or minutes.
|
| Flowchart > PROCESS ELAPSED TIME | function that returns the elapsed time of a process (from the start date to the current date). The <return> may be in days, hours, or minutes. |
| Flowchart > QUANTITY OF EXECUTED ACTIONS | function that returns the number of times an action has been taken in the same activity executed by multiple executors. |
| Flowchart > STARTER DEPARTMENT | function that returns the ID # of the process starter's department. |
| Flowchart > VALUE OF THE TABLE FIELD | function that returns the value of a table field. |
| Flowchart > VALUE OF THE TABLE LIST | function that returns the value of a table list. |
| Flowchart > EXECUTED ACTION | function that returns the name of the action execution in the activity. |
| Flowchart > EXECUTOR DEPARTMENT | function that returns the ID # of the activity executor's department. |
| Flowchart > PRIORITY | function that returns the priority. |
| Flowchart > PERCENTAGE STATUS SUBPROCESS | function that returns the percentage of sub-process instances finished with a certain status. |
| Date and time > ADD DAYS | function that returns the date of a working day after summing the days of the start date, in accordance with the defined calendar. |
| Date and time > ADD HOURS | function that returns the time of a working day after summing the minutes of the start date, in accordance with the defined calendar. |
| Date and time > SEPARATE DATE | function that returns only the date in the yyyy-mm-dd format, from a date and time value. |
| Date and time > SEPARATE TIME | function that returns only the time in the hh:mm format, from a date and time value. |
| Activity dynamic duration > DEFINES ACTIVITY DURATION | function used in the return of the formula to define the duration of an activity, considering a formula-based dynamic duration. |
| Activity dynamic duration > DEFINE DURATION OF THE PROCESS | function used to define a new duration for a process. The date parameter must be in the yyyy-mm-dd format. Note: This function will only be executed when the process does not have SLA. |
| Consider dynamic executor for the activity > DEFINE EXECUTOR | function used in the return of the formula to define the executor of an activity, considering a formula-based dynamic executor. |
| Dynamically set the instance manager > DEFINE INSTANCE MANAGER | function used in the return of the formula to define the manager of an instance, considering a formula-based dynamic manager type. |
| Gateway output flow data > DEFINE PROCESS ATTRIBUTE | function that defines the value of a process attribute; if the attribute is multivalued, the values must be separated by "|". |
| Gateway output flow data > DEFINES THE FIELD OF THE TABLE | function that defines the value of an attribute referring to a process form table. |
| Gateway output flow data > DEFINES THE RELATIONSHIP OF THE TABLE | function that defines the value of a relationship referring to a process form table. |
| Gateway output flow data > CHANGE PROCESS TITLE | function used to define a new title for a process. |
| Gateway output flow data > DEFINE DURATION OF THE PROCESS | function used to define a new duration for a process. The date parameter must be passed in the yyyy-MM-DD format. Note: This function will only be executed when the process does not have SLA. |
SoftExpert Form
| Search for value > VALUE OF THE TABLE LIST | function that returns the value of a table field. |
| Search for value > VALUE OF THE TABLE FIELD | function that returns the value of a table field. |
| Search for value > FIELD VALUE WITH FILTER | function that returns the value of a table. |
| Search for value > TOTAL GRID | function that returns the total value. |
| Action > ACTION | function that executes actions on form items. The number of parameters varies, as it depends on the number of variables for the calculation to be performed. |
| Action > ACTION IN GROUP | function that applies actions to an item group within a fieldset. |
| Action > ACTION IN LAYOUT ITEM | function that applies actions to a layout element. |
| Action > DISABLE ACTION GRID | function that disables certain form grid buttons. |
| Action > ENABLE ACTION GRID | function that enables certain form grid buttons. |
| Action > EXECUTE SCRIPT | function that executes an event in a chosen element. |
| Action > EDIT LIMIT | function that changes the field limit from 4000 characters to less. |
| Action > OPEN FORM | function that opens a form in execution mode. |
| Action > IMPORT SPREADSHEET TO GRID | function that updates a grid with the content of a spreadsheet. |
| Action > FIELD OPERATION WITH FILTER | function that returns the operation of the "filter" field. |
| Action > EDIT COMBOBOX FIELD | function that edits the field of a combobox. |
| Action > GEOLOCATION | function that returns the current longitude or latitude, with 1 returning longitude, and 2 returning latitude. |
| Action > EXECUTE QUERY | function that executes a query and updates fields with the execution result. |
| Action > OPEN ZOOM FROM QUERY | function that opens a screen and displays the query execution results. A record can be selected to update form fields. |
| Action > OPEN ZOOM FROM QUERY TO UPGRADE GRID | function that opens a screen and displays the query execution result. Multiple records can be selected for insertion in the form grid. |
| Message > ALERT | function that returns an alert on screen. |
| Message > CONFIRM | function that returns a dialog box. |
| Auxiliar > TABLE FILTER | function that returns a filter object. |
| Auxiliar > DATE TO NUMBER | function that converts the <Date> parameter to a Timestamp Unix number. If the <Date> is in any format other than the system's default one, the format can be specified in the <Format> parameter. By default, ' ' (empty) can be inserted. |
| Auxiliar > NUMBER TO DATE | function that converts a Timestamp Unix <Number> to a date using the <Format> parameter; for example, NUMBERTODATE(1411603200; 'dd/mm/yyyy'). The <Format> parameter is optional, that is, ' ' (empty) can be passed. |
| Auxiliar > HOUR TO NUMBER | function that converts the <Hour> parameter to number of seconds. If the <Hour> is in any format other than the system's default one, the format can be specified using the <Format> parameter. By default, ' ' (empty) can be inserted. |
| Auxiliar > NUMBER TO HOUR | function that converts a <Number> of seconds to hours using the <Format> parameter; for example, NUMBERTOHOUR(7200; 'HH:mm:ss'). The <Format> parameter is optional, that is, ' ' (empty) can be passed. |
| Auxiliar > TEXT TO NUMBER | function that converts the <Text> parameter to a number. |
| Process > Process data > PROCESS TITLE | function that returns the title of a process. |
| Process > Process data > SE SUITE ID | function that returns the ID # of the process or project with which a form has been associated. |
| Process > Process data > PROCESS DESCRIPTION | function that returns the description of a process. |
| Process > Process data > STATUS | function that returns the automation status of a process instance. |
| Process > Process data > PRIORITY | function that returns the priority. |
| Process > Process data > ACTIVITY | function that returns the activity in which a form is displayed. |
| Process > Process data > REQUESTER CHARACTER | function that returns a requester's information. For a customer, it can be a company's name or a contact's name or e-mail. For a user, it can be their login, ID, username, e-mail, and department or position. |
| Process > User data > STARTER | function that returns the user who started the process. The <return> may be the user's login, ID, or name. |
| Process > User data > STARTER DEPARTMENT | function that returns the ID # of the process starter's department. |
| Process > User data > STARTER POSITION | function that returns the ID # of the process starter's position. |
| Process > User data > LOGGED USER | function that returns the user logged in to the system. The <return> may be the user's code, login, ID, or name. |
| Process > Action in process > SAVE COMMENT | function that saves a comment in the history of workflow, incident, or problem instances. |
| Process > Action in process > EXECUTE ACTIVITY ACTION | function that executes the action with the same name that is related to the activity that loaded the form. |
| Process > Action in process > CHANGE STATUS | function that changes the status of an instance. |
| Process > Action in process > CHANGE PRIORITY | functions that changes the priority. |
| Process > Action in process > CREATE CAUSE | function that creates a cause in a problem instance. |
| Date and time > Search for date > TODAY | function that creates a date that represents today's date. |
| Date and time > Search for date > SERVER DATE | function that returns the date of the server. |
| Date and time > Search for time > CURRENT TIME | function that returns the current time in the “hh:mm:ss” format. |
| Date and time > Search for time > SERVER TIME | function that returns the time of the server. |
| Date and time > Calendar > CALENDAR | function that returns the ID # of a calendar. |
| Date and time > Calendar > PROCESS CALENDAR | function that returns the ID # of a process calendar. |
| Date and time > Calendar > WORKING DATE | function that informs whether a calendar considers a certain date as a working day. |
| Date and time > Create date/time > DATE/TIME | function that returns date/time in the “dd/mm/yyyy hh:mm>ss” format. |
| Date and time > Compare date > BEFORE | function that compares two dates and returns TRUE if <Date 1> comes before <Date 2>. |
| Date and time > Compare date > EQUAL DATE | function that compares two dates and returns TRUE if <Date 1> is equal to <Date 2>. |
| Date and time > Compare date > AFTER | function that informs whether a calendar considers a certain date as a working day. |
| Date and time > Compare date > DAYS BETWEEN | function that returns the number of days in between the <Date 1> and <Date 2> dates. |
| Date and time > Compare time > HOURS BETWEEN | function that returns the number of hours in between hours. The <Time 1> and <Time 2> parameters must be in the "HH:mm:ss" format. |
| Date and time > Compare time > HOURS BEFORE | function that compares two times and returns TRUE if <Time 1> comes before <Time 2>. The <Time 1> and <Time 2> parameters must be in the "HH:mm:ss" format. |
| Date and time > Compare time > HOURS AFTER | function that compares two times and returns TRUE if <Time 1> comes after <Time 2>. The <Time 1> and <Time 2> parameters must be in the "HH:mm:ss" format. |
| Date and time > Compare time > EQUAL TIME | function that compares two dates and returns TRUE if <Date 1> is equal to <Date 2>. The <Date 1> and <Date 2> parameters must be in the "HH:mm:ss" format. |
| Date and time > Create date/time > DATE/TIME BEFORE | function that compares two dates and returns TRUE if <Date 1> comes before <Date 2>. The <Date 1> and <Date 2> parameters must be in the "dd/mm/yyyy HH:mm:ss" format. |
| Date and time > Compare date/time > DATE/TIME AFTER | function that compares two dates and returns TRUE if <Date 1> comes after <Date 2>. The <Date 1> and <Date 2> parameters must be in the "dd/mm/yyyy HH:mm:ss" format. |
| Date and time > Compare date/time > EQUAL DATE/TIME | function that compares two dates and returns TRUE if <Date 1> is equal to <Date 2>. The <Date 1> and <Date 2> parameters must be in the "dd/mm/yyyy HH:mm:ss" format. |
| Date and time > Separate date/time > SEPARATE DATE FROM DATE/TIME | function that returns the date from <date/time> in the "dd/mm/yyyy" format. The parameter must be in the "dd/mm/yyyy HH:mm:ss" format. |
| Date and time > Separate date/time > SEPARATE TIME FROM DATE/TIME | function that returns the time from <date/time> in the "dd/mm/yyyy" format. The parameter must be in the "dd/mm/yyyy HH:mm:ss" format. |
| Date and time > Separate date/time > EQUAL DATE/TIME | function that compares two dates and returns TRUE if <Date 1> is equal to <Date 2>. The <Date 1> and <Date 2> parameters must be in the "dd/mm/yyyy HH:mm:ss" format. |
| Date and time > Separate date > YEAR | function that extracts the year from a <Date>. |
| Date and time > Separate date > MONTH | function that extracts the month from a <Date>. |
| Date and time > Separate date > DAY | function that extracts the day from a <Date>. |
| Date and time > Separate date > WEEK DAY | function that extracts the week day from a <Date>. |
| Date and time > Separate date > WEEK NUMBER | function that extracts the number of a week (within the year) from a <Date>. |
| Date and time > Separate time > HOUR | function that returns the number of hours. |
| Date and time > Separate time > MONTH | function that returns the minutes. |
| Date and time > Add time/day > ADD NUMBER OF HOURS | function that returns a new date/time, in the "dd/mm/yyyy HH:mm:ss" format, after addition of a number of hours. |
| Date and time > Add time/day > ADD HOURS | function that returns a new date/time, in the "dd/mm/yyyy HH:mm:ss" format, after hours are added. |
| Date and time > Add time/day > ADD DAYS | function that returns a new date/time, in the "dd/mm/yyyy HH:mm:ss" format, after addition of a number of days. |
SoftExpert Performance
| Logic > ISNUM | function that checks the specified value and returns TRUE or FALSE, depending on the result. It will return the TRUE logic value if the argument of the value is a reference to a number. Otherwise, it will return FALSE. |
| Performance > DYNAMIC ACCUMULATION | function that returns the accumulated value of an indicator, based on the accumulation parameters (Vertical or Horizontal), fields (Target or Actual), operation (Sum or Average), and periods that are selected. |
| Performance > NOT APPLICABLE | function that set a measurement value as not applicable for the period of an indicator. |
| Performance > INDICATOR | function that returns the field of a selected indicator (Target, Measurement, Accumulated target, Accumulated measurement) in the current period. |
| Performance > SUB-INDICATORS AVERAGE | function that returns the average of the sub-indicators associated with an indicator in the current period. |
| Performance > SUB-INDICATORS WEIGHTED AVERAGE | function that returns the average weighted by the weights of the sub-indicators associated with an indicator in the current period. |
| Performance > SUB-INDICATORS SUM | function that returns the sum of the sub-indicators associated with an indicator in the current period. |
| Performance > PERIOD START DATE | function that returns the start date of the period that is being calculated, based on the frequency of an indicator. |
| Performance > PERIOD END DATE | function that returns the end date of the period that is being calculated, based on the frequency of an indicator. |
| Analytics > NUMBER | function that returns the value of a number-type vision from an analysis panel of the Analytics component. The value will be distributed across the periods of an indicator, in accordance with its frequency and with the reference field. |
| Process > STARTED INSTANCES | function that returns the number of started instances. |
| Process > FINISHED INSTANCES | function that returns the number of finished instances. |
| Process > CANCELLED INSTANCES | function that returns the number of cancelled instances. |
| Process > AVERAGE ELAPSED TIME OF INSTANCES | function that returns the average elapsed time, in minutes, between the start and end of instances. |
| Process > AVERAGE TIME OF INSTANCES IN WORKING HOURS | function that returns the average elapsed time, in minutes, considering working hours, between the start and end of instances. |
| Process > EXECUTED ACTIVITIES | function that returns the number of executions for an activity. |
| Process > AVERAGE ELAPSED TIME OF ACTIVITIES | function that returns the average elapsed time, in minutes, for the execution of an activity. |
| Process > AVERAGE WORKING TIME OF ACTIVITIES | function that returns the average working time, in minutes, spent on the execution of an activity. |
Conclusion
Thus, a formula can be edited and created to perform actions in the system.