Link Account To Batch

  • 1 March 2021
  • 3 replies

Userlevel 2

Hello Guys 


Can you help me with finding a way to link “Account” and “Batch”; I tried using GLTran but I have the same line repeated multiple times 

select * from account INNER join GLTran on Account.AccountID=GLTran.AccountID
INNER join Batch on GLTran.BatchNbr=batch.BatchNbr
order by AccountCD


I’m missing something ? 


Best answer by Gabriel Michaud 1 March 2021, 20:26

View original

3 replies

Userlevel 7
Badge +10

Acumatica is a multi-tenant system, and most tables in the application contain a field named CompanyID (it would be more appropriate to name it TenantID, but it’s like that for historical reasons). You need to make sure to join and filter on the company ID that you’re looking for.

Next, the Batch and GLTran table have two keys: Module and BatchNbr. This has to be part of your join conditions too.

Finally, keep in mind that a single GL Batch can and will generally contain more than one line (the only exception could be when working with a statistical ledger)

Here’s a working SQL query:

INNER JOIN GLTran ON Batch.CompanyID=GLTran.CompanyID AND Batch.Module=GLTran.Module AND Batch.BatchNbr=GLTran.BatchNbr
INNER JOIN Account ON Batch.CompanyID=Account.CompanyID AND GLTran.AccountID = Account.AccountID
WHERE Batch.CompanyID=2


Userlevel 2

@Gabriel Michaud Thank you ; I’ll try the inquiry 


Again Thnx for the help :) 


Userlevel 7
Badge +18


Below attached Generic Inquiry may help you!!



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