BAQ for APTran and CheckHed

Epicor ERP 10
1

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

2

What Error are you getting?

3

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

4

Make sure you have the TranGLC set to RelatedToFile = CheckHed

image
image837×149 4.62 KB

image
image933×469 18.5 KB

5

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.

6

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

7

An example would be great.

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

image
image1099×142 5.37 KB

8

Epiuser.baq (42.6 KB)

Try this out

create a filter query (how I like to do it btw)

image
image1461×873 152 KB

Then create your report as such

image
image1471×829 153 KB

Relationship from TranGLC_Key to ERP.CheckHead

image
image1495×260 52.7 KB

Relationship from ERP.CheckHead to ERP.APTran

image
image1502×260 48.5 KB

Display any fields you want at that point - in my example I have the following

image
image1771×683 164 KB

Hope this helps

9

Peter

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

image
image1423×609 99.2 KB

image
image1712×616 124 KB

10

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?

11

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)

12

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

13

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

14

Thanks John - Got it! appreciate your help

15

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

16

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

17

I’m all set now - thanks for checking back