How Do I Control What Data a BAQ Lists With?

Kinetic ERP
1

Hello again!

I know this one is probably pretty straightforward for a lot of you, but I can’t seem to figure out how to control the way my BAQs list rows. I’m trying to create a simple BAQ that takes each release for each line on each PO and prints the GL Account associated. I know I can use a combo of PORel and TranGLC to achieve this, but when I test the BAQ it lists the same PO release over and over. (I am signed up for Epicor’s BAQ class, and that should help a lot with my understanding of BAQs.)

This is what my Query Builder looks like:

image

This is what it prints:

image

Thanks everyone! :slight_smile:

2

First question - what are your table relationships on the join between the 2 tables? I noticed you don’t have a criteria set on the TranGLC table either, you’ll want to set where RelatedToFile = PORel.

3

You are seeing that they are many TranGLC happening for the PO. Each transaction in TranGLC will appear for each PO/LINE/REL, which is why you are seeing multiple items.

Many way to reduce the number of entries, depends on what data elements are needed. If you just want a list of PO/LINE/REL and TranGLC combinations, you really only need the TranGLC table.

Use the TranGLC table

Select the fields, then check the select distinct.

Now that you have a consolidated list, and you need to get fields from other table you create another subquery and reference the subquery you just made.

This is good practice as subquery BAQs are common.

4

Currently it is just company. Hmm, that makes me think I might need another table to create a better relationship between the two? I tried creating a criteria of relatedtofile = PORel, but that didn’t seem to work

5

Wow this was very helpful knash! The distinct option in SubQuery options will be very useful for me going forward.

Thank you for the help, I was able to easily create the BAQ with this.