Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/926
Table Type: Company
Table Name: G/L Acc. Balance Buffer
Database Table Name:
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 Acc. Balance Buffer can be queried.
Show/hide an example select of all columns
SELECT
*
FROM
[]Show/hide an example select of all columns by name
SELECT
[Period Type]
,[Period Name]
,[Period Start]
,[Period End]
,[Debit Amount]
,[Credit Amount]
,[Net Change]
,[$systemId]
,[$systemCreatedAt]
,[$systemCreatedBy]
,[$systemModifiedAt]
,[$systemModifiedBy]
FROM
[]Show/hide an example select of all columns including those from all joined tables
SELECT
*
FROM
[CRONUS UK Ltd_$G_L Acc_ Balance Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Acc. Balance Buffer]
LEFT JOIN
[User] AS [User]
ON
[G/L Acc. Balance Buffer].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
[User] AS [User]
ON
[G/L Acc. Balance Buffer].[SystemModifiedBy] = [User].[User Security ID]Show/hide columns in G/L Acc. Balance Buffer table
| Field No. | Field Name | Field Caption | Class | Type | Length | SQL Column Name | SQL DataType |
|---|---|---|---|---|---|---|---|
| 5 | Period Type  | Period Type | Normal | Option | 4 | Period Type | int |
Available options are:
| |||||||
| 6 | Period Name  | Period Name | Normal | Text | 50 | Period Name | nvarchar(100) |
| 7 | Period Start | Period Start | Normal | Date | 4 | Period Start | datetime |
| 8 | Period End | Period End | Normal | Date | 4 | Period End | datetime |
| 10 | Debit Amount | Debit Amount | Normal | Decimal | 12 | Debit Amount | decimal(38,38) |
| 11 | Credit Amount | Credit Amount | Normal | Decimal | 12 | Credit Amount | decimal(38,38) |
| 12 | Net Change | Net Change | Normal | Decimal | 12 | Net Change | decimal(38,38) |
| 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 Acc. Balance Buffer].[$systemCreatedBy] ,[User].[User Security ID] FROM [] AS [G/L Acc. Balance Buffer] LEFT JOIN [User] AS [User] ON [G/L Acc. Balance Buffer].[$systemCreatedBy] = [User].[User Security ID] | |||||||
| 2000000003 | SystemModifiedAt | Modified At | Normal | DateTime | 8 | $systemModifiedAt | datetime |
| 2000000004 | SystemModifiedBy | Modified By | Normal | GUID | 16 | $systemModifiedBy | uniqueidentifier |