RB Join Problem

Calvin,

I appreciate your offer to help me with this problem. I did the changes you
recommended and none seemed to help. We don't have multiple revisions of
purchased parts. This data was imported to Vantage a year ago from Macola
(done by Epicor) and so all started out with no revs or only 1 ID'd as
'converted'. Since we started on Vantage we are now adding revs and keeping
multiple revs for the mfg'd parts.

There was one suggestion to set a filter for approved to 'yes' in the
PartRev, which I tried and did not help. Besides, we would like to keep all
revs approved as we sometimes go back and build the same part again for same
customer or for repairs.

I am wondering if my problem may be with the master table. Should I have
started with the PartMtl or PartRev?

If it would be of any help, I would be happy to fax a copy of the report
printout with the problem circled.

Attached please find the library IOS.PRL

Thanks again for the advice.


John Himebaugh
MIS
InterOcean Systems, Inc.
3540 Aero Court
San Diego, CA 92123
858.565.8400
johnh@...




-----Original Message-----
From: calvin [mailto:calvin@...]
Sent: Monday, October 16, 2000 5:04 AM
To: vantage@egroups.com
Subject: Re: [Vantage] RB Join Problem

John -

Just a preliminary review:

Your links between Part and PartRev is an INNER JOIN -
do all of your purchased parts have a revision? If not you
may want to use an OUTER JOIN and include the data
from the right side of the join.

Without having tested your report, I also saw that
your link between PartMtl and ios_Part is an INNER JOIN -
Since this is a "many to one" relationship you may want to
try a RIGHT OUTER JOIN. Likewise, why do you have the
Company field linked twice (once from Part to PartRev and then
PartRev to ios_PartRev)?

Calvin

ps: If this doesn't work, you can email me a copy of the report
library file with the report.
Remember - don't send via egroups - please send direct.


----- Original Message -----
From: John <johnh@...>
To: <Vantage@egroups.com>
Sent: Thursday, October 12, 2000 11:57 AM
Subject: [Vantage] RB Join Problem


> We have customers (mostly government) who require a parts list that has to
> be included in the manual and it has to have a specific layout. I have
> created a RB report that works great except for one problem, which I can't
> figure out.
>
> The report filters for the most recent revision of the upper level part
and
> then pulls all the components (both manufactured and purchased) for the
> part. However, here is the problem, if a subassembly has multiple
> revisions it pulls that one part in once for each revision. It doesn't
> duplicate purchased parts, only those manufactured parts with more than
one
> revision. My databases and joins are as follow:
>
> Master Table: Part
> Related Tables:
> PartMtl
> PartRev
> Part
> (Alias: ios_Part)
> PartRev
> (Alias: IOS_PartRev)
> Table Joins:
> DCDV7.Part to DCDV7.PartMtl
> INNER JOIN
> DCDV7.Part.Company -> DCDV7.PartMtl.Company
> DCDV7.Part.PartNum -> DCDV7.PartMtl.PartNum
> DCDV7.Part to DCDV7.PartRev
> INNER JOIN
> DCDV7.Part.Company -> DCDV7.PartRev.Company
> DCDV7.PartMtl.PartNum -> DCDV7.PartRev.PartNum
> DCDV7.PartMtl.RevisionNum -> DCDV7.PartRev.RevisionNum
> DCDV7.PartMtl to DCDV7.ios_Part
> INNER JOIN
> DCDV7.PartMtl.Company -> DCDV7.ios_Part.Company
> DCDV7.PartMtl.MtlPartNum -> DCDV7.ios_Part.PartNum
> DCDV7.Part to DCDV7.IOS_PartRev
> LEFT OUTER JOIN
> DCDV7.Part.Company -> DCDV7.IOS_PartRev.Company
> DCDV7.PartMtl.MtlPartNum -> DCDV7.IOS_PartRev.PartNum
> DCDV7.PartRev.Company -> DCDV7.IOS_PartRev.Company
>
> FILTER INFORMATION
> Include all records where Part.PartNum is equal to '45000661001'
and
> PartRev.RevisionNum is equal to PartRev.AggMaxPartRev (this is a
> pre-pass aggregated field)
>
> If anyone has any suggestions, I would really appreciate the help.
>
> John Himebaugh
> MIS
> InterOcean Systems, Inc.
> 3540 Aero Court
> San Diego, CA 92123
> 858.565.8400
> johnh@...
>
>
>
>
> We no longer allow attachments to files. To access/share Report Files,
please go to the following link: http://www.egroups.com/files/vantage/
> (Note: If this link does not work for you the first time you try it, go
to www.egroups.com, login and be sure to save your password, choose My
Groups, choose Vantage, then choose Files. If you save the password, the
link above will work the next time you try it.)



We no longer allow attachments to files. To access/share Report Files,
please go to the following link: http://www.egroups.com/files/vantage/
(Note: If this link does not work for you the first time you try it, go to
www.egroups.com, login and be sure to save your password, choose My Groups,
choose Vantage, then choose Files. If you save the password, the link above
will work the next time you try it.)


[Non-text portions of this message have been removed]
We have customers (mostly government) who require a parts list that has to
be included in the manual and it has to have a specific layout. I have
created a RB report that works great except for one problem, which I can't
figure out.

The report filters for the most recent revision of the upper level part and
then pulls all the components (both manufactured and purchased) for the
part. However, here is the problem, if a subassembly has multiple
revisions it pulls that one part in once for each revision. It doesn't
duplicate purchased parts, only those manufactured parts with more than one
revision. My databases and joins are as follow:

Master Table: Part
Related Tables:
PartMtl
PartRev
Part
(Alias: ios_Part)
PartRev
(Alias: IOS_PartRev)
Table Joins:
DCDV7.Part to DCDV7.PartMtl
INNER JOIN
DCDV7.Part.Company -> DCDV7.PartMtl.Company
DCDV7.Part.PartNum -> DCDV7.PartMtl.PartNum
DCDV7.Part to DCDV7.PartRev
INNER JOIN
DCDV7.Part.Company -> DCDV7.PartRev.Company
DCDV7.PartMtl.PartNum -> DCDV7.PartRev.PartNum
DCDV7.PartMtl.RevisionNum -> DCDV7.PartRev.RevisionNum
DCDV7.PartMtl to DCDV7.ios_Part
INNER JOIN
DCDV7.PartMtl.Company -> DCDV7.ios_Part.Company
DCDV7.PartMtl.MtlPartNum -> DCDV7.ios_Part.PartNum
DCDV7.Part to DCDV7.IOS_PartRev
LEFT OUTER JOIN
DCDV7.Part.Company -> DCDV7.IOS_PartRev.Company
DCDV7.PartMtl.MtlPartNum -> DCDV7.IOS_PartRev.PartNum
DCDV7.PartRev.Company -> DCDV7.IOS_PartRev.Company

FILTER INFORMATION
Include all records where Part.PartNum is equal to '45000661001' and
PartRev.RevisionNum is equal to PartRev.AggMaxPartRev (this is a
pre-pass aggregated field)

If anyone has any suggestions, I would really appreciate the help.

John Himebaugh
MIS
InterOcean Systems, Inc.
3540 Aero Court
San Diego, CA 92123
858.565.8400
johnh@...
If you mark old revisions as unapproved you could then filter for only
approved revisions.

Patrick J. Winter
Information Services Manager
sSc Specialty Screw Corporation
Vantage 3.00.614, Progress 8.3A

-----Original Message-----
From: John [mailto:johnh@...]
Sent: Thursday, October 12, 2000 11:57 AM
To: Vantage@egroups.com
Subject: [Vantage] RB Join Problem


We have customers (mostly government) who require a parts list that has to
be included in the manual and it has to have a specific layout. I have
created a RB report that works great except for one problem, which I can't
figure out.

The report filters for the most recent revision of the upper level part and
then pulls all the components (both manufactured and purchased) for the
part. However, here is the problem, if a subassembly has multiple
revisions it pulls that one part in once for each revision. It doesn't
duplicate purchased parts, only those manufactured parts with more than one
revision. My databases and joins are as follow:

Master Table: Part
Related Tables:
PartMtl
PartRev
Part
(Alias: ios_Part)
PartRev
(Alias: IOS_PartRev)
Table Joins:
DCDV7.Part to DCDV7.PartMtl
INNER JOIN
DCDV7.Part.Company -> DCDV7.PartMtl.Company
DCDV7.Part.PartNum -> DCDV7.PartMtl.PartNum
DCDV7.Part to DCDV7.PartRev
INNER JOIN
DCDV7.Part.Company -> DCDV7.PartRev.Company
DCDV7.PartMtl.PartNum -> DCDV7.PartRev.PartNum
DCDV7.PartMtl.RevisionNum -> DCDV7.PartRev.RevisionNum
DCDV7.PartMtl to DCDV7.ios_Part
INNER JOIN
DCDV7.PartMtl.Company -> DCDV7.ios_Part.Company
DCDV7.PartMtl.MtlPartNum -> DCDV7.ios_Part.PartNum
DCDV7.Part to DCDV7.IOS_PartRev
LEFT OUTER JOIN
DCDV7.Part.Company -> DCDV7.IOS_PartRev.Company
DCDV7.PartMtl.MtlPartNum -> DCDV7.IOS_PartRev.PartNum
DCDV7.PartRev.Company -> DCDV7.IOS_PartRev.Company

FILTER INFORMATION
Include all records where Part.PartNum is equal to '45000661001' and
PartRev.RevisionNum is equal to PartRev.AggMaxPartRev (this is a
pre-pass aggregated field)

If anyone has any suggestions, I would really appreciate the help.

John Himebaugh
MIS
InterOcean Systems, Inc.
3540 Aero Court
San Diego, CA 92123
858.565.8400
johnh@...




We no longer allow attachments to files. To access/share Report Files,
please go to the following link: http://www.egroups.com/files/vantage/
(Note: If this link does not work for you the first time you try it, go to
www.egroups.com, login and be sure to save your password, choose My Groups,
choose Vantage, then choose Files. If you save the password, the link above
will work the next time you try it.)
John -

Just a preliminary review:

Your links between Part and PartRev is an INNER JOIN -
do all of your purchased parts have a revision? If not you
may want to use an OUTER JOIN and include the data
from the right side of the join.

Without having tested your report, I also saw that
your link between PartMtl and ios_Part is an INNER JOIN -
Since this is a "many to one" relationship you may want to
try a RIGHT OUTER JOIN. Likewise, why do you have the
Company field linked twice (once from Part to PartRev and then
PartRev to ios_PartRev)?

Calvin

ps: If this doesn't work, you can email me a copy of the report
library file with the report.
Remember - don't send via egroups - please send direct.


----- Original Message -----
From: John <johnh@...>
To: <Vantage@egroups.com>
Sent: Thursday, October 12, 2000 11:57 AM
Subject: [Vantage] RB Join Problem


> We have customers (mostly government) who require a parts list that has to
> be included in the manual and it has to have a specific layout. I have
> created a RB report that works great except for one problem, which I can't
> figure out.
>
> The report filters for the most recent revision of the upper level part
and
> then pulls all the components (both manufactured and purchased) for the
> part. However, here is the problem, if a subassembly has multiple
> revisions it pulls that one part in once for each revision. It doesn't
> duplicate purchased parts, only those manufactured parts with more than
one
> revision. My databases and joins are as follow:
>
> Master Table: Part
> Related Tables:
> PartMtl
> PartRev
> Part
> (Alias: ios_Part)
> PartRev
> (Alias: IOS_PartRev)
> Table Joins:
> DCDV7.Part to DCDV7.PartMtl
> INNER JOIN
> DCDV7.Part.Company -> DCDV7.PartMtl.Company
> DCDV7.Part.PartNum -> DCDV7.PartMtl.PartNum
> DCDV7.Part to DCDV7.PartRev
> INNER JOIN
> DCDV7.Part.Company -> DCDV7.PartRev.Company
> DCDV7.PartMtl.PartNum -> DCDV7.PartRev.PartNum
> DCDV7.PartMtl.RevisionNum -> DCDV7.PartRev.RevisionNum
> DCDV7.PartMtl to DCDV7.ios_Part
> INNER JOIN
> DCDV7.PartMtl.Company -> DCDV7.ios_Part.Company
> DCDV7.PartMtl.MtlPartNum -> DCDV7.ios_Part.PartNum
> DCDV7.Part to DCDV7.IOS_PartRev
> LEFT OUTER JOIN
> DCDV7.Part.Company -> DCDV7.IOS_PartRev.Company
> DCDV7.PartMtl.MtlPartNum -> DCDV7.IOS_PartRev.PartNum
> DCDV7.PartRev.Company -> DCDV7.IOS_PartRev.Company
>
> FILTER INFORMATION
> Include all records where Part.PartNum is equal to '45000661001'
and
> PartRev.RevisionNum is equal to PartRev.AggMaxPartRev (this is a
> pre-pass aggregated field)
>
> If anyone has any suggestions, I would really appreciate the help.
>
> John Himebaugh
> MIS
> InterOcean Systems, Inc.
> 3540 Aero Court
> San Diego, CA 92123
> 858.565.8400
> johnh@...
>
>
>
>
> We no longer allow attachments to files. To access/share Report Files,
please go to the following link: http://www.egroups.com/files/vantage/
> (Note: If this link does not work for you the first time you try it, go
to www.egroups.com, login and be sure to save your password, choose My
Groups, choose Vantage, then choose Files. If you save the password, the
link above will work the next time you try it.)