After taking a detour last week and exploring how to add new fields to existing screens in Acumatica (click here), I decided to return to the task of learning to create Excel reports that point directly to the Acumatica database. I began with this idea three weeks ago (click here) and continued two weeks ago by taking some baby steps to create a very simple Excel report (click here). This week I decided to pickup with where I left off a couple weeks ago and attempt to continue reproducing the Vendor Summary (AP401000) screen in an Excel report.
Continuing With Baby Steps
I ended the post a couple of weeks ago (click here) with the following report in Excel:
This week I continued trying to reproduce the columns in the Vendor Summary (AP401000) inquiry screen which looks like this:
The next step was to try and add the Last Activity Period and Beginning Balance columns since I already had the Vendor ID and Vendor Name columns displaying correctly. After looking through the database tables and trying to find the data that matched the screenshot above, I quickly realized that this was going to be more difficult than I originally thought. The Vendor ID and Vendor Name columns were easy, but the calculation columns seemed to be much harder to find in the database.
More Baby Steps – Running a SQL Trace
After unsuccessfully trying to find the relevant database fields, I decided to try running a SQL Trace. A SQL Trace allows you to see the query that Acumatica is running against the database. So, I turned on a SQL Trace and opened the Vendor Summary (AP401000) screen. Then I found the line where the Acumatica application was trying to get information from the database. Here is the query that I found:
SELECT MAX(APLatestHistory.BranchID), MAX(APLatestHistory.VendorID), MAX(APLatestHistory.AccountID), MAX(APLatestHistory.SubID), APLatestHistory.curyID, APLatestHistory.lastActivityPeriod, Vendor.bAccountID, MAX(Vendor.AcctReferenceNbr), MAX(Vendor.DefAddressID), MAX(Vendor.DefContactID), MAX(Vendor.DefLocationID), MAX(Vendor.TaxRegistrationID), MAX(Vendor.WorkgroupID), MAX(Vendor.ClassID), MAX(Vendor.tstamp), ( NULL ), MAX(Vendor.CreatedByScreenID), MAX(Vendor.CreatedDateTime), ( NULL ), MAX(Vendor.LastModifiedByScreenID), MAX(Vendor.LastModifiedDateTime), MAX(Vendor.AcctCD), MAX(Vendor.AcctName), MAX(Vendor.ParentBAccountID), MAX(Vendor.Type), MAX(Vendor.VendorClassID), MAX(Vendor.TermsID), MAX(Vendor.DefPOAddressID), MAX(Vendor.CuryID), MAX(Vendor.CuryRateTypeID), MAX(Vendor.PriceListCuryID), MAX(Vendor.DefaultUOM), ( NULL ), ( NULL ), MAX(Vendor.DiscTakenAcctID), MAX(Vendor.DiscTakenSubID), MAX(Vendor.PrepaymentAcctID), MAX(Vendor.PrepaymentSubID), MAX(Vendor.POAccrualAcctID), MAX(Vendor.POAccrualSubID), MAX(Vendor.PrebookAcctID), MAX(Vendor.PrebookSubID), ( NULL ), MAX(Vendor.BaseRemitContactID), MAX(Vendor.TaxZoneID), MAX(Vendor.Status), ( NULL ), MAX(Vendor.Box1099), ( NULL ), ( NULL ), MAX(Vendor.TaxReportPrecision), MAX(Vendor.TaxReportRounding), ( NULL ), ( NULL ), MAX(Vendor.TaxPeriodType), MAX(Vendor.SalesTaxAcctID), MAX(Vendor.SalesTaxSubID), MAX(Vendor.PurchTaxAcctID), MAX(Vendor.PurchTaxSubID), MAX(Vendor.TaxExpenseAcctID), MAX(Vendor.TaxExpenseSubID), ( NULL ), ( NULL ), MAX(Vendor.NoteID), ( NULL ), ( NULL ), ( NULL ), MAX(Sub.SubID), MAX(Sub.SubCD), ( NULL ), MAX(Sub.Description), MAX(Sub.ConsoSubID), MAX(Sub.ConsoSubCode), ( NULL ), MAX(Sub.NoteID), ( NULL ), ( NULL ), MAX(Sub.tstamp), ( NULL ), MAX(Sub.CreatedByScreenID), MAX(Sub.CreatedDateTime), ( NULL ), MAX(Sub.LastModifiedByScreenID), MAX(Sub.LastModifiedDateTime), MAX(CuryAPHistory.BranchID), MAX(CuryAPHistory.AccountID), MAX(CuryAPHistory.SubID), MAX(CuryAPHistory.FinPeriodID), MAX(CuryAPHistory.VendorID), MAX(CuryAPHistory.CuryID), ( NULL ), SUM(CuryAPHistory.FinBegBalance), SUM(CuryAPHistory.FinPtdPurchases), SUM(CuryAPHistory.FinPtdPayments), SUM(CuryAPHistory.FinPtdDrAdjustments), SUM(CuryAPHistory.FinPtdCrAdjustments), SUM(CuryAPHistory.FinPtdDiscTaken), SUM(CuryAPHistory.FinPtdWhTax), SUM(CuryAPHistory.FinPtdRGOL), SUM(CuryAPHistory.FinYtdBalance), SUM(CuryAPHistory.FinPtdDeposits), SUM(CuryAPHistory.FinYtdDeposits), MAX(CuryAPHistory.FinPtdRevalued), SUM(CuryAPHistory.TranBegBalance), SUM(CuryAPHistory.TranPtdPurchases), SUM(CuryAPHistory.TranPtdPayments), SUM(CuryAPHistory.TranPtdDrAdjustments), SUM(CuryAPHistory.TranPtdCrAdjustments), SUM(CuryAPHistory.TranPtdDiscTaken), SUM(CuryAPHistory.TranPtdWhTax), SUM(CuryAPHistory.TranPtdRGOL), SUM(CuryAPHistory.TranYtdBalance), SUM(CuryAPHistory.TranPtdDeposits), SUM(CuryAPHistory.TranYtdDeposits), SUM(CuryAPHistory.CuryFinBegBalance), SUM(CuryAPHistory.CuryFinPtdPurchases), SUM(CuryAPHistory.CuryFinPtdPayments), SUM(CuryAPHistory.CuryFinPtdDrAdjustments), SUM(CuryAPHistory.CuryFinPtdCrAdjustments), SUM(CuryAPHistory.CuryFinPtdDiscTaken), SUM(CuryAPHistory.CuryFinPtdWhTax), SUM(CuryAPHistory.CuryFinYtdBalance), SUM(CuryAPHistory.CuryFinPtdDeposits), SUM(CuryAPHistory.CuryFinYtdDeposits), SUM(CuryAPHistory.CuryTranBegBalance), SUM(CuryAPHistory.CuryTranPtdPurchases), SUM(CuryAPHistory.CuryTranPtdPayments), SUM(CuryAPHistory.CuryTranPtdDrAdjustments), SUM(CuryAPHistory.CuryTranPtdCrAdjustments), SUM(CuryAPHistory.CuryTranPtdDiscTaken), SUM(CuryAPHistory.CuryTranPtdWhTax), SUM(CuryAPHistory.CuryTranYtdBalance), SUM(CuryAPHistory.CuryTranPtdDeposits), SUM(CuryAPHistory.CuryTranYtdDeposits), MAX(CuryAPHistory.tstamp) FROM ( SELECT CuryAPHistory.BranchID AS [BranchID], CuryAPHistory.VendorID AS [VendorID], CuryAPHistory.AccountID AS [AccountID], CuryAPHistory.SubID AS [SubID], CuryAPHistory.CuryID AS [CuryID], MAX(CuryAPHistory.FinPeriodID) AS [LastActivityPeriod] FROM CuryAPHistory CuryAPHistory WHERE CuryAPHistory.CompanyID = 13 AND ( CuryAPHistory.BranchID IS NULL OR CuryAPHistory.BranchID IN ( 5, 6, 10, 11, 12, 13 ) ) GROUP BY CuryAPHistory.BranchID, CuryAPHistory.VendorID, CuryAPHistory.AccountID, CuryAPHistory.SubID, CuryAPHistory.CuryID ) APLatestHistory INNER JOIN ( SELECT BAccount.BAccountID AS [BAccountID], BAccount.AcctReferenceNbr AS [AcctReferenceNbr], BAccount.DefAddressID AS [DefAddressID], BAccount.DefContactID AS [DefContactID], BAccount.DefLocationID AS [DefLocationID], BAccount.TaxRegistrationID AS [TaxRegistrationID], BAccount.WorkgroupID AS [WorkgroupID], BAccount.ClassID AS [ClassID], BAccount.tstamp AS [tstamp], BAccount.CreatedByID AS [CreatedByID], BAccount.CreatedByScreenID AS [CreatedByScreenID], BAccount.CreatedDateTime AS [CreatedDateTime], BAccount.LastModifiedByID AS [LastModifiedByID], BAccount.LastModifiedByScreenID AS [LastModifiedByScreenID], BAccount.LastModifiedDateTime AS [LastModifiedDateTime], BAccount.AcctCD AS [AcctCD], BAccount.AcctName AS [AcctName], BAccount.ParentBAccountID AS [ParentBAccountID], BAccount.Type AS [Type], Vendor.VendorClassID AS [VendorClassID], Vendor.TermsID AS [TermsID], Vendor.DefPOAddressID AS [DefPOAddressID], Vendor.CuryID AS [CuryID], Vendor.CuryRateTypeID AS [CuryRateTypeID], Vendor.PriceListCuryID AS [PriceListCuryID], Vendor.DefaultUOM AS [DefaultUOM], Vendor.AllowOverrideCury AS [AllowOverrideCury], Vendor.AllowOverrideRate AS [AllowOverrideRate], Vendor.DiscTakenAcctID AS [DiscTakenAcctID], Vendor.DiscTakenSubID AS [DiscTakenSubID], Vendor.PrepaymentAcctID AS [PrepaymentAcctID], Vendor.PrepaymentSubID AS [PrepaymentSubID], Vendor.POAccrualAcctID AS [POAccrualAcctID], Vendor.POAccrualSubID AS [POAccrualSubID], Vendor.PrebookAcctID AS [PrebookAcctID], Vendor.PrebookSubID AS [PrebookSubID], Vendor.PayToParent AS [PayToParent], Vendor.BaseRemitContactID AS [BaseRemitContactID], BAccount.TaxZoneID AS [TaxZoneID], BAccount.Status AS [Status], Vendor.Vendor1099 AS [Vendor1099], Vendor.Box1099 AS [Box1099], Vendor.TaxAgency AS [TaxAgency], Vendor.UpdClosedTaxPeriods AS [UpdClosedTaxPeriods], Vendor.TaxReportPrecision AS [TaxReportPrecision], Vendor.TaxReportRounding AS [TaxReportRounding], Vendor.TaxUseVendorCurPrecision AS [TaxUseVendorCurPrecision], Vendor.TaxReportFinPeriod AS [TaxReportFinPeriod], Vendor.TaxPeriodType AS [TaxPeriodType], Vendor.SalesTaxAcctID AS [SalesTaxAcctID], Vendor.SalesTaxSubID AS [SalesTaxSubID], Vendor.PurchTaxAcctID AS [PurchTaxAcctID], Vendor.PurchTaxSubID AS [PurchTaxSubID], Vendor.TaxExpenseAcctID AS [TaxExpenseAcctID], Vendor.TaxExpenseSubID AS [TaxExpenseSubID], Vendor.GroupMask AS [GroupMask], BAccount.OwnerID AS [OwnerID], BAccount.NoteID AS [NoteID], NULL AS [NoteText], NULL AS [NoteFiles], Vendor.LandedCostVendor AS [LandedCostVendor] FROM Vendor Vendor INNER JOIN BAccount BAccount ON BAccount.CompanyID = 13 AND BAccount.DeletedDatabaseRecord = 0 WHERE Vendor.CompanyID = 13 AND Vendor.DeletedDatabaseRecord = 0 AND Vendor.BAccountID = BAccount.BAccountID ) Vendor ON ( APLatestHistory.vendorID = Vendor.bAccountID --AND ( (0 = SUBSTRING(Vendor.GroupMask, 1, 4) & @P0 -- OR 0 <> SUBSTRING(Vendor.GroupMask, 1, 4) & @P1) -- ) ) LEFT JOIN Sub Sub ON Sub.CompanyID = 13 AND Sub.DeletedDatabaseRecord = 0 AND ( APLatestHistory.subID = Sub.SubID ) LEFT JOIN CuryAPHistory CuryAPHistory ON CuryAPHistory.CompanyID = 13 AND ( CuryAPHistory.BranchID IS NULL OR CuryAPHistory.BranchID IN ( 5, 6, 10, 11, 12, 13 ) ) AND ( APLatestHistory.accountID = CuryAPHistory.AccountID AND APLatestHistory.branchID = CuryAPHistory.BranchID AND APLatestHistory.vendorID = CuryAPHistory.VendorID AND APLatestHistory.subID = CuryAPHistory.SubID AND APLatestHistory.curyID = CuryAPHistory.CuryID AND APLatestHistory.lastActivityPeriod = CuryAPHistory.FinPeriodID ) WHERE ( APLatestHistory.branchID = 5 ) GROUP BY APLatestHistory.lastActivityPeriod, APLatestHistory.curyID, Vendor.bAccountID
You don’t have to understand anything about this query other than the fact that it’s very long and complicated. There are only 14 columns in the Vendor Summary (AP401000) inquiry screen, but there are 135 columns getting returned by the query to the database. That’s right, 135 columns instead of 14!!! Also, many of the columns contain duplicate information so it’s impossible to determine which one is actually getting displayed on the Vendor Summary (AP401000) inquiry screen.
It seems that I’ve run into a brick wall with my Excel report project.
DAC Not The Same As A Database Table
Another thing I realized is that DAC is not the same thing as a database table. For example, if you open the Vendor DAC in the Acumatica Report Designer, there are 125 fields available, but there are only 47 fields available in the Vendor database table. Hmmm, maybe this is why there are so many fields returned by the complicated query above. Maybe it returns all the DAC columns and depends on the Acumatica screen to pick the ones that it wants to display. Well, I’m not really sure why it does this, but for sure DAC is not the same thing as a database table.
One of my original assumptions was that learning the database structure would help me with DAC. However, this doesn’t seem to be the case.
I wonder if there is any documentation on the internet about Acumatica DAC. I think I’ll Google “acumatica dac documentation” and see what comes up:
Hmmm, it looks like the first link takes me to Sergey’s blog which is a great Acumatica resource. And, wow, he already has a post on this exact topic from almost two years ago:
Data Access Classes (DAC) are starting to look very attractive right now. It’s starting to get colder and colder out here alone in the cold with the Acumatica database. I’m not sure how much longer I’m going to last. It seems like the whole assumption is that you will let the Acumatica platform do the database work for you, just like it does when you add a field to a screen (click here).