Item Statistics Buffer

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

Table Number: 5821

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 NameField CaptionClassTypeLengthSQL Column NameSQL DataType
1Code Primary Key SearchCodeNormalCode20Codenvarchar(40)
2Item Filter SearchItem FilterFlowFilterCode20
3Variant Filter SearchVariant FilterFlowFilterCode10
4Location Filter SearchLocation FilterFlowFilterCode10
5Budget Filter SearchBudget FilterFlowFilterCode10
6Global Dimension 1 Filter SearchGlobal Dimension 1 FilterFlowFilterCode20
7Global Dimension 2 Filter SearchGlobal Dimension 2 FilterFlowFilterCode20
9Date Filter SearchDate FilterFlowFilterDate4
10Entry Type Filter SearchEntry Type FilterFlowFilterOption4
11Item Ledger Entry Type Filter SearchItem Ledger Entry Type FilterFlowFilterOption4
12Item Charge No. Filter SearchItem Charge No. FilterFlowFilterCode20
13Source Type Filter SearchSource Type FilterFlowFilterOption4
14Source No. Filter SearchSource No. FilterFlowFilterCode20
15Invoiced Quantity SearchInvoiced QuantityFlowFieldDecimal12
16Sales Amount (Actual) SearchSales Amount (Actual)FlowFieldDecimal12
17Cost Amount (Actual) SearchCost Amount (Actual)FlowFieldDecimal12
18Cost Amount (Non-Invtbl.) SearchCost Amount (Non-Invtbl.)FlowFieldDecimal12
19Variance Type Filter SearchVariance Type FilterFlowFilterOption4
20Sales (LCY) SearchSales (LCY)NormalInteger4Sales (LCY)int
21COGS (LCY) SearchCOGS (LCY)NormalInteger4COGS (LCY)int
22Profit (LCY) SearchProfit (LCY)NormalInteger4Profit (LCY)int
23Profit % SearchProfit %NormalInteger4Profit _int
24Inventoriable Costs SearchInventoriable CostsNormalInteger4Inventoriable Costsint
25Direct Cost (LCY) SearchDirect Cost (LCY)NormalInteger4Direct Cost (LCY)int
26Revaluation (LCY) SearchRevaluation (LCY)NormalInteger4Revaluation (LCY)int
27Rounding (LCY) SearchRounding (LCY)NormalInteger4Rounding (LCY)int
28Indirect Cost (LCY) SearchIndirect Cost (LCY)NormalInteger4Indirect Cost (LCY)int
29Variance (LCY) SearchVariance (LCY)NormalInteger4Variance (LCY)int
30Inventoriable Costs, Total SearchInventoriable Costs, TotalNormalInteger4Inventoriable Costs, Totalint
31Inventory (LCY) SearchInventory (LCY)NormalInteger4Inventory (LCY)int
34Non-Invtbl. Costs (LCY) SearchNon-Invtbl. Costs (LCY)NormalInteger4Non-Invtbl_ Costs (LCY)int
40Line Option SearchLine OptionNormalOption4Line Optionint
Available options are:
  • “Profit Calculation”
  • “Cost Specification”
  • “Purch. Item Charge Spec.”
  • “Sales Item Charge Spec.”
  • “Period”
  • “Location”
41Column Option SearchColumn OptionNormalOption4Column Optionint
Available options are:
  • “Profit Calculation”
  • “Cost Specification”
  • “Purch. Item Charge Spec.”
  • “Sales Item Charge Spec.”
  • “Period”
  • “Location”
  • “Undefined”
42Analysis Area Filter SearchAnalysis Area FilterFlowFilterOption4
45Quantity SearchQuantityFlowFieldDecimal12
46Sales Amount (Expected) SearchSales Amount (Expected)FlowFieldDecimal12
47Cost Amount (Expected) SearchCost Amount (Expected)FlowFieldDecimal12
50Budgeted Quantity SearchBudgeted QuantityFlowFieldDecimal12
51Budgeted Sales Amount SearchBudgeted Sales AmountFlowFieldDecimal12
52Budgeted Cost Amount SearchBudgeted Cost AmountFlowFieldDecimal12
70Analysis View Filter SearchAnalysis View FilterFlowFilterCode10
71Dimension 1 Filter SearchDimension 1 FilterFlowFilterCode20
72Dimension 2 Filter SearchDimension 2 FilterFlowFilterCode20
73Dimension 3 Filter SearchDimension 3 FilterFlowFilterCode20
80Analysis – Quantity SearchAnalysis – QuantityFlowFieldDecimal12
81Analysis – Invoiced Quantity SearchAnalysis – Invoiced QuantityFlowFieldDecimal12
82Analysis – Sales Amt. (Actual) SearchAnalysis – Sales Amt. (Actual)FlowFieldDecimal12
83Analysis – Sales Amt. (Exp) SearchAnalysis – Sales Amt. (Exp)FlowFieldDecimal12
84Analysis – Cost Amt. (Actual) SearchAnalysis – Cost Amt. (Actual)FlowFieldDecimal12
85Analysis – Cost Amt. (Exp) SearchAnalysis – Cost Amt. (Exp)FlowFieldDecimal12
86Analysis CostAmt.(Non-Invtbl.) SearchAnalysis CostAmt.(Non-Invtbl.)FlowFieldDecimal12
91Analysis – Budgeted Quantity SearchAnalysis – Budgeted QuantityFlowFieldDecimal12
92Analysis – Budgeted Sales Amt. SearchAnalysis – Budgeted Sales Amt.FlowFieldDecimal12
93Analysis – Budgeted Cost Amt. SearchAnalysis – Budgeted Cost Amt.FlowFieldDecimal12
2000000000$systemId SearchSystem IDNormalGUID16$systemIduniqueidentifier
2000000001SystemCreatedAt SearchCreated AtNormalDateTime8$systemCreatedAtdatetime
2000000002SystemCreatedBy SearchCreated ByNormalGUID16$systemCreatedByuniqueidentifier
Key to join to the User table.

Show/hide example query

SELECT
       [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]
2000000003SystemModifiedAt SearchModified AtNormalDateTime8$systemModifiedAtdatetime
2000000004SystemModifiedBy SearchModified ByNormalGUID16$systemModifiedByuniqueidentifier
Key to join to the User table.

Show/hide example query

SELECT
       [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]

Leave a Reply

Your email address will not be published. Required fields are marked *