Hi,
Does anyone know the best way to join APTran and CheckHed with TRANGLC?
Or would this need to be 2 separate BAQs?
I believe they both have Key 1 in common but I can seem to get the SQL to not error in the BAQ, as a novice.
Thank you
Pete
Hi,
Does anyone know the best way to join APTran and CheckHed with TRANGLC?
Or would this need to be 2 separate BAQs?
I believe they both have Key 1 in common but I can seem to get the SQL to not error in the BAQ, as a novice.
Thank you
Pete
What Error are you getting?
Whats the best way to link these if the “Related To” field between APTran and CheckHDR is not the same?
I tried to join based on key and it just says bad SQL.
Thank you!
Thanks - Getting closer. what if I want to see both the debits and credits though of the journal though?
if I only use CheckHDR I just see the credit.
The Key in Tranglc is a string and thus would need to an integer first. I create that as a innerquery first.
convert(int, TranGLC.Key1)
From there join the calculated field to the headnum from checkhead
If you need an example let me know
Epiuser.baq (42.6 KB)
Try this out
create a filter query (how I like to do it btw)
Then create your report as such
Relationship from TranGLC_Key to ERP.CheckHead
Relationship from ERP.CheckHead to ERP.APTran
Display any fields you want at that point - in my example I have the following
Hope this helps
Peter
In the TranGLC_Key is where I have the formula to convert the string headnum to an integer for the record.
Thanks John!
How do you get from screenshot 1 to screenshot 2? I know who to create a subquery and the calculated field, I’m just not sure how to connect subquery 1 and 2 together via that new calculated field?
In the designer instead of adding a table (first icon) add the subquery (second icon) and manually add the join (in this case company and headernum)
If you download and install the BAQ I included you should have what you need (as a framework)
Take a look at [KB0039633 - Efficient Joining Between the TranGLC and PartTran Tables] on EpicCare. Has a lot of useful info
Also take a look at KB0037268 FAQ Linking the TranGLC table to the Original Transaction Tables and the GLJrnDtl table
Thanks John - Got it! appreciate your help
HI John,
How would you handle it if you had more than 1 field you needed to convert? Created a calculated field for each to do the conversion? all in the same inner subquery?
i.e… TranGLC and RcvDtl
Thanks
Pete
Peter
Sorry I have not been on this month. Do you need help still?
In general, I would create the calculated field to control what the results are.
Rob’s links are on point also and the same concept - probable cleaner but I have not tried them.
John
I’m all set now - thanks for checking back