BAQ to return Part and Latest Approved Revision (If one exists)?

Yahoo Archive
1
Are you doing this in E9? If so there is a Sort tab on the Display tab in the BAQ Designer.

Jim Kinneman
Encompass Solutions, Inc

--- In vantage@yahoogroups.com, "rwhalebelly" <richard.whalebelly@...> wrote:
>
> Did we ever get a solution to this?
>
> e.g using first or last doesn't mean much if we can't sort it or know the order it is in?
>
>
>
> --- In vantage@yahoogroups.com, "cubcrafters_it" <jason.navarrete@> wrote:
> >
> > Can a BAQ be written that returns a list of all the parts, with the latest approved revision if one exists?
> >
> > Seems like using the FIRST is the way to go, but I'd have to sort the join with the PartRev table, and I don't know how to do that...
> >
> > I also though about just starting w/ the PartRev table, but that excludes parts that don't have a rev at all...
> >
> > Ideas?
> >
>
2
Can a BAQ be written that returns a list of all the parts, with the latest approved revision if one exists?

Seems like using the FIRST is the way to go, but I'd have to sort the join with the PartRev table, and I don't know how to do that...

I also though about just starting w/ the PartRev table, but that excludes parts that don't have a rev at all...

Ideas?
3
for each Part no-lock , last PartRev no-lock outer-join where (Part.Company = PartRev.Company and Part.PartNum = PartRev.PartNum ) and PartRev.Approved = TRUE .

Drag Part
Drag PartRev

Table Relations = Outer Join
Table List:
Part = Each
PartRev = Last
Criteria
PartRev.Approved = True

Display:
Part.PartNum
PartRev.RevisionNum


Regards,

Miguel A. Santillan

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of cubcrafters_it
Sent: Monday, March 12, 2012 12:26 PM
To: vantage@yahoogroups.com
Subject: [Vantage] BAQ to return Part and Latest Approved Revision (If one exists)?



Can a BAQ be written that returns a list of all the parts, with the latest approved revision if one exists?

Seems like using the FIRST is the way to go, but I'd have to sort the join with the PartRev table, and I don't know how to do that...

I also though about just starting w/ the PartRev table, but that excludes parts that don't have a rev at all...

Ideas?



[Non-text portions of this message have been removed]
4
Thanks Miguel!

However, how can I ensure that the PartRev is sorted by approved date so that when the 'last' record is selected, we can ensure that we're getting the right record?

--- In vantage@yahoogroups.com, Miguel Santillan <msantillan@...> wrote:
>
> for each Part no-lock , last PartRev no-lock outer-join where (Part.Company = PartRev.Company and Part.PartNum = PartRev.PartNum ) and PartRev.Approved = TRUE .
>
> Drag Part
> Drag PartRev
>
> Table Relations = Outer Join
> Table List:
> Part = Each
> PartRev = Last
> Criteria
> PartRev.Approved = True
>
> Display:
> Part.PartNum
> PartRev.RevisionNum
>
>
> Regards,
>
> Miguel A. Santillan
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of cubcrafters_it
> Sent: Monday, March 12, 2012 12:26 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] BAQ to return Part and Latest Approved Revision (If one exists)?
>
>
>
> Can a BAQ be written that returns a list of all the parts, with the latest approved revision if one exists?
>
> Seems like using the FIRST is the way to go, but I'd have to sort the join with the PartRev table, and I don't know how to do that...
>
> I also though about just starting w/ the PartRev table, but that excludes parts that don't have a rev at all...
>
> Ideas?
>
>
>
> [Non-text portions of this message have been removed]
>
5
Anyone else have any thoughts? It seems like I've got cases where the latest approved revision is in the middle of the returned records from the PartRev table, so using LAST falls apart unless I can sort that table first.


--- In vantage@yahoogroups.com, "cubcrafters_it" <jason.navarrete@...> wrote:
>
> Thanks Miguel!
>
> However, how can I ensure that the PartRev is sorted by approved date so that when the 'last' record is selected, we can ensure that we're getting the right record?
>
> --- In vantage@yahoogroups.com, Miguel Santillan <msantillan@> wrote:
> >
> > for each Part no-lock , last PartRev no-lock outer-join where (Part.Company = PartRev.Company and Part.PartNum = PartRev.PartNum ) and PartRev.Approved = TRUE .
> >
> > Drag Part
> > Drag PartRev
> >
> > Table Relations = Outer Join
> > Table List:
> > Part = Each
> > PartRev = Last
> > Criteria
> > PartRev.Approved = True
> >
> > Display:
> > Part.PartNum
> > PartRev.RevisionNum
> >
> >
> > Regards,
> >
> > Miguel A. Santillan
> >
> > From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of cubcrafters_it
> > Sent: Monday, March 12, 2012 12:26 PM
> > To: vantage@yahoogroups.com
> > Subject: [Vantage] BAQ to return Part and Latest Approved Revision (If one exists)?
> >
> >
> >
> > Can a BAQ be written that returns a list of all the parts, with the latest approved revision if one exists?
> >
> > Seems like using the FIRST is the way to go, but I'd have to sort the join with the PartRev table, and I don't know how to do that...
> >
> > I also though about just starting w/ the PartRev table, but that excludes parts that don't have a rev at all...
> >
> > Ideas?
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
6
Perhaps if you are going to use a dashboard, change the PartRev = Each and then group Part Num in the dashboard and sort by date.

Or if this a just a query, PartRev=Each, dump to excel and Sort by Part Num, Date, Rev

Regards,

Miguel A. Santillan
ERP Administrator
Compass Components, Inc.
510-661-6666 Office
510-656-0603 Fax
msantillan@...<mailto:msantillan@...>
www.ccicms.com<http://www.ccicms.com/>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of cubcrafters_it
Sent: Wednesday, March 14, 2012 10:33 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: BAQ to return Part and Latest Approved Revision (If one exists)?



Anyone else have any thoughts? It seems like I've got cases where the latest approved revision is in the middle of the returned records from the PartRev table, so using LAST falls apart unless I can sort that table first.

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "cubcrafters_it" <jason.navarrete@...<mailto:jason.navarrete@...>> wrote:
>
> Thanks Miguel!
>
> However, how can I ensure that the PartRev is sorted by approved date so that when the 'last' record is selected, we can ensure that we're getting the right record?
>
> --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, Miguel Santillan <msantillan@> wrote:
> >
> > for each Part no-lock , last PartRev no-lock outer-join where (Part.Company = PartRev.Company and Part.PartNum = PartRev.PartNum ) and PartRev.Approved = TRUE .
> >
> > Drag Part
> > Drag PartRev
> >
> > Table Relations = Outer Join
> > Table List:
> > Part = Each
> > PartRev = Last
> > Criteria
> > PartRev.Approved = True
> >
> > Display:
> > Part.PartNum
> > PartRev.RevisionNum
> >
> >
> > Regards,
> >
> > Miguel A. Santillan
> >
> > From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of cubcrafters_it
> > Sent: Monday, March 12, 2012 12:26 PM
> > To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
> > Subject: [Vantage] BAQ to return Part and Latest Approved Revision (If one exists)?
> >
> >
> >
> > Can a BAQ be written that returns a list of all the parts, with the latest approved revision if one exists?
> >
> > Seems like using the FIRST is the way to go, but I'd have to sort the join with the PartRev table, and I don't know how to do that...
> >
> > I also though about just starting w/ the PartRev table, but that excludes parts that don't have a rev at all...
> >
> > Ideas?
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>



[Non-text portions of this message have been removed]
7
Miguel,



If I was coding this I would specify the table index "PartEffectiveDate"
and filter for "Approved" revisions but it doesn't appear that you can
specify which index to use in the BAQ Designer.



Patrick Winter



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of cubcrafters_it
Sent: Wednesday, March 14, 2012 12:33 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: BAQ to return Part and Latest Approved Revision
(If one exists)?





Anyone else have any thoughts? It seems like I've got cases where the
latest approved revision is in the middle of the returned records from
the PartRev table, so using LAST falls apart unless I can sort that
table first.

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"cubcrafters_it" <jason.navarrete@...> wrote:
>
> Thanks Miguel!
>
> However, how can I ensure that the PartRev is sorted by approved date
so that when the 'last' record is selected, we can ensure that we're
getting the right record?
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
Miguel Santillan <msantillan@> wrote:
> >
> > for each Part no-lock , last PartRev no-lock outer-join where
(Part.Company = PartRev.Company and Part.PartNum = PartRev.PartNum ) and
PartRev.Approved = TRUE .
> >
> > Drag Part
> > Drag PartRev
> >
> > Table Relations = Outer Join
> > Table List:
> > Part = Each
> > PartRev = Last
> > Criteria
> > PartRev.Approved = True
> >
> > Display:
> > Part.PartNum
> > PartRev.RevisionNum
> >
> >
> > Regards,
> >
> > Miguel A. Santillan
> >
> > From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of cubcrafters_it
> > Sent: Monday, March 12, 2012 12:26 PM
> > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > Subject: [Vantage] BAQ to return Part and Latest Approved Revision
(If one exists)?
> >
> >
> >
> > Can a BAQ be written that returns a list of all the parts, with the
latest approved revision if one exists?
> >
> > Seems like using the FIRST is the way to go, but I'd have to sort
the join with the PartRev table, and I don't know how to do that...
> >
> > I also though about just starting w/ the PartRev table, but that
excludes parts that don't have a rev at all...
> >
> > Ideas?
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>





[Non-text portions of this message have been removed]
8
Did we ever get a solution to this?

e.g using first or last doesn't mean much if we can't sort it or know the order it is in?



--- In vantage@yahoogroups.com, "cubcrafters_it" <jason.navarrete@...> wrote:
>
> Can a BAQ be written that returns a list of all the parts, with the latest approved revision if one exists?
>
> Seems like using the FIRST is the way to go, but I'd have to sort the join with the PartRev table, and I don't know how to do that...
>
> I also though about just starting w/ the PartRev table, but that excludes parts that don't have a rev at all...
>
> Ideas?
>