Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/1021
Table Type: Company
Table Name: Job WIP Total
Database Table Name: CRONUS UK Ltd_$Job WIP Total$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 WIP Total can be queried.
Show/hide an example select of all columns
SELECT
*
FROM
[CRONUS UK Ltd_$Job WIP Total$437dbf0e-84ff-417a-965d-ed2bb9650972]Show/hide an example select of all columns by name
SELECT
[Entry No_]
,[Job No_]
,[Job Task No_]
,[WIP Method]
,[WIP Posting Date]
,[WIP Posting Date Filter]
,[WIP Planning Date Filter]
,[Posted to G_L]
,[Schedule (Total Cost)]
,[Schedule (Total Price)]
,[Usage (Total Cost)]
,[Usage (Total Price)]
,[Contract (Total Cost)]
,[Contract (Total Price)]
,[Contract (Invoiced Price)]
,[Contract (Invoiced Cost)]
,[Calc_ Recog_ Sales Amount]
,[Calc_ Recog_ Costs Amount]
,[Cost Completion _]
,[Invoiced _]
,[$systemId]
,[$systemCreatedAt]
,[$systemCreatedBy]
,[$systemModifiedAt]
,[$systemModifiedBy]
FROM
[CRONUS UK Ltd_$Job WIP Total$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 WIP Total$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job WIP Total]
LEFT JOIN
[CRONUS UK Ltd_$Job WIP Method$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job WIP Method]
ON
[Job WIP Total].[WIP Method] = [Job WIP Method].[Code]
LEFT JOIN
[User] AS [User]
ON
[Job WIP Total].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
[User] AS [User]
ON
[Job WIP Total].[SystemModifiedBy] = [User].[User Security ID]Show/hide columns in Job WIP Total table
| Field No. | Field Name | Field Caption | Class | Type | Length | SQL Column Name | SQL DataType |
|---|---|---|---|---|---|---|---|
| 1 | Entry No.   | Entry No. | Normal | Integer | 4 | Entry No_ | int |
| 2 | Job No. | Job No. | Normal | Code | 20 | Job No_ | nvarchar(40) |
Key to join to the Job table.Show/hide example querySELECT
[Job WIP Total].[Job No_]
,[Job].[No_]
FROM
[CRONUS UK Ltd_$Job WIP Total$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job WIP Total]
LEFT JOIN
[CRONUS UK Ltd_$Job$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job]
ON
[Job WIP Total].[Job No_] = [Job].[No_] | |||||||
| 3 | Job Task No. | Job Task No. | Normal | Code | 20 | Job Task No_ | nvarchar(40) |
| 4 | WIP Method | WIP Method | Normal | Code | 20 | WIP Method | nvarchar(40) |
Key to join to the Job WIP Method table.Show/hide example querySELECT
[Job WIP Total].[WIP Method]
,[Job WIP Method].[Code]
FROM
[CRONUS UK Ltd_$Job WIP Total$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job WIP Total]
LEFT JOIN
[CRONUS UK Ltd_$Job WIP Method$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job WIP Method]
ON
[Job WIP Total].[WIP Method] = [Job WIP Method].[Code] | |||||||
| 5 | WIP Posting Date | WIP Posting Date | Normal | Date | 4 | WIP Posting Date | datetime |
| 6 | WIP Posting Date Filter | WIP Posting Date Filter | Normal | Text | 250 | WIP Posting Date Filter | nvarchar(500) |
| 7 | WIP Planning Date Filter | WIP Planning Date Filter | Normal | Text | 250 | WIP Planning Date Filter | nvarchar(500) |
| 8 | WIP Warnings | WIP Warnings | FlowField | Boolean | 4 | ||
| 9 | Posted to G/L | Posted to G/L | Normal | Boolean | 4 | Posted to G_L | tinyint |
| 10 | Schedule (Total Cost) | Budget (Total Cost) | Normal | Decimal | 12 | Schedule (Total Cost) | decimal(38,38) |
| 11 | Schedule (Total Price) | Budget (Total Price) | Normal | Decimal | 12 | Schedule (Total Price) | decimal(38,38) |
| 12 | Usage (Total Cost) | Usage (Total Cost) | Normal | Decimal | 12 | Usage (Total Cost) | decimal(38,38) |
| 13 | Usage (Total Price) | Usage (Total Price) | Normal | Decimal | 12 | Usage (Total Price) | decimal(38,38) |
| 14 | Contract (Total Cost) | Billable (Total Cost) | Normal | Decimal | 12 | Contract (Total Cost) | decimal(38,38) |
| 15 | Contract (Total Price) | Billable (Total Price) | Normal | Decimal | 12 | Contract (Total Price) | decimal(38,38) |
| 16 | Contract (Invoiced Price) | Billable (Invoiced Price) | Normal | Decimal | 12 | Contract (Invoiced Price) | decimal(38,38) |
| 17 | Contract (Invoiced Cost) | Billable (Invoiced Cost) | Normal | Decimal | 12 | Contract (Invoiced Cost) | decimal(38,38) |
| 20 | Calc. Recog. Sales Amount | Calc. Recog. Sales Amount | Normal | Decimal | 12 | Calc_ Recog_ Sales Amount | decimal(38,38) |
| 21 | Calc. Recog. Costs Amount | Calc. Recog. Costs Amount | Normal | Decimal | 12 | Calc_ Recog_ Costs Amount | decimal(38,38) |
| 30 | Cost Completion % | Cost Completion % | Normal | Decimal | 12 | Cost Completion _ | decimal(38,38) |
| 31 | Invoiced % | Invoiced % | Normal | Decimal | 12 | Invoiced _ | 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
[Job WIP Total].[$systemCreatedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Job WIP Total$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job WIP Total]
LEFT JOIN
[User] AS [User]
ON
[Job WIP Total].[$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 WIP Total].[$systemModifiedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Job WIP Total$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Job WIP Total]
LEFT JOIN
[User] AS [User]
ON
[Job WIP Total].[$systemModifiedBy] = [User].[User Security ID] | |||||||