Calculated field – syntax error
Tracy Jo Dance asked 5 years ago
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 ifYou 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
Jared Dux Staff answered 5 years ago
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
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 5 years ago
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 5 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