Skip to content
+1-888-319-3663

COMMUNITY FORUM

Calculated field – syntax error

Tracy Jo Dance asked 5 years ago
I am following this directions in this post and getting a syntax error:
http://www.eonesolutions.com/help-article/kba-01064-m6m9x3/
Using a SmartConnect script to separate debits and credits when your source file only contains 1 column that has positive and negative values.
You need to create 2 calculated fields. One called DEBIT and the other called CREDIT.

Your DEBIT script will look like this:
if _amount>0 then return _amount else return 0 end if
Your CREDIT script will look like this:
if _amount < 0 then return System.Math.Abs(_amount) else return 0 end if
You need to replace _amount in the script with the actual name of the column that contains your debits and credits.
 
My calculation:
if _DEBIT > 0
 
then return _DEBIT
 
else return 0
 
end if
 
 
if _CREDIT < 0
 
then return System.Math.Abs(_CREDIT)
 
else return 0
 
end if
 
 
 
 
Answers
Jared Dux Staff answered 5 years ago
You need to create 2 separate calculated fields.  Right now it looks like you have one calculated field that is referencing two source columns _CREDIT and _DEBIT which I assume you don’t have as source fields since you are referring to an article where the credits and debits are in the same column.  So you would need to create a calculated field (called Debit or whatever you choose) and then add you debit script to that field, but you need to use the actual source column name that you have.  Do the same process for a credit calculated field.  For example, one of the calculated fields would be:
if _YOURSOURCEFIELDNAME > 0 then 
return _YOURSOURCEFIELDNAME
else 
return 0 
end if
Tracy Jo Dance replied 5 years ago

I did create two separate calculated fields – per the post which I included in the link

DEBIT field

if _DEBIT > 0

then return _DEBIT

else return 0

end if

CREDT field

if _CREDIT < 0 then return System.Math.Abs(_CREDIT) else return 0 end if

Jared Dux Staff replied 5 years ago

What is your source field name that contains the debits and credits? That is what you need to use in your calculation.

Tracy Jo Dance replied 5 years ago

I do know this. I’ve created these calculated fields in the past.
I selected the field directly from the Source Colum list.

Please update the post – the calculation needs to look exactly like this:

if _DEBIT > 0 then
return _DEBIT
else
return 0
end if

Jared Dux Staff replied 5 years ago

Updated my first response to fix the syntax. Initially I was concerned that you had two source columns right away in your scripts when the article you were looking at specifically was sorting debits and credits from one column. Anyways, good to see you got it working for your need.

Patrick Roth Staff answered 5 years ago
While vb.net is forgiving in a lot of respects (compared to C#), it is picky about how you format your code and where you put statements.
It doesn’t like code after a “then” statement or an “else” statement as you have it here.
So it will fail to compile on:

if _CREDIT < 0

then return System.Math.Abs(_CREDIT)

else return 0

end if
but if you switch it to

if _CREDIT < 0 then 
    return System.Math.Abs(_CREDIT)
else
  return 0
end if
then it should compile correctly.
 
Heidi answered 4 years ago
I am trying to create this calculated column where I’m looking up a pay code (DREG) and if that is the value, then I want the column to return the number 1, else return the number 0. This is my statement and I’m getting a syntax error:
If _CODE = “DREG”
then return 1
else 
return 0
 
I’ve added quotes on the 1 and 0 and removed them but still receive “”error in calculation” syntax error, twice.
end if
Lorren Zemke replied 4 years ago

Heidi,
As Pat had noted in his response that VB.NET is not forgiving when working with some statements. Also, always click Validate after writing or changing a script.

if _CODE = “DREG” then
return 1
else
return 0
end if


If you would like to submit an answer or comment, please sign in to the eOne portal.