BOM database

[slovette]

I haven't been able to figure out how to make a BOM system with FM. Any ideas?

There is a table with two fields: parent_part_numbers and child_part_numbers.

Generate a table or list of records (Results) where parent_part_number = TOP_PARENT (entered by user)

Repeat [Concatenate to Results records from original table where parent_part_number in original table = child_part_number in Results] until no more matches or a specified number of iterations.

This should create a results table of all decendents of the TOP_PARENT part number.

It is essencially a recursive or nested search problem, where the functions are field contents found in the database.

In Access, I performed this function for six iterations with a SQL union command to concatinate results from the nested SQL queries.

[Ender]

Don't know what a BOM is, but it sounds like you want to see all descendant records in a self-join (recursively.) This could be scripted of course, but I have posted a sample file that essentially has this type of relationship:

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=135595

The trick is making multi-line keys that have all the ID's in the hierarchy built as you go.

[Ted S]

BOM stands for Bill of Materials. It's a standard module in the information systems (ERP systems) that manufacturing companies use.

It is simple in certain respects and very complicated in other respects. It is much like the directory structure in a computer. It all starts in one folder (final assembly) and branches outward and downward from there. Assemblies go into other assemblies go into other assemblies and so on and so forth. The tip of each branch of this "upside down tree" is a purchased part.

The basic structure only involves two tables; an Item Master table and a BOM table. One challenge comes when writing the logic for structuring the BOM. A fundamental rule is that no part or assembly can be made from itself. Therefore, to build this properly you must ensure that the component that you are adding does not already occur further up the tree. If is does you will have put yourself into and endless loop.

The other big challenge is producing what is called the "Indented Bill of Materials". This is a report that lists the part numbers, quantities, and structure of the entire finished product. In my business the indented BOM is 2000-3000 items long and goes about 7 levels deep. Writing efficient code to produce this report is not a trivial task.

This all leads to the dreaded MRP. MRP stands for Material Requirements Planning. It is a module who's sole purpose is to determine what parts and assembiles to buy and build, when to buy and build them, and how many of each to buy and build. It takes into account; the master build schedule, quantities on-hand, quantities on order, scrap factors, lead-times, and qty's per assembly, order policies, safety stock, etc. There is so much going on that in the good-old-days the MRP run was scheduled to run only once per week and only on the weekend because it would take most of the weekend to crunch the numbers.

If all you need is Single Level BOM reports that's pretty easy. But it never seems to stop there, eventually the folks want it all. I have successfully built single level BOM reports, was moderately successful at building a limited Indented BOM report, had a disasterous experience building a Summarized BOM and I wouldn't touch the MRP logic with a 10 foot pole.

I guess what I'm saying here is that this will likely be the most difficult report that you will ever write.

[Ender]

Thanks for the explanation Ted.

The multi-levels stuff sounds similar to a multi-level portals structure (though admittedly, I don't have a good understanding of those.) Maybe Ugo can comment on this.

[transpower]

It might be best to look at some commercial FM solutions, like Biz Basics. This package has many, many reports, a few of which might be just what you're looking for.

[slovette]

Thanks for the responses, especially Ted's fine explanation. I agree, I wouldn't touch MRP with a 10 foot pole. I am only looking to the multilevel (up to say six levels) of BOM, not indented. Once the table of parts in the struture are assembled, it is good enough to simply sort by level, parent part number, child part number. That prints top levels first and bottom levels last. The indented BOM report is another 10 foot pole job.

Now off to study Ender's link.

[slovette]

Ender, quite clever. The concatination occurs in a caculation for auto entry on the all supervisors field. As an employee is added the next level supervisor and all his supervisors up the tree are concatenated in a text field. I am not sure what happens if I add an middle manager. I don't think the auto entry will insert this new supervisor into the lower level employee's lists automatically. If so, this structure is only effective if built in the correct order, top down, without changes.

For a BOM application I would need a dynamically generated linking between parent and child. I think I need to find records where the parents in the table match the children in a previously found set from the same table. I don't think filemaker allows this, unless perhaps you export each found set.

Spencer

[Ender]

You could script this by performing a Copy All Records command on a layout with just the key field after you have the found set. This gives you a multi-line of all the child keys. Now paste that into a regular text field in the parent to give yourself a multi-key of child IDs. Then use this to look at the next level down.

This would likely be a recursive script that checks each level down until they have all been checked. It will probably require marking the records, or some other method to check that a record has not been visited twice. Sounds a little like a Breadth-First-Search on a binary tree.

[Ugo DI LUCA]

Hi,

Attached is a revisited version of a MultiLevel solution I posted some month ago here, but I couldn't find the english version anymore.

I hope it makes sense anyway.

For reports involving BOM, with 6, I used to involve another file for these kind of reports with looping scripts setting the children ids under the Parent record which was invoiced.

I may have a light version of Multilevels in my hardrive...

MultiNiveaux.zip

[Ugo DI LUCA]

Also see this thread for just printing the reports if it may help.

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=68220

[Ugo DI LUCA]

And the Super-Light version here attached in case it may help.

MultiLevelsLight.zip

[bruceR]

The book "Advanced Filemaker Techniques for Developers" by Moyer and Bowers covers exactly this situation, and has lots of other useful information.