Ahhhh it was the first level not the second! Good catch
Nice
The other thing you need to be aware of is that the PartMtl table stores materials for each revision of a part. So if your organization maintains multiple revisions for a part you need to address what specific revision you are trying to find the SR part number in when querying the partmtl table. We would always use the most current revision, but this might not make sense for your organization.
It should be only looking for the latest revision. Should this approach be doing that already?
Nah itās not doing that already. You would have to join the part revision table onto the part table and each subsequent part mtl table to get the current rev.
We would always unapprove every rev except the current rev so when I joined the rev table I would put a criteria on there that said approved = 1 so that I would only bring the approved/current revision.
This is how it looks at the moment. I am putting a filter in the first partmtl to look at mtlpartnum that begin with PW or SR, then the second partmtl is looking at mtlpartnum that begin with SR. The SR values are either in the first level or second level depending on if the ordered part has a PW number or not. I am able to get both the PW values and the SR that correspond to it, but the SR values in the first level disappear
If I do not tell the 2nd partmtl to look for SR values the first partmtl finds all PW and SR values as it should
Yash, based on the way you have this query structured here is what it would return:
All order detail lines that have a part material in their BOM that starts with PW or SR AND ALSO have a part material inside of those PW or SR parts that starts with SR.
That is exactly what I need it to do. Expect, when the SR value shows up in the part material from the order detail, it should stop and not look for anything else. It seems as if it only finds the SR parts for the PW parts but doesnāt keep the first level SR parts
Yeah because the first level SR parts donāt have a material inside them that starts with SR, right? BUT the first level PW parts DO have a part inside their method that starts with SR, correct?
Correct
Yash, make the second two tables underneath the first part mtl table LEFT OUTER JOINS. This is the equivalent of saying, these tables/conditions donāt need to exist for my query. In other words, I donāt care if the PW or SR parts have an SR part inside of them, but if they do, show it.
1 Like
Still not getting all the values from the first level
I did right outer join and it worked!
Thanks for the help! @utaylor
Hi Everyone!
Iāve had good look with the Indented BOM BAQ outlined in the CTE Case Study. However, Iād like to do something that I assume is relatively simple add a field / column for āMaster Part Numberā to show the upper level part number that was entered.
Can anyone help?
Thanks Very Much!
A.
Make a calculated field that shows the parameter.
@yourParameter
1 Like
One way would be to include the field in your BAQ. There are many threads that will have this code. Here is one of them.