BAQ for APTran and CheckHed

littlesistersba · December 11, 2020, 1:40pm

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

MelissaC · December 11, 2020, 1:53pm

What Error are you getting?

littlesistersba · December 11, 2020, 2:03pm

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!

MelissaC · December 11, 2020, 2:24pm

Make sure you have the TranGLC set to RelatedToFile = CheckHed

littlesistersba · December 11, 2020, 2:42pm

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.

GTI07094 · December 11, 2020, 2:47pm

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

littlesistersba · December 11, 2020, 2:50pm

An example would be great.

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

GTI07094 · December 11, 2020, 3:20pm

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

GTI07094 · December 11, 2020, 3:22pm

Peter

In the TranGLC_Key is where I have the formula to convert the string headnum to an integer for the record.

littlesistersba · December 11, 2020, 6:35pm

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?

GTI07094 · December 11, 2020, 6:38pm

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)

Rob_Schey · December 16, 2020, 1:57am

Take a look at [KB0039633 - Efficient Joining Between the TranGLC and PartTran Tables] on EpicCare. Has a lot of useful info

Rob_Schey · December 16, 2020, 2:05am

Also take a look at KB0037268 FAQ Linking the TranGLC table to the Original Transaction Tables and the GLJrnDtl table

littlesistersba · December 17, 2020, 5:20pm

Thanks John - Got it! appreciate your help

littlesistersba · December 18, 2020, 1:46pm

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

GTI07094 · January 29, 2021, 2:11pm

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

littlesistersba · February 3, 2021, 7:27pm

I’m all set now - thanks for checking back