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?
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
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.
Did you manage to come up with a report that can show this?
I am also looking for something similar.
I want to find any demands for warehouse without an approved part.
I tired the below be it brings back revision that are both approved and non approved, where i am only interested in demands for warehouse that have no approved part.
SELECT dbo.PartRev.PartNum, dbo.PartQty.DemandQty, dbo.PartRev.Approved, MAX(dbo.PartRev.RevisionNum) AS RevNum
FROM dbo.PartPlant AS OUTTER INNER JOIN
dbo.PartQty ON OUTTER.Company = dbo.PartQty.Company AND OUTTER.PartNum = dbo.PartQty.PartNum INNER JOIN
dbo.PartRev ON OUTTER.Company = dbo.PartRev.Company AND OUTTER.PartNum = dbo.PartRev.PartNum
GROUP BY dbo.PartQty.DemandQty, dbo.PartRev.Approved, dbo.PartRev.PartNum
HAVING (dbo.PartQty.DemandQty > 0)
@Lee_Myers I ended up looking at open jobs to figure this out. The jobs have the revs, so that simplified the problem.
We have 2 jobs that are “linked”, 1 job is the part and rev we sell customers(tested part) and the 2nd job is to build the untested product. I created a BAQ with 2 copies of JobHead(one for tested parts and one for untested parts) we then return any open jobs where the revisions for the 2 jobs don’t match. In other words we are building one revision of a product and telling the customer we’ll ship a different revision…
Based on what you describe I’d create a subquery to create a calculated field that counts the number of approved revs, then at the top level you could filter on parts that have 0 approved revs.