Multiple stock from line item

[Oyseka]

I am trying to design an invoice and stock system for a friend who has a bar and resteraunt and have now found out how limited my knowledge is. Of the 360 items sold, about 80 have multiple stock ingredients. How can I show the correct stock.

I have the invoice file related to the line items file and the line items file related to the items file

[Oyseka]

Excuse me if I did not state the problem clearly. The Items file contains an item called "Bowl of Dragons ***" and is priced at R45. This Item consists of 1 x Orange Juice, 1 x Vodka, 1 x Schnapps, 1 x Tequila, 1 x Blue Curacao and 1 x Malibu. While the Items file contains this product, the stock file only contains the various ingredients priced per unit.

[Slobey]

I'm not sure I understand you completely as to what you want to do, but you may want to create a 3rd table as a Item Line item table which is related to the stock table by a Stock ID and to the item table by a Item ID. Then create a portal in your item table.

[Oyseka]

I have an invoice file, an items file, a line items file and a stock file. The invoice file is related to the line items file by the invoice number, The line items file is linked to the item file by the item code. The problem is that an "Item" for invoicing purposes contains more than one stock item. For instance a Pizza is one item for the invoice, the item file and the line items file but it contains many ingredients which have to be deducted from the stock and I can't figure out how to make this one item take a measure of cheese, onion, salami, peppers, dough, puree etc from the stock for all the various compound items. I then have to apply this solution to all 60 menu items and also certain drinks which are compound

Chris

[chemlady]

In my opinion, you need two additional tables; one will have recipes for all your "finished" products (let's call it Recipes) and another one - RecipesLI. Each recipe will show the ingredients used, their cost and amount through a portal from RecipesLI. Then you'll need a relationship from Recipes to ItemLI and from there to Invoices.

I can provide an example file, but I'm using FM Pro 7.

[chemlady]

Ooops, you're FM Pro 7 too... Overlooked. I'll post the file a little later today.

[Oyseka]

But would that not mean that the portal in my invoice wouldhave to related to both the line items and the recipes line items.

Chris

[chemlady]

Sorry, for the delay. Never got to it yesterday. This file will give you an idea. Guest account has full access.

As to your question, I would pull them all, compounded and single ingredient products, through the recipe table. You will have to figure out how to do that with different units of measure. In the example file I did not account for that.

Oyseka.zip

[Oyseka]

Thanks for the file, it will fit the bill nicely with some mods. I am having trouble getting the product name first in the invoice instead of the code to allow ease of product selection.

[Oyseka]

Thank you for your assistance, it is greatly appreciated. I have added a new stock table and denoted the Weight field as a "Sales Unit" . This allows for all classes of product whether it is a weight or a measure of volume and the price per pound is now price per unit i.e. 1 litre of whisky or 1 Kg of cheese which divided by the sales unit gives a number of portions. However I do not understand how you have brought into the invoice in the "Product_ID_fk" field both the product ID number and the Product name from the Product line items table as this field in the line items table only shows the ID number. Could you explain this please as I may have to re-do this in FM 5.5 because the user is on Windows 98. Thank you in anticipation

Chris Courtney-Stones

[chemlady]

Product_ID_fk field is formatted as a pop-up list. Go to Define Value List, and select Products Value List. There you'll see that this value list uses values from Product_ID_fk field and Name field from Recipe table. So it displays both fields but only the first field displayed will be entered into the field that is defined as a pop-up list. You can sort them either by first or second field.

Hope this helps.