Hello,
I need some help crafting a BAQ that would determine what BOMs have been changed recently by date or date range. I am not sure how to begin.
Thank you!
In your BAQ bring in the table ECORev. Display the fields you need for relevance, and set the sort order to descending on the Check In Date column. This tells you the last time someone checked a part in.
select
[ECORev].[GroupID] as [ECORev_GroupID],
[ECORev].[PartNum] as [ECORev_PartNum],
[ECORev].[RevisionNum] as [ECORev_RevisionNum],
[ECORev].[CheckInDate] as [ECORev_CheckInDate],
[ECORev].[CheckedOut] as [ECORev_CheckedOut],
[ECORev].[CheckOutDate] as [ECORev_CheckOutDate],
[ECORev].[CheckedOutBy] as [ECORev_CheckedOutBy],
[ECORev].[ApprovedDate] as [ECORev_ApprovedDate],
[ECORev].[EffectiveDate] as [ECORev_EffectiveDate],
[ECORev].[RollupDate] as [ECORev_RollupDate]
from Erp.ECORev as ECORev
order by ECORev.CheckInDate Desc
2 Likes
Thank you that is exactly what my user was looking for!
1 Like
So my user came back and wants another…:
“…need for report on P parts w/o BOMs but there’s a catch because of distinction between those with revs (drawings) and others w/o (5-digit commodity items)”.
From what I can gather, he wants similar information such as when it was entered but for purchased parts.
Is this possible?
Something like this BAQ:
select
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[PartRev].[RevisionNum] as [PartRev_RevisionNum]
from Erp.Part as Part
left outer join Erp.PartRev as PartRev on
Part.Company = PartRev.Company
and Part.PartNum = PartRev.PartNum
where (Part.TypeCode = 'P')
and (PartRev.PartNum is null)
Basically, we are taking a look into the part master table. We need to figure out if any of those parts do not have revs (and thus no BOM). So, we link the Part Rev to the Part table, and only show records where there is no part rev. We also filter the parts by purchase parts only. This should get you close. You can’t really tell when someone edits a part without a rev. It is the revision (engineering workbench) that keeps track of who changed what on a rev.
Within the part table there is also the Part.CreatedOn datetime field that you can pull to report the date a part was created.
Hello,
This part I am having a bit of trouble with. Does that go into the Table Criteria or the SubQuery Criteria?
Typecode will go into the table criteria for Part. Then PartRev.PartNum is Null goes into the subquery criteria.
select
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[PartRev].[RevisionNum] as [PartRev_RevisionNum]
from Erp.Part as Part
inner join Erp.PartRev as PartRev on
Part.Company = PartRev.Company
and Part.PartNum = PartRev.PartNum
where (Part.TypeCode = 'P')
and (PartRev.PartNum is null)
So this is what I have and it returns blank. I am wondering if its the wording of the join? I apologize… I do not know how to change it to be left outer.
The join link is the little line that links the table in the Phrase Build tab. The line has an icon in the middle. By default, it is a grey diamond shape. Click that line or shape to edit the link. When you click the link, it should highlight and show in the bottom right corner a drop-down box to set the join type. Choose All Rows from Part. That should do the trick!
1 Like
Thank you! It worked!
Not done yet, user came back to me…
Is it possible to show only 5 digit part numbers?
They would also like to search by p/n or by date range (except some parts don’t have a created date??).
PartNum is a string field. You can limit the output using string functions like left() or right().
From the BAQ, you can search by part number by right clicking on the part column header and turning on Filters, then choose what you want to filter by. Same with the date fields.
I am sorry, I am asking so much of you, but it is very appreciated. I think this is the last bit. How would I go about doing this?
Instead of adding the part num fields directly to the display fields, create a calculated field that rips up that part number and only spits out the section you want. The expression should be as simple as:
right(Part.PartNum,5)
if you want the right-most 5 characters. Or use Left for the leftmost.
Your new calculated field type should be an nvarchar, not an int.
Are you trying to filter to only show 5 digit part numbers? Or only show 5 digits of the part number?
What @NateS is showing you is going to show you 5 characters of all part numbers.
You’ll need to to a count of the characters and then filter by that result.
1 Like
Yes, Great point Brandon!
