Skip to main content
Solved

Joining GLTran to INTran


Michaelh
Semi-Pro II
Forum|alt.badge.img+1

I’m trying to get all inventory movements through a purchases accrual account in an inquiry. I can get the APTran joined nicely. I have about HALF of my INTran records showing as expected.

The issue I have is that I “think” I need to use Line Number, but the Line Number on INTran is 1, but in the GLTran it’s 2 for a record (Purchases Accrual comes in as the 2nd line, the 1st line is the offsetting account). Without the Line Number, I’m concernced that a transaction could have two rows with the same Item and Account ID, thus creating duplicate entries. The DAC Browser doesn’t have anything regarding these two tables that I can find (hopefully those get more flushed out as we go along).

 

Joins between GLTran and INTran

Thanks for looking/helping everyone!

Best answer by Michaelh

I ended up making two inquiries to solve the problem.

View original
Did this topic help you find an answer to your question?

9 replies

Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3377 replies
  • July 12, 2023

Hi @Michael Hansen  Below are the join conditions for the table GLTRAN and INTRAN.

 

Select * from INTran I inner join GLTran G on I.CompanyID= G.CompanyID and I.TranType=G.TranType and I.RefNbr = G.RefNbr
Where I.RefNbr='002051' and I.DocType='R'

If you could not able to figure it out, just share your GI, so that I will make the correction and send it back to you.


Michaelh
Semi-Pro II
Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 184 replies
  • July 12, 2023

I don’t think that would account for an INTran line with two items, or even two identical items would it? 

I don’t know how the INTran module works persay, but if an INtran /GLTran Document had:

 

INTran:
Item A qty 5

Item A qty 10

GLTran:

Item A qty 5

Item A qty 10

Wouldn’t I get four results with the joins you mentioned above? Nothing here is choosing the item by the ID or by the line number. Again, I’m not sure how the GLTran works, maybe it forces only one item per GLTran line, but I know INTran can have MANY items on it, thus my intention to use the LineNbr field to isolate that item uniquely.


Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3135 replies
  • July 12, 2023

Hello Michael,

In addition to joining on appropriate Key Fields from both GL Tran and IN Tran,

and solving whether Line order of Inventory batch is the same as line order of the GL Batch,

please consider whether to use Debit Amount vs. Credit Amount from GL Tran.  Inventory can be a Debit or a  Credit in GL depending on the Transaction Type in Inventory, Receipt versus Return.

GLTran key fields:  Company ID, Module, Batch Number, Linenbr

INTran key fields: Company ID, DocType, Reference nbr, LineNbr.

To find out how inventory items work in GLTran, I suggest you create inventory batches and then study the results in the GL batches, making sure “Post Summary on Updating GL” is NOT selected in IN Preferences screen.

 

Laura


Michaelh
Semi-Pro II
Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 184 replies
  • July 12, 2023

I have a sample set for validating. Here is my issue with the joins we’ve posted.

 

I want all GLTran lines for a specific account (Purchase Accruals). I have it filtered to only show those rows. This is my starting set, I now have all the dollars moving through the account. Now I want to tie it back to the PO who created it, so I need to bind to INTran.

 

INTran has ONE line in it, but GLTran has TWO lines in it. The TranLineNbr for GLTran is “2” (line 1 being the offsetting GL entry), but since the INTran only has one line, it has LineNbr “1”. My join fails here. I need a way to get from the second GLTran Line (which holds my purchases accrual account data) back to INTran, who only has a single line.

 

My concern now, is that if I drop the TranLine identifier, and my transaction has multiple items with the same ID, I will get duplicate entries. 

Also, thank you everyone for helping me dig into this, it is GREATLY appreciated!


Forum|alt.badge.img
  • Semi-Pro II
  • 81 replies
  • July 13, 2023

There’s some unintuitive behavior with GLTran that makes it hard to do cross-table inquiry, at least under our setup:

  • INTran’s LineNumber goes 1>3>5>7
  • GLTran’s TranLineNbr for the AP leg (RefNbr = AP) goes 1>2>3>4 - which may or may not be the same as your Receipt lines depending on the bill.
  • GLTran’s TranLineNbr straight up doesnt show in the IN leg (RefNbr = INTran Receipt’s RefNbr)
  • GLTran’s LineNbr has offsetting accounts - hard to trace back since can have other offsetting accounts like tax

If I were facing your issue, I would ignore GLTran and use INTran as the base, you already have AcctID as the account and ExtCost as the dollar amount. By filtering out only PO accrual accounts and only Released transactions, you could (theoretically) tie back to GL, barring any reclassifications of course. 


Michaelh
Semi-Pro II
Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 184 replies
  • July 13, 2023

Unfortunately, I still need to bind APTran as well. I’m not sure I could get from INTran → GLTran and still have APTran records for my account without making duplicate entries.

This is why I started in GLTran (Both APTran and INTran make GLTran records).

I can make two inquries to get the answer for now. Just feels like a miss for linking the tables forwards and backwards. You would think that there’d HAVE to be a 1:1 relationship here… At least I’m not the only nerd struggling with these line numbers :D


Chris Hackett
Community Manager
Forum|alt.badge.img
  • Acumatica Community Manager
  • 2620 replies
  • August 9, 2023

Hi @Michael Hansen were you able to find a solution? Thank you!


Michaelh
Semi-Pro II
Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 184 replies
  • Answer
  • August 9, 2023

I ended up making two inquiries to solve the problem.


Graeme Laughton-Mutu
Freshman II
Forum|alt.badge.img

I’ve come across this same issue and can only join GLTran to INTran to a certain extent, but not 100% unique 1 to 1.

Therefore my report duplicates lines when the IN record has lines with the same Inventory ID, Qty, Value etc.

Having GLTran.TranLineNbr populate with the INTran.LineNbr (as it does with AP and AR LineNbr’s) would give a unique 1 to 1 join and resolve this. I’ve created an idea for this:

 

https://community.acumatica.com/ideas/populate-gltran-tranlinenbr-for-all-intran-26600

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings