We were working on a project and came across a need that required some customization that we'd like to share in this Algorithm Acumatica Tip.
The Need: A Grid Showing Summarized Debts and Credits for Expense-Type General Ledger Accounts.
The Catch: We wanted totals for both period-to-date (PTD) and year-to-date (YTD) on the same generic inquiry (GI).In SQL, we would use a sub-query, but we needed a different solution in a generic inquiry.
We joined the general ledger transaction table (GLTran) to itself using aliases ([GLTranYTD] & [GLTranPTD]). Then GLTranYTD and GLTranPTD were connected using a left join so that we could maintain all GLTranYTD data.
The connection was then made by linking the primary keys for these tables together. At the end, we added one more relationship to check if transactions happened in the current month.
As an added touch, we created a checkbox to allow users to toggle between seeing posted-only, and posted + released transactions.
If you would like to see the XML file that contains the full solution architecture, please contact us with your request.