Integration Field Mapping

Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/5336

Table Type: Company

Table Name: Integration Field Mapping
Database Table Name: CRONUS UK Ltd_$Integration Field Mapping$437dbf0e-84ff-417a-965d-ed2bb9650972$ext

Table Number: 5336

Due to how Dynamics BC tables and columns are named, square brackets need to be wrapped around the table name and column names. Below are example queries showing how Integration Field Mapping can be queried.

Show/hide an example select of all columns

SELECT
   *
FROM
   [CRONUS UK Ltd_$Integration Field Mapping$437dbf0e-84ff-417a-965d-ed2bb9650972$ext]

Show/hide an example select of all columns by name

SELECT
   [No_]
   ,[Integration Table Mapping Name]
   ,[Field No_]
   ,[Integration Table Field No_]
   ,[Direction]
   ,[Constant Value]
   ,[Validate Field]
   ,[Validate Integration Table Fld]
   ,[Clear Value on Failed Sync]
   ,[Status]
   ,[Not Null]
   ,[Transformation Rule]
   ,[Transformation Direction]
   ,[Use For Match-Based Coupling]
   ,[Case-Sensitive Matching]
   ,[Match Priority]
   ,[Field Caption]
   ,[Integration Field Caption]
   ,[$systemId]
   ,[$systemCreatedAt]
   ,[$systemCreatedBy]
   ,[$systemModifiedAt]
   ,[$systemModifiedBy]
FROM
   [CRONUS UK Ltd_$Integration Field Mapping$437dbf0e-84ff-417a-965d-ed2bb9650972$ext]

Show/hide an example select of all columns including those from all joined tables

SELECT
   *
FROM
   [CRONUS UK Ltd_$Integration Field Mapping$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Integration Field Mapping]
LEFT JOIN
   [CRONUS UK Ltd_$Integration Table Mapping$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] AS [Integration Table Mapping]
      ON
         [Integration Field Mapping].[Integration Table Mapping Name] = [Integration Table Mapping].[Name]
LEFT JOIN
   [CRONUS UK Ltd_$Transformation Rule$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Transformation Rule]
      ON
         [Integration Field Mapping].[Transformation Rule] = [Transformation Rule].[Code]
LEFT JOIN
   [User] AS [User]
      ON
         [Integration Field Mapping].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
   [User] AS [User]
      ON
         [Integration Field Mapping].[SystemModifiedBy] = [User].[User Security ID]

Show/hide columns in Integration Field Mapping table

Field No.Field NameField CaptionClassTypeLengthSQL Column NameSQL DataType
1No. Primary Key SearchNo.NormalInteger4No_int
2Integration Table Mapping Name SearchIntegration Table Mapping NameNormalCode20Integration Table Mapping Namenvarchar(40)
Key to join to the Integration Table Mapping table.

Show/hide example query

SELECT
       [Integration Field Mapping].[Integration Table Mapping Name]
       ,[Integration Table Mapping].[Name]
FROM
       [CRONUS UK Ltd_$Integration Field Mapping$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] AS [Integration Field Mapping]
LEFT JOIN
       [CRONUS UK Ltd_$Integration Table Mapping$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] AS [Integration Table Mapping]
              ON
                     [Integration Field Mapping].[Integration Table Mapping Name] = [Integration Table Mapping].[Name]
3Field No. SearchField No.NormalInteger4Field No_int
4Integration Table Field No. SearchIntegration Table Field No.NormalInteger4Integration Table Field No_int
6Direction SearchDirectionNormalOption4Directionint
Available options are:
  • “Bidirectional”
  • “ToIntegrationTable”
  • “FromIntegrationTable”
7Constant Value SearchConstant ValueNormalText100Constant Valuenvarchar(200)
8Validate Field SearchValidate FieldNormalBoolean4Validate Fieldtinyint
9Validate Integration Table Fld SearchValidate Integration Table FldNormalBoolean4Validate Integration Table Fldtinyint
10Clear Value on Failed Sync SearchClear Value on Failed SyncNormalBoolean4Clear Value on Failed Synctinyint
11Status SearchStatusNormalOption4Statusint
Available options are:
  • “Enabled”
  • “Disabled”
12Not Null SearchNot NullNormalBoolean4Not Nulltinyint
13Transformation Rule SearchTransformation RuleNormalCode20Transformation Rulenvarchar(40)
Key to join to the Transformation Rule table.

Show/hide example query

SELECT
       [Integration Field Mapping].[Transformation Rule]
       ,[Transformation Rule].[Code]
FROM
       [CRONUS UK Ltd_$Integration Field Mapping$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] AS [Integration Field Mapping]
LEFT JOIN
       [CRONUS UK Ltd_$Transformation Rule$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Transformation Rule]
              ON
                     [Integration Field Mapping].[Transformation Rule] = [Transformation Rule].[Code]
14Transformation Direction SearchTransformation DirectionNormalOption4Transformation Directionint
Available options are:
  • “ToIntegrationTable”
  • “FromIntegrationTable”
15Use For Match-Based Coupling SearchUse For Match-Based CouplingNormalBoolean4Use For Match-Based Couplingtinyint
16Case-Sensitive Matching SearchCase-Sensitive MatchingNormalBoolean4Case-Sensitive Matchingtinyint
17Match Priority SearchMatch PriorityNormalInteger4Match Priorityint
18Field Caption SearchField CaptionNormalText250Field Captionnvarchar(500)
19Integration Field Caption SearchIntegration Field CaptionNormalText250Integration Field Captionnvarchar(500)
7234Overwrite Local Change SearchOverwrite Local ChangeNormalBoolean4
2000000000$systemId SearchSystem IDNormalGUID16$systemIduniqueidentifier
2000000001SystemCreatedAt SearchCreated AtNormalDateTime8$systemCreatedAtdatetime
2000000002SystemCreatedBy SearchCreated ByNormalGUID16$systemCreatedByuniqueidentifier
Key to join to the User table.

Show/hide example query

SELECT
       [Integration Field Mapping].[$systemCreatedBy]
       ,[User].[User Security ID]
FROM
       [CRONUS UK Ltd_$Integration Field Mapping$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] AS [Integration Field Mapping]
LEFT JOIN
       [User] AS [User]
              ON
                     [Integration Field Mapping].[$systemCreatedBy] = [User].[User Security ID]
2000000003SystemModifiedAt SearchModified AtNormalDateTime8$systemModifiedAtdatetime
2000000004SystemModifiedBy SearchModified ByNormalGUID16$systemModifiedByuniqueidentifier
Key to join to the User table.

Show/hide example query

SELECT
       [Integration Field Mapping].[$systemModifiedBy]
       ,[User].[User Security ID]
FROM
       [CRONUS UK Ltd_$Integration Field Mapping$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] AS [Integration Field Mapping]
LEFT JOIN
       [User] AS [User]
              ON
                     [Integration Field Mapping].[$systemModifiedBy] = [User].[User Security ID]

Leave a Reply

Your email address will not be published. Required fields are marked *