INT PH/Cloud Info: Expression Builder in Integrations

Summary:

This article details syntax related to making expressions in Solver Cloud and Private host. This article will help you better understand the syntax required to transform your data when coming into Solver. 

Note: Complex aggregation is not supported in the Expression Builder. Major data alteration should be handled in the source system. 

Article Sections:

• More Information
  • Syntax Tutorial
  • IF Statement
  • Nested IF Statement
  • IF Statement Combining with (OR...)
  • IF Statement Combining with (AND...)
  • Expression Samples
• Properties

More Information:

Syntax Tutorial

Solver integrations support limited Excel “IF statements” in the Expression Builder. A complex IF statement such as nested IF, IF(OR), IF(AND) may not work unless it contains proper syntax. Below are some examples of accepted formulas along with the translation from Excel to Javascript. 

• Expressions within Expression Builder need to be in Javascript Functions syntax.
• The Expression Builder is case-sensitive. 
  • i.e. if source field is [Payment], using [PAYMENT] will not return data. 

IF Statement

? = THEN
: = ELSE

Example

• Excel: IF([Account]='40010' , 'Revenue' , 'Other')
• Cloud: ([Account]='40010') ? 'Revenue' : 'Other'

Nested IF Statement

? = THEN
: = ELSE

Example

• Excel: IF([Account]='40010' , 'Revenue' , IF([Account]='60010' , 'Expense' , 'Other')))
• Cloud: ([Account]='40010') ? 'Revenue' : ([Account]='60010') ? 'Expense' : 'Other'

IF Statement Combining with (OR…)

|| = OR
? = THEN  (Take note of parentheses in the Cloud formula)
: = ELSE

Example #1

• Excel: IF(OR(LEFT([Account Number] , 1)='2' , LEFT([Account Number] , 1)='3' , LEFT([Account Number] , 1)='4') , -1*[Amount] , [Amount])
• Cloud: (LEFT([Account Number] , 1)='2' || LEFT([Account Number] , 1)='3' || LEFT([Account Number] , 1)='4') ? -1*[Amount] : [Amount]

Example #2

To replace cells in condition A or B with a different value, otherwise keep the original value

• Excel: IF((OR([Entity]='SAS' , [Entity]='sus')) , 'CORP' , [Entity])
• Cloud: IF(([Entity]='SAS' || [Entity]='sus') , 'CORP' , [Entity])

IF Statement Combining with (AND…)

&& = AND  (Take note of parentheses in the Cloud formula)
? = THEN
: = ELSE

Example

• Excel: IF(AND(LEFT([Account Number] , 1)='2' , right([Account Number] , 1)='3' , -1*[Amount] , [Amount])
• Cloud: (LEFT([Account Number] , 1)='2' && Right([Account Number] , 1)='3' ? -1*[Amount] : [Amount]

Note: If copying and pasting formulas directly from this KB does not work, the KB might have converted the single quotes to apostrophes. Replace the apostrophe with single quote should resolve this issue. 

Expression Samples

text([PostingDate],"yyyymmdd")

if([PostDate]='06/01/2019','20190901',text([PostDate],'yyyymmdd'))

left([Period],6)+'01'

concatenate([Period],'01')

new Date(Date.parse([CreatedOn]) + (N*60*60*1000))

right('000'+[Employee],N)

[RoomTypeDescription].trim()).toUpperCase()

parseFloat([Monthly_Amount]).toFixed(16)

Properties:

Cloud

Private Host