@ally,

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

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]

  rPXCacheName("ARMViewableGLHistory")]

  public class ARMViewableGLHistory : IBqlTable

  {

    #region OrganizationCD

    oPXDBString(30, IsUnicode = true, InputMask = "")]

    tPXUIField(DisplayName = "Organization CD")]

    public virtual string OrganizationCD { get; set; }

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

    #endregion

    #region AccountClassID

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

    public virtual string AccountClassID { get; set; }

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

    #endregion

    #region AccountType

    nPXDBString(1, IsFixed = true, InputMask = "")]

    
    public virtual string AccountType { get; set; }

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

    #endregion

    #region AccountCD

    cPXDBString(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 = "")]

    BPXUIField(DisplayName = "Account Description")]

    public virtual string AccountDescription { get; set; }

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

    #endregion

    #region BalanceType

    tPXDBString(1, IsFixed = true, InputMask = "")]

    aPXUIField(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()]

    bPXUIField(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 = "")]

    
    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

    fPXDBDate()]

    BPXUIField(DisplayName = "Period End Date")]

    public virtual DateTime? PeriodEndDate { get; set; }

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

    #endregion

    #region QuarterNum

    rPXDBString(13, InputMask = "")]

    dPXUIField(DisplayName = "Quarter Num")]

    public virtual string QuarterNum { get; set; }

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

    #endregion

    #region MonthNum

    lPXDBString(2, InputMask = "")]

    rPXUIField(DisplayName = "Month Num")]

    public virtual string MonthNum { get; set; }

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

    #endregion

    #region MonthName

    dPXDBString(30, IsUnicode = true, InputMask = "")]

    aPXUIField(DisplayName = "Month Name")]

    public virtual string MonthName { get; set; }

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

    #endregion

    #region BranchCD

    bPXDBString(30, IsUnicode = true, InputMask = "")]

    bPXUIField(DisplayName = "Branch CD")]

    public virtual string BranchCD { get; set; }

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

    #endregion

    #region Subcd

    rPXDBString(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

    
    IPXUIField(DisplayName = "Sub Description")]

    public virtual string SubDescription { get; set; }

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

    #endregion

  }

}

Database Script:

/****** Object:  View Xdbo].iARMViewableGLHistory]    Script Date: 11/1/2021 9:07:13 AM ******/

DROP VIEW IF EXISTS cdbo].bARMViewableGLHistory]

GO

/****** Object:  View adbo].cARMViewableGLHistory]    Script Date: 11/1/2021 9:07:13 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW �dbo].XARMViewableGLHistory]

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 bMonthNum],

                         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

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!