Shopping List

[Jammer Six]

My next problem is a shopping list.

This database is for a game, in the game I play at manufacturing, and this database helps me determine what products to manufacture and which to avoid.

I’ve attached a copy of the database.                            
What I want to do now is create a list of all the base materials if I decide to build one product or another.

All the Materials are listed in the table “Materials”, however, the number I need for various Goods is scattered throughout the database in various join tables.

If you take a Material, (for instance, Tritanium) and you go to its record in Materials, I don’t see how to assemble a list of all the Tritanium used in a single Good, because it can be used on its own, and it can be used in various Items that are also used in the finished Good.

And even if you managed to display a list of the different places and amounts where Tritanium is used, how can you total that, and display it on one line, so you can create a list of all the different Materials and their amounts for a shopping list?

EVE Manufacturing.fmp12

[comment]

First, you have a misnomer: the thing you are are trying to produce is not a "shopping list". It is a BOM (Bill Of Materials). Look it up, it's a thing.

Next, having the numbers you need "scattered throughout the database in various join tables" is not a good idea. The preferred structure is a table of Items that lists all materials, sub-assemblies and final products. This table is joined to itself in a many-to-many relationship (one item can have many components; one item can be used by many assemblies), using a single join table. On Filemaker's relationships graph, this could look like:

Now, to produce the actual BOM for an assembly, you can either drill-down from the assembly to its components, and then to their components, and so on until you reach the raw materials (i.e. items that have no child components), or let the quantities "bubble up" from the components to their parent assemblies. Neither one of these is particularly simple, and if you're not at least an intermediate-level developer, then this is not a good project for you. 

I will roughly outline the "bubble-up" approach because I believe it is easier. However, it needs to be said that this is a cascading calculation and Filemaker places a limit (a few hundred, IIRC) on the length of the chain of a cascading calculation. The basic idea is that each record in the items table computes a list of its components and their qualities. And each record in the join table multiplies the quantities listed in its component's list by its own quantity, and passes the result to its parent assembly record to compute a new list. The list itself could be in a text format (a row for each entry, with a separator between the quantity and the item), or it could be two return-separated lists (one for quantities, one for item IDs), or it could be JSON.

So eventually each item has a list of all the raw materials used to produce it and their quantities. However, the list may easily contain multiple entries for the same material. These entries need be grouped and summarized - and again, this could be done using several methods - for example, by using a technique known as "virtual list" table.

Hopefully this can help steer you in the right direction - or scare you off, which sometimes is the correct path to take.🙂

 

Edited March 14, 2021 by comment

[Ocean West]

@comment that's a great explanation for BOM.

I usually have to go the route of What's in a McDonald's Happy Meal?.  -  Toy, Fries, Hamburger. Sometimes they get that, but then I say, what goes into the hamburger? Bun, beef patty, ketchup, mustard, pickle. Then they are like 'Oh hadn't thought of that'  the depth to this can continue as set by the business rules depending on the goal.

In the above example what about the box, and the wrapper for burger, and container for the fries these sub assemblies are enclosed in, there is a supply cost to these, along with storage requirements and lead times to acquire. This data is needed so you know you have enough on hand to produce the final assembly.

And if you really wish to get in to the weeds there you can go even further such has the flour, yeast, and other dry goods to make the bun, and or other individual ingredients if you are manufacturing them as well.

Not to mention the time component or even the energy foot print to cook each individual product. - most things are made in bulk granted but there is a finite time and cost attributed to each element in the BOM so that at the most highest level you can potentially roll this up compute a potential cost / time factor which in some businesses use this for forecasting labor and and purchasing needs.

 

 

[comment]

3 hours ago, Ocean West said:

the depth to this can continue as set by the business rules depending on the goal.

True. Specifically for a BOM, the chain ends (or starts, depending on from which direction you are looking at it) with items you purchase from outside sources. So if you buy your ketchup, then ketchup is a raw material and has no further components. If, OTOH, you make your own, then it continues to sugar, water, food coloring and (possibly) tomatoes.

However, the same design pattern is applicable to many other situations - so if for example your goal is to compute the ecological impact of manufacturing a product, the chain would extend much further. 

 

Edited March 14, 2021 by comment

[Ocean West]

That remind me of some thought experiment where they took something simple a margherita pizza.

We all know the basic ingredients but they extrapolated back to the effort behind one pizza.

The acres for the cow to make the milk, plus all that it entails to raise dairy cow. The space to grow the wheat and other produce including the olive tree to produce olives so you can press olive oil. Any ancillary requirements such as a beehive needed for crop pollination etc.

What is interesting that would have been the most expensive pizza in the world.

 

[comment]

1 hour ago, Ocean West said:

that would have been the most expensive pizza in the world.

Well, yeah - if you only made one. In reality, all those expenses are divided across many pizzas.

Also, I don't know about milk, but we are very close to growing meat in a lab, saving enormous resources + reducing methane emissions.

 

[Jammer Six]

@commentYou won't "scare me off".

I'll just leave here, and go somewhere where mothers taught manners.

Having run forums, I understand that leaving a forum and never going back, and telling other folks why are the two worst things a subscriber can do to a forum.

Good luck, and goodbye.

[Steve Martino]

1 hour ago, Jammer Six said:

@commentYou won't "scare me off".

I'll just leave here, and go somewhere where mothers taught manners.

Having run forums, I understand that leaving a forum and never going back, and telling other folks why are the two worst things a subscriber can do to a forum.

Good luck, and goodbye.

Lol. You were given an excellent explanation and excellent advice, by easily one of the best. Comment took his own time, volunteering for free, as we all do to provide you with help.

You ignored all of it and took the last sentence out of context. 

I think it’s you who will need the 'good luck', and maybe take a refresher course on manners-which now I may also need...lol

Edited March 22, 2021 by Steve Martino