Tips and Tricks Examples How to point to a specific cell in Excel source file Refer to the the cell sheet name and coordinate. Use the following syntax: [sheetname]![cellcoordinate] Entrydate;import!B12 refers to cell B12 on the sheet with the name import. entrydate;b12 refers to cell b12 What values can be used for boolean fields The values "1", "J", "JA", "Y" and "YES" are considered as a True value Items;DiscountItem;"Y" How to refer to values in a Ascii Source file Set the start position and length of the field. Also make sure to set the SourceType to "FIX" Items;SourceType;"FIX" Items;Itemcode;3;8 Items;Description;11;15 example: below the item code is located from position 3 with a length of 8. The Item description starts at postion 11 with a has a length of 15. characters. 1234567890123456789012345 --ITEMCODEITEMDESCRIPTION--- Using a value from an ASCII file in a formula like fngetsting should be done by putting the start position and lenght separated by a semi collon and between so called curly brackets {startpostion;lenght}. fngetstring("select '{3;11}'") How to work with addresses in the Accounts xml Use POS, DEL, VIS and INV. These are mandatory for Main contact persons. For non-main contact persons the POS is mandatory and the DEL is optional. ACCOUNT;DEFAULTYN;"1" ACCOUNT;ADDRESSTYPECP;"INV" Date values Date values can be delivered to the XConnector in several formats. In Excel source files the date format is recognized automatically. In CSV files the format mask should be set. Entry;Entrydate;6 Entry;EntrydateFormat;"dd-mm-yyyy" Working with sign columns When a source has a separate column to indicate the sign of a value or quantity. As negative the following values are recognized: "-", "N", "C", "S", "*" lnAmountSign;10 Working with factors This is useful when the value in the source has to be multiplied with a factor. Ie. When an amount is stated in cents or thousands. lnAmountFactor;"0.01" value in the source file is stated in cents, so it will be multiplied with 0.01. So 1250 becomes 12.50 lnAmountFactor;"1000" value in the source file is stated in thousands and will be multiplied with 1000. So 12,500 becomes 12500. How to import Synergy Request / Absences free fields. The numbering used in Synergy and the XML import file are not completely transparent. Use the following numbers to get to the correct result. Targetted freefield Use Xconnector setting XML import file result Free Text 1 - 15 FreeText1..15 <FreeText number="1 - 15">...</FreeText> Free GUID 1 - 10 FreeText16..25 <FreeText number="16 - 25">...</FreeText> Free Date 1 - 5 FreeDate1..5 <FreeDate number="1 - 10">...</FreeDate> Free Amount 1 - 5 FreeNumber1..5 <FreeNumber number="1 - 5">...</FreeNumber> Free Number 1 - 5 FreeNumber6..10 <FreeNumber number="6 - 10">...</FreeNumber> Free Yes/No 1 - 5 FreeYesNo1..5 <FreeYesNo number="1 - 5">...</FreeYesNo> Also see Exact doc: 03.374.379 on the customer / partner portal Request;FreeText16;"619e5925-dbab-4a36-8c54-86b4afc4f569" After import in Exact /Synergy the value will be at the freeguid1. Working with empty values By default unused fields in the import definition or empty values from the source file are not taken into the XML import file. In case an empty fields is needed the following setting can be used: "|empty|". This can be combined with the fnif function to evaluate the content of the source field Items;Freetext3;"|empty|" Items;Freetext3;fnif({41}="","|empty|",{41})
The numbering used in Synergy and the XML import file are not completely transparent. Use the following numbers to get to the correct result.
Also see Exact doc: 03.374.379 on the customer / partner portal
In case an empty fields is needed the following setting can be used: "|empty|".
This can be combined with the fnif function to evaluate the content of the source field
Items;Freetext3;"|empty|"
Items;Freetext3;fnif({41}="","|empty|",{41})
Doc id: 00.005.494