By using this website, you agree to our Terms of Use (click here)
Joins can be tricky. In general, whenever you are joining from one table to another, you want to join on the primary keys in the second table. Here's an example where that doesn't work for some reason.
Before we go into the join between GLTran and INTran, let's talk about the "why".
Let's say that we are looking at this journal entry on the Journal Transactions (GL301000) screen:
If we click the VIEW SOURCE DOCUMENT button, to see where the journal entry came from, we'll see in this case that it came from the Receipts (IN301000) screen:
If we use Inspect Element (click here) on the Journal Transactions (GL301000) screen, we can see that the lines of the journal entry are stored in the GLTran Data Access Class (DAC).
If we use Inspect Element (click here) on the Receipts (IN301000) screen, we can see that the lines of the inventory transaction are stored in the INTran Data Access Class (DAC).
GLTran has some information about the inventory transaction, like the Item, but maybe we want to know the Warehouse Location which can only be found in INTran.
That's where we need to join from GLTran to INTran.
We know that the relationship must exist because pressing the VIEW SOURCE DOCUMENT button takes us from the journal entry to the inventory transaction.
Back to that "in general" rule from the beginning, we should find the primary keys for INTran which we can do using the Source Code (SM204570) screen which shows us that the primary keys are: DocType, RefNbr, and LineNbr
So, let's try joining from GLTran to INTran. How do we know which fields to use in GLTran? There is probably a better way to do this, but I used a scientific method known as "guessing" to find them. This is the Generic Inquiry that I came up with, including the joins:
The results of the Generic Inquiry look like this with the "IN" fields noticeably empty:
Why didn't this work? Well for some reason (maybe someone can explain why), you have to use INTran.TranType rather than INTran.DocType in the join, even though INTran.TranType is not part of the primary key. Let's fix that in the join:
Ah, now our Generic Inquiry is returning the data that we were expecting:
Hi @timrodman, does this still work for you?
Perhaps since this post things have changed.
On version 23.112.0032 the join above doesn't return the INTran data.
I've seen a few posts online and can see in my instance that GLTran.TranLineNbr doesn't always populate for INTran lines, meaning I can join to the whole GL batch, but not per line (which causes duplication).
Any ideas/recommendations on new ways to join GLTran to INTran at the specific line level?
Since posting this I've submitted an idea to populate the field always and allow the tables to be joint in all scenarios.
Any votes appreciated as this is quite the gap, and it would be great if it was fixed sooner than later 🙂