Working with lookup fields When importing data supplied by an external system it may happen the the external code does not match the codes used in Exact. Examples of this are general ledger-, items- and customer codes. Same result can be reached with the fngetstring function. In the fngetstring function you can use your own written query to lookup the needed value from Exact. See: Lookup and manipulation functions The XConnector can make a mapping between the external code and the Exact code. For this the external code must be available in the Exact database. For example an free field or an other field in the the Exact table. While generating the XML file the XConnector will lookup the required code from the Exact database. The import definition defines what field is used for the lookup function. Example: For the entry of time sheets an hour entry file is supplied by an external system. The personcode used in the external system is usr_id. In the XML definition the res_id is needed. The hourcode used in the external system is an other code than the hour(item)code used in Exact. The external code is stored in freefield6 in the Item maintenance of Exact. Import definition: Adm to define the Exact database LookupHumresid to define the reference field in the Humres table containing the Resource code from the source file LookupItemcode to define the reference field in the Item table containing the itemcode from the source file. HOURS;ADM;"100" HOURLINE;LNRESOURCE;2 HOURLINE;LOOKUPHUMRESID;"usr_id" HOURLINE;LNITEM;3 HOURLINE;LOOKUPITEMCODE;"UserField_06" The XConnector will query the humres table and get the res_id based on the value in the usr_id field with is supplied in kolom2 of the source file. An external Item(hour) code is stored in column 3 of the source file. This code is also present in the in the FreeTextField6 field of the Item table. The XConnector will query the Items table and get the Itemcode and use this in the XML file Supported lookup fields Subject Lookup in table Retreived field Idfparameter linelookup Account Contact persons Hours Request Items Budgets Item prices General ledger Resources Financial entries Exchange rates Sales invoices Sales orders Purchase orders Quotations Projects Header Line Humresid humres res_id lookupHumresid lookuplnHumresid ü ü Itemcode items itemcode lookupItemcode lookuplnItemcode ü ü ü ü ü ü Itemgroup Itemassortment Code lookupItemgroup lookuplnItemgroup General ledger grtbk reknr lookupReknr lookuplnReknr ü Accountcode cicmpy cmp_code lookupAccountcode lookuplnAccountcode ü Accountcode cicmpy debcode/crdcode lookupAccountcode lookuplnAccountcode ü ü ü Costcenter kstpl kstplcode lookupCostcenter lookuplnCostcenter ü ü ü ü Costunit kstdr kstdrcode lookupCostunit lookuplnCostunit ü ü ü ü Project PRProject ProjectNr lookupProject lookuplnProject ü ü ü Companysize cisize siz_code lookupCompanysize lookuplnCompanysize ü ü Companyorigin source src_code lookupCompanyorigin lookuplnCompanyorigin ü ü Companysector cisect sct_code lookupCompanysector lookuplnCompanysector ü ü Companysubsector subsectors SubSectorID lookupCompanysubsector lookuplnCompanysubsector ü ü Freetext16 Absences Id lookupFreetext16 ü* Freetext17 Absences Id lookupFreetext17 ü* Freetext18 Absences Id lookupFreetext18 ü* Freetext19 Absences Id lookupFreetext19 ü* Freetext20 Absences Id lookupFreetext20 ü* Freetext21 Absences Id lookupFreetext21 ü* Freetext22 Absences Id lookupFreetext22 ü* Freetext23 Absences Id lookupFreetext23 ü* Freetext24 Absences Id lookupFreetext24 ü* Freetext25 Absences Id lookupFreetext25 ü*
Working with lookup fields When importing data supplied by an external system it may happen the the external code does not match the codes used in Exact. Examples of this are general ledger-, items- and customer codes.
Same result can be reached with the fngetstring function. In the fngetstring function you can use your own written query to lookup the needed value from Exact. See: Lookup and manipulation functions
The XConnector can make a mapping between the external code and the Exact code. For this the external code must be available in the Exact database. For example an free field or an other field in the the Exact table. While generating the XML file the XConnector will lookup the required code from the Exact database. The import definition defines what field is used for the lookup function.
Example: For the entry of time sheets an hour entry file is supplied by an external system. The personcode used in the external system is usr_id. In the XML definition the res_id is needed. The hourcode used in the external system is an other code than the hour(item)code used in Exact. The external code is stored in freefield6 in the Item maintenance of Exact.
Import definition: Adm to define the Exact database LookupHumresid to define the reference field in the Humres table containing the Resource code from the source file LookupItemcode to define the reference field in the Item table containing the itemcode from the source file. HOURS;ADM;"100" HOURLINE;LNRESOURCE;2 HOURLINE;LOOKUPHUMRESID;"usr_id" HOURLINE;LNITEM;3 HOURLINE;LOOKUPITEMCODE;"UserField_06"
The XConnector will query the humres table and get the res_id based on the value in the usr_id field with is supplied in kolom2 of the source file. An external Item(hour) code is stored in column 3 of the source file. This code is also present in the in the FreeTextField6 field of the Item table. The XConnector will query the Items table and get the Itemcode and use this in the XML file
Supported lookup fields
Doc id: 00.001.068