Does anyone have any xml files for basic Cash Account Generic Inquiries?
I would really like to build something simple that just shows me a total for a cash account based on todays current Balance and adjust for any AR or AP that is due within the parameter date range I give it.
I want to be able to say, how much will I have on 11/20/2025 based on the balance of today, any due AR and AP, and any future AR and AP that has not been paid yet.
Almost all of the banking and finance inquiries are Customizations that do not let me pull that information in as a data source. And I am not experienced enough to mess with the customizations themselves.
Has anyone made something like this? Or does anyone know how to make one?
Needed info:
Cash account
Beginning balance @Today
Date filter/parameter
Sum AR completed and due within Date filter
Sum AP completed and due within Date filter
Ending Balance running total for each day
Best answer by PaulMainard55
Does anyone have any xml files for basic Cash Account Generic Inquiries?
I would really like to build something simple that just shows me a total for a cash account based on todays current Balance and adjust for any AR or AP that is due within the parameter date range I give it.
I want to be able to say, how much will I have on 11/20/2025 based on the balance of today, any due AR and AP, and any future AR and AP that has not been paid yet.
Almost all of the banking and finance inquiries are Customizations that do not let me pull that information in as a data source. And I am not experienced enough to mess with the customizations themselves.
Has anyone made something like this? Or does anyone know how to make one?
Needed info:
Cash account
Beginning balance @Today
Date filter/parameter
Sum AR completed and due within Date filter
Sum AP completed and due within Date filter
Ending Balance running total for each day
@bodiec - The way I see it is that you need to join both the ARInvoice and APInvoice tables the GLHistory table, by way of the CashAccount table. You would probably need to include the GLHistorybyPeriod table as well.
In looking at the tables, I don’t see any Cash module tables that maintains daily balances and GLHistory table calculates ending balances for all periods (not date based). This can make the query a little challenging when it comes to selecting the correct ending balance for the current period. I suspect you’ll need to include an additional filter to always select the current financial period from the GLHistory table, in addition to the date filters you referred to.
This smells like your query would likely need a custom SQL View and DAC in order for you to efficiently get what you’re looking for. While the GI tools is a good place to start, none of the internal inquiries in Acumatca that calculates and displays calculated balances are built with GIs. I’m betting that most have some underlying views or stored procedures to deliver the data.
You could try reviewing the existing list of GIs to try to find something close to what you’re trying to develop. I’m often surprised by the number of queries that have been built for dashboards.
Not sure if this was helpful to you - I’m just thinking out loud here. :)
Have you tried the Cash Flow Forecast Inquiry included in Banking module? Select Company, Select Bank account, select Start Date. Options to include/exclude entered/non-released, non-posted transactions.
There is a related setting, “Default Date Range” in Cash Preferences.
Balance as of today is included, through one week because the Default Date Range is set to Week in this example.
(AR and AP is included in the Cash totals by day, but isn’t shown as a separate line.)
Did you try the checkbox that says, “Show Summary Only”? Seems like the results will be reduced to one line per Cash Account, still not listing AR and AP Due as a sum total separate from posted cash balances.
I’m sure it’s built fine. I wasn’t entirely sure from the Help, what the summary version provides. I’ll keep thinking about this and update you if possible. 🤞
Does anyone have any xml files for basic Cash Account Generic Inquiries?
I would really like to build something simple that just shows me a total for a cash account based on todays current Balance and adjust for any AR or AP that is due within the parameter date range I give it.
I want to be able to say, how much will I have on 11/20/2025 based on the balance of today, any due AR and AP, and any future AR and AP that has not been paid yet.
Almost all of the banking and finance inquiries are Customizations that do not let me pull that information in as a data source. And I am not experienced enough to mess with the customizations themselves.
Has anyone made something like this? Or does anyone know how to make one?
Needed info:
Cash account
Beginning balance @Today
Date filter/parameter
Sum AR completed and due within Date filter
Sum AP completed and due within Date filter
Ending Balance running total for each day
@bodiec - The way I see it is that you need to join both the ARInvoice and APInvoice tables the GLHistory table, by way of the CashAccount table. You would probably need to include the GLHistorybyPeriod table as well.
In looking at the tables, I don’t see any Cash module tables that maintains daily balances and GLHistory table calculates ending balances for all periods (not date based). This can make the query a little challenging when it comes to selecting the correct ending balance for the current period. I suspect you’ll need to include an additional filter to always select the current financial period from the GLHistory table, in addition to the date filters you referred to.
This smells like your query would likely need a custom SQL View and DAC in order for you to efficiently get what you’re looking for. While the GI tools is a good place to start, none of the internal inquiries in Acumatca that calculates and displays calculated balances are built with GIs. I’m betting that most have some underlying views or stored procedures to deliver the data.
You could try reviewing the existing list of GIs to try to find something close to what you’re trying to develop. I’m often surprised by the number of queries that have been built for dashboards.
Not sure if this was helpful to you - I’m just thinking out loud here. :)
