BAQ for APTran and CheckHed

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!

image

Make sure you have the TranGLC set to RelatedToFile = CheckHed

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

An example would be great.

This is the end result sample I’m trying to get to:

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) image

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