Manufacturing BOM Where Used query

Winthin manufacturing there is a where-used inquiry window that will show all 
BOMs that a given item appears in.  You can also expand that view to see what 
BOMs and item's parent is used in.  Some of our BOMs are 10 layers deep.

In our environment we need to know which category of finished goods each 
purchased item is used in.  Currently we maintain this manually and store the 
info in an inventory user-defined field.  Over time as the number of our 
finished goods and purchased parts has increased this has become an 
unmangeable process.

I need to find a way to query the BOM tables and work back up the chain for 
any given item to find the item class of the highest level parent item.  Any 
suggestions on a way to do this?  Thanks.
-- 
Jim@TurboChef
0
jim.harris (398)
2/20/2009 4:31:01 PM
greatplains 29623 articles. 6 followers. Follow

4 Replies
1240 Views

Similar Articles

[PageSpeed] 26

------=_NextPart_0001_2EDF8716
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Hello Jim,

Thank you for using Online Technical Resources.

The BOM 'Where Used' inquiry is built in temp tables off the BM010415 (BOM 
Header) and BM010115 (BOM Line) tables.

We do not have a smartlist or report that will expode from a component to 
the top level finished good as it is too extensive of a build.  

The aforementioned tables will provide the BOM structure.  The BM010115 
values you'd need to focus on are:

PPN_I - Parent
CPN_I - Child/Component
SUBCAT_I -  a value of 0 means it is a buy component, the value of 1 means 
it is a subassembly component.

I know this has been attempted before, but I can't say it's ever been fully 
accomplished fully.

Thank you,

Jim Schauer
Online Technical Support
=====================================================
PLEASE NOTE:  The partner managed newsgroups are provided
 to assist with break/fix issues and simple how to questions.  

We also love to hear your product feedback!
Let us know what you think by posting 
       from the web interface: Partner Feedback 
       from your newsreader:  
microsoft.private.directaccess.partnerfeedback. 
We look forward to hearing from you!
====================================================== 
When responding to posts, please "Reply to Group" via your newsreader 
so that others may learn and benefit from this issue. 
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights. 
======================================================
------=_NextPart_0001_2EDF8716
Content-Type: text/x-rtf
Content-Transfer-Encoding: 7bit

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fprq2\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\f0\fs20 Hello Jim,
\par 
\par Thank you for using Online Technical Resources.
\par 
\par The BOM 'Where Used' inquiry is built in temp tables off the BM010415 (BOM Header) and BM010115 (BOM Line) tables.
\par 
\par We do not have a smartlist or report that will expode from a component to the top level finished good as it is too extensive of a build.  
\par 
\par The aforementioned tables will provide the BOM structure.  The BM010115 values you'd need to focus on are:
\par 
\par PPN_I - Parent
\par CPN_I - Child/Component
\par SUBCAT_I -  a value of 0 means it is a buy component, the value of 1 means it is a subassembly component.
\par 
\par I know this has been attempted before, but I can't say it's ever been fully accomplished fully.
\par 
\par Thank you,
\par 
\par Jim Schauer
\par Online Technical Support
\par =====================================================
\par PLEASE NOTE:  The partner managed newsgroups are provided
\par  to assist with break/fix issues and simple how to questions.  
\par 
\par We also love to hear your product feedback!
\par Let us know what you think by posting 
\par        from the web interface: Partner Feedback 
\par        from your newsreader:  microsoft.private.directaccess.partnerfeedback. 
\par We look forward to hearing from you!
\par ====================================================== 
\par When responding to posts, please "Reply to Group" via your newsreader 
\par so that others may learn and benefit from this issue. 
\par ======================================================
\par This posting is provided "AS IS" with no warranties, and confers no rights. 
\par ======================================================
\par 
\par }
------=_NextPart_0001_2EDF8716--

0
jimscha (11)
2/20/2009 10:30:16 PM
I've done it.  I used an example in the SQL help files (about hierarchical 
data) to create a SPROC that returns an exploded BOM.

From this I have created SSRS reports where you can explore BOM/Where used 
information by 'drilling' through BOMs to Where Used info and back again.

I am away from the office this week...I may be able to work with you off 
this board...

Try emailing t.fos.ter@tml.co.rp.com 
remove all the extraneous dots (except the obviously correct one)

-- 
Tim Foster


"Jim@TurboChef" wrote:

> Winthin manufacturing there is a where-used inquiry window that will show all 
> BOMs that a given item appears in.  You can also expand that view to see what 
> BOMs and item's parent is used in.  Some of our BOMs are 10 layers deep.
> 
> In our environment we need to know which category of finished goods each 
> purchased item is used in.  Currently we maintain this manually and store the 
> info in an inventory user-defined field.  Over time as the number of our 
> finished goods and purchased parts has increased this has become an 
> unmangeable process.
> 
> I need to find a way to query the BOM tables and work back up the chain for 
> any given item to find the item class of the highest level parent item.  Any 
> suggestions on a way to do this?  Thanks.
> -- 
> Jim@TurboChef
0
tfoster (136)
2/23/2009 2:37:15 AM
------=_NextPart_0001_48123F4E
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Hello Jim,

Checking in to see if there is anything else we can do for you on this 
topic.
Please post again at your convenience and we will be here for you.

Have a great week!

Mitch Randall

Microsoft Online Support Engineer

 

Get Secure! - www.microsoft.com/security

==============================================

PLEASE NOTE:  The partner managed newsgroups are provided 

to assist with break/fix issues and simple how to questions.   

 

We also love to hear your product feedback!  

Let us know what you think by posting 

    from the web interface: Partner Feedback 

    from your newsreader:  microsoft.private.directaccess.partnerfeedback. 

We look forward to hearing from you!

===================================================== 

When responding to posts, please "Reply to Group" via your newsreader 

so that others may learn and benefit from this issue. 

======================================================

This posting is provided "AS IS" with no warranties, and confers no rights. 

======================================================
------=_NextPart_0001_48123F4E
Content-Type: text/x-rtf
Content-Transfer-Encoding: 7bit

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fprq2\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\f0\fs20 Hello Jim,
\par 
\par Checking in to see if there is anything else we can do for you on this topic.
\par Please post again at your convenience and we will be here for you.
\par 
\par Have a great week!
\par 
\par Mitch Randall
\par 
\par Microsoft Online Support Engineer
\par 
\par  
\par 
\par Get Secure! - www.microsoft.com/security
\par 
\par ==============================================
\par 
\par PLEASE NOTE:  The partner managed newsgroups are provided 
\par 
\par to assist with break/fix issues and simple how to questions.   
\par 
\par  
\par 
\par We also love to hear your product feedback!  
\par 
\par Let us know what you think by posting 
\par 
\par     from the web interface: Partner Feedback 
\par 
\par     from your newsreader:  microsoft.private.directaccess.partnerfeedback. 
\par 
\par We look forward to hearing from you!
\par 
\par ===================================================== 
\par 
\par When responding to posts, please "Reply to Group" via your newsreader 
\par 
\par so that others may learn and benefit from this issue. 
\par 
\par ======================================================
\par 
\par This posting is provided "AS IS" with no warranties, and confers no rights. 
\par 
\par ======================================================
\par 
\par }
------=_NextPart_0001_48123F4E--

0
A-MITCHR (48)
2/25/2009 7:56:06 PM
------=_NextPart_0001_525FF0D1
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Hi there Jim,

Anything else we can do for you here on this query?

Please post back at your convenience and we will be here for you.
Thanks again for using the partner newsgroups!

Have a great week!

Mitch Randall

Microsoft Online Support Engineer

 

Get Secure! - www.microsoft.com/security

==============================================

PLEASE NOTE:  The partner managed newsgroups are provided 

to assist with break/fix issues and simple how to questions.   

 

We also love to hear your product feedback!  

Let us know what you think by posting 

    from the web interface: Partner Feedback 

    from your newsreader:  microsoft.private.directaccess.partnerfeedback. 

We look forward to hearing from you!

===================================================== 

When responding to posts, please "Reply to Group" via your newsreader 

so that others may learn and benefit from this issue. 

======================================================

This posting is provided "AS IS" with no warranties, and confers no rights. 

======================================================
------=_NextPart_0001_525FF0D1
Content-Type: text/x-rtf
Content-Transfer-Encoding: 7bit

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fprq2\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\f0\fs20 Hi there Jim,
\par 
\par Anything else we can do for you here on this query?
\par 
\par Please post back at your convenience and we will be here for you.
\par Thanks again for using the partner newsgroups!
\par 
\par Have a great week!
\par 
\par Mitch Randall
\par 
\par Microsoft Online Support Engineer
\par 
\par  
\par 
\par Get Secure! - www.microsoft.com/security
\par 
\par ==============================================
\par 
\par PLEASE NOTE:  The partner managed newsgroups are provided 
\par 
\par to assist with break/fix issues and simple how to questions.   
\par 
\par  
\par 
\par We also love to hear your product feedback!  
\par 
\par Let us know what you think by posting 
\par 
\par     from the web interface: Partner Feedback 
\par 
\par     from your newsreader:  microsoft.private.directaccess.partnerfeedback. 
\par 
\par We look forward to hearing from you!
\par 
\par ===================================================== 
\par 
\par When responding to posts, please "Reply to Group" via your newsreader 
\par 
\par so that others may learn and benefit from this issue. 
\par 
\par ======================================================
\par 
\par This posting is provided "AS IS" with no warranties, and confers no rights. 
\par 
\par ======================================================
\par 
\par }
------=_NextPart_0001_525FF0D1--

0
A-MITCHR (48)
2/27/2009 7:57:14 PM
Reply:

Similar Artilces:

hey ya'll how do i auto sum a cell using the letter x instaed of .
hey ya'll how do i auto sum a colum using the letter x instead of using a number To count number of "x"es =COUNTIF(A1:A100,"x") Gord Dibben Excel MVP On Mon, 25 Oct 2004 14:25:04 -0700, jrod98 <jrod98@discussions.microsoft.com> wrote: >hey ya'll how do i auto sum a colum using the letter x instead of using a >number Gord answered but I couldn't resist asking what the sum of x and x is? Sinister Rod Serling music here. -- Don Guillett SalesAid Software donaldb@281.com "jrod98" <jrod98@discussions.microsoft.com> wrote in mes...

paste text into a used cell
I need to move text from one row to a single cell that has been formatted to wrap text and repeat the process for other rows. Paste is not working. Please advise. When you copy and paste, you paste the format as well as the contents If you want to copy text to a cell that is formatted differently instead of paste, slelct paste special, values -- ElsiePO ----------------------------------------------------------------------- ElsiePOA's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=390 View this thread: http://www.excelforum.com/showthread.php?threadid=26962 ...

How can I use the exchange mode in Outlook? Can I use it in my PC
Can I use it in my PC? To use it I'm supposed to have Microsoft Exchange Server 2003, but what is that? Using the exchange mode in Outlook, if Outlook makes a security copy for my emails, that means that if an email has a virus, it gets saved in my PC? If by "Exchange Mode" you mean use an Exchange server in "Add / Edit Email Accounts" you can't - unless you are connecting to an Exchange server (which is often the case in larger companies). If you had an Exchange server, I suspect you would have been informed by your tech staff - so my guess is that the answer...

COUNTIF using amount of time
I have some data with various time intervals in mm:ss.ms and I want to use the COUNTIF function to count how many of them fall between 1-2 minutes, 2-3 minutes, 3-4 minutes, etc., but I keep getting a count of all the values. Am I comparing the time intervals wrong? For example, the data (in the A column) is: 01:19.666 01:13.287 01:28.308 02:29.872 05:36.658 01:01.770 My formula for counting those between 1-2 minutes: =COUNTIF(A:A,">=00:01.000&&<00:02.000") The returned value is 6. Any help would be appreciated, Dennis Dennis, For 1 to 2 minutes, try =SUMPRODUCT(...

to use workday function in excel vba code
hi all, is there a way to use wrokday function in my worksheet controls.? i have a date time picker in my worksheet and a text box and a button. on click of this button i should get next desired date. like when i select a date from the calendar and click on button ther is onclick function in my macro. this onclick should calculate next desired date(assume if i choos 10/11/2005 and add 2 to this date i should get 12/11/2005) i know how to use workday function using a cell reference but................ i don't know how to put this into a vba code. if i use workday("10/11/2005&qu...

Using a sub procedure to change a subform property
I am currently using the following code to change the AllowEdits property on any form. The form calls the Sub procedure through a button's OnClick event. Sub UnlockForm(strFormName As String) On Error GoTo Err_unlockform If Forms(strFormName).AllowEdits = False Then Forms(strFormName).AllowEdits = True MsgBox "Form is now unlocked for editing." End If Exit_unlockform: Exit Sub Err_unlockform: MsgBox Err.Description Resume Exit_unlockform End Sub I am trying to call this Sub procedure from a button on a subform. I was going to us...

Help with debugging query (Rolling Average)
Hi I'm trying to do a rolling average on data that is ordered by a date time column. I'm after adivce about how best to do this, I've tried this - SELECT Time, ValueX, (HOUR(Time) * 3600 + Minute(Time) * 60 + Second(Time)) As TickTock (SELECT Avg(ValueX) FROM My_Table WHERE TickTock Between (TickTock) AND (TickTock) + 30) AS RollingAverage FROM My_Table; Basically my thinking was that it would be easier to work on this datetime data as Seconds, So that my rolling average can be based on 10, at 3 second intervals When I try this I get nothing in my RollingAverage box and also a...

Help with query-dont want to see dupes
I've made a query to find customers that have ordered a certain product between a period of time. Some customers order a lot so they'll appear multiple times but I only want to show each customer once. The field to compare is ContactID On Tue, 10 Apr 2007 10:02:51 -0700, "Rob" <nospam@nospam.com> wrote: >I've made a query to find customers that have ordered a certain product >between a period of time. Some customers order a lot so they'll appear >multiple times but I only want to show each customer once. > >The field to compare is Contac...

Use of Dialog
Hi all, I'm curious about use of excel dialog. What is for? and how to use it and what other benefits or advantages in using for common financial worksheet? Thank you. :) -- markuss ------------------------------------------------------------------------ markuss's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34966 View this thread: http://www.excelforum.com/showthread.php?threadid=553085 Dialogs can make things easier for you and your end user. They can be used to pre-validate entered information, apply business rules to it before placing it onto a w...

Use Folders for E-mail
I organize my e-mail message into folder by client or topic. When I look at a message in the "sent items" folder it is shown under the name of the person to whom I sent it. However, when I drag that same item into a client or topic folder it is now shown under my name, apparently since I was the sender. Is there a way to force the dragged item to keep the same name as it had in the sent items folder? Chuck Humphrey All you have to do is switch the view on the client or topic folder to "Sent To" -- that's the view used in the Sent Items folder by default. -- Jocelyn...

How do I Excel formula =LEFT(A2,LEN(A2)-3) in an Access Query
I need to delete the last 12 characters (phone number) from a field that contains names and phone numbers. Lou Are you asking how to do this in MS Access? If so, take a look in Access HELP re: Mid() function and Len() function. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. ...

Inserting blank rows plus using Sum
I have a list of stocks sorted by date. When the stock symbol changes, I need to insert 2 blank rows and AutoSum columns 2, 6,7, and 8 of the group and display them on the first of the 2 blank rows, leaving a blank row before the next group. Symbol Qty Price Action Name US Trade Date Amount Commission Fees MSFT -210 28.95 Sell 2/13/2008 6079.43 -0.07 -0.07 MSFT -790 28.95 Sell 2/13/2008 22863.24 -7.26 -0.26 -1000 28942.67 -7.33 -0.33 C -700 22.16 Sell 3/4/2008 15511.82 -0.18 -0.18 C -300 22.15 Sell 3/4/2008 6637.92 -7.08 -0.08 -1000 22149.74 -7.26 -0.26 MSFT -500...

Not able to send mails using Ms Outlook 2000
Whenever i try to send mails using Ms Outlook 2000 i get this error SMTP-MAIL: died on signal 11', Port: 25, Secure(SSL): No, Server But i am able to send the same mails with same id configured with Outlook Express 6 and 5 both What will be the solutions to this Regards Shardul What you try to do is to export your mailaccount setting from Outlook exprees into Outook 2000 >-----Original Message----- >Whenever i try to send mails using Ms Outlook 2000 i get >this error >SMTP-MAIL: died on signal 11', Port: 25, Secure(SSL): No, >Server > > > >But i a...

Countdown a Query Duration
I would like to have Access show the user the wait time left for a query to finish. Most queries I've ran into in the past were rather fast (1 or 2 secs in duration), however, my database now has some queries that can take up to 20 seconds to process. I want to have a pop-up of some sort that shows the wait time remaining. Is this even possible? I don't think the actual time is computed. For long running queries, Access displays a status bar in the Access window footer. "Mercadogs" <Mercadogs@discussions.microsoft.com> wrote in message news:1652E7D8-940F-4559...

Manufacturing Component Transaction Automation
I am trying to automate the manufacturing component transaction process. I have added the proper buttons and forms to Visual Basic. When I run the following VBA code: [CODE] TRXType = 1 ManufactureOrderStart.Value = "12345" ManufactureOrderEnd.Value = "12345" Refresh = 1 DoEvents ManufacturingComponentDetail.TRXQTY = 300 ManufacturingComponentDetail.CB.Value = 1 [/CODE] I get the following error: "You can't select this item because it no longer exists on the picklist". When I manually perform the same st...

Query Criteria 02-10-10
I have a query in which I only want the top 10 stores for item velocity for multiple groups. I have used the top value and max criteria but it only brings back the top or max for the entire dataset, not for each group within the data. What am I missing??? Post your table and field names with datatype that you would use in the query along with sample data (need not be real but workable). -- Build a little, test a little. "gabbyccf" wrote: > I have a query in which I only want the top 10 stores for item velocity for > multiple groups. I have used the top...

Using Delete Dialog
Hi NG, I try to create a Custom Web Page, which will insert to the CRM by NavBar. This is working fine. Now the should be a seperate Button, for deleting. I found a way to call the standard delete dialog with: window.showModalDialog("/grid/cmds/dlg_delete.aspx?iObjType=112&iTotal=1&sIds=") My Problem now: How can I set the id for the Case which should deleted? When I set the argumten like: window.showModalDialog("/grid/cmds/dlg_delete.aspx?iObjType=112&iTotal=1&sIds=", "{C1B55815-104E-DB11-9C89-0003FFA2910B}") I get the error: "XML Parse...

Picture viewing in outlook 2000 using XP
Using outlook 2000 standard (no updates). XP Home with all SP's and updates. I view picture attachments using the default picture and fax viewer. When the last picture is viewed. I click next. Instead of restarting the same file, another file appears. It is always the same picture file that appears regardless of which email I am viewing. I have removed the intruder picture file from the C drive(MY Pictures) and from outlook (sent items). This intruder still happens. Any ideas ??? Metro Pete <Metro Pete@discussions.microsoft.com> wrote: > Using outlook 2000 standard (no u...

what pdf software do you use to redact text?
There are a lot of software packages that will converted back and forth between Word and PDF documents - however, I need to know if anyone uses a good converter that will allow true redactions that cannot be seen in the PDF version as well as remove metadata from the output document. Thanks!! CH Do your redaction in Word. Output the document to a graphics format such as TIFF using SnagIt's 'printer' driver, then print the results to PDF using Acrobat to combine multiple pages. That should stay redacted :) -- <>>< ><<> ><<> ...

how can I use a picture to fill text?
I am trying to use a big balloon font, and have a picture as the fill color. How do I do this? Create the phrase with WordArt. Fill, Fill effects, Picture tab. -- Mary Sauer http://msauer.mvps.org/ "lee254" <lee254@discussions.microsoft.com> wrote in message news:0EA083FF-61A9-4DF7-9D65-6CAE1A3B02E5@microsoft.com... >I am trying to use a big balloon font, and have a picture as the fill color. > How do I do this? ...

Mysterious question mark (?) when using OWA
Mysterious question mark (?) when using OWA It seems that my users whom use OWA to send email always seem to send along an "?" question mark at the beginning of their email messages. I am trying to find out why that is happening and how to go about eliminating it. If clients are XP SP2, check kba 883543 and apply the hotfix. Pe-XP SP2 clients: reinstall the S/MIME control from OWA - options. -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Mr. Backup" <backup@yahoo.com> wrote in message new...

Ignore table formatting when importing data using OLEDB
I am using Excel 2010. I want to write a macro in a 'consolidate' XLSM file that will query another selected XLSM file and import data from one of the range names in that file, into the 'consolidate' file. When the import occurs, it formats the imported table using the default Excel 2010 table style (see screenshot), however i don't want to format the cells - i just want to import raw data and leave the cells formatted as they are in the 'consolidate' XLSM file. The PreserveFormatting property has no impact, as the damage is already done once the query is added. ...

Using a variable to refer to a control in VBA
Good Afternoon, I was wondering if any one can tell me how to fix the 3rd line to work like the 2nd one does. The 2nd one works fine but one the 3rd one they system throws an error. thanks! Dale Dim intloop as integer intloop=8 Forms![frmDate]![frmsubMonth].Form![subForm8].visible = False Forms![frmDate]![frmsubMonth].Form!("subFrom" & intloop & "").visible = False Dale K wrote: >Good Afternoon, > >I was wondering if any one can tell me how to fix the 3rd line to work like >the 2nd one does. The 2nd one works fine but one ...

increase BOM component quantity with SQL
We've got a component item that we're tracking quantities on in 100 or so BOMs. We've noticed that the item quantity is consistently out, and would like to increase the quantity used in each BOM it's in by an across the board percentage to correct. Specifically, we'd like to raise all quantities of this item by 12% across the board, without manually touching every single BOM. Does anyone know what the SQL command would be for this (assumuing it can be done practically? scrapperc1. Backup the database 2. Read Step 1 3. Execute the following ...

Import external database query not working with other users?
Help! I set up an import external database query (ms access) for an excel report used on our network that works fine for me, but will not refresh the data for anyone else. It does not appear to be due to any difference in user access or mapping. I set everything up through folders and files that all users have access to. I can't figure out why it only works for me. Is the query set up by the wizzard somehow tied to the user? Thanks, Dave. -- devbox ------------------------------------------------------------------------ devbox's Profile: http://www.excelforum.com/member.php?...