Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/1014
Table Type: Company
Table Name: Job G/L Account Price
Database Table Name: CRONUS UK Ltd_$Job G_L Account Price$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 Job G/L Account Price can be queried.
Show/hide an example select of all columns
SELECT
*
FROM
[CRONUS UK Ltd_$Job G_L Account Price$437dbf0e-84ff-417a-965d-ed2bb9650972]Show/hide an example select of all columns by name
SELECT
[Job No_]
,[Job Task No_]
,[G_L Account No_]
,[Unit Price]
,[Currency Code]
,[Unit Cost Factor]
,[Line Discount _]
,[Unit Cost]
,[$systemId]
,[$systemCreatedAt]
,[$systemCreatedBy]
,[$systemModifiedAt]
,[$systemModifiedBy]
FROM
[CRONUS UK Ltd_$Job G_L Account Price$437dbf0e-84ff-417a-965d-ed2bb9650972]Show/hide an example select of all columns including those from all joined tables
SELECT
*
FROM
[CRONUS UK Ltd_$Job G_L Account Price$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job G/L Account Price]
LEFT JOIN
[CRONUS UK Ltd_$Currency$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Currency]
ON
[Job G/L Account Price].[Currency Code] = [Currency].[Code]
LEFT JOIN
[User] AS [User]
ON
[Job G/L Account Price].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
[User] AS [User]
ON
[Job G/L Account Price].[SystemModifiedBy] = [User].[User Security ID]Show/hide columns in Job G/L Account Price table
| Field No. | Field Name | Field Caption | Class | Type | Length | SQL Column Name | SQL DataType |
|---|---|---|---|---|---|---|---|
| 1 | Job No.   | Job No. | Normal | Code | 20 | Job No_ | nvarchar(40) |
Key to join to the Job table.Show/hide example querySELECT
[Job G/L Account Price].[Job No_]
,[Job].[No_]
FROM
[CRONUS UK Ltd_$Job G_L Account Price$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job G/L Account Price]
LEFT JOIN
[CRONUS UK Ltd_$Job$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job]
ON
[Job G/L Account Price].[Job No_] = [Job].[No_] | |||||||
| 2 | Job Task No. | Job Task No. | Normal | Code | 20 | Job Task No_ | nvarchar(40) |
| 3 | G/L Account No. | G/L Account No. | Normal | Code | 20 | G_L Account No_ | nvarchar(40) |
Key to join to the G/L Account table.Show/hide example querySELECT
[Job G/L Account Price].[G_L Account No_]
,[G/L Account].[No_]
FROM
[CRONUS UK Ltd_$Job G_L Account Price$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job G/L Account Price]
LEFT JOIN
[CRONUS UK Ltd_$G_L Account$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Account]
ON
[Job G/L Account Price].[G_L Account No_] = [G/L Account].[No_] | |||||||
| 5 | Unit Price | Unit Price | Normal | Decimal | 12 | Unit Price | decimal(38,38) |
| 6 | Currency Code | Currency Code | Normal | Code | 10 | Currency Code | nvarchar(20) |
Key to join to the Currency table.Show/hide example querySELECT
[Job G/L Account Price].[Currency Code]
,[Currency].[Code]
FROM
[CRONUS UK Ltd_$Job G_L Account Price$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job G/L Account Price]
LEFT JOIN
[CRONUS UK Ltd_$Currency$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Currency]
ON
[Job G/L Account Price].[Currency Code] = [Currency].[Code] | |||||||
| 7 | Unit Cost Factor | Unit Cost Factor | Normal | Decimal | 12 | Unit Cost Factor | decimal(38,38) |
| 8 | Line Discount % | Line Discount % | Normal | Decimal | 12 | Line Discount _ | decimal(38,38) |
| 9 | Unit Cost | Unit Cost | Normal | Decimal | 12 | Unit Cost | decimal(38,38) |
| 10 | Description | Description | FlowField | Text | 100 | ||
| 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
[Job G/L Account Price].[$systemCreatedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Job G_L Account Price$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job G/L Account Price]
LEFT JOIN
[User] AS [User]
ON
[Job G/L Account Price].[$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
[Job G/L Account Price].[$systemModifiedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Job G_L Account Price$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job G/L Account Price]
LEFT JOIN
[User] AS [User]
ON
[Job G/L Account Price].[$systemModifiedBy] = [User].[User Security ID] | |||||||