Skip to main content
Solved

DAC Table, On-Premise SQL to Acumatica, Generic Inquiry has Number Fields Multiplied by 24 or 40

  • November 1, 2021
  • 3 replies
  • 159 views
ally
Freshman II
  • ally
  • Freshman II
  • 3 replies

The weird thing is that I was typing “SQL to Acumatica Number Fields Multiplied by 24 or 40” into Google and it autocompleted right after I started typing “multiplied” but then there were no fruitful results.

So, we’re on-premise SQL hosted Acumatica 2021 R1 and I’m attempting to make a Pivot Table run faster by doing the query, main filtering, and calculations on SQL in a View that I have on a Customization as a DAC Table (generated from database) and a Database Script. Everything runs great except for the actual numbers. They are fine when I look at the View in SQL Server Management Studio and there are the same number of rows for the SQL View and the Generic Inquiry on the View in Acumatica, but all of the values of the decimal and integer columns are coming into Acumatica as Number columns multiplied by 24 or 40. Admittedly, this is my first DAC and Customization Project but I’ve checked three different DAC tutorials (the one in the official customization manual and two others by third parties) and I don’t know what I’m missing. Additionally, the original slow running Generic Inquiry we were running for the Pivot Table has the same numbers as the View on SQL.

Best answer by ally

I found the problem. It wasn’t in my work, it was the work an Acumatica provider did for us a year ago that I was told worked but I was tasked with taking it further. Stupid mistake was taking someone in Accounting’s word that it “worked”. It “worked” because the Acumatica provider had made a table where they linked User Roles to GL Account numbers. What I did not know was that their View for the table only worked if the account had just one role. We have since went to a finer granularity of User Roles so by the time I had added all the possible User Roles and matched those Roles to Accounts, the Generic Inquiry was spitting out 24 records for the same GL Account. Turns out the Generic Inquiry they were using was grouping on everything but Role which is why it was getting rolled up 24 or 40 times into a sum amount but still having the same number of rows. So anyway, moral of the story is you should thoroughly check someone else’s work before attaching it to yours!

View original
Did this topic help you find an answer to your question?

3 replies

Gabriel Michaud
Captain II
Forum|alt.badge.img+11

@ally,

Can you share DAC source code as well as table structure? It looks like some sort of automated type casting going on.

Gabriel Michaud Founder @ Velixo, Excel-based reporting for Acumatica
ally
Freshman II
  • ally
  • Author
  • Freshman II
  • 3 replies
  • November 1, 2021

DAC STRUCTURE (QuarterNum and MonthNum used to be Integers before I switched to strings because they were being multiplied by 24)

using System;

using PX.Data;

namespace ARMAccountMapping

{

  [Serializable]

  [PXCacheName("ARMViewableGLHistory")]

  public class ARMViewableGLHistory : IBqlTable

  {

    #region OrganizationCD

    [PXDBString(30, IsUnicode = true, InputMask = "")]

    [PXUIField(DisplayName = "Organization CD")]

    public virtual string OrganizationCD { get; set; }

    public abstract class organizationCD : PX.Data.BQL.BqlString.Field<organizationCD> { }

    #endregion

    #region AccountClassID

    [PXDBString(20, IsUnicode = true, InputMask = "")]

    [PXUIField(DisplayName = "Account Class ID")]

    public virtual string AccountClassID { get; set; }

    public abstract class accountClassID : PX.Data.BQL.BqlString.Field<accountClassID> { }

    #endregion

    #region AccountType

    [PXDBString(1, IsFixed = true, InputMask = "")]

    [PXUIField(DisplayName = "Account Type")]

    public virtual string AccountType { get; set; }

    public abstract class accountType : PX.Data.BQL.BqlString.Field<accountType> { }

    #endregion

    #region AccountCD

    [PXDBString(10, IsUnicode = true, InputMask = "")]

    [PXUIField(DisplayName = "Account CD")]

    public virtual string AccountCD { get; set; }

    public abstract class accountCD : PX.Data.BQL.BqlString.Field<accountCD> { }

    #endregion

    #region AccountDescription

    [PXDBString(73, IsUnicode = true, InputMask = "")]

    [PXUIField(DisplayName = "Account Description")]

    public virtual string AccountDescription { get; set; }

    public abstract class accountDescription : PX.Data.BQL.BqlString.Field<accountDescription> { }

    #endregion

    #region BalanceType

    [PXDBString(1, IsFixed = true, InputMask = "")]

    [PXUIField(DisplayName = "Balance Type")]

    public virtual string BalanceType { get; set; }

    public abstract class balanceType : PX.Data.BQL.BqlString.Field<balanceType> { }

    #endregion

    #region FinPtdDebit

    [PXDBDecimal()]

    [PXUIField(DisplayName = "Fin Ptd Debit")]

    public virtual Decimal? FinPtdDebit { get; set; }

    public abstract class finPtdDebit : PX.Data.BQL.BqlDecimal.Field<finPtdDebit> { }

    #endregion

    #region FinPtdCredit

    [PXDBDecimal()]

    [PXUIField(DisplayName = "Fin Ptd Credit")]

    public virtual Decimal? FinPtdCredit { get; set; }

    public abstract class finPtdCredit : PX.Data.BQL.BqlDecimal.Field<finPtdCredit> { }

    #endregion

    #region Amount

    [PXDBDecimal()]

    [PXUIField(DisplayName = "Amount")]

    public virtual Decimal? Amount { get; set; }

    public abstract class amount : PX.Data.BQL.BqlDecimal.Field<amount> { }

    #endregion

    #region Year

    [PXDBString(4, IsFixed = true, InputMask = "")]

    [PXUIField(DisplayName = "Year")]

    public virtual string Year { get; set; }

    public abstract class year : PX.Data.BQL.BqlString.Field<year> { }

    #endregion

    #region FinPeriodID

    [PXDBString(6, IsFixed = true, InputMask = "")]

    [PXUIField(DisplayName = "Fin Period ID")]

    public virtual string FinPeriodID { get; set; }

    public abstract class finPeriodID : PX.Data.BQL.BqlString.Field<finPeriodID> { }

    #endregion

    #region PeriodStartDate

    [PXDBDate()]

    [PXUIField(DisplayName = "Period Start Date")]

    public virtual DateTime? PeriodStartDate { get; set; }

    public abstract class periodStartDate : PX.Data.BQL.BqlDateTime.Field<periodStartDate> { }

    #endregion

    #region PeriodEndDate

    [PXDBDate()]

    [PXUIField(DisplayName = "Period End Date")]

    public virtual DateTime? PeriodEndDate { get; set; }

    public abstract class periodEndDate : PX.Data.BQL.BqlDateTime.Field<periodEndDate> { }

    #endregion

    #region QuarterNum

    [PXDBString(13, InputMask = "")]

    [PXUIField(DisplayName = "Quarter Num")]

    public virtual string QuarterNum { get; set; }

    public abstract class quarterNum : PX.Data.BQL.BqlString.Field<quarterNum> { }

    #endregion

    #region MonthNum

    [PXDBString(2, InputMask = "")]

    [PXUIField(DisplayName = "Month Num")]

    public virtual string MonthNum { get; set; }

    public abstract class monthNum : PX.Data.BQL.BqlString.Field<monthNum> { }

    #endregion

    #region MonthName

    [PXDBString(30, IsUnicode = true, InputMask = "")]

    [PXUIField(DisplayName = "Month Name")]

    public virtual string MonthName { get; set; }

    public abstract class monthName : PX.Data.BQL.BqlString.Field<monthName> { }

    #endregion

    #region BranchCD

    [PXDBString(30, IsUnicode = true, InputMask = "")]

    [PXUIField(DisplayName = "Branch CD")]

    public virtual string BranchCD { get; set; }

    public abstract class branchCD : PX.Data.BQL.BqlString.Field<branchCD> { }

    #endregion

    #region Subcd

    [PXDBString(30, IsUnicode = true, InputMask = "")]

    [PXUIField(DisplayName = "Subcd")]

    public virtual string Subcd { get; set; }

    public abstract class subcd : PX.Data.BQL.BqlString.Field<subcd> { }

    #endregion

    #region SubDescription

    [PXDBString(288, IsUnicode = true, InputMask = "")]

    [PXUIField(DisplayName = "Sub Description")]

    public virtual string SubDescription { get; set; }

    public abstract class subDescription : PX.Data.BQL.BqlString.Field<subDescription> { }

    #endregion

  }

}

Database Script:

/****** Object:  View [dbo].[ARMViewableGLHistory]    Script Date: 11/1/2021 9:07:13 AM ******/

DROP VIEW IF EXISTS [dbo].[ARMViewableGLHistory]

GO

/****** Object:  View [dbo].[ARMViewableGLHistory]    Script Date: 11/1/2021 9:07:13 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO


CREATE VIEW [dbo].[ARMViewableGLHistory]

AS

SELECT        dbo.GLHistory.CompanyID, dbo.Organization.OrganizationCD, dbo.Account.AccountClassID, dbo.Account.Type AS AccountType, dbo.Account.AccountCD, CONCAT(dbo.Account.AccountCD, ' - ', 

                         dbo.Account.Description) AS AccountDescription, dbo.GLHistory.BalanceType, dbo.GLHistory.FinPtdDebit, dbo.GLHistory.FinPtdCredit, 

                         CASE WHEN dbo.GLHistory.BalanceType = 'A' THEN CASE WHEN (dbo.Account.Type = 'A') OR

                         (dbo.Account.Type = 'E') THEN (dbo.GLHistory.FinPtdDebit - dbo.GLHistory.FinPtdCredit) ELSE (dbo.GLHistory.FinPtdCredit - dbo.GLHistory.FinPtdDebit) END ELSE CASE WHEN (dbo.Account.Type = 'A') OR

                         (dbo.Account.Type = 'E') THEN (dbo.GLHistory.FinPtdCredit - dbo.GLHistory.FinPtdDebit) ELSE (dbo.GLHistory.FinPtdDebit - dbo.GLHistory.FinPtdCredit) END END AS Amount, dbo.FinPeriod.FinYear AS Year, 

                         dbo.FinPeriod.FinPeriodID, dbo.FinPeriod.StartDate AS PeriodStartDate, dbo.FinPeriod.EndDate AS PeriodEndDate,

                         Concat('Q', DATEPART(quarter, Concat(LEFT(dbo.FinPeriod.FinPeriodID, 4), '-', RIGHT(dbo.FinPeriod.FinPeriodID, 2), '-01'))) AS [QuarterNum], 

                         RIGHT(dbo.FinPeriod.FinPeriodID, 2) AS [MonthNum],

                         DATENAME(month, Concat(LEFT(dbo.FinPeriod.FinPeriodID, 4), '-', RIGHT(dbo.FinPeriod.FinPeriodID, 2), '-01')) AS [MonthName],

                         dbo.Branch.BranchCD, dbo.Sub.SubCD, CONCAT(dbo.Sub.SubCD, ' - ', dbo.Sub.Description) AS SubDescription

FROM            dbo.GLHistory INNER JOIN

                         dbo.Account ON dbo.GLHistory.CompanyID = dbo.Account.CompanyID AND dbo.GLHistory.AccountID = dbo.Account.AccountID INNER JOIN

                         dbo.Branch ON dbo.GLHistory.CompanyID = dbo.Branch.CompanyID AND dbo.GLHistory.BranchID = dbo.Branch.BranchID INNER JOIN

                         dbo.Sub ON dbo.GLHistory.CompanyID = dbo.Sub.CompanyID AND dbo.GLHistory.SubID = dbo.Sub.SubID INNER JOIN

                         dbo.FinPeriod ON dbo.GLHistory.CompanyID = dbo.FinPeriod.CompanyID AND dbo.GLHistory.FinPeriodID = dbo.FinPeriod.FinPeriodID INNER JOIN

                         dbo.Organization ON dbo.Branch.CompanyID = dbo.Organization.CompanyID AND dbo.Branch.OrganizationID = dbo.Organization.OrganizationID

WHERE        (dbo.FinPeriod.OrganizationID = 0) AND (dbo.Organization.Active = 1)

GO

ally
Freshman II
  • ally
  • Author
  • Freshman II
  • 3 replies
  • Answer
  • November 1, 2021

I found the problem. It wasn’t in my work, it was the work an Acumatica provider did for us a year ago that I was told worked but I was tasked with taking it further. Stupid mistake was taking someone in Accounting’s word that it “worked”. It “worked” because the Acumatica provider had made a table where they linked User Roles to GL Account numbers. What I did not know was that their View for the table only worked if the account had just one role. We have since went to a finer granularity of User Roles so by the time I had added all the possible User Roles and matched those Roles to Accounts, the Generic Inquiry was spitting out 24 records for the same GL Account. Turns out the Generic Inquiry they were using was grouping on everything but Role which is why it was getting rolled up 24 or 40 times into a sum amount but still having the same number of rows. So anyway, moral of the story is you should thoroughly check someone else’s work before attaching it to yours!

Joe Schmucker
Chris Hackett
2 people like this
  • Joe Schmucker
    Joe Schmucker
  • Chris Hackett
    Chris Hackett

Reply


Most helpful members this week

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved