Im pretty sure this should be simple using a case when of some sort, but I cant seem to figure this one out.
Ive tried searching but didn’t know how to word it… here goes…
I want to check if all PartMtl records are FDA Approved., if they are FDAApproved for All PartMtl, then I want to mark their Parent PartNum as True. It is important that All associated PartMtl records must be true though for the Parent Part to be marked as true…
does this requirement make sense?
I was hoping a case when could be used to go through each PartMtl record but I cant seem to get it to work…
Since PartMtl is set to PartRev, would you want to mark the PartRev as FDA approved? You could have multiple Revs and not all of them have the PartMtl records FDA approved.
Are you trying to do this moving forward or are you trying to catch up older records?
For the former, the only place where PartMtl records are added is through Part/Rev Check-In. Here you could create two calculated fields: one that is always 1 and the other only 1 if the PartMtl record (or it’s PartRev record) is FDA approved. If the total is equal then you can mark the current PartRev as FDA approved.
To catch up on old PartRev records, you would run a BAQ by level code doing the highest ones first (the deepest BOM level) and calculate approval that way and then assign the FDA approval using DMT.
So I only needs to check against ParRev which equals BASE. I have no requirement for any other revision checks.
There are 2 new fields called Part.MaxFda and Part.FDAApproved. A user will complete these fields in advance of the new baq being run.
I require my baq to
Find all Compounds for BASE revision which ALL containing Raw Materials Part.MaxFDA <= (PartMtl.Qty Per *100).
If a Parent Part exists which has all of its containing part mtls FDAApproved I want to then set the Parents Part to FDAApproved.
Does that make sense?
I planned to setup an automated routine to dmt these approved parts in on a weekly basis via dmt. so each week the baq would be automatically run to find any compounds that meet the criteria above then dmt in the FDAapproved
To me, sounds like something you would want to run as a BPM every time there was an update to a partmtl record instead but I don’t have the entire context so maybe that wont work.
For your BAQ, sounds like you could do it a few ways but the first that comes to mind is to put the tables up there to return all rows from partrev. display partnum and group by. Do a calculated field with your test, return 1 if it passes and 0 if it does not, wrap it in a sum. So
sum(
case
when Part.MaxFDA <= (PartMtl.Qty Per *100) then 1
else 0
end
)
do another calculated field to count the rows… count( pick a field)
then one more calculated field to see if the value of these two calculated fields is the same… maybe you could get that all into one field but why bother.
do a subquery filter to return rows in which the third calculated field = true.
Make a Sub Query (call it something like FDACheck) that checks if FDA Approved = false and only shows the Parent Part and the FDA Approved field and Group By/Distinct. Then in the top level, do a left join to this sub query. In a calculated field on the top query use something like:
IsNull(FDACheck.FDA_Approved_c, true)
This will show the “false” value if it exists and “true” if it can’t find a “false”.
thankyou so much - this is the area I struggle with sometimes, you worded it perfectly and I will save this info for my future baqs too, thanks very much. really appreciated