Perpetual Inventory - Extracting totals of goods/ingredients used

[AndyCan]

Hi Guys,

I wonder if someone could point me in the right direction as i'm finding it difficult to structure this part of the DB.

The solution i'm working on is part of an inventory system for a bar. Every menu item has it's own record in a MENU table with respecive price, category, etc. and this is linked to a RECIPES table where the contents of each drink is listed (eg. a martini may include a number of different liquors). The RECIPES table holds the foriegn keys of a PRODUCTS table and obviously a field for the quantity of product used.

Two other tables, SALES and SALES LINES, register each and every sale made, using a foreign key that references the MENU table.

What i would like to do is set up a perpetual inventory so that for each sale, the respective stock of each product is reduced to reflect the amount used in the preparation of the drink. The starting stock level is established by a physical inventory done every monday which i already have working. This way, every Monday we can compare the physical inventory and the perpetual inventory and acertain the amount of liquor lost to wastage or theft. The perpetual inventory is then reset to use the latest physical inventory totals and thus an accurate stock level is established on a weekly basis from which to add and subract with respective deliveries and sales.

Given that the base stock level is reset weekly via the physical inventory, i don't think it would be too processor intensive to simply use calculation fields for the perpetual inventory totals. The SALES LINES could be filtered by a date range with the starting date being when the ultimate physical inventory was taken.

What i can't figure out and where i need your help is how to structure this so that i can calculate the total amount of product consumed. How do i relate the tables SALES LINES, MENU, RECIPES and PRODUCTS to end up with a list of products and the quantities consumed during the chosen date range.

Specifically i'm confused by the extra step required to translate a menu item into product amounts and sum up the amounts used bearing in mind that the same product (liquor) may appear in many different menu items.

Thanks in advance for all and any help!

Andy

[AndyCan]

Hi Guys,

I'm still racking my brains with this one. I get the feeling that i need some kind of join table between SALES LINES and RECIPES which is filteres/viewed from the perspective of PRODUCTS.

I don't know if that helps clarify my question at all... As i said before, i'm still i bit confused.

Thanks again.

Andy

[AndyCan]

To put it another way...

I need to display records from SALES LINES where the menu item contains product 'x'.

This way i can show all sales for product x in a portal with a grand total calculated in the parent layout. Thus i get the total consumption for each product in the Inventory. (Should the parent layout be displayed from the perspective of the PRODUCTS table? I'm guessing yes).

Andy

[elo]

You'll probably get more help if you post the relationships in your db, but long story short.

In FMPro, you are going to need to create enough table occurrences to "walk" from (connect) SALESLINES to PRODUCTS.

Then all you need to do is generate a layout of quantities for each product during a sales period.

As for reconciling the inventory, you probably need some more tables to store the weekly inventory results and then compare the two values.

[AndyCan]

Elo,

Thanks for the tip. I had come to the same conclusion shortly before seeing your post. I figured there had to be some kind of cascading query by linking TOs.

Here is the working setup:

PRODUCTS:ProductID <-->ProductID:RECIPES:MenuItemID <--> SALES LINES:MenuItemID

This works fine to calculate the total amount of product consumed for the sales registered.

However, this calculates the amount for all sales ever made. At the moment i have no way of filtering the result via a date range. While i understand that i need two global fields to hold the arbitrary dates i select, i'm not sure where to place them. An even larger problem is that the date for each record in SALES LINES is held in the parent table SALES. These of course are connected by a primary key in the SALES records and a foreign key in the SALES LINES records.

My problem is the following: How can i implement this filter by a date range if the date is held in the SALES table?

From what i can tell i need to add a SALES T.O. to the relationships graph and insert it into the chain shown above so that it becomes part of the filter. Somthing like this...

PRODUCTS:ProductID <--> ProductID:RECIPES:MenuItemID <--> :?:SALES:SaleDate <-(multiple match)-> SALES LINES:gStartDate, gEndDate

However, while i can connect SALES to SALES LINES, there is no obvious way to connect SALES to RECIPES.

I get the feeling that this is the wrong way to do it, but i can't see any other way. Where am i going wrong?

Thanks!

Andy

Edited August 10, 2008 by Guest
Cleaned up post

[AndyCan]

Hi guys,

If anyone can help, this has not been resolved yet. Thanks for your time!

Andy.

PS. I've attached an image of the relevant section of my relationship graph where i tried putting the SALES to after the SALES LINES to and hoped that this would filter the SALES LINES to, but it doesn't seem to be doing so.

The Master Products and Mprods tos have the same base table and their purpose is to only show those items that we have chosen to track via a global field.

Thanks

Edited August 11, 2008 by Guest

[AndyCan]

Hi, I still need help with this. Please reply even if just to tell me that i need to rephrase my question to make it easier to understand.

Thanks again!

[Fenton]

A Product is composed of several ingredients. A Recipe for that product would have the ingredients,* the amount of each ingredient to make 1 product. I don't know whether you'd do that with "parts per", or "oz" or what.

But if it's "parts per", then you'd need an volume for the product, and the units would have to consistent (liquid) or you don't have anything really to measure. If it's not "parts per," then each ingredient could have its own units.

Recipes would need to be accessible from both Sales and Inventory (though possibly as disconnected table occurrence groups),** as each deals with Products; but a product is the sum of its ingredients; you don't measure the product (though you would multiply times its quantity), you measure the ingredients.

**I'd have Sales and Inventory in separate table occurrence groups. It would cut down on confusion if nothing else.

So I'm kind of wondering what's wrong. I cannot tell from the picture; especially I can't tell what your Recipe table is, as the name "menu item" doesn't jibe with the "ingredients" that I'm thinking of. I would want to see an example file of this. It is too hard to follow in words and pictures (well it might not be too hard to follow if it worked; but there's the rub, as they say -)

*Whether an ingredient can also be a product itself, or whether Ingredients and Products are never the same is another important question. This determines whether Recipes is a "join" table (an ingredient is a product) or a "child" table (an ingredient is never a product).

[AndyCan]

Hi Fenton,

Thanks for your reply. I can see that i wasn't completely clear in my question. Each ingredient is in fact a product. Products themselves are never sold. They are the base materials for the 'menu items' that we sell.

Perhaps a real world example will help.

In our menu we have a 'menu item' called Lychee Martini. This drink is made up of different products such as Lychee Liquor, Vodka, etc. as we buy them from suppliers.

So here is a run-down of the tables with real world examples:

PRODUCTS (Absolut Vodka, Lychee Liquor, etc and contains product details such as Alcohol percentage, Volume, etc.)

MENU (Lychee Martini, Cuba Libre, Cosmopolitan, etc and includes pricing details, etc.)

RECIPES (References MENU and PRODUCTS and contains quantities of each product referenced)

I haven't got much time right now, but i'll check in again later and expand on this... Hope it helps!

[Fenton]

That makes perfect sense. I'm just more used to "products" being what is sold, and "product components" being the parts of a "product".

It seems to me that you are very much on the right track. That is why an example file from you would be very helpful, to see where you're missing something.

It is also true that the relationship/calculation at the end is going to need a date or timestamp range, as you only want to see sales within a certain time span.

[AndyCan]

Ok, great!

I'll put together a simplified example file tomorrow so as not to confuse with you with the whole DB. Who know's maybe i'll solve the problem in the process!

Thanks again,

Andy.

[AndyCan]

Hi Fenton!

Ok, here's my example file. I had to zip it to be able to upload it to the forum. You'll notice that the calculations for the total Mls of each product consumed is somewhat broken. I'm not sure if it's because i was unable to reproduce what i did with my master database or if this means that the calculations in the master database are also broken. Due to the sheer quantity of sales, it's hard to tell.

You'll also notice that i'm still having trouble getting the global date field to have any effect as far a filtering the results is concerned.

Many thanks for your time in looking at this! The sooner i get it working, the sooner i can sort out some troubles we're having in the bar!

Andy.

example.fp7.zip

[comment]

This seems very similar to:

http://www.fmforums.com/forum/showtopic.php?tid/192677/

[AndyCan]

Thanks Comment,

I had a quick read through the various links, but while i'm still struggling to understand the concepts, it's even more difficult to understand when viewed from the perspective of someone elses post. I'm afraid i don't even understand the other posts and i'm not even sure where the examples coincide!

I'm sure it would be obvoius with more experience, but for the moment i'm still a bit of a newbie...

Andy

[comment]

Short version: this is not an easy thing to do. It will be even more difficult here, since you want to limit the result to a date range.

[Fenton]

I think this might do it. Though my brain is slightly fried by this time of day.

Basically, only the Recipe table knows how much of each product is in a Menu Item. Only the Sales Lines knows the Qty (Menu Item quantity). Sales Lines also has to know the Date (I brought it in from Sales).

The global dates to filter the relationship therefore must be in a Recipes TO, looking at a Sales Lines TO.

Using the relationship the Recipes TO (in the Inventory table occurrence group, TOG) can then Sum ( Qty ) over that date range. It can then multiply that times the ML of product per to get the total ML sold for that date range.

Unless I'm completely wrong -]

Bar_example.fp7.zip

[comment]

Well done. I was mistaken - this is not like the other thread (it would be, if the purpose was to get a breakdown of ingredients for each sale).

Cheating on the dates, though. Here's one that doesn't.

LiquorInRange.fp7.zip

Edited August 16, 2008 by Guest
simplified the relationships graph

[AndyCan]

Thanks guys, you were both a great help and a special thanks to Comment for the previously attached file. I had forgotten about that little trick of using a calculation field to filter by date.

I decided to redesign the database over the weekend as it was getting too complicated. Having imported the data i now have everything in a single file (no separation of layout and data), no old irrelevant fields and a much neater Anchor Bouy layout in my relationship graph.

Thanks again!

Andy.