BAQ question on finding child parts with no approved revs


(Eddie Dawydiuk) #1

We use different part numbers to capture if a part has been tested or not. That is we have “untested” and “tested” part numbers in our system where a “tested” part is what we ship and in the BOM it requires an “untested” part. Also, there is a one to one relationship between the rev of a “tested” rev and “untested” rev. As an example if we sell part number “tested-xyz” and it has revs a, b, and c then in the system we should have “untested-xyz” with revs a, b, and c.

So here is the challenge I’m a bit unsure how to solve. We need to ensure if an “untested” rev is unapproved the “tested” rev is also approved. Otherwise we create sales orders for a rev we don’t have or can’t ship but don’t find out until the order hits the production floor…

So the question / challenge is how can I go about this. My first thought is I need to find all approved part revs that are a “tested” part, then run a where used to find all child “untested” parts and display. I don’t know how to run a where used in a BAQ though. Is this possible? Maybe there is another way to solve this problem, any thoughts?

(Ken Nash) #2

Is this SQLServer? How are the parts tied? by the words tested/untested? I understand you can see the link, but is there a field that joins the parts?

if there is a standard you could create a join to do the compare the two part lists (one subquery of part that start with tested and another with a list of part starting with untested)

testest-xyz revA approved untested-xyz revA unapproved and a calculated field case when tested-xyz is approved and untested-xyz is unapproved THEN ‘GOOD’ ELSE ‘BAD’ end AS CalculatedISPartGood

(Eddie Dawydiuk) #3

We use a standard naming convention, tested parts always start with the same three digits and untested parts always start with a different three digit code, so in the BAQ I can find them by looking at the partNum and begins with. We’re currently using 9.05.605 with a Progress DB.

I ended up using PartRev, PartMtl to show all approved “tested” part nums/revs/approved, as well as all "untested partnums/revs/approved. This gave me a link between “tested” partnums and “untested” partnums. I then dumped the results to Excel and looked for all rows where “tested” rev == “untested” rev, then I looked for situations where the “untested” rev was unapproved and it worked!

I’m currently thinking about how I can automate this for the future, so I can send out a monthly email notifying us of situations where we have a “tested” rev approved but the corresponding “untested” rev is unapproved. I think I’ll use a script to call the BAQ from a the SOAP APIs(I can’t wait to switch to E10 and be able to use REST APIs, and SQL subqueries), then in PHP I’ll have to do what I did in Excel… Ugly but I think it will work.