G/L Account (Analysis View)

Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/376

Table Type: Company

Table Name: G/L Account (Analysis View)
Database Table Name: CRONUS UK Ltd_$G_L Account (Analysis View)$437dbf0e-84ff-417a-965d-ed2bb9650972

Table Number: 376

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 Account (Analysis View) can be queried.

Show/hide an example select of all columns

SELECT
   *
FROM
   [CRONUS UK Ltd_$G_L Account (Analysis View)$437dbf0e-84ff-417a-965d-ed2bb9650972]

Show/hide an example select of all columns by name

SELECT
   [No_]
   ,[Name]
   ,[Search Name]
   ,[Account Type]
   ,[Account Source]
   ,[Global Dimension 1 Code]
   ,[Global Dimension 2 Code]
   ,[Income_Balance]
   ,[Debit_Credit]
   ,[No_ 2]
   ,[Blocked]
   ,[Direct Posting]
   ,[Reconciliation Account]
   ,[New Page]
   ,[No_ of Blank Lines]
   ,[Indentation]
   ,[Last Date Modified]
   ,[Totaling]
   ,[Consol_ Debit Acc_]
   ,[Consol_ Credit Acc_]
   ,[Gen_ Posting Type]
   ,[Gen_ Bus_ Posting Group]
   ,[Gen_ Prod_ Posting Group]
   ,[Automatic Ext_ Texts]
   ,[Tax Area Code]
   ,[Tax Liable]
   ,[Tax Group Code]
   ,[VAT Bus_ Posting Group]
   ,[VAT Prod_ Posting Group]
   ,[Exchange Rate Adjustment]
   ,[$systemId]
   ,[$systemCreatedAt]
   ,[$systemCreatedBy]
   ,[$systemModifiedAt]
   ,[$systemModifiedBy]
FROM
   [CRONUS UK Ltd_$G_L Account (Analysis View)$437dbf0e-84ff-417a-965d-ed2bb9650972]

Show/hide an example select of all columns including those from all joined tables

SELECT
   *
FROM
   [CRONUS UK Ltd_$G_L Account (Analysis View)$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Account (Analysis View)]
LEFT JOIN
   [CRONUS UK Ltd_$Dimension$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension]
      ON
         [G/L Account (Analysis View)].[Global Dimension 1 Code] = [Dimension].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Dimension$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension]
      ON
         [G/L Account (Analysis View)].[Global Dimension 2 Code] = [Dimension].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Cash Flow Forecast$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Cash Flow Forecast]
      ON
         [G/L Account (Analysis View)].[Cash Flow Forecast Filter] = [Cash Flow Forecast].[No_]
LEFT JOIN
   [CRONUS UK Ltd_$G_L Budget Name$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Budget Name]
      ON
         [G/L Account (Analysis View)].[Budget Filter] = [G/L Budget Name].[Name]
LEFT JOIN
   [CRONUS UK Ltd_$Business Unit$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Business Unit]
      ON
         [G/L Account (Analysis View)].[Business Unit Filter] = [Business Unit].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Tax Area$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Tax Area]
      ON
         [G/L Account (Analysis View)].[Tax Area Code] = [Tax Area].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Tax Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Tax Group]
      ON
         [G/L Account (Analysis View)].[Tax Group Code] = [Tax Group].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Analysis View$437dbf0e-84ff-417a-965d-ed2bb9650972$ext] AS [Analysis View]
      ON
         [G/L Account (Analysis View)].[Analysis View Filter] = [Analysis View].[Code]
LEFT JOIN
   [User] AS [User]
      ON
         [G/L Account (Analysis View)].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
   [User] AS [User]
      ON
         [G/L Account (Analysis View)].[SystemModifiedBy] = [User].[User Security ID]

Show/hide columns in G/L Account (Analysis View) table

Field No.Field NameField CaptionClassTypeLengthSQL Column NameSQL DataType
1No. Primary Key SearchNo.NormalCode20No_nvarchar(40)
2Name SearchNameNormalText100Namenvarchar(200)
3Search Name SearchSearch NameNormalCode100Search Namenvarchar(200)
4Account Type SearchAccount TypeNormalOption4Account Typeint
Available options are:
  • “Posting”
  • “Heading”
  • “Total”
  • “Begin-Total”
  • “End-Total”
5Account Source Primary Key SearchAccount SourceNormalOption4Account Sourceint
Available options are:
  • “G/L Account”
  • “Cash Flow Account”
  • “Statistical Account”
6Global Dimension 1 Code SearchGlobal Dimension 1 CodeNormalCode20Global Dimension 1 Codenvarchar(40)
Key to join to the Dimension table.

Show/hide example query

SELECT
       [G/L Account (Analysis View)].[Global Dimension 1 Code]
       ,[Dimension].[Code]
FROM
       [CRONUS UK Ltd_$G_L Account (Analysis View)$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Account (Analysis View)]
LEFT JOIN
       [CRONUS UK Ltd_$Dimension$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension]
              ON
                     [G/L Account (Analysis View)].[Global Dimension 1 Code] = [Dimension].[Code]
7Global Dimension 2 Code SearchGlobal Dimension 2 CodeNormalCode20Global Dimension 2 Codenvarchar(40)
Key to join to the Dimension table.

Show/hide example query

SELECT
       [G/L Account (Analysis View)].[Global Dimension 2 Code]
       ,[Dimension].[Code]
FROM
       [CRONUS UK Ltd_$G_L Account (Analysis View)$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Account (Analysis View)]
LEFT JOIN
       [CRONUS UK Ltd_$Dimension$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension]
              ON
                     [G/L Account (Analysis View)].[Global Dimension 2 Code] = [Dimension].[Code]
9Income/Balance SearchIncome/BalanceNormalOption4Income_Balanceint
Available options are:
  • “Income Statement”
  • “Balance Sheet”
10Debit/Credit SearchDebit/CreditNormalOption4Debit_Creditint
Available options are:
  • “Both”
  • “Debit”
  • “Credit”
11No. 2 SearchNo. 2NormalCode20No_ 2nvarchar(40)
12Comment SearchCommentFlowFieldBoolean4
13Blocked SearchBlockedNormalBoolean4Blockedtinyint
14Direct Posting SearchDirect PostingNormalBoolean4Direct Postingtinyint
16Reconciliation Account SearchReconciliation AccountNormalBoolean4Reconciliation Accounttinyint
17New Page SearchNew PageNormalBoolean4New Pagetinyint
18No. of Blank Lines SearchNo. of Blank LinesNormalInteger4No_ of Blank Linesint
19Indentation SearchIndentationNormalInteger4Indentationint
26Last Date Modified SearchLast Date ModifiedNormalDate4Last Date Modifieddatetime
27Cash Flow Forecast Filter SearchCash Flow Forecast FilterFlowFilterCode20
28Date Filter SearchDate FilterFlowFilterDate4
29Global Dimension 1 Filter SearchGlobal Dimension 1 FilterFlowFilterCode20
30Global Dimension 2 Filter SearchGlobal Dimension 2 FilterFlowFilterCode20
31Balance at Date SearchBalance at DateFlowFieldDecimal12
32Net Change SearchNet ChangeFlowFieldDecimal12
33Budgeted Amount SearchBudgeted AmountFlowFieldDecimal12
34Totaling SearchTotalingNormalText250Totalingnvarchar(500)
35Budget Filter SearchBudget FilterFlowFilterCode10
36Balance SearchBalanceFlowFieldDecimal12
37Budgeted at Date SearchBudgeted at DateFlowFieldDecimal12
40Consol. Debit Acc. SearchConsol. Debit Acc.NormalCode20Consol_ Debit Acc_nvarchar(40)
41Consol. Credit Acc. SearchConsol. Credit Acc.NormalCode20Consol_ Credit Acc_nvarchar(40)
42Business Unit Filter SearchBusiness Unit FilterFlowFilterCode20
43Gen. Posting Type SearchGen. Posting TypeNormalOption4Gen_ Posting Typeint
Available options are:
  • ” “
  • “Purchase”
  • “Sale”
44Gen. Bus. Posting Group SearchGen. Bus. Posting GroupNormalCode20Gen_ Bus_ Posting Groupnvarchar(40)
Key to join to the Gen. Business Posting Group table.

Show/hide example query

SELECT
       [G/L Account (Analysis View)].[Gen_ Bus_ Posting Group]
       ,[Gen. Business Posting Group].[Code]
FROM
       [CRONUS UK Ltd_$G_L Account (Analysis View)$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Account (Analysis View)]
LEFT JOIN
       [CRONUS UK Ltd_$Gen_ Business Posting Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Gen. Business Posting Group]
              ON
                     [G/L Account (Analysis View)].[Gen_ Bus_ Posting Group] = [Gen. Business Posting Group].[Code]
45Gen. Prod. Posting Group SearchGen. Prod. Posting GroupNormalCode20Gen_ Prod_ Posting Groupnvarchar(40)
Key to join to the Gen. Product Posting Group table.

Show/hide example query

SELECT
       [G/L Account (Analysis View)].[Gen_ Prod_ Posting Group]
       ,[Gen. Product Posting Group].[Code]
FROM
       [CRONUS UK Ltd_$G_L Account (Analysis View)$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Account (Analysis View)]
LEFT JOIN
       [CRONUS UK Ltd_$Gen_ Product Posting Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Gen. Product Posting Group]
              ON
                     [G/L Account (Analysis View)].[Gen_ Prod_ Posting Group] = [Gen. Product Posting Group].[Code]
47Debit Amount SearchDebit AmountFlowFieldDecimal12
48Credit Amount SearchCredit AmountFlowFieldDecimal12
49Automatic Ext. Texts SearchAutomatic Ext. TextsNormalBoolean4Automatic Ext_ Textstinyint
52Budgeted Debit Amount SearchBudgeted Debit AmountFlowFieldDecimal12
53Budgeted Credit Amount SearchBudgeted Credit AmountFlowFieldDecimal12
54Tax Area Code SearchTax Area CodeNormalCode20Tax Area Codenvarchar(40)
Key to join to the Tax Area table.

Show/hide example query

SELECT
       [G/L Account (Analysis View)].[Tax Area Code]
       ,[Tax Area].[Code]
FROM
       [CRONUS UK Ltd_$G_L Account (Analysis View)$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Account (Analysis View)]
LEFT JOIN
       [CRONUS UK Ltd_$Tax Area$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Tax Area]
              ON
                     [G/L Account (Analysis View)].[Tax Area Code] = [Tax Area].[Code]
55Tax Liable SearchTax LiableNormalBoolean4Tax Liabletinyint
56Tax Group Code SearchTax Group CodeNormalCode20Tax Group Codenvarchar(40)
Key to join to the Tax Group table.

Show/hide example query

SELECT
       [G/L Account (Analysis View)].[Tax Group Code]
       ,[Tax Group].[Code]
FROM
       [CRONUS UK Ltd_$G_L Account (Analysis View)$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Account (Analysis View)]
LEFT JOIN
       [CRONUS UK Ltd_$Tax Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Tax Group]
              ON
                     [G/L Account (Analysis View)].[Tax Group Code] = [Tax Group].[Code]
57VAT Bus. Posting Group SearchTax Bus. Posting GroupNormalCode20VAT Bus_ Posting Groupnvarchar(40)
Key to join to the VAT Business Posting Group table.

Show/hide example query

SELECT
       [G/L Account (Analysis View)].[VAT Bus_ Posting Group]
       ,[VAT Business Posting Group].[Code]
FROM
       [CRONUS UK Ltd_$G_L Account (Analysis View)$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Account (Analysis View)]
LEFT JOIN
       [CRONUS UK Ltd_$VAT Business Posting Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [VAT Business Posting Group]
              ON
                     [G/L Account (Analysis View)].[VAT Bus_ Posting Group] = [VAT Business Posting Group].[Code]
58VAT Prod. Posting Group SearchTax Prod. Posting GroupNormalCode20VAT Prod_ Posting Groupnvarchar(40)
Key to join to the VAT Product Posting Group table.

Show/hide example query

SELECT
       [G/L Account (Analysis View)].[VAT Prod_ Posting Group]
       ,[VAT Product Posting Group].[Code]
FROM
       [CRONUS UK Ltd_$G_L Account (Analysis View)$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Account (Analysis View)]
LEFT JOIN
       [CRONUS UK Ltd_$VAT Product Posting Group$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [VAT Product Posting Group]
              ON
                     [G/L Account (Analysis View)].[VAT Prod_ Posting Group] = [VAT Product Posting Group].[Code]
60Additional-Currency Net Change SearchAdditional-Currency Net ChangeFlowFieldDecimal12
61Add.-Currency Balance at Date SearchAdd.-Currency Balance at DateFlowFieldDecimal12
62Additional-Currency Balance SearchAdditional-Currency BalanceFlowFieldDecimal12
63Exchange Rate Adjustment SearchExchange Rate AdjustmentNormalOption4Exchange Rate Adjustmentint
Available options are:
  • “No Adjustment”
  • “Adjust Amount”
  • “Adjust Additional-Currency Amount”
64Add.-Currency Debit Amount SearchAdd.-Currency Debit AmountFlowFieldDecimal12
65Add.-Currency Credit Amount SearchAdd.-Currency Credit AmountFlowFieldDecimal12
66Analysis View Filter SearchAnalysis View FilterFlowFilterCode10
67Dimension 1 Filter SearchDimension 1 FilterFlowFilterCode20
68Dimension 2 Filter SearchDimension 2 FilterFlowFilterCode20
69Dimension 3 Filter SearchDimension 3 FilterFlowFilterCode20
70Dimension 4 Filter SearchDimension 4 FilterFlowFilterCode20
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
       [G/L Account (Analysis View)].[$systemCreatedBy]
       ,[User].[User Security ID]
FROM
       [CRONUS UK Ltd_$G_L Account (Analysis View)$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Account (Analysis View)]
LEFT JOIN
       [User] AS [User]
              ON
                     [G/L Account (Analysis View)].[$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
       [G/L Account (Analysis View)].[$systemModifiedBy]
       ,[User].[User Security ID]
FROM
       [CRONUS UK Ltd_$G_L Account (Analysis View)$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [G/L Account (Analysis View)]
LEFT JOIN
       [User] AS [User]
              ON
                     [G/L Account (Analysis View)].[$systemModifiedBy] = [User].[User Security ID]

Show/hide tables related from G/L Account (Analysis View) table

Leave a Reply

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