Ideas for Acumatica

Feedback processing: We do not reply to all messages, but we do read them, analyze them, and work to improve Acumatica based on the feedback we receive. Ideas and comments may not appear immediately. Some legitimate ideas are flagged as spam and will be added when we review the spam folders.
Content: This portal is for product ideas and feedback only. If you need customer service assistance, contact your Acumatica Support Partner, submit a support case, or get assistance from community resources: LinkedIn Group or StackOverflow
No Reliance: Information is maintained on a best-efforts basis and may be changed without notice. Acumatica cannot guarantee the accuracy of the information provided or guarantee completion of features/ideas described on this portal.

Add A Screen To Translate SQL Into BQL

When developing customizations, it would be extremely helpful to have the ability to translate a SQL select statement into BQL.

  • David Eichner
  • Jan 24 2019
  • Future consideration
  • Attach files
  • David Eichner commented
    January 31, 2019 18:06

    SQL to GI would be great as well...as long as we could press a button and convert the GI to a BQL select statement as well

  • David Eichner commented
    January 31, 2019 18:03

    Perhaps the trace could show the BQL select statement in addition to the SQL select statement that is already there?

  • David Eichner commented
    January 31, 2019 18:03

    We have been trying to write a customization to create XBRL from ending balance for a specific financial period.  We ran a GL Trial Balance Summary report and looked at the trace which showed us the SQL.  Since the SQL is extremely complex, trying to write the BQL would be a lot of work.  Here's the SQL Select from the trace:

    SELECT GLHistoryByPeriod.BranchID, GLHistoryByPeriod.LedgerID, GLHistoryByPeriod.AccountID, GLHistoryByPeriod.SubID, GLHistoryByPeriod.FinPeriodID, GLHistoryByPeriod.LastActivityPeriod, Account.[AccountCD], Account.[AccountID], Account.[Type], Account.[COAOrder], Account.[Description], Account.[NoteID], NULL, NULL, NULL, Sub.[SubCD], Sub.[NoteID], NULL, NULL, NULL, AHCurrent.[LedgerID], AHCurrent.[BranchID], AHCurrent.[AccountID], AHCurrent.[SubID], AHCurrent.[FinPeriodID], AHCurrent.[FinPtdCredit], AHCurrent.[FinPtdDebit], AHCurrent.[FinYtdBalance], AHCurrent.[FinBegBalance], AHLast.[LedgerID], AHLast.[BranchID], AHLast.[AccountID], AHLast.[SubID], AHLast.[FinPeriodID], AHLast.[FinPtdCredit], AHLast.[FinPtdDebit], AHLast.[FinYtdBalance], AHLast.[FinBegBalance], GLSetup.[YtdNetIncAccountID], GLSetup.[TrialBalanceSign], Branch.[BranchCD]
    FROM (
        SELECT [GLHistory].[BranchID] AS [BranchID], [GLHistory].[LedgerID] AS [LedgerID], [GLHistory].[AccountID] AS [AccountID], [GLHistory].[SubID] AS [SubID], MAX([GLHistory].[FinPeriodID]) AS [LastActivityPeriod], [MasterFinPeriod_FinPeriod].[FinPeriodID] AS [FinPeriodID]
        FROM [GLHistory] [GLHistory]
        INNER JOIN
            [FinPeriod] MasterFinPeriod_FinPeriod
         ON [MasterFinPeriod_FinPeriod].[FinPeriodID] >= [GLHistory].[FinPeriodID] AND [MasterFinPeriod_FinPeriod].CompanyID = 4 AND [MasterFinPeriod_FinPeriod].[OrganizationID] = 0
        WHERE [GLHistory].CompanyID = 4 AND ([GLHistory].BranchID IS NULL OR [GLHistory].BranchID = 21)
        GROUP BY [GLHistory].[BranchID], [GLHistory].[LedgerID], [GLHistory].[AccountID], [GLHistory].[SubID], [MasterFinPeriod_FinPeriod].[FinPeriodID]
    ) GLHistoryByPeriod
    INNER JOIN [Account] [Account] ON Account.CompanyID = 4 AND [Account].[DeletedDatabaseRecord] = 0 AND GLHistoryByPeriod.AccountID = Account.[AccountID]
    INNER JOIN [Sub] [Sub] ON Sub.CompanyID = 4 AND [Sub].[DeletedDatabaseRecord] = 0 AND GLHistoryByPeriod.SubID = Sub.[SubID]
    LEFT JOIN [GLHistory] [AHCurrent] ON AHCurrent.CompanyID = 4 AND (AHCurrent.BranchID IS NULL OR AHCurrent.BranchID = 21) AND GLHistoryByPeriod.LedgerID = AHCurrent.[LedgerID] AND GLHistoryByPeriod.AccountID = AHCurrent.[AccountID] AND GLHistoryByPeriod.SubID = AHCurrent.[SubID] AND GLHistoryByPeriod.FinPeriodID = AHCurrent.[FinPeriodID] AND GLHistoryByPeriod.BranchID = AHCurrent.[BranchID]
    LEFT JOIN [GLHistory] [AHLast] ON AHLast.CompanyID = 4 AND (AHLast.BranchID IS NULL OR AHLast.BranchID = 21) AND GLHistoryByPeriod.LedgerID = AHLast.[LedgerID] AND GLHistoryByPeriod.AccountID = AHLast.[AccountID] AND GLHistoryByPeriod.SubID = AHLast.[SubID] AND GLHistoryByPeriod.LastActivityPeriod = AHLast.[FinPeriodID] AND GLHistoryByPeriod.BranchID = AHLast.[BranchID]
    INNER JOIN [GLSetup] [GLSetup] ON GLSetup.CompanyID = 4
    INNER JOIN [Branch] [Branch] ON Branch.CompanyID = 4 AND [Branch].[DeletedDatabaseRecord] = 0 AND GLHistoryByPeriod.BranchID = Branch.[BranchID]
    WHERE GLHistoryByPeriod.finPeriodID = '201901' AND (Account.[Type] = 'A' OR Account.[Type] = 'L' AND Account.[AccountID] <> GLSetup.[YtdNetIncAccountID] OR GLHistoryByPeriod.lastActivityPeriod >= '201901') AND (GLHistoryByPeriod.branchID = NULL OR NULL IS NULL) AND (GLHistoryByPeriod.ledgerID = 24 OR 'ACTUAL' IS NULL)
    ORDER BY GLHistoryByPeriod.ledgerID ASC, Account.[COAOrder] ASC, Account.[Type] ASC, Account.[AccountCD] ASC OPTION(OPTIMIZE FOR UNKNOWN)

  • Morgan Taylor commented
    January 31, 2019 18:00

    The ability to convert SQL to a GI schema would be even more useful for myself. I think that is a great idea! 

  • Vladimir Panchenko commented
    January 31, 2019 17:52

    Hi David,

    Your idea is awesome! We've been thinking about it for a while, and we even have a prototype from one of our hackathons. But there are some difficulties because there is no "one-to-one" relation between DACs and tables in the database. We'll think how we can make it into the release.

    Also, it would be quite useful to convert SQL to GI schema. What do you think about it?