Joining Fields from Other Tables Dynamically (Derived Columns)

The Customer Ledger Entries and Vendor Ledger Entries tables in Business Central contain the fields Customer Name and Vendor Name. Since these do not exist in NAV, they have to be derived from the respective master records during the migration. This requires an adjustment to the configuration.

The following figure shows an example of the required configuration change:

Customization for the ledger entry table

• Joined Table: indicates which additional table is included in the data transfer.
• Join Clause: links the table defined in the "Joined Table" column with the corresponding main table.
• Calculation: defines the expression that is passed to Business Central.
• Source Field No. -8 and Source Field Name --- calculated field --- do not exist in the source. They therefore serve documentation purposes only. The actual value determination is defined by the 3 fields Joined Table, Join Clause and Calculation.

This adjustment ensures that the missing names are taken correctly from the master data.

For those interested in the technical side, 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