Solved

BAQ - Join Problem

  • 22 July 2021
  • 3 replies
  • 415 views

Hi.  We are putting into place a procedure to monitor our tooling work - this will include linking to Sales Orders received from the Customers and Purchase Orders placed with Suppliers.  We are using UD01 as the ‘Header’ table for the information.  I am creating a dashboard for this to be monitored and have a summary tab that groups everything together with total ordered, invoiced, etc.

My problem is that I also want a detail tab but as we could have multiple Sales Order Lines and / or Purchase Order lines for one lot of work and all my attempts have resulted in the SO rows and the PO rows being linked together (see pic).  The problem with this is that we want to use grouping and summarizing and it is obviously then giving false results.  I feel like I should know how to do this but I am going round in circles… can anyone help?

 

 

icon

Best answer by tracy.smith 22 July 2021, 14:00

View original

3 replies

Userlevel 3

One option is to create two queries and then join the result sets together using a union. 

Query 1 (lets say it’s for Sales Orders) will have a filter on the BAQ along the lines of OrderNum is not NULL 

Query 2 has the is not NULL against the PONum field.

The UNION will create one set of results as shown in your ‘Results Required’ screenshot. You can also apply filters and sorts to the final result set so that all Orders and PO’s for 10001-1 and together.

If you’re unsure about how to create the Union, I have some instructions and can dig them out. 

OK, I have never done a UNION query and I was working with InnerSubQuery’s.  I will look into this and come back if I am still struggling.  Thank you :)  Tracy

Think I’ve cracked it!  Thank you for the pointer in the right direction :grinning:   Tracy

Reply