API Functions
Below is a list of API functions available for use.
| # | Item Function id | Description | Parameters | Examples |
|---|---|---|---|---|
| 1 | fnIf | Function Type: Conditional | Value1, Condition, Value2, True, False | Example of fnIf |
| 2 | fnIsNull | Function Type: Conditional | Value1, True, False | Example of fnIsNull |
| 3 | fnAddDays | Function Type: Date Calcs | DateTime, Days | Example of fnAddDays |
| 4 | fnAddMonths | Function Type: Date Calcs | DateTime, Months | Example of fnAddMonths |
| 5 | fnDaysBetween | Function Type: Date Calcs | DateTime, DateTime | Example of fnDaysBetween |
| 6 | fnSubtractDays | Function Type: Date Calcs | DateTime, Days | Example of fnSubtractDays |
| 7 | fnSubtractMonths | Function Type: Number Calcs | DateTime, Months | Example of fnSubtractMonths |
| 8 | fnAbs | Function Type: Number Calcs | Value | Example of fnAbs |
| 9 | fnAdd | Function Type: Number Calcs | Value, Value | Example of fnAdd |
| 10 | fnCumulative | Function Type: Number Calcs | Value | Example of fnCumulative |
| 11 | fnDivide | Function Type: Number Calcs | Value, Divisor | Example of fnDivide |
| 12 | fnMax | Function Type: Number Calcs | Value | Example of fnMax |
| 13 | fnMin | Function Type: Number Calcs | Value | Example of fnMin |
| 14 | fnMultiply | Function Type: Number Calcs | Value1, Value2 | Example of fnMultiply |
| 15 | fnSubtract | Function Type: Number Calcs | Value1, Value2 | Example of fnSubtract |
| 16 | fnSum | Function Type: Number Calcs | Value1, Value2 | Example of fnSum |
| 17 | fnComma | Function Type: Others | Example of fnComma | |
| 18 | fnConcat2Strings | Function Type: Others | String1, String2 | Example of fnConcat2Strings |
| 19 | fnEqualsSign | Function Type: Others | Example of fnEqualsSign | |
| 20 | fnFalse | Function Type: Others | Example of fnFalse | |
| 21 | fnFormatDateWithMask | Function Type: Others | Date, Format Mask | Example of fnFormatDateWithMask |
| 22 | fnFormatNumberAsWords | Function Type: Others | Number | Example of fnFormatNumberAsWords |
| 23 | fnFormatNumberWithMask | Function Type: Others | Number, Format Mask | Example of fnFormatNumberWithMask |
| 24 | fnFormatRawNumber | Function Type: Others | Number | Example of fnFormatRawNumber |
| 25 | fnFormatShortDate | Function Type: Others | Date | Example of fnFormatShortDate |
| 26 | fnFormatYearAndMonth | Function Type: Others | Date | Example of fnFormatYearAndMonth |
| 27 | fnFormatYearAndMonthName | Function Type: Others | Date | Example of fnFormatYearAndMonthName |
| 28 | fnNull | Function Type: Others | Example of fnNull | |
| 29 | fnReplace | Function Type: Others | String value, Search string, Replace string | Example of fnReplace |
| 30 | fnSpace | Function Type: Others | Number of space | Example of fnSpace |
| 31 | fnStaticText | Function Type: Others | Example of fnStaticText | |
| 32 | fnString | Function Type: Others | Non-String Value | Example of fnString |
| 33 | fnStringLength | Function Type: Others | String | Example of fnStringLength |
| 34 | fnSubstring | Function Type: Others | String value, Starting index (from 1), Length | Example of fnSubstring |
| 35 | fnTrue | Function Type: Others | Example of fnTrue | |
| 36 | fnUTCDate | Function Type: Others | Example of fnUTCDate | |
| 37 | fnUTCDateTime | Function Type: Others | Example of fnUTCDateTime | |
| 38 | fnLookupItemValue | Function Type: Lookups. Use as Sideway lookup | Dataobject id, Lookup Item id, Key Item Value | Example of fnLookupItemValue |
| 39 | fnLookupItemValue2Params | Function Type: Lookups. Use as Sideway lookup | Dataobject id, Lookup Item id, Search Item id1, Search Item id2, Search Item Value1, Search Item Value2 | Example of fnLookupItemValue2Params |
| 40 | fnLookupItemValue3Params | Function Type: Lookups. Use as Sideway lookup | Dataobject id, Lookup Item id, Search Item id1, Search Item id2, Search Item id3, Search Item Value1, Search Item Value2, Search Item Value3 | Example of fnLookupItemValue3Params |
Example of fnIf (Top)
Parameters: fnIf(i:itemref, condition, i:itemref, True: as fixed value or i:itemref, False: as fixed value or i:itemref)
Example of returning fix value, if buysellcode equal B, True: set value to Buy, False: set value to Sell.
fnIf(i:buysellcode,eq,B,Buy,Sell)
Example of returning itemref, if buysellcode equal S, True: set value to Settlementvalue, False: set value to Settlementvalue*-1.
fnIf(i:buysellcode,eq,S,i:settlementvalue,fnMultiply(i:settlementvalue,-1))
Example of condition is true, if actualbillofladingdateind equal true, True: set value to billofladingdate, False: set value to null.
fnIf(i:actualbillofladingdateind,eq,fnTrue(),i:billofladingdate,fnNull())
*For False,use fnFalse()*
Available Conditions for use below:
| Param | Description |
|---|---|
| .cn. | Contains |
| .nc. | Not Contains |
| .eq. | Equal |
| .ne. | Not Equal To |
| .in. | In |
| .ni. | Not In |
| .lt. | Less Than |
| .gt. | Greater Than |
| .ge. | Greater Than or Equal To |
| .le. | Less Than or Equal To |
Example of fnIsNull (Top)
Parameters: fnIsNull(i:itemref, True: as fixed value or i:itemref, False: as fixed value or i:itemref)
Example, if settlementitem settlementvalue is null, Null: set value to settlementvalue, Not Null: set value to 0.
fnIsNull(i:settlementitem.settledvalue,i:settlementvalue,0)
Example of fnAddDays (Top)
Parameters: fnAddDays(i:itemref, Number of Days)
Return: Datetime or Date
Example, adding 2 days to transactionstartdatetime.
fnAddDays(i:transactionstartdatetime,2)
Example of fnAddMonths (Top)
Parameters: fnAddMonths(i:itemref, Number of Months)
Return: Datetime or Date
Example, adding 2 months to transactionstartdatetime.
fnAddMonths(i:transactionstartdatetime,2)
Example of fnDaysBetween (Top)
Parameters: fnDaysBetween(i:itemref, i:itemref)
Return: Number
Example, finding number of days between transactionstartdatetime and transactionstartdatetime.
fnDaysBetween(i:transactionstartdatetime,i:transactionenddatetime)
Example of fnSubtractDays (Top)
Parameters: fnSubtractDays(i:itemref, Number of Days)
Return: Datetime or Date
Example, Subtracting 3 days from transactionstartdatetime.
fnSubtractDays(i:transactionstartdatetime,3)
Example of fnSubtractMonths (Top)
Parameters: fnSubtractMonths(i:itemref, Number of Months)
Return: Datetime or Date
Example, subtracting 3 months from transactionstartdatetime.
fnSubtractMonths(i:transactionstartdatetime,3)
Example of fnAbs (Top)
Parameters: fnAbs(i:itemref)
Return: Number
Example, returning absolute number for settledvalue.
fnAbs(i:settledvalue)
Example of fnCumulative (Top)
Parameters: fnCumulative(i:itemref)
Return: Number
Example, returning cumulative total of totalchangevalue.
fnCumulative(i:totalchangevalue)
Example of fnDivide (Top)
Parameters: fnDivide(i:itemref, fixed value or i:itemref)
Return: Number
Example, finding no of demurrage days by dividing settlementvalue by demurragedailyrate.
fnDivide(i:settlementvalue,i:demurragedailyrate)
Example of fnMax (Top)
Parameters: fnMax(i:itemref)
Return: Date or Number
Example, returning the maximum of transactionstartdatetime.
fnMax(i:transactionenddatetime)
Example of fnMin (Top)
Parameters: fnMin(i:itemref)
Return: Date or Number
Example, returning the minimum of transactionstartdatetime.
fnMin(i:transactionenddatetime)
Example of fnMultiply (Top)
Parameters: fnMultiply(i:itemref, fixed value or i:itemref)
Return: Number
Example, finding total amount by multiplying effectiveamount with transactionquantity.
fnMultipfnMultiply(i:effectiveamount,i:transactionquantity)
Example of fnSubtract (Top)
Parameters: fnSubtract(i:itemref, fixed value or i:itemref)
Return: Number
Example, finding the floating price of derivative by subtracting pricingfixedprice with effectiveprice.
fnSubtract(i:pricingfixedprice,i:effectiveprice)
Example of fnSum (Top)
Parameters: fnSum(i:itemref, fixed value or i:itemref)
Return: Number
Example, summing all the contractitemquantity.
fnSum(i:contractitemquantity)
Example of fnComma (Top)
Parameters: fnComma()
Return: ,
Example, returning a string using "incotermscode,locationname". Example: FOB,Singapore
fnConcat3Strings(i:incotermscode,fnComma(),i:locationname)
Example of fnConcat2Strings (Top)
Parameters: fnConcat2Strings(i:itemref, fixed value or i:itemref)
Return: String
Example, returning a string using caclcassignedpct and %.
fnConcat2Strings(fnFormatNumber(i:calcassignedpct),%)
*There are functions to concatenate up to 10 strings. Example are : fnConcat2Strings, fnConcat3Strings...fnConcat10Strings.*
Example of fnEqualsSign (Top)
Parameters: fnEqualsSign()
Return: =
Example, returning a string using "incotermscode=locationname". Example: FOB=Singapore.
fnConcat3Strings(i:incotermscode,fnEqualsSign(),i:locationname)
Example of fnFalse (Top)
Parameters: fnFalse()
Example of condition is false, if actualbillofladingdateind equal false, True: set value to null, False: set value to billofladingdate.
fnIf(i:actualbillofladingdateind,eq,fnFalse(),fnNull(),i:billofladingdate)
Example of fnFormatDateWithMask (Top)
Parameters: fnFormatDateWithMask(i:itemref as Datetime, Format Mask)
Return: Formatted Date
Example returning today datetime in MM/dd/yyyy hh:mm:ss tt. Example: 2023-01-16 00:00:00Z
fnFormatDateWithMask(fnUTCdate(),u)
The available Date Format Mask can be found here https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings
Example of fnFormatNumberAsWords (Top)
Parameters: fnFormatNumberAsWords(i:itemref, fixed value or i:itemref)
Return: String
Example, returning settledvalue in words in US dollars.
Example: settledvalue=10,000, Return: USD Ten Thousand Dollars Only.
fnFormatNumberAsWords(i:settledvalue)
Example of fnFormatNumberWithMask (Top)
Parameters: fnFormatNumberWithMask(i:itemref as Number, Format Mask)
Return: Formatted Number
Example returning contractitemquantity in N2 format.
Example: contractitemquantity=10,000 ; Return:10,000.00.
fnFormatNumberWithMask(i:contractitemquantity,N2)
The available Number Format Mask can be found here https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-numeric-format-strings
Example of fnFormatRawNumber (Top)
Parameters: fnFormatRawNumber(i:itemref)
Return: Number
Example, returning settlementvalue without thousand separator.
Example: settledvalue=1,014,840.3 ; Return:1014840.3.
fnFormatRawNumber(i:settledvalue)
Example of fnFormatShortDate (Top)
Parameters: fnFormatShortDate(i:itemref)
Return: Date
Example, returning startdatetime in dd/MM/yyyy format.
Example: startdatetime=2022-11-01T00:00Z ; Return:01/11/2022.
fnFormatShortDate(i:startdatetime)
Example of fnFormatYearAndMonth (Top)
Parameters: fnFormatYearAndMonth(i:itemref)
Return: Date
Example, returning actualdatetime in yyyy-MM format.
Example: actualdatetime=2022-11-01T00:00Z ; Return:2022-11.
fnFormatYearAndMonth(i:actualdatetime)
Example of fnFormatYearAndMonthName (Top)
Parameters: fnFormatYearAndMonthName(i:itemref)
Return: String
Example, returning actualdatetime in yyyyMM format.
Example: actualdatetime=2022-11-01T00:00Z ; Return: 2022Nov.
fnFormatYearAndMonthName(i:actualdatetime)
Example of fnNull (Top)
Parameters: fnNull()
Return:
Example, returning blank(Null) value.
fnNull()
Example of fnReplace (Top)
Parameters: fnReplace(i:itemref,String to replace, Replace String)
Return: String
Example, returning settlementdate in string as yyyyMMdd replacing - with blank.
Example: settlementdate=2022-08-16 ; Return: 20220816.
fnReplace(i:settlementdate,-,)
Example of fnSpace (Top)
Parameters: fnSpace(Number of Space)
Return: Number of space
Example, returning a concatenation of incotermscode with 2 space and locationname.
fnConcat3Strings(i:incotermscode,fnSpace(2),i:locationname)
Example of fnStaticText (Top)
Parameters: fnStaticText(Text)
Return: Text Value
Example, returning static text value "Order".
fnstatictext(Order)
Example of fnString (Top)
Parameters: fnString(Value)
Return: String
Example, returning a concatenation of startdatetime(cast to String) and enddatetime(cast to String) with - inbetween.
fnConcat3Strings(fnString(fnFormatShortDate(i:startdatetime)), - ,fnString(fnFormatShortDate(i:enddatetime))))
Example of fnStringLength (Top)
Parameters: fnStringLength(i:itemref)
Return: Number
Example, returning the length of contractreference.
Example: contractreference=test100 ; Return: 7.
fnStringLength(i:contractreference)
Example of fnSubstring (Top)
Parameters: fnSubstring(i:itemref, Start index (from 1), length)
Return: String
Example, returning first 4 string of contractreference.
Example: contractreference=test100 ; Return: test.
fnSubstring(i:contractreference,1,4)
Example of fnTrue (Top)
Parameters: fnTrue()
Example of condition is true, if actualbillofladingdateind equal true, True: set value to billofladingdate, False: set value to null.
fnIf(i:actualbillofladingdateind,eq,fnTrue(),i:billofladingdate,fnNull())
Example of fnUTCDate (Top)
Parameters: fnUTCDate()
Return: Current Date
Example, returning current date in yyyy-MM-dd format.
Example: Return: 2023-01-13.
fnUTCDate()
Example of fnUTCDateTime (Top)
Parameters: fnUTCDateTime()
Return: Current Datetime
Example, returning current datetime in yyyy-MM-ddThh:mm:ss.000z format.
Example: Return: 2023-01-13T06:32:18.000Z.
fnUTCDateTime()
Example of fnLookupItemValue (Top)
Parameters: fnLookupItemValue(Dataobject id, Lookup Item id, i:itemref)
Return: Value
Example, returning the containername.
fnLookupItemValue(XXX_container,containername,i:containerseqnum)
*When using fnLookupItemValue, i:itemref needs to be the primary key of the object itself.*
Example of fnLookupItemValue2Params (Top)
Parameters: fnLookupItemValue2Params(Dataobject id, Search Item id1, Search Item id2, i:itemref1 or fixed value1, i:itemref2 or fixed value2)
Return: Value
Example, returning the actualreference using transactionseqnum and invoicequantityind is true.
fnLookupItemValue2Params(XXX_transaction_actual,actualreference,transactionseqnum,invoicequantityind,i:transactionseqnum,fnTrue())
Example of fnLookupItemValue3Params (Top)
Parameters: fnLookupItemValue3Params(Dataobject id, Search Item id1, Search Item id2, Search Item id3, i:itemref1 or fixed value1, i:itemref2 or fixed value2, i:itemref3 or fixed value3)
Return: Value
Example, returning the netactualquantity2 using transactionseqnum and invoicequantityind is true and unitcode is MT.
fnLookupItemValue3Params(XXX_transaction_actual,netactualquantity2,transactionseqnum,invoicequantityind,netactualquantity2unitcode,i:transactionseqnum,fnTrue(),MT)