Please help, newbe

[Monger]

I have used FM for years as a simple storage solution but suddenly I have to deal with relationships and after two days of trying and reading I am nowhere.

Here is what I need to do:

I have a database containing about 300 different wines. Each different type of wine has a unique number, the "wine number" and a certain inventory.

I am trying to put together a catalog in a separate table, that shows some of these wines.

Where it get tricky, is that my boss wants to sell some of these wines bundled, - meaning he wants to offer something like a case of three.

How do I set these tables up to where I can maintain an inventory count?

Do I

A. Create a separate record for each of these bundled products with their own unique ID?

If so, the ID would have to be different from the "Wine Number" to be able to still do an inventory count, right?

Would I then pull in the different wines (of the bundled product) as a portal?

B. Can I just pull the different wines into a record in the product catalog by setting up a portal there? I tried that but failed at figuring out how to set up a relationship that makes sense. I guess this is not an option at all.

C. Is there a smarter way of doing all this?

Thanks for any help,

Stephan

[Fenton]

I've just been converting one of my invoice solutions to version 7, that has this "bundling" going on. Mine is a bit more complex, because it changes the inventory via a script (so the result is stored). You can try that next, but it's not essential, at least not at first.

Basically a "bundle" is a separate product, with a unique product ID just like any other product. But it is composed of several other "sub-products." These may or may not be separate products of their own. In most cases they are, so they would also exist independently in the Products file, with their own IDs.

The way to create a bundle is to have a separate Bundle file (or table in 7). It has two important foreign keys. It has the ProductID of the bundle product (it's ID in the Product file/table), which will be common to the several individual products in a bundle. It also the ProductID of its original product (also from the Product table).

One additional field the Bundle table has is a NumPerBundle, which is how many of the original unique product item are in the bundle, in case more than one. You can ignore if always 1.

The best simple example I've seen of this is an old one by Steve Wilmes. It's not available on his web site anymore, so I'll attach it here. It's only v.5 (it began as v.3). In 7 the bundles, and other line-items files would be tables in their parent file. I don't have a simple v.7 example.

StevesInvoices.zip

[Monger]

Thank you Fenton!

Fortunately my needs are not quite that complex: All we need is a database that allows us to output our products to a comma or tab separated file and upload that file to an order processing software (www.ordermotion.com) and our shopping cart software (www.monstercommerce.com). Since both those solutions have their independent databases, I would want my database to reduce its inventory whenever a part of our catalog is fed into the store (represented by those two databases on Monster Commerce and OrderMotion).

If some wine is being sold but some is still in the warehouse, I will have to reduce the inventory to the actuals in our database before I do another upload of our inventory. But that should be fairly easy and could be done by hand.

From what you are saying, I have come up with this database design and please tell me whether I am on the right track:

Table A for all the wines. Product Name, Product ID (SKU Number) and Wine ID for each product.

Table B for all the bundles. Prodcut Name, Product ID (SKU) for each bundle and Wine IDs are pulled into a portal.

Table C for the catalog items. Product ID (SKU) and Product Name.

The match fields are Prodcut Name and Product ID between all tables.

Do I need a table D for the inventory or is there a simpler way? I tried to figure this out from the example you sent me, but I am not quite comprehending it yet,- I guess.

Thanks again,

Stephan

[Fenton]

I would think that table A is the inventory. There's a little terminology confusion here with the ProductID and the WineID. If WineID is something you enter, i.e., not an auto-entered ID, then it is just another attribute of a wine record, descriptive but not used for relationships; basically we will ignore it, and ProductID is the key.

Table B:

There are 2 instances of a ProductID from table A. In table A a "bundle product" is a product record. It has its own unique ProductID, just like any individual product.

So this ProductID is in table B as the BundleID. It is the same for the individual products in that bundle. However, each record in table B is also an individual product, and it has its own ProductID, which is the same as its individual product ID in table A.

Each record in table B also has a "number per bundle," if any have 2 or more per bundle. If not, it's not needed.

I was just looking at Steve Wilme's example. You will have to study it and set yours up with the relationships and calculations as he did. It makes sense, but it's difficult to explain in detail.

Basically, an invoice line item determines the quantity sold. But, if it's a bundled product, then the quantity is decremented from stock not only for the "bundle product," but also for each individual product in that bundle, according to how many per bundle.

He does this from the product table, A, by including in the calculation a substraction of the product in table B. This provides the accurate result. It would be, however, a bit slow for reports, because of these unstored calculations including other unstored calculations. Fine for seeing one product though.

It isn't the way I set mine up. I used scripts to Loop through the bundle (: and decrement the stock back in A. In some ways the scripted method is easier to understand; and it produces a stored result. Unfortunately my files also include a complex "backorders" module, which I haven't finished converting yet. It's kind of a mess right now. If I had a simple scripted version I'd post it. His version is safer in some ways, because once set up it will always be accurate.

[Monger]

Sorry for the late reply. I had to leave town for a while. Anyway,- I tried to figure this expale out but with my time being very limited (I hope it's just that) I never got anywhere.

I wanted to thank you for your help though.

I decided to create just three tables (all wines, catalog, budles) and I got this to a point that allows me to few the info the way I need it, but I can't export it quite yet. Since Product ID and Wine ID are pulled into flieds through a relationship (and in the latter case into a portal), the export to a tab file ignors all the data that's not sitting in the same table. Well,- I guess this is trivial and I will figure it out eventually but if you have a hint for me, please drop me a line.

Once your invoicing solution is ready I would love to take a look at it.

Thanks again,

Stephan