BOM and ODBC (Vantage 6.1)

Yahoo Archive
1
> Then add the tables you want (PartBom, PartBoo, etc)


Steve,

In 6.1, PartBOM and PartBOO are temp tables, they only contain data when
a BOM report is generated in Vantage. They are empty all other times.
The tables needed to generate a complete BOM & router are PartRev,
PartOpr and PartMtl.

As Bill noted, it's easy to pull same-level materials and operations,
but looping through to find all the children can be problematic. Things
to watch for: is the Part type P or M? Is Pull-As-Assembly Yes or No?

These two fields in particular should signal your loop to continue or
stop. But if your BOMs are constructed incorrectly, you could end up
with an inaccurate query.

Additionally, we've experienced "discontinuities" between QtyPer in
PartMtl vs QtyPer on the Summarized BOM report. I have a tech request
on this problem in to Epicor with no response so far.

have fun,
john


-----Original Message-----
From: Ashton, Steve <Steve.Ashton2@...>
Reply-To: vantage@yahoogroups.com
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BOM and ODBC (Vantage 6.1)
Date: Mon, 27 Aug 2007 11:36:41 -0400

In Excel, go to Data > Import Data > New Database Query and select your
data source. Then add the tables you want (PartBom, PartBoo, etc) and
click Close. Microsoft Query will open but will be minimized. Right
click on it and restore it. Here you can add more tables, change links,
add filters (criteria) and preview the data. When you've got what you
want you can return it to Excel. I suggest you get a book on Excel and
familiarize yourself with all the features.



There are some short-comings though: Only two tables can be linked when
left outer joins are required. (The work-around is to add more
worksheets to the workbook, each with its own embedded query, and use
vlookups to collect the data in the format you need it.) And to change
the filters you must open the embedded query, which puts the whole query
at risk of being compromised.



Both of the above issues are negated when I create a Crystal Report and
publish it with EasyView (http://www.easystreetsoftware.net). Moreover,
Crystal's parameter screens make data selections easy, and Easyview's
export functions are easy to learn.



Steve Ashton
2
Is there a way to pull a BOM directly into Excel using ODBC.
We have been using report builder exports and the preview text import
method for quite a while, but it would be much easier
If there was a way to pull a BOM for a part directly into Excel using
ODBC.

Any idea's or help is appreciated.

Jim Moore


------------------------------------------------------------------------
------------------------------
"This email message is for the sole use of the intended
recipient(s) and may contain proprietary and/or confidential
information for which such intended recipients(s) may have
an obligation to treat as proprietary. Any unauthorized review,
use, disclosure, or distribution is strictly prohibited. If you are
not the intended recipient, please contact sender by reply
email and destroy all copies of this e-mail."
------------------------------------------------------------------------
-----




[Non-text portions of this message have been removed]
3
Jim,



We regularly pull most Vantage files into Access with the ODBC connection.

In fact every Vantage file can be pulled in or linked for reporting.



In some cases we have actually linked to Vantage tables like Pub_Part and
made changes in Access which would have taken hours in Vantage! However you
need to be careful of certain indexes in these files



>

Mike McCullough

CST, Inc.

888-262-4153 x232



_____

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Moore, Jim (Anniston)
Sent: Monday, August 27, 2007 9:31 AM
To: vantage@yahoogroups.com
Subject: [Vantage] BOM and ODBC (Vantage 6.1)



Is there a way to pull a BOM directly into Excel using ODBC.
We have been using report builder exports and the preview text import
method for quite a while, but it would be much easier
If there was a way to pull a BOM for a part directly into Excel using
ODBC.

Any idea's or help is appreciated.

Jim Moore

----------------------------------------------------------
------------------------------
"This email message is for the sole use of the intended
recipient(s) and may contain proprietary and/or confidential
information for which such intended recipients(s) may have
an obligation to treat as proprietary. Any unauthorized review,
use, disclosure, or distribution is strictly prohibited. If you are
not the intended recipient, please contact sender by reply
email and destroy all copies of this e-mail."
----------------------------------------------------------
-----

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





[Non-text portions of this message have been removed]
4
Thanks Mike,

Yes, I'm aware that you can link to tables using ODBC and have done
that, but what I'm having trouble understanding how to do is; how do you
pull into Excel all the supporting parts along with the op, description
and required quantities.

Something that would look like this in Excel.



OP

Part Number

Part Description

QtyPer

12345

Widget (Top Level


10

12345(a)

wire

2

20

12345(b)

washer

2

30

12345(c)

rivet

2

ect.





I've looked at the RB report that does this and it groups by MtlSeq.

The toplevel part has a P.PartNum listing for the field and those below
it M2.MtlPartNum - M8.MtlPartNum or however deep you need to go.

Same for the OP, M2MtlSeq - M8.MtlSeq.



Sorry for not being more clear in my original post.



Thanks.



From: Mike McCullough [mailto:mike@...]
Sent: Monday, August 27, 2007 9:39 AM
To: vantage@yahoogroups.com
Cc: Moore, Jim (Anniston)
Subject: Bom and ODBC (Vantage 6.1)



Jim,



We regularly pull most Vantage files into Access with the ODBC
connection.

In fact every Vantage file can be pulled in or linked for reporting.



In some cases we have actually linked to Vantage tables like Pub_Part
and made changes in Access which would have taken hours in Vantage!
However you need to be careful of certain indexes in these files



>

Mike McCullough

CST, Inc.

888-262-4153 x232



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Moore, Jim (Anniston)
Sent: Monday, August 27, 2007 9:31 AM
To: vantage@yahoogroups.com
Subject: [Vantage] BOM and ODBC (Vantage 6.1)



Is there a way to pull a BOM directly into Excel using ODBC.
We have been using report builder exports and the preview text import
method for quite a while, but it would be much easier
If there was a way to pull a BOM for a part directly into Excel using
ODBC.

Any idea's or help is appreciated.

Jim Moore

----------------------------------------------------------
------------------------------
"This email message is for the sole use of the intended
recipient(s) and may contain proprietary and/or confidential
information for which such intended recipients(s) may have
an obligation to treat as proprietary. Any unauthorized review,
use, disclosure, or distribution is strictly prohibited. If you are
not the intended recipient, please contact sender by reply
email and destroy all copies of this e-mail."
----------------------------------------------------------
-----




.


<http://geo.yahoo.com/serv?s=97359714/grpId=20369/grpspId=1705007183/msg
Id=58747/stime=1188225099/nc1=4507179/nc2=3848643/nc3=4699088>




[Non-text portions of this message have been removed]
5
Using ODBC, get BOM from the 'PartMtl' Table, keep in mind that this will be a 'Single-Level' BOM, no Methods or Operations. I do it thru Excel, 'Data/Import External Data-you can record the Macro, manipultate it to accept a 'User Input' for Part Number.

( you'll need a ODBC-DSN setup of course , here is the resulting query for my example:

SELECT PartMtl_0.PartNum, PartMtl_0.MtlPartNum, PartMtl_0.MtlSeq, PartMtl_0.QtyPer
FROM PUB.PartMtl PartMtl_0
WHERE (PartMtl_0.PartNum Like '001-A0280%')
ORDER BY PartMtl_0.MtlSeq

Note that this Table doesn't include the Part Description for the 'child' parts, you'd need to complicate things by 'looping' thru each 'child' record, and extracting the Description from the 'Part' Table.

Excel Results:
PartNum MtlPartNum MtlSeq QtyPer 001-A0280 112-126642-15.44 10 1 001-A0280 001-A0298 20 1 001-A0280 112-138840 30 1 001-A0280 112-125452 40 8 001-A0280 999-462-0030 50 3 001-A0280 999-264-SP64-B 60 1



"Moore, Jim (Anniston)" <jmoore@...-ots.com> wrote:
Is there a way to pull a BOM directly into Excel using ODBC.
We have been using report builder exports and the preview text import
method for quite a while, but it would be much easier
If there was a way to pull a BOM for a part directly into Excel using
ODBC.

Any idea's or help is appreciated.

Jim Moore

----------------------------------------------------------
------------------------------
"This email message is for the sole use of the intended
recipient(s) and may contain proprietary and/or confidential
information for which such intended recipients(s) may have
an obligation to treat as proprietary. Any unauthorized review,
use, disclosure, or distribution is strictly prohibited. If you are
not the intended recipient, please contact sender by reply
email and destroy all copies of this e-mail."
----------------------------------------------------------
-----

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






---------------------------------
Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when.

[Non-text portions of this message have been removed]
6
Thanks Bill,

That worked very well.

I'll see what I can do with the descriptions.



Jim





From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Bill Jackson
Sent: Monday, August 27, 2007 10:09 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] BOM and ODBC (Vantage 6.1)



Using ODBC, get BOM from the 'PartMtl' Table, keep in mind that this
will be a 'Single-Level' BOM, no Methods or Operations. I do it thru
Excel, 'Data/Import External Data-you can record the Macro, manipultate
it to accept a 'User Input' for Part Number.

( you'll need a ODBC-DSN setup of course , here is the resulting query
for my example:

SELECT PartMtl_0.PartNum, PartMtl_0.MtlPartNum, PartMtl_0.MtlSeq,
PartMtl_0.QtyPer
FROM PUB.PartMtl PartMtl_0
WHERE (PartMtl_0.PartNum Like '001-A0280%')
ORDER BY PartMtl_0.MtlSeq

Note that this Table doesn't include the Part Description for the
'child' parts, you'd need to complicate things by 'looping' thru each
'child' record, and extracting the Description from the 'Part' Table.

Excel Results:
PartNum MtlPartNum MtlSeq QtyPer 001-A0280 112-126642-15.44 10 1
001-A0280 001-A0298 20 1 001-A0280 112-138840 30 1 001-A0280 112-125452
40 8 001-A0280 999-462-0030 50 3 001-A0280 999-264-SP64-B 60 1



"Moore, Jim (Anniston)" <jmoore@...-ots.com
<mailto:jmoore%40dri.gd-ots.com> > wrote:
Is there a way to pull a BOM directly into Excel using ODBC.
We have been using report builder exports and the preview text import
method for quite a while, but it would be much easier
If there was a way to pull a BOM for a part directly into Excel using
ODBC.

Any idea's or help is appreciated.

Jim Moore



.


<http://geo.yahoo.com/serv?s=97359714/grpId=20369/grpspId=1705007183/msg
Id=58750/stime=1188227357/nc1=4507179/nc2=3848642/nc3=4836037>




[Non-text portions of this message have been removed]
7
In Excel, go to Data > Import Data > New Database Query and select your
data source. Then add the tables you want (PartBom, PartBoo, etc) and
click Close. Microsoft Query will open but will be minimized. Right
click on it and restore it. Here you can add more tables, change links,
add filters (criteria) and preview the data. When you've got what you
want you can return it to Excel. I suggest you get a book on Excel and
familiarize yourself with all the features.



There are some short-comings though: Only two tables can be linked when
left outer joins are required. (The work-around is to add more
worksheets to the workbook, each with its own embedded query, and use
vlookups to collect the data in the format you need it.) And to change
the filters you must open the embedded query, which puts the whole query
at risk of being compromised.



Both of the above issues are negated when I create a Crystal Report and
publish it with EasyView (http://www.easystreetsoftware.net). Moreover,
Crystal's parameter screens make data selections easy, and Easyview's
export functions are easy to learn.



Steve Ashton

Decoustics Limited

65 Disco Road

Toronto, Ontario

Canada M9W 1M2

T (416) 675-3983 x336

F (416) 679-8838

sashton@...

Steve.Ashton2@...



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Moore, Jim (Anniston)
Sent: Monday, August 27, 2007 10:31 AM
To: vantage@yahoogroups.com
Subject: [Vantage] BOM and ODBC (Vantage 6.1)



Is there a way to pull a BOM directly into Excel using ODBC.
We have been using report builder exports and the preview text import
method for quite a while, but it would be much easier
If there was a way to pull a BOM for a part directly into Excel using
ODBC.

Any idea's or help is appreciated.

Jim Moore

----------------------------------------------------------
------------------------------
"This email message is for the sole use of the intended
recipient(s) and may contain proprietary and/or confidential
information for which such intended recipients(s) may have
an obligation to treat as proprietary. Any unauthorized review,
use, disclosure, or distribution is strictly prohibited. If you are
not the intended recipient, please contact sender by reply
email and destroy all copies of this e-mail."
----------------------------------------------------------
-----

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





[Non-text portions of this message have been removed]
8
Hey jim,



Go into ms query (when in the odbc wizard, click [Cancel] and answer
[Yes] to enter MS query) , you should be able to join in the part table,
which has the part descriptions...it will bring them all in.



Take care,



Steve





From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Moore, Jim (Anniston)
Sent: Monday, August 27, 2007 11:32 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BOM and ODBC (Vantage 6.1)



Thanks Bill,

That worked very well.

I'll see what I can do with the descriptions.

Jim

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Bill Jackson
Sent: Monday, August 27, 2007 10:09 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] BOM and ODBC (Vantage 6.1)

Using ODBC, get BOM from the 'PartMtl' Table, keep in mind that this
will be a 'Single-Level' BOM, no Methods or Operations. I do it thru
Excel, 'Data/Import External Data-you can record the Macro, manipultate
it to accept a 'User Input' for Part Number.

( you'll need a ODBC-DSN setup of course , here is the resulting query
for my example:

SELECT PartMtl_0.PartNum, PartMtl_0.MtlPartNum, PartMtl_0.MtlSeq,
PartMtl_0.QtyPer
FROM PUB.PartMtl PartMtl_0
WHERE (PartMtl_0.PartNum Like '001-A0280%')
ORDER BY PartMtl_0.MtlSeq

Note that this Table doesn't include the Part Description for the
'child' parts, you'd need to complicate things by 'looping' thru each
'child' record, and extracting the Description from the 'Part' Table.

Excel Results:
PartNum MtlPartNum MtlSeq QtyPer 001-A0280 112-126642-15.44 10 1
001-A0280 001-A0298 20 1 001-A0280 112-138840 30 1 001-A0280 112-125452
40 8 001-A0280 999-462-0030 50 3 001-A0280 999-264-SP64-B 60 1

"Moore, Jim (Anniston)" <jmoore@...-ots.com
<mailto:jmoore%40dri.gd-ots.com>
<mailto:jmoore%40dri.gd-ots.com> > wrote:
Is there a way to pull a BOM directly into Excel using ODBC.
We have been using report builder exports and the preview text import
method for quite a while, but it would be much easier
If there was a way to pull a BOM for a part directly into Excel using
ODBC.

Any idea's or help is appreciated.

Jim Moore

.

<http://geo.yahoo.com/serv?s=97359714/grpId=20369/grpspId=1705007183/msg
Id=58750/stime=1188227357/nc1=4507179/nc2=3848642/nc3=4836037>


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





[Non-text portions of this message have been removed]
9
Jim/ Steve:

That's right Steve, I was trying to make it too complicated , no 'looping' needed, just add the Part table, revise query as such:

SELECT PartMtl_0.PartNum, PartMtl_0.MtlSeq, PartMtl_0.MtlPartNum, PartMtl_0.QtyPer, PartMtl_0.PullAsAsm, Part_0.PartDescription
FROM PUB.Part Part_0, PUB.PartMtl PartMtl_0
WHERE Part_0.Company = PartMtl_0.Company AND Part_0.PartNum = PartMtl_0.MtlPartNum AND ((PartMtl_0.PartNum Like '001-A4839-31.45%'))
ORDER BY PartMtl_0.MtlPartNum DESC

Steve Wirch <wirch@...> wrote:
Hey jim,

Go into ms query (when in the odbc wizard, click [Cancel] and answer
[Yes] to enter MS query) , you should be able to join in the part table,
which has the part descriptions...it will bring them all in.

Take care,

Steve

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Moore, Jim (Anniston)
Sent: Monday, August 27, 2007 11:32 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BOM and ODBC (Vantage 6.1)

Thanks Bill,

That worked very well.

I'll see what I can do with the descriptions.

Jim

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Bill Jackson
Sent: Monday, August 27, 2007 10:09 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] BOM and ODBC (Vantage 6.1)

Using ODBC, get BOM from the 'PartMtl' Table, keep in mind that this
will be a 'Single-Level' BOM, no Methods or Operations. I do it thru
Excel, 'Data/Import External Data-you can record the Macro, manipultate
it to accept a 'User Input' for Part Number.

( you'll need a ODBC-DSN setup of course , here is the resulting query
for my example:

SELECT PartMtl_0.PartNum, PartMtl_0.MtlPartNum, PartMtl_0.MtlSeq,
PartMtl_0.QtyPer
FROM PUB.PartMtl PartMtl_0
WHERE (PartMtl_0.PartNum Like '001-A0280%')
ORDER BY PartMtl_0.MtlSeq

Note that this Table doesn't include the Part Description for the
'child' parts, you'd need to complicate things by 'looping' thru each
'child' record, and extracting the Description from the 'Part' Table.

Excel Results:
PartNum MtlPartNum MtlSeq QtyPer 001-A0280 112-126642-15.44 10 1
001-A0280 001-A0298 20 1 001-A0280 112-138840 30 1 001-A0280 112-125452
40 8 001-A0280 999-462-0030 50 3 001-A0280 999-264-SP64-B 60 1

"Moore, Jim (Anniston)" <jmoore@...-ots.com
<mailto:jmoore%40dri.gd-ots.com>
<mailto:jmoore%40dri.gd-ots.com> > wrote:
Is there a way to pull a BOM directly into Excel using ODBC.
We have been using report builder exports and the preview text import
method for quite a while, but it would be much easier
If there was a way to pull a BOM for a part directly into Excel using
ODBC.

Any idea's or help is appreciated.

Jim Moore

.

<http://geo.yahoo.com/serv?s=97359714/grpId=20369/grpspId=1705007183/msg
Id=58750/stime=1188227357/nc1=4507179/nc2=3848642/nc3=4836037>

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

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






---------------------------------
Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when.

[Non-text portions of this message have been removed]
10
Bill / Steve,

That worked great. Thanks for taking the time to reply.

This will save tons of time for the user that was typing stuff in, or
using the other methods to get the BOM into Excel.



Thanks again.

Jim



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Bill Jackson
Sent: Monday, August 27, 2007 1:47 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BOM and ODBC (Vantage 6.1)



Jim/ Steve:

That's right Steve, I was trying to make it too complicated , no
'looping' needed, just add the Part table, revise query as such:

SELECT PartMtl_0.PartNum, PartMtl_0.MtlSeq, PartMtl_0.MtlPartNum,
PartMtl_0.QtyPer, PartMtl_0.PullAsAsm, Part_0.PartDescription
FROM PUB.Part Part_0, PUB.PartMtl PartMtl_0
WHERE Part_0.Company = PartMtl_0.Company AND Part_0.PartNum =
PartMtl_0.MtlPartNum AND ((PartMtl_0.PartNum Like '001-A4839-31.45%'))
ORDER BY PartMtl_0.MtlPartNum DESC

Steve Wirch <wirch@... <mailto:wirch%40hittite.com> > wrote:
Hey jim,

Go into ms query (when in the odbc wizard, click [Cancel] and answer
[Yes] to enter MS query) , you should be able to join in the part table,
which has the part descriptions...it will bring them all in.

Take care,

Steve

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Moore, Jim (Anniston)
Sent: Monday, August 27, 2007 11:32 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] BOM and ODBC (Vantage 6.1)

Thanks Bill,

That worked very well.

I'll see what I can do with the descriptions.

Jim

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Bill Jackson
Sent: Monday, August 27, 2007 10:09 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] BOM and ODBC (Vantage 6.1)

Using ODBC, get BOM from the 'PartMtl' Table, keep in mind that this
will be a 'Single-Level' BOM, no Methods or Operations. I do it thru
Excel, 'Data/Import External Data-you can record the Macro, manipultate
it to accept a 'User Input' for Part Number.

( you'll need a ODBC-DSN setup of course , here is the resulting query
for my example:

SELECT PartMtl_0.PartNum, PartMtl_0.MtlPartNum, PartMtl_0.MtlSeq,
PartMtl_0.QtyPer
FROM PUB.PartMtl PartMtl_0
WHERE (PartMtl_0.PartNum Like '001-A0280%')
ORDER BY PartMtl_0.MtlSeq

Note that this Table doesn't include the Part Description for the
'child' parts, you'd need to complicate things by 'looping' thru each
'child' record, and extracting the Description from the 'Part' Table.

Excel Results:
PartNum MtlPartNum MtlSeq QtyPer 001-A0280 112-126642-15.44 10 1
001-A0280 001-A0298 20 1 001-A0280 112-138840 30 1 001-A0280 112-125452
40 8 001-A0280 999-462-0030 50 3 001-A0280 999-264-SP64-B 60 1

"Moore, Jim (Anniston)" <jmoore@...-ots.com
<mailto:jmoore%40dri.gd-ots.com>
<mailto:jmoore%40dri.gd-ots.com>
<mailto:jmoore%40dri.gd-ots.com> > wrote:
Is there a way to pull a BOM directly into Excel using ODBC.
We have been using report builder exports and the preview text import
method for quite a while, but it would be much easier
If there was a way to pull a BOM for a part directly into Excel using
ODBC.

Any idea's or help is appreciated.

Jim Moore

.

<http://geo.yahoo.com/serv?s=97359714/grpId=20369/grpspId=1705007183/msg
Id=58750/stime=1188227357/nc1=4507179/nc2=3848642/nc3=4836037>

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

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

---------------------------------
Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's
on, when.

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





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