SAP Knowledge Base Article - Public

2817897 - Basic Guidelines While Using Formula Editor in Workflow

Symptom

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

Environment

SAP Cloud for Customer

Resolution

  • Always keep one space between every token. For example, 1+2 is wrong. It should be 1 + 2.
  • 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.
  • 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. Valid operations:

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

Here #1# can be any date, datetime field or it can be NOW( ) function.
Above valid expressions are just base operation. User can use this basic operation to make complex formula. For example, Complex formula can be ( ( #1# + Duration ) – ( Duration + #1# ) + NOW( ) ).

     3. Invalid operations:

#1# + #1#
Duration + Duration

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
Duration + #1#
#1# - Duration

            Here #1# can be any time field or it can be NOW( ) function.
           Above valid expressions are just base operation. User can use this basic operation to make complex formula. For example, Complex formula can be ( ( #1# + Duration ) – ( Duration + #1# ) + NOW( ) ).

     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

Keywords

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

Product

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