Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/45
Table Type: Company
Table Name: G/L Register
Database Table Name: CRONUS UK Ltd_$G_L Register$437dbf0e-84ff-417a-965d-ed2bb9650972
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 G/L Register can be queried.
Show/hide an example select of all columns
SELECT
*
FROM
[CRONUS UK Ltd_$G_L Register$437dbf0e-84ff-417a-965d-ed2bb9650972]Show/hide an example select of all columns by name
SELECT
[No_]
,[From Entry No_]
,[To Entry No_]
,[Creation Date]
,[Source Code]
,[User ID]
,[Journal Batch Name]
,[From VAT Entry No_]
,[To VAT Entry No_]
,[Reversed]
,[Creation Time]
,[Journal Templ_ Name]
,[$systemId]
,[$systemCreatedAt]
,[$systemCreatedBy]
,[$systemModifiedAt]
,[$systemModifiedBy]
FROM
[CRONUS UK Ltd_$G_L Register$437dbf0e-84ff-417a-965d-ed2bb9650972]Show/hide an example select of all columns including those from all joined tables
SELECT
*
FROM
[CRONUS UK Ltd_$G_L Register$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Register]
LEFT JOIN
[CRONUS UK Ltd_$Source Code$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Source Code]
ON
[G/L Register].[Source Code] = [Source Code].[Code]
LEFT JOIN
[User] AS [User]
ON
[G/L Register].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
[User] AS [User]
ON
[G/L Register].[SystemModifiedBy] = [User].[User Security ID]Show/hide columns in G/L Register table
| Field No. | Field Name | Field Caption | Class | Type | Length | SQL Column Name | SQL DataType |
|---|---|---|---|---|---|---|---|
| 1 | No.   | No. | Normal | Integer | 4 | No_ | int |
| 2 | From Entry No. | From Entry No. | Normal | Integer | 4 | From Entry No_ | int |
| 3 | To Entry No. | To Entry No. | Normal | Integer | 4 | To Entry No_ | int |
| 4 | Creation Date | Creation Date | Normal | Date | 4 | Creation Date | datetime |
| 5 | Source Code | Source Code | Normal | Code | 10 | Source Code | nvarchar(20) |
Key to join to the Source Code table.Show/hide example querySELECT
[G/L Register].[Source Code]
,[Source Code].[Code]
FROM
[CRONUS UK Ltd_$G_L Register$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Register]
LEFT JOIN
[CRONUS UK Ltd_$Source Code$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Source Code]
ON
[G/L Register].[Source Code] = [Source Code].[Code] | |||||||
| 6 | User ID | User ID | Normal | Code | 50 | User ID | nvarchar(100) |
| 7 | Journal Batch Name | Journal Batch Name | Normal | Code | 10 | Journal Batch Name | nvarchar(20) |
| 8 | From VAT Entry No. | From Tax Entry No. | Normal | Integer | 4 | From VAT Entry No_ | int |
Key to join to the VAT Entry table.Show/hide example querySELECT
[G/L Register].[From VAT Entry No_]
,[VAT Entry].[Entry No_]
FROM
[CRONUS UK Ltd_$G_L Register$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Register]
LEFT JOIN
[CRONUS UK Ltd_$VAT Entry$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [VAT Entry]
ON
[G/L Register].[From VAT Entry No_] = [VAT Entry].[Entry No_] | |||||||
| 9 | To VAT Entry No. | To Tax Entry No. | Normal | Integer | 4 | To VAT Entry No_ | int |
Key to join to the VAT Entry table.Show/hide example querySELECT
[G/L Register].[To VAT Entry No_]
,[VAT Entry].[Entry No_]
FROM
[CRONUS UK Ltd_$G_L Register$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Register]
LEFT JOIN
[CRONUS UK Ltd_$VAT Entry$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [VAT Entry]
ON
[G/L Register].[To VAT Entry No_] = [VAT Entry].[Entry No_] | |||||||
| 10 | Reversed | Reversed | Normal | Boolean | 4 | Reversed | tinyint |
| 11 | Creation Time | Creation Time | Normal | Time | 4 | Creation Time | datetime |
| 12 | Journal Templ. Name | Journal Template Name | Normal | Code | 10 | Journal Templ_ Name | nvarchar(20) |
| 2000000000 | $systemId | System ID | Normal | GUID | 16 | $systemId | uniqueidentifier |
| 2000000001 | SystemCreatedAt | Created At | Normal | DateTime | 8 | $systemCreatedAt | datetime |
| 2000000002 | SystemCreatedBy | Created By | Normal | GUID | 16 | $systemCreatedBy | uniqueidentifier |
Key to join to the User table.Show/hide example querySELECT
[G/L Register].[$systemCreatedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$G_L Register$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Register]
LEFT JOIN
[User] AS [User]
ON
[G/L Register].[$systemCreatedBy] = [User].[User Security ID] | |||||||
| 2000000003 | SystemModifiedAt | Modified At | Normal | DateTime | 8 | $systemModifiedAt | datetime |
| 2000000004 | SystemModifiedBy | Modified By | Normal | GUID | 16 | $systemModifiedBy | uniqueidentifier |
Key to join to the User table.Show/hide example querySELECT
[G/L Register].[$systemModifiedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$G_L Register$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Register]
LEFT JOIN
[User] AS [User]
ON
[G/L Register].[$systemModifiedBy] = [User].[User Security ID] | |||||||