We are trying to identify parts that have only TranClass “A” or TranClass “X” transactions in their Part Tran table. Here is an example of one part like that:
Everything I’ve tried still pulls in parts that have other TranClass types in the transaction history. Any suggestions on how to get a list of parts that only have TranClass “A” or “X” in the PartTran table?
Did you Join the PartTran Table and add a filter to the table? What does your BAQ look like?
I would expect this should work for you:
Also I mean it should work in as a starter, you’d likely want a subquery that is grouped by partnum and returns company and partnum. At least that is likely where I would start and see what kind of performance is returned.
Maybe I’m misreading it but I think he’s looking for parts that only have TranClass A/X and no other classes?
If so, two PartTran subqueries grouped by company and partnum…
(1) [TranClass=A or TranClass=X] for inclusion
(2) [TranClass<>A and TranClass<>X] for exclusion…activity OUTSIDE of A/X
Link Part table and display records where it exists in subquery 1 and does NOT exist in subquery 2.
Hi John,
Yes, that is exactly what I am looking to do. Thank you for your suggestion. I had to move on to another task but will give this a try tomorrow. Thank you all for your help!
I revised the BAQ as suggested but zero records are returned. I know there are parts with TranClass A or X transactions in the PartTran table that don’t have any of the other TranClass types in them. If I use only one subquery criteria of EXISTS in TranClass=A or X I get results, but when I add the second subquery criteria TranClass<>A and X, I get zero results. Here is my query phrase:
select
[Part].[Company] as [Part_Company],
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription]
from Erp.Part as Part
inner join (select
[PartTran].[Company] as [PartTran_Company],
[PartTran].[PartNum] as [PartTran_PartNum]
from Erp.PartTran as PartTran
where (not PartTran.PartNum = ‘WD’ and (PartTran.TranClass = ‘A’ or PartTran.TranClass = ‘X’ ))
group by [PartTran].[Company],
[PartTran].[PartNum]) as TranClassAorX on
Part.Company = TranClassAorX.PartTran_Company
and Part.PartNum = TranClassAorX.PartTran_PartNum
inner join (select
[PartTran1].[Company] as [PartTran1_Company],
[PartTran1].[PartNum] as [PartTran1_PartNum]
from Erp.PartTran as PartTran1
where (not PartTran1.PartNum like ‘WD%’ and (PartTran1.TranClass <> ‘A’ and PartTran1.TranClass <> ‘X’ ))
group by [PartTran1].[Company],
[PartTran1].[PartNum]) as NotTranClassAorX on
Part.Company = NotTranClassAorX.PartTran1_Company
and Part.PartNum = NotTranClassAorX.PartTran1_PartNum
where (Part.InActive = False)
and (exists (select
[PartTran].[Company] as [PartTran_Company],
[PartTran].[PartNum] as [PartTran_PartNum]
from Erp.PartTran as PartTran
where (not PartTran.PartNum = ‘WD’ and (PartTran.TranClass = ‘A’ or PartTran.TranClass = ‘X’ ))
group by [PartTran].[Company],
[PartTran].[PartNum]) and not exists (select
[PartTran1].[Company] as [PartTran1_Company],
[PartTran1].[PartNum] as [PartTran1_PartNum]
from Erp.PartTran as PartTran1
where (not PartTran1.PartNum like ‘WD%’ and (PartTran1.TranClass <> ‘A’ and PartTran1.TranClass <> ‘X’ ))
group by [PartTran1].[Company],
[PartTran1].[PartNum]))
order by Part.PartNum
Are both subqueries pointing back to Part? Can you drop a screenshot of your BAQ diagram?
Both subqueries are joined by Company & PartNum. Both subqueries only have the Company and PartNum fields as Display Fields and both are flagged Group By.
Try this one - I’m getting output on our side
BAQForExport_PartTran_AXOnly.baq (40.1 KB)
Hi John,
The BAQ you whipped up appears to be working like a charm! I see that instead of putting table criteria on the subqueries you used SubQuery Criteria. And you used a calculated field to filter it all for the result I was looking for. Awesome! Thank you again for all your help on this! So many years doing this and I still learn new things everyday.
No problem, glad to help…took it as a challenge to try and code what I was visualizing. I’m only about 16 months in (live) so every day is a learn-something-new kinda thing too. I tried to keep it as basic as I could…no criteria outside of the TranClass values. You can always add date dependencies etc.
