How to Use Field Value Mapping (Lookups)
This guide explains how to use field value mappings to translate source values into Business Central values using lookups.
What is Field Value Mapping?
Field value mapping (lookup) allows you to translate source data values into Business Central values by searching another table for a matching value.
Example:
- Source file has:
"SALES_REP_JOHN" - BC Employee table has: Employee "E001" with External Code "SALES_REP_JOHN"
- Lookup translates:
"SALES_REP_JOHN"→"E001"
When to Use Lookups
Use lookups when:
- Code Translation - External system uses different codes than BC
- Employee Matching - Match external employee IDs to BC employees
- Department Mapping - Map external department codes to BC departments
- Item Lookup - Match vendor item numbers to BC item numbers
- Customer Matching - Match external customer numbers to BC customers
How Lookups Work
Source Data → Lookup Value → Search BC Table → Find Match → Return Mapped Value
↓ ↓ ↓ ↓ ↓
"CUST_001" Customer Customer Table "10001" "10001"
"External ID" "External ID" matches
Configuring a Lookup
Step 1: Identify the Lookup Table
First, identify which BC table contains the matching values:
| Scenario | Table | Lookup Field | Return Field |
|---|---|---|---|
| Employee Code | Employee | "External Employee Code" | "No." |
| Customer Number | Customer | "Your Reference" | "No." |
| Item Number | Item | "Vendor Item Code" | "No." |
| Department Code | Department | "Code" | "Name" |
| Salesperson | Salesperson/Purchaser | "External Code" | "Code" |
Step 2: Add Lookup to Mapping
On the Integration Mapping record:
- Enable: Get Value From Lookup - Yes
- Set: Lookup Table - Select the BC table (e.g., "Customer")
- Set: Lookup Field - Field to search in (e.g., "Your Reference")
- Set: Return Field - Field to extract result (e.g., "No.")
Step 3: How It Processes
When processing:
- Takes source value (e.g., "CUST_001")
- Searches Lookup Table (Customer)
- Finds row where Lookup Field ("Your Reference") = "CUST_001"
- Returns value from Return Field ("No.") - e.g., "10001"
- Stores returned value in destination field
Common Lookup Scenarios
Scenario 1: Customer Code Translation
Problem: Source file uses customer codes like "EXT_CUSTOMER_123" but BC expects internal customer numbers "10001".
Setup:
First, in BC, add external code to customer:
- Customer "10001" got "Your Reference" = "EXT_CUSTOMER_123"
Mapping:
Source Field: "Customer Code"
Destination Table: Sales Header
Destination Field: "Bill-to Customer No."
Lookup Configuration:
Get Value From Lookup: Yes
Lookup Table: Customer
Lookup Field: "Your Reference"
Return Field: "No."
Processing:
Source: "EXT_CUSTOMER_123"
→ Search Customer."Your Reference" for "EXT_CUSTOMER_123"
→ Found in Customer "10001"
→ Write "10001" to Sales Header."Bill-to Customer No."
Scenario 2: Employee/Salesperson Matching
Problem: Source has salesperson external IDs; BC has employee numbers.
Setup:
- Employee "E001" has "Search Name" = "SALES_JOHN"
- Employee "E002" has "Search Name" = "SALES_JANE"
Mapping:
Source Field: "Salesperson Code"
Destination Table: Sales Line
Destination Field: "Salesperson Code"
Lookup Configuration:
Get Value From Lookup: Yes
Lookup Table: Salesperson/Purchaser
Lookup Field: "Search Name"
Return Field: "Code"
Processing:
Source: "SALES_JOHN"
→ Search Salesperson."Search Name" = "SALES_JOHN"
→ Found: "E001"
→ Write "E001" to Sales Line."Salesperson Code"
Scenario 3: Item Lookup by Vendor Code
Problem: Supplier sends item numbers (vendor SKUs) but BC uses internal item numbers.
Setup:
- Item "IT001" has "Vendor Item Code" = "VEN_SKU_4521"
- Item "IT002" has "Vendor Item Code" = "VEN_SKU_4522"
Mapping:
Source Field: "Item Code"
Destination Table: Sales Line
Destination Field: "No."
Lookup Configuration:
Get Value From Lookup: Yes
Lookup Table: Item
Lookup Field: "Vendor Item Code"
Return Field: "No."
Processing:
Source: "VEN_SKU_4521"
→ Search Item."Vendor Item Code" = "VEN_SKU_4521"
→ Found: "IT001"
→ Write "IT001" to Sales Line."No."
Scenario 4: Lookup with Custom Field
Problem: You have a custom lookup table for status code mapping.
Custom Table Setup:
- Create table "Integration Lookup" with fields:
- "Source Code" = "A"
- "BC Code" = "10"
- "Source Code" = "B"
- "BC Code" = "20"
Mapping:
Source Field: "Status Code"
Destination Table: Customer
Destination Field: "Customer Posting Group"
Lookup Configuration:
Get Value From Lookup: Yes
Lookup Table: Integration Lookup
Lookup Field: "Source Code"
Return Field: "BC Code"
Processing:
Source: "A"
→ Search Integration Lookup."Source Code" = "A"
→ Return Integration Lookup."BC Code" = "10"
Multiple Lookups in One Import
You may use multiple lookups in a single integration:
Example:
Mapping 1: Customer Code → (lookup) → Customer.No.
Mapping 2: Item Code → (lookup) → Item.No.
Mapping 3: Salesperson → (lookup) → Salesperson.Code
Mapping 4: Status → (lookup) → Status Code
Each mapping can have its own lookup configuration.
Handling Lookup Failures
Lookup Fail Action
When the lookup engine cannot find a matching record, the Lookup Fail Action field on the field mapping controls what happens next:
| Lookup Fail Action | Behavior |
|---|---|
| Error (default) | The row is rejected with an error. The import log records "Value not found in lookup: [value]". |
| Use Default | The value specified in Lookup Default Value is written to the destination field instead. The row continues processing. |
| Skip Row | The entire row is silently skipped — no record is inserted or modified for this row. Useful when missing lookup values are expected and unmatched rows should be discarded. |
| Keep Original | The source value is written to the destination field as-is, without lookup translation. Useful when you want lookup as a best-effort enrichment rather than a hard requirement. |
To configure:
- On the Integration Mapping record, set Lookup Fail Action to the desired behavior
- If Use Default is selected, set Lookup Default Value to the fallback value
Lookup Default Value
When Lookup Fail Action = Use Default, the value in Lookup Default Value is used in place of the lookup result whenever the lookup finds no match. This is useful when:
- A large percentage of source rows map to the same fallback value
- You want to preserve rows without requiring a perfect lookup table
Example: if "CUST_UNKNOWN" should map to customer "MISC" when no specific customer is found:
Lookup Fail Action: Use Default
Lookup Default Value: MISC
Lookup Filter Field
Use the Lookup Filter Field to narrow the lookup search to a subset of rows in the lookup table. This is useful when the same lookup field value appears in multiple rows with different contexts.
The filter value can reference other source fields using the [FieldName] syntax — the value is substituted at runtime from the current source row:
| Field | Description |
|---|---|
| Lookup Filter Field | The field in the lookup table to apply the filter on |
| Lookup Filter Value | The value to filter by. Use [SourceFieldName] to substitute a value from the current source row at runtime |
Example: Look up an item by Vendor Item Code, but only within items belonging to the current row's vendor:
Lookup Table: Item
Lookup Field: Vendor Item Code
Return Field: No.
Lookup Filter Field: Vendor No.
Lookup Filter Value: [VendorCode]
At runtime, [VendorCode] is replaced with the value of the VendorCode column from the current source row before the lookup is executed.
Custom Lookup Logic
For lookup scenarios that cannot be expressed through standard table/field configuration, subscribe to the OnLookupValue integration event on the Integration Framework lookup codeunit. Your event subscriber receives the source value and can return a custom result, bypassing the standard table search.
Debugging Lookup Issues
Check 1: Does lookup table have the value?
Open the lookup table list in BC
Search for the Lookup Field containing the source value
If not found → Add the record or fix the source value
Check 2: Is lookup field correct?
Verify the field exists in the lookup table
Verify the field contains expected values
Try an alternative field if available
Check 3: Is value exact match?
Lookups are case-sensitive by default
Check for leading/trailing spaces
"CUST001" ≠ "cust001" (if case-sensitive)
Check 4: Is return field correct?
Verify the return field exists
Check what values are in the return field
Ensure values are compatible with the destination field type
Check 5: Does a Lookup Filter Field restrict matches?
If Lookup Filter Field is set, verify the filter value resolves correctly
Use [FieldName] syntax only for fields that exist in the source data
Check whether the filter is excluding the intended rows
Best Practices for Lookups
- Prepare Lookup Data First - Ensure BC table has all source values before import
- Use Unique Lookup Fields - Use fields that uniquely identify records
- Document Mappings - Document which field is lookup source
- Test with Samples - Test lookups with sample data first
- Validate Lookups - Manually check few source-to-destination mappings before full import
- Error Review - Review "not found" errors before reprocessing
- Maintain Lookup Tables - Keep lookup tables up-to-date
- Alternative Fields - Know backup fields if primary lookup field missing
- Avoid Null Values - Don't use empty/null values in lookup fields
- Case Sensitivity - Test whether lookups are case-sensitive for your system
Creating Lookup Tables for Complex Mappings
For scenarios not covered by standard BC tables, create a custom Integration Lookup table:
Table Fields:
Field 1: "Source Code" (Text[20])
Field 2: "BC Code" (Text[20])
Field 3: "Description" (Text[100])
Example Data:
Source Code: "STATUS_A" → BC Code: "Active"
Source Code: "STATUS_B" → BC Code: "Blocked"
Source Code: "STATUS_C" → BC Code: "Closed"
Then use in mapping:
- Lookup Table: "Integration Lookup"
- Lookup Field: "Source Code"
- Return Field: "BC Code"
Performance Considerations
- Lookups perform exact match searches in BC table
- Performance is fast for tables with <100K records
- For very large lookup tables, ensure BC table has proper indexes
- Multiple lookups in single import processed sequentially (not parallel)