Hello,
My first goal is to write a BAQ and find all parts that we have not made on jobs in the last 3 years. Parts that we have not made in 3 years, I would like to make inactive.
My broad strokes plan is to look at all jobs for the last 3 years and get the top level part number (BAQ Subquery List 1, Job4PN). With the top level part number, I then want to go down the BOM and find all part numbers inside the BOM, including parent parts of assemblies and subcomponents. I achieve this goal with BAQ Subquery List 2 - 4 which is a CTE, Union All, and Top Level. The Top Level is called MtlPartNum and it is all the part numbers that we have been making on jobs for the last 3 years. I have confirmed that this is working as I expect. My final step is the Except statement in the BAQ. I want to look at all the active part numbers and remove from the list all the part numbers that we are making (results inside MtlPartNum).
I have an Except statement working but I have the Except in the wrong query order.
I want to achieve this:
It should leave me a list of all active part numbers that we have not made in the last 3 years. I will then make these parts inactive.
I want the AllPN to be the first query and except to the MtlPartNum as the second query. I have never used the ( ) inside the Subquery List and I think that might be the answer, but I have not been able to get the ( ) to work without errors.
Can you please make suggestions for solving my problem?
Heide
I think you are going to need one more level if I’m understanding you correctly. I would make TopLevel be a CTE (also)
Put Parenthesis between 2 and 3
I will try to clarify my goal. We are a make direct company. Our jobs call for a top level part number. That assembly calls for subassemblies and material parts.
Since the assembly (example 150268) gets rolled into the job, I can’t open Part Transaction History Tracker for 150268 and see a transaction type for STK-MTL. That is why I have to open past jobs and bring down the BOM.
My assumption is that if I can find all the parts that we have made and all the corresponding bill of materials then this will tell me the parts we used in the past 3 years. I plan to take a list of all the active part numbers and then exclude from that list the parts that are recently run. This remaining list will be the parts that we have not used in a long time.
I want to clean up our database. We keep adding parts and never inactivating older, unused parts. If my query is correct, then I will have about 29,000 part number to inactivate. I am concerned about the risks in activating all these parts.
Can you think of flaws with my plan and areas of improvement? Would you go about making parts inactive in a similar way? BOM_Jobs.baq (63.8 KB)
You mentioned 29,000 parts, if you have 30,000 total parts, I would say that would be a bad idea. It is hard for anyone on the internet to understand your part data, let alone how they are used. Managing the Parts Lifecycle can be a full time job.
It’s funny when I read this the first time, I thought you were being funny like “you have 29K I wouldn’t worry about it” but if you had “30K” then you REALLy need to do this
I don’t that was your intent but I thought it was funny (on accident)
