Solved

How to return all batches where a given account is involved.

cdiaz
Rank
Userlevel 1

Hi everyone, 

I want to create a GI to look at where our cash is going. My first attempt was to start with CATran and join GLTran, then to join GLTran on itself with just the BatchNbr. I thought this would return all rows for all batches that involve the cash account.

Has anyone done something like this before?

 

So far, it appears I may need an SQL nested view… would love it if there was another way. I don’t think I have access to make new DACs with SQL
Generic Inquiry – Use subqueries for a field – Acumatica Generic Inquiries & Pivot Tables – Acumatica User Group Forums (augforums.com)

icon

Best answer by bryanb39 10 June 2022, 18:58

View original

4 replies

Laura02
Rank
Badge +18

Hello,

I can’t help you with your query (someone else will come along and help) but I do have some ideas for seeing where cash is going using standard Acumatica features.

Have you considered using the Cash Flow report in Financial Reporting? The purpose of the cash flow is to identify sources & uses of cash.

If you want to see all batches that included transactions to a particular cash account, try Cash Account Details, generic inquiry in the Banking module.

Laura ~ Klear Systems, Inc. Irvine, CA. ~ AUG Forums Episode130 ~
cdiaz
Rank
Userlevel 1

Thanks Laura, 

I think Cash Account Details is what I’m looking for but I need it in a GI format so I can expose via ODATA and build a dashboard in Power BI.

bryanb39
Rank
Userlevel 6
Badge +5

If you related the GLTran to GL.Batch

BatchNbr = BatchNbr
AccountID =   =’1678’  (ID, not the CD, or add another relationship and maybe do class or a parameter to select the account). 

Then relate GL.Batch to GLTran (with an alias)
BatchNbr = BatchNbr

Then on the results:

Group by Batch and Line number with a Min or Max of your Credits and Debits to remove duplicates.

 

 

https://www.fiverr.com/acudatica
bryanb39
Rank
Userlevel 6
Badge +5

Just keep in mind that it doesn’t always show where it went.  There could be multiple source accounts.  For example if a cash drop or fee is used it may not balance.

You may end up having to do some more work externally to clean that up, but sounds like that’s your plan since you want this exposed to OData.  

https://www.fiverr.com/acudatica

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved