Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/5821
Table Type: Company
Table Name: Item Statistics Buffer
Database Table Name: CRONUS UK Ltd_$Item Statistics Buffer$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 Item Statistics Buffer can be queried.
Show/hide an example select of all columns
SELECT
*
FROM
[CRONUS UK Ltd_$Item Statistics Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972]
Show/hide an example select of all columns by name
SELECT
[Code]
,[Sales (LCY)]
,[COGS (LCY)]
,[Profit (LCY)]
,[Profit _]
,[Inventoriable Costs]
,[Direct Cost (LCY)]
,[Revaluation (LCY)]
,[Rounding (LCY)]
,[Indirect Cost (LCY)]
,[Variance (LCY)]
,[Inventoriable Costs, Total]
,[Inventory (LCY)]
,[Non-Invtbl_ Costs (LCY)]
,[Line Option]
,[Column Option]
,[$systemId]
,[$systemCreatedAt]
,[$systemCreatedBy]
,[$systemModifiedAt]
,[$systemModifiedBy]
FROM
[CRONUS UK Ltd_$Item Statistics Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972]
Show/hide an example select of all columns including those from all joined tables
SELECT
*
FROM
[] AS [Item Statistics Buffer]
LEFT JOIN
[CRONUS UK Ltd_$Item$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] AS [Item]
ON
[Item Statistics Buffer].[Item Filter] = [Item].[No_]
LEFT JOIN
[CRONUS UK Ltd_$Item Variant$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Variant]
ON
[Item Statistics Buffer].[Variant Filter] = [Item Variant].[Code]
LEFT JOIN
[CRONUS UK Ltd_$Location$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Location]
ON
[Item Statistics Buffer].[Location Filter] = [Location].[Code]
LEFT JOIN
[CRONUS UK Ltd_$G_L Budget Name$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Budget Name]
ON
[Item Statistics Buffer].[Budget Filter] = [G/L Budget Name].[Name]
LEFT JOIN
[CRONUS UK Ltd_$Analysis View$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] AS [Analysis View]
ON
[Item Statistics Buffer].[Analysis View Filter] = [Analysis View].[Code]
LEFT JOIN
[User] AS [User]
ON
[Item Statistics Buffer].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
[User] AS [User]
ON
[Item Statistics Buffer].[SystemModifiedBy] = [User].[User Security ID]
Show/hide columns in Item Statistics Buffer table
Field No. | Field Name | Field Caption | Class | Type | Length | SQL Column Name | SQL DataType |
---|---|---|---|---|---|---|---|
1 | Code | Code | Normal | Code | 20 | Code | nvarchar(40) |
2 | Item Filter | Item Filter | FlowFilter | Code | 20 | ||
3 | Variant Filter | Variant Filter | FlowFilter | Code | 10 | ||
4 | Location Filter | Location Filter | FlowFilter | Code | 10 | ||
5 | Budget Filter | Budget Filter | FlowFilter | Code | 10 | ||
6 | Global Dimension 1 Filter | Global Dimension 1 Filter | FlowFilter | Code | 20 | ||
7 | Global Dimension 2 Filter | Global Dimension 2 Filter | FlowFilter | Code | 20 | ||
9 | Date Filter | Date Filter | FlowFilter | Date | 4 | ||
10 | Entry Type Filter | Entry Type Filter | FlowFilter | Option | 4 | ||
11 | Item Ledger Entry Type Filter | Item Ledger Entry Type Filter | FlowFilter | Option | 4 | ||
12 | Item Charge No. Filter | Item Charge No. Filter | FlowFilter | Code | 20 | ||
13 | Source Type Filter | Source Type Filter | FlowFilter | Option | 4 | ||
14 | Source No. Filter | Source No. Filter | FlowFilter | Code | 20 | ||
15 | Invoiced Quantity | Invoiced Quantity | FlowField | Decimal | 12 | ||
16 | Sales Amount (Actual) | Sales Amount (Actual) | FlowField | Decimal | 12 | ||
17 | Cost Amount (Actual) | Cost Amount (Actual) | FlowField | Decimal | 12 | ||
18 | Cost Amount (Non-Invtbl.) | Cost Amount (Non-Invtbl.) | FlowField | Decimal | 12 | ||
19 | Variance Type Filter | Variance Type Filter | FlowFilter | Option | 4 | ||
20 | Sales (LCY) | Sales (LCY) | Normal | Integer | 4 | Sales (LCY) | int |
21 | COGS (LCY) | COGS (LCY) | Normal | Integer | 4 | COGS (LCY) | int |
22 | Profit (LCY) | Profit (LCY) | Normal | Integer | 4 | Profit (LCY) | int |
23 | Profit % | Profit % | Normal | Integer | 4 | Profit _ | int |
24 | Inventoriable Costs | Inventoriable Costs | Normal | Integer | 4 | Inventoriable Costs | int |
25 | Direct Cost (LCY) | Direct Cost (LCY) | Normal | Integer | 4 | Direct Cost (LCY) | int |
26 | Revaluation (LCY) | Revaluation (LCY) | Normal | Integer | 4 | Revaluation (LCY) | int |
27 | Rounding (LCY) | Rounding (LCY) | Normal | Integer | 4 | Rounding (LCY) | int |
28 | Indirect Cost (LCY) | Indirect Cost (LCY) | Normal | Integer | 4 | Indirect Cost (LCY) | int |
29 | Variance (LCY) | Variance (LCY) | Normal | Integer | 4 | Variance (LCY) | int |
30 | Inventoriable Costs, Total | Inventoriable Costs, Total | Normal | Integer | 4 | Inventoriable Costs, Total | int |
31 | Inventory (LCY) | Inventory (LCY) | Normal | Integer | 4 | Inventory (LCY) | int |
34 | Non-Invtbl. Costs (LCY) | Non-Invtbl. Costs (LCY) | Normal | Integer | 4 | Non-Invtbl_ Costs (LCY) | int |
40 | Line Option | Line Option | Normal | Option | 4 | Line Option | int |
Available options are:
| |||||||
41 | Column Option | Column Option | Normal | Option | 4 | Column Option | int |
Available options are:
| |||||||
42 | Analysis Area Filter | Analysis Area Filter | FlowFilter | Option | 4 | ||
45 | Quantity | Quantity | FlowField | Decimal | 12 | ||
46 | Sales Amount (Expected) | Sales Amount (Expected) | FlowField | Decimal | 12 | ||
47 | Cost Amount (Expected) | Cost Amount (Expected) | FlowField | Decimal | 12 | ||
50 | Budgeted Quantity | Budgeted Quantity | FlowField | Decimal | 12 | ||
51 | Budgeted Sales Amount | Budgeted Sales Amount | FlowField | Decimal | 12 | ||
52 | Budgeted Cost Amount | Budgeted Cost Amount | FlowField | Decimal | 12 | ||
70 | Analysis View Filter | Analysis View Filter | FlowFilter | Code | 10 | ||
71 | Dimension 1 Filter | Dimension 1 Filter | FlowFilter | Code | 20 | ||
72 | Dimension 2 Filter | Dimension 2 Filter | FlowFilter | Code | 20 | ||
73 | Dimension 3 Filter | Dimension 3 Filter | FlowFilter | Code | 20 | ||
80 | Analysis – Quantity | Analysis – Quantity | FlowField | Decimal | 12 | ||
81 | Analysis – Invoiced Quantity | Analysis – Invoiced Quantity | FlowField | Decimal | 12 | ||
82 | Analysis – Sales Amt. (Actual) | Analysis – Sales Amt. (Actual) | FlowField | Decimal | 12 | ||
83 | Analysis – Sales Amt. (Exp) | Analysis – Sales Amt. (Exp) | FlowField | Decimal | 12 | ||
84 | Analysis – Cost Amt. (Actual) | Analysis – Cost Amt. (Actual) | FlowField | Decimal | 12 | ||
85 | Analysis – Cost Amt. (Exp) | Analysis – Cost Amt. (Exp) | FlowField | Decimal | 12 | ||
86 | Analysis CostAmt.(Non-Invtbl.) | Analysis CostAmt.(Non-Invtbl.) | FlowField | Decimal | 12 | ||
91 | Analysis – Budgeted Quantity | Analysis – Budgeted Quantity | FlowField | Decimal | 12 | ||
92 | Analysis – Budgeted Sales Amt. | Analysis – Budgeted Sales Amt. | FlowField | Decimal | 12 | ||
93 | Analysis – Budgeted Cost Amt. | Analysis – Budgeted Cost Amt. | FlowField | Decimal | 12 | ||
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
[Item Statistics Buffer].[$systemCreatedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Item Statistics Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Statistics Buffer]
LEFT JOIN
[User] AS [User]
ON
[Item Statistics Buffer].[$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
[Item Statistics Buffer].[$systemModifiedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Item Statistics Buffer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Item Statistics Buffer]
LEFT JOIN
[User] AS [User]
ON
[Item Statistics Buffer].[$systemModifiedBy] = [User].[User Security ID] |