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
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:
SELECT * FROM Batch
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
+11Acumatica 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:
SELECT * FROM Batch
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
+19Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
