BAQ threeway

Epicor ERP 10
1

So I have an admittedly odd join, but I need to join these tables so that I get a row with any of the three tables have something n it. As shown below. The problem that I am having is that it looks like it looks for a join in table1 to table2, and if there isn’t join there, doesn’t do table 1 to table 3. It treats them like they don’t match I can change the behavior by changing the order of the tables, but it will always leave off the last table and just make a new row…

image
image.png877×785 46 KB

These two rows should be 1 row.

image
image.png1771×679 66.6 KB

Is there a better way to make a join like this? Should I put this in the suqbqery criteria instead? Or do I need yet another level to make this work? (this query already has 9 subs plus the top level, lol)

1 Like
2

I think you need another level(s) - either a sub to join two of them first, or make all three tables into a UNION subquery to the parent…

3

Union won’t put them onto the same row. I need them in separate columns but on the same row.

4

Join the first 2 tables in a subquery, then join the subquery to the top table.

3 Likes
5

I ended up going a little different direction and instead of doing the joins that way (in order to get that calculated field at the beginning with the job information in one column) I ended up using the XML path method. I can adjust what I am doing in my Bartender report to work with page breaks and start of group headers to make this work.

1 Like