Ideas for Acumatica

Important! Please note that 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.
This portal is for product ideas and feedback only. If you need customer service assistance, please contact your Acumatica Support Partner or submit a support case.  You can also 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
  • 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?

  • 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! 

  • 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)

  • 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:06

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