 |
|
String and value functions
|
| |
The XConnector supports several string functions to manipulate or lookup strings.
The result of these functions a string that is put in the output XML or used in the web- or entity service communication.
fnGetString
|
Description:
|
Returns a string based on a select query execute on the Exact database.
|
|
Syntax:
|
fnGetString(string)
The fnGetString function syntax has these parts:
string Required. String expression with the SQL statement to excute on the Exact database. This can be a Select-statement but also the execution of an Stored Procedure.
A value from the source data used by the XConnector can be used in the statement by putting the column number between braces {}.
Note: the Exact database used in this query should be indicated with the setting Adm
|
|
Example 1:
|
Adm;"200"
Invoices;InvoiceTo;fngetstring("select debnr from cicmpy where textfield1 = '{1}'")
returns the debnr from the Company table where freefield textfield1 is equal to the code from source column 1.
|
|
Example 2:
|
Adm;"200"
Pricelists;Unit;fngetstring("select rtrim(packagedescription) from items where itemcode = '{1}'")
returns the packagedescription from the Items table where itemcode is equal to the itemcode from source column 1.
|
|
Example 3:
|
Adm;"200"
Invoices;DeliveryMethod;fngetstring("exec [StoredProcedureName] @ParameterName = '{3}'....")
returns a delivery method code from the sql stored procedure containing the required logic. The value of column 3 from the source is passed to the stored procedure as parameter.
The Stored Procedure itself is not a part of the XConnector.
|
fnIf
|
Description:
|
If-Then-Else function to evaluate a source field
|
|
Syntax:
|
fnIf(Logical test, True-statement, False-statement)
The fnGetString function syntax has these parts:
Logical test Required. Must evaluate to True of False. The following comparison operators can be used: =, <>, >, <, <=, >=.
Next to this the logical operators AND and OR can be used in the logical test.
True-statement Required. Statement if condition evaluated to True
False-statement Required. Statement if condition evaluated to False
|
|
Example 1:
|
Pricelists;Unit;fnif({4}="A",{2},{3})
If the value in column 4 is "A" then return the value from colum 2 else return the value of column 3.
|
|
Example 2:
|
Pricelists;Unit;fnif({4}="A","X" ,"Y")
If the value in column 4 is "A" then return "X" else "Y".
|
|
Example 3:
|
Pricelists;Unit;fnif({4}="A" OR {4}="B","X" ,"Y")
If the value in column 4 is equal "A" OR the value in colum 4 is equal to "B" then return "X" else "Y".
|
|
Example 4:
|
Pricelists;Unit;fnif({4}="A" AND {5}="B","X" ,"Y")
If the value in column 4 is equal "A" AND the value in colum 5 is equal to "B" then return "X" else "Y".
|
fnRowCount
|
Description:
|
Add a row number to an XML field.
The value returned takes the Startrow import definition setting into account.
So when your data starts on the 3rd row in the source the StartRow setting should be set to 3.
When handling row 3 from the source, the fnRowCount will return a "1"
|
|
XConnector version
|
439
|
|
Syntax:
|
fnRowCount
The fnRowCount has no parts.
|
|
Example 1:
|
Invoices;Reference2;fnrowcount
Puts the record number from the source in the invoice reference2.
|
|
Example 2:
|
Entry;YourRef;fngetstring("select '{5}' + right(('000' + '{fnrowcount}'),3)")
Here we use the fngetstring sql function to combine the value of source field 5 with the rowcount.
Please note the the reference to fnrowcount is put in between braces { }.
And we pre-fill the rowcount with zero's.
source field5 = "xxxxxxxxxx" on row 1 results in: "xxxxxxxxxx001"
source field5 = "xxxxxxxxxx" on row 15 results in: "xxxxxxxxxx015"
|
fnMid
|
Function:
|
|
|
Description:
|
Returns a substring containing a specified number of characters from a string.
|
|
Syntax:
|
fnMid(string, start, length)
The fnMid function syntax has these parts:
string Required. String expression from which characters are returned.
start Required; Long. Character position in string at which the part to be taken begins.
length Optional; Long. Number of characters to return.
|
|
Example 1:
|
Invoices;Description;fnMid("XConnector", 3, 4)
returns= "onne"
Invoices;Description;fnMid(6, 3, 4)
returns 4 characters starting from postion 3 from the 6th field of the source file.
|
fnLeft
|
Description:
|
Returns a substring containing a specified number of characters from the beginning (left side) of a string.
|
|
Syntax:
|
fnLeft(string, length)
The Left function syntax has these parts:
string Required. String expression from which the leftmost characters are returned.
length Required; Long. Numeric expression indicating how many characters to return.
|
|
Example:
|
Invoices;Description;fnLeft("XConnector", 3) ' returns "XCo"
Invoices;Description;fnLeft(6, 3) ' returns the first 3 postion of the 6th field of the source file.
|
fnRight
|
Function:
|
fnRight
|
|
Description:
|
Returns a substring containing a specified number of characters from the end (right side) of a string.
|
|
Syntax:
|
fnRight(string, length)
The fnRight function syntax has these parts:
string Required. String expression from which the rightmost characters are returned.
length Required; Long. Numeric expression indicating how many characters to return.
|
|
Example:
|
Invoices;Description;fnRight("XConnector", 3) ' returns "tor"
Invoices;Description;fnRight(6, 3) ' returns the last 3 postion of the 6th field of the source file.
|
fnConcatenate
|
Description:
|
Joining two character strings.
Maximum of 10 strings can be concatenated with the fnConcatenate.
|
|
Syntax:
|
fnConcatenate(string1, string2 [, string3] [, string4] [, string5] [, string6] [, string7] [, string8] [, string9] [, string10])
The fnConcatenate function syntax has these parts:
string Required. String expression. This can also refer to a column or field in the source file
|
|
Example:
|
Invoices;Description;fnConcatenate("Fixed Text ", 3) ' returns "Fixed Text 123"
Where the content of field 3 has the text "123"
|
fnNewCompanyCode
|
Description:
|
get the next free company code from Exact
|
|
Syntax:
|
Accounts;Accountcode;fnNewCompanyCode(ForeignKeyColumn optional)
The fnConcatenate function syntax has one optional parameter.
Without the ForeignkeyColumn a company code is created for every record.
In case the source file has duplicated companies the column index of the foreign key (eg. external customer number) can be indicated to prevent generating multiple numbers for the same company.
Note: the Exact database used in this query should be indicated with the setting Adm
|
|
Example 1:
|
Account;adm;"200"
Accounts;Accountcode;fnNewCompanyCode
Is generating a new company code for every record from the source file
|
|
Example 2:
|
Account;adm;"200"
Accounts;Accountcode;fnNewCompanyCode(1)
Is generating a new company code for every record but skips records in which the foreign key is duplicated.
In this example the foreign key is located in column 1 of the source file.
|
External company code
|
Name
|
Result
|
|
1010
|
Company A
|
New Company code generated
|
|
1010
|
Company A
|
Skipped
|
|
1020
|
Company B
|
New Company code generated
|
|
1030
|
Company C
|
New Company code generated
|
|
1030
|
Company C
|
Skipped
|
|
1020
|
Company B
|
Skipped
|
|
|
Important remarks:
|
1) the XConnector does not increment the setting in Exact. The setting is updated automatically by Exact during import.
2) Numbering conflicts may occure when accounts are entered manually in between the generation and import of an xml file by the XConnector.
3) After generating an xml file by the XConnector it should be imported into Exact before generating the next one.
|
fnSum
|
Description:
|
Adding up multilple values. (max. 5)
|
|
Syntax:
|
fnSum(value1, value2 [, value_n])
The fnSum function syntax has the following arguments:
value1, value 2 required. Expression. These can also refer to a column or field in the source.
|
|
Example:
|
Orderline;Quantity;fnSum({5}, {6}) returns "15.41"
Where the content of field 5 has the value "10.20" and field 6 has the value "5.21"
|
fnSubstitute
|
Description:
|
Substitutes new_text for old_text in a text string.
|
|
Syntax:
|
fnSubstitute(text, old_text, new_text)
The SUBSTITUTE function syntax has the following arguments:
Text required. The text or the reference to a string in the source containing text for which you want to substitute characters.
Old_Text required. Required. The text you want to replace.
New_Text required. Required. The text you want to replace old_text with.
|
|
Example:
|
Orderline;Project;fnSubstitute({5},"BR","BRON-") returns "BRON-41"
Where the content of field 5 has the value "BR41"
|
next to these:
- fnUpper - converts a string to upper-case. fnUpper(string).
- fnLower - converts a string to lower-case. fnLower(string).
- fnAbs - returns the absolute value. fnAbs(value).
- fnInverse - mulitplies a value with -1. Using a minus sign also works. So fninverse(value) has the same result as -value.
The - can also be used in a fnSum function.
- fnGetDate - returns the current date.
- fnLtrim, fnRtrim, fnTrim - Remove leading and/or trailing spaces from a string.
- AND and OR logical operators are supported in the fnIf function. (see above)
Doc id: 00.001.824