2817897 - Basic Guidelines While Using Formula Editor in Workflow

This document is not a standard guideline, but is helpful in understanding about the rules while creating a formula in the Workflows.

SAP Cloud for Customer

You are configuring a new Workflow with "Field Update" action that uses "Formula" as update value.

Best-practice tips for structuring your formula:

• Always keep one space between every token. For example, 1+2 is wrong. It should be 1 + 2.

• The result of formula should be of same data type as the data type of field to be updated.

As an example:

1. Date time field can’t be updated with formula 1 + 2 (Because result type will be Integer).
2. Decimal fields can’t be updated with CONCATENATE(‘SAP’,’LABS’).

To use placeholder in formula, first placeholder should be mapped to appropriate field.

Using Syntax Check button:

After elaborating your formula, you may use "Syntax Check" to validate the logic. This feature will validate if the Placeholders, Operators and Functions are in a valid order and logically sound.

While the Syntax Check can validate a formula, it cannot preview the final outcome of the calculation.

You may face the error below when triggering your Workflow, even with a valid Syntax Check:

"Error: An error occurred while evaluating the formula."

When this happens, please review your formula. It means it's not possible to generate a result from your formula.

• For Fields of type Amount, Measure and Quantity:

1. To update Amount field, only placeholder mapped with Amount type fields can be used. Similarly, to update Measure type field, only placeholder mapped with measure type fields can be used and same goes for field of type Quantity.
2. Fields of type Amount, Measure and Quantity will have some unit like dollar, mg etc. So only numerical values can’t be used in formula. For example, if user want to update field Price then formula can’t be 1 + 1. Because it 1 + 1 will be not return any unit. Formula should have at least one placeholder of type Amount in this case.
3. While execution of workflow rule, placeholder used in formula should not have different unit. For example, if formula is #1# + #2# then #1# and #2# should be of same unit.

• For Fields of type date and datetime:

1. To update date and datetime field, result of formula should also be date and datetime.
2. It is not possible to calculate a period of duration by using the difference of two date or datetime fields to get an integer number.
3. Valid operations:

#1# + Duration
#1# - Duration
NOW( ) + Duration

Here #1# can be any date, datetime field or it can be NOW( ) function.

     3. Invalid operations:

#1# + #1#
Duration + Duration
#1# - #1#

4. Date and Datetime are mutually compatible. It means, if user uses date field to update datetime then by default, time 00:00:00 will get appended to date field. Similarly if user uses datetime field to update date field then time section will get truncated from datetime field.

• For Fields of type time:

1. To update time field, result of formula should also be of type time.
2. Valid operations:

#1# + Duration
#1# - Duration
NOW( ) + Duration

           Here #1# can be any time field or it can be NOW( ) function.

     3.Invalid operations:

#1# + #1#
Duration + Duration

Operators:

Add( + )
Close Bracket ( ‘)’ )
Divide ( / )
Multiply ( * )
Open Bracket ( ‘)’ )
Subtract ( - )

Functions

• ABS() : Absolute Value 

Example :  Absolute value of given list of numbers : +756.25, -315, +145, -980.35, -75, +1250.78, +560 are 756.25, 315, 145, 980.35, 75, 1250.78, 560 respectively.
Sample example to use in formula editor:  ABS( -2 )

• CONCATENATE : Concatenates two strings.

Sample example to use in formula editor: CONCATENATE( 'SAP' , ' Ariba' )
Result of above expression will be : SAP Ariba

       [Note] :

1. String input of CONCATENATE function should be under single quote ( ‘ ’ ).
2. Any kind of character, number and symbols can be get concatenated by this function. For example : if you want to add pin code to any address then you can use CONCATENATE( 'SAP Labs India' , ' 560048' ) .
   
   
3. This function can take only two string as input. If user want to concatenate more than two Strings, then use implicit function call as shown below:

CONCATENATE( 'SAP Labs India' , CONCATENATE( ' Whitefield' , ' Bengaluru' )  )
Result : SAP Labs India Whitefield Bengaluru

• DURATION : Add Duration to Date, Datetime and Time field

 Click of this function will open a new modal dialog where you can provide duration in form of Years, Months, Days, Hours, Minutes or Seconds. Modal dialog screen will vary according to the selected field ( which has to be updated ) If Selected field is of type Date or Datetime then user can provide duration in form of Years, Months, Days, Hours, Minutes or Seconds but if Selected field is of type Time then user can provide duration in form of Hours, Minutes or seconds only.

Sample example to use in formula editor for Date or Datetime: #1# + 0Y2M0D3h0m0s.
Here #1# can corresponds to any date or datetime field. Let say, Field corresponds to #1# has value 17/07/2019 : 08:40:03 then result will be 17/09/2019 : 11:40:03.

Sample example to use in formula editor for Time: #1# + 3h0m0s.
Here #1# can corresponds to any time field. Let say, Field corresponds to #1# has value 08:40:03 then result will be 11:40:03.

 

• MOD( ) : Calculates the integer remainder from dividing two numbers.

Example : 15 MOD 4  = 3
                15 MOD 3 = 0
                15 MOD 7 = 1
Sample example to use in formula editor : 15 MOD 4

• NOW( ) : To get current date time.
  Sample example to use in formula editor : NOW( ) 
  
  

• TOLOWER( ) : Converts a character string into Lower case.
  
  Sample example to use in formula editor : TOLOWER( 'SAP LAbs' )
  Result : sap labs
  If user want to use placeholder in the function, then it can be passed as TOLOWER( #1# ) 
  
  

• TOUPPER( ) : Converts a character string into Upper case.
  
  Sample example to use in formula editor : TOUPPER( 'sap LAbs' )
  Result : SAP LABS
  If user want to use placeholder in the function, then it can be passed as TOUPPER( #1# )
   

• SUBSTRING( , , ) : Returns Part of a Character String

Sample example to use in formula editor : SUBSTRING ( 'SAP LABS' , 4,4)
Result : LABS

Workflow, formula, Editor, Functions, Operators, syntax, error , KBA , LOD-CRM-WKF , Workflow , How To

SAP Cloud for Customer add-ins all versions ; SAP Cloud for Customer core applications all versions