The Customer name and Vendor name fields exist in the Customer items and Vendor items tables in Business Central. As these do not exist in NAV, they must be derived from the respective master data records during the migration. This requires an adjustment to the configuration.
The following figure shows an example of the necessary configuration change:
Adjustment for the item table
- Joined Table: Specifies which additional table is included in the data transfer.
- Join Clause: Joins the table defined in the “Joined Table” column with the corresponding main table.
- The alias for the source table no. is set as T by default.
- All other added tables are given the prefix T, followed by the respective table number.
- Calculation: Defines the expression that is transferred to Business Central.
- Source Field No. -8 and Source Field Name — calculated field — do not exist in the source. These are therefore only used for documentation purposes. The actual value determination is determined by the 3 fields Joined Table, Join Clause and Calculation.
This adjustment ensures that the missing names are transferred correctly from the master data.
For those interested in the technology, here is the generated SQL command:
;WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY T.[Entry No_]) [RowNumber]
, T18.[Name][Customer Name]
, T.[Entry No_]
, T.[Customer No_]
, ...
FROM [Demo Database NAV (6-0)].dbo.[CRONUS AG$Cust_ Ledger Entry] T WITH (NOLOCK)
, [Demo Database NAV (6-0)].dbo.[CRONUS AG$Customer] T18 WITH (NOLOCK)
WHERE T.[Customer No_]=T18.[No_]
)
SELECT T.* FROM T WHERE T.[RowNumber] BETWEEN 1 AND 10000000