Skip to main content

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 ? 

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

 

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

 

Again Thnx for the help :) 

 

@NAWRES 

Below attached Generic Inquiry may help you!!

 

Reply


Terms & ConditionsCookie settings