Help Needed - Show all open releases for ParentPart when searching by MaterialPart

Kinetic ERP
, ,
1

Hi All, I am a first-time poster in need of guidance please.

I have created a BAQ which shows all open releases for a given part number. This works fine. However, our company uses multi-level BOM’s. I need to amend the BAQ so that if a user searches a MaterialPart, the query will return not only releases for the MaterialPart, but also any releases for the ParentPart (and only the very top-level parent part not any of the sub-assemblies).

I have built a separate query which can give the top-level part for a given material part (using a CTE). My plan was to use the results of this query and somehow bring in all releases for those parts too (haven’t worked that bit out yet). But when I try to insert it into my initial query it fails because it contains a Union All which is different to the Union All in the first query.
Is it possible to have two different union all’s in the same BAQ?
Am I going about this the wrong way?

2

Hi Jonathan! Welcome to the madness!

In the newer Kinetic UI BAQ designer, you can use one BAQ inside another as if it was a different datasource.

Not sure if you’re using Classic or Kinetic, but this could be an opportunity to try that out.

3

Hi thanks, it is indeed madness. Right in the deep end here…

I did wonder if this was possible but couldn’t see how to do it

4

Did you find it? If not… in the BAQ Studio… use the upper right-hand 3-dot menu and choose “Query Reference”.

image

Click the “New” (plus) icon and search for the BAQ you want to add into the main query. Once added, it will now show up if click on the (Sub)Queries tab in the upper right:

image

You can double click on it there and it will be added the query currently open on your designer workspace.

Connect the reference query to your desired table, adjust the join, etc.

image
image700×269 10.2 KB

You can then add columns from that joined query to show up in your result dataset:

image
image1116×480 26.2 KB

5

You should be able to add ( ) around the sub queries to group them appropriately.

image

Does that not work?

1 Like
6

This is awesome and I think will be invaluable. Thank you for your time

7

This is also great and would solve my problem. So, if I understand you correctly, I can separate out one Union all from another so there is no conflict?

8

That’s correct.