End Item Where Used

[spucelik]

Within a bill of materials I have an item that is burried in the bill structure that I want to know where else it is used. This is very similar to the End Item Where Used Report in Macola.

Does anyone know how this report is built so I can replicate the functionality.

Thanks!

 

[dgillz]

Why is the inquiry itself not sufficient? Where and how does it fail?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[spucelik]

The problem with he report is that I have hundereds of items to see where they are used and wanted to automate the logic.

 

[dgillz]

The report already does this.

At any rate, this is not an easy report to automate, which explains why the report is not a crystal report.

You would need multiple aliases (is that a word?) ofthe item master and bill of material tables, to match the number of levels deep you BOMs are. Then use supression logic to suppress the levels that are not end levels.

Doable, but all in all I would use the canned report. I still do not understand why it is unacceptable. What are you wanting this info for?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[spucelik]

dgillz...thanks for you thoughts. I would think that Macola has theses alias tables for their own purposes so they can generate the report. I didn't see them though so maybe they are using temp tables in memory??

What I'm trying to do is a user has requested that within a particular bill of materials to pull out all the purchased parts. I have a proceedure that will traverse a specific BOM and identify these parts. Now that I know what the purchased parts are, they want to know what other BOM those purchased parts are used in. Meaning I would need to traverse all the BOM for that purchase part to see where it exists, but that's not practical. Keep in mind that for a given BOM there could be 200 purchased parts.

This is why I wanted to automate the end item where used report and simply pass each purchased part as a parameter to see what other BOM it exists in.

Hopefully this makes sense but you've given me another angle to think about.

Thanks@

 

[dgillz]

OK but let me put my question another way:

What BUSINESS PROBLEM are you solving if you had this report?

There is a reason why such a report does not exist - very few people have requested it. So please tell me what you are trying to accomplish, I am sure there is another way to do this.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[spucelik]

We have several purchased parts that need to be replaced and we need to understand what are all the BOM they exist in so we know what needs to be changed.

 

[dgillz]

OK we have come full circle. The canned end item where used report is the PERFECT solution for this. Have you actually run the report to see it for yourself?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[spucelik]

I've run the report and it does give me what I'm looking for. keep in mind that i have 500 or so purchased parts that I'm trying to use this report for, which is the reason for the automation of it, so people don't have to look up 500 different items to see where they are used.

 

[crystalreporting]

You would still have to key the 500 items in somewhere I would think - unless there is some common characteristic to the items that you could use to 'automate' your search.

I have a series of SQL stored procedures that run on a scheduled basis to create a flatted BOM table - I then use this table for pulling BOM data into Excel or Crystal as it is much easier to deal with than the heirarchical structure of the standard BMPRDSTR table.

I got the original idea and code from this post and something along these lines may work for you too;

http://www.tek-tips.com/viewthread.cfm?qid=1234949

Peter Shirley
Macola Consultant, PA and surrounding states.

http://www.srhconsulting.com

 

[TLeaders]

Spucelik

If you use the code that Peter referred to and changed the code “b.Comp_Item_no = ITEM” to reference the parent item number instead, the code will go up the find the initial parent item. The problem is that you will still have to enter the item numbers one at a time.

This brings us back to the business problem that is being addressed. There are only a few reasons that I would want to see all the parents created by a group of items.
1) Price of the raw material is expected to rise
2) Availability
3) Political (like a competitor bought the raw material supplier)
4) Boredom set in and I just wanted to know
The result of this thought process is that it should be possible to select the items from the existing database and load the query in a single pass. For example finding all the items from a specific supplier and loading that into code, like the code that was posted, is a single line. If you had a material cost type that was going up by 10% and you wanted to see the effect on the parent items that is also possible.

Contact your business partner – or post the real issue.

Tom