 |
|
Lookup fields
|
| |
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 |
|
|
|
|
ü* |
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| *) Synergy import of guidfields. See Exact doc 03.374.379 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Free Text 1 - 15: <FreeText number="1 - 15">...</FreeText> |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Free GUID 1 - 10: <FreeText number="16 - 25">...</FreeText> |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Free Date 1 - 5: <FreeDate number="1 - 10">...</FreeDate> |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Free Amount 1 - 5: <FreeNumber number="1 - 5">...</FreeNumber> |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Free Number 1 - 5: <FreeNumber number="6 - 10">...</FreeNumber> |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Free Yes/No 1 - 5: <FreeYesNo number="1 - 5">...</FreeYesNo> |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Doc id: 00.001.068