I am still trying to wrap my brain around the concept of a JOIN TABLE.
I have an inventory database for a restaurant. I create a new
inventory item, i.e. olives. I have a table of locations for storing
inventory.

When I print the count sheets I want the olives to show on multiple
sheets. Potentially olives will be in the dry goods storeroom when
they are first received. The kitchen has some in the cooler that they
use to make snake trays for functions. The bar(s) have them as a
garnish for the martinis.

I would use a join-table since one inventory item can be in more than
online location and one location can hold more than one inventory item.
The inventory table holds the detail for all inventory items. There
would be a unique serial number per item.

The location table has fewer records with one record per location, with
a unique number for each location. Are these fields linked to each
other? Does the Join-Table have at least one record for each inventory
item, but multiple could happen when an item is in more than one
location? I would print the count sheets from this file?

Am I thinking of the join-table the correct way?
TK

TKnTexas a écrit :
Il does not necessarily have one record for each item, for instance an
item could be temporarily out of stock and not referenced in the join
table. But generally you're right in saying that multiple records would
reference the same item (one record per location), and the same applies
to locations (one record per item).

Usually these join tables need only a few fields, the IDs from both
parent tables (item_ID, location_ID) and fields to hold what is specific
to that "itee in location", such as quantity in your case I suppose.
Records are usually created through portals in either of the parent
tables, whichever is more appropriate. From the inventory side, you
could go to the record for olives, create related records for each
location, and enter the quantity. Likewise, from the location side, you
could select the record for the bar, and create related records for each
item located at the bar. Either way, the join table records would end up
storing exactly the same information, IDs from both parent tables and
the quantity.

Just to make sure - you asked whether the ID fields are linked to each
other. Not directly, as in establishing a relationship between 2 fields
in distinct tables. Rather, the join table::location_ID has a
relationship with the location table::location_ID, while the join
table::item_ID is linked to the inventory table::item_ID.
When you create a record through a portal, the match field is
auto-populated by Filemaker. So, from the inventory side, you create a
related record by entering the location_ID (or maybe by selecting the
location name in a pulldown menu, and having Filemaker store the ID
instead of the name), and the item_ID is populated automatically. From
the location table, you would enter the item_ID (or select in a pulldown
menu) and filemaker auto-enters the location_ID.

As you said, you can then print the count sheets from the join table, as
a report, sorting by either location or inventory items. Sorting by
location, any item that is present in more than one location would
indeed appear under each of the location.

This issue came up yestarday in a thread about client payments vs job
payments. John Weinshel gives other examples as well as better worded
explanations. (That english is not my nother's tongue is no excuse. John
could also provide better-worded explanations in french

For someone whose native language is not English you did excellent. I
am printing this to study. But between the two examples I am getting
there.

Out of stock is not an issue for the phase that I working on.. just
setting up the master inventory records. If I have an inventory item
that will only be in one location EVER, does a record need to be in the
Join-Table. My ultimate tool is to print physical count sheets for all
locations by locations.

Most hotels and restaurants do not perform perpetual inventories for
the whole inventory. However, they may pick key items (6-8) that they
track. Beverage inventory is a little easier to track perpetuals on
.... from the main storeroom.

However, they do complete physical inventories at least twice a month,
some restaurants do it weekly. Having inventory sheets by location
would mean not having to flip back to another set of sheets to record
items.

After I complete the inventory, I will tackle the B&B application a
friend wants next year when he opens his.

Thanks again Marc-André
TK

Marc-André Paiement wrote:

There are three components to inventory: the starting quantity, what goes
out, and what comes in.

'What comes in' can be tracked by incorporating the vendor packing slips, or
some other form of intake data entry, into the database system.

'What goes out' is harder in a restaurant, but not that much harder: the
orders, or whatever is used to generate the bill, must be connected to the
database system, in susch a way that it accurately reflects 'what goes out'
for the items you want to track. The hard part is stuff not explicitly part
of the check, such as catsup, oil, etc. Breakage, loss, spoilage, returns
are harder, but not that hard.

'Starting Quantity' is the result of that twice a month inventory check. At
that point, after the first time, you can reconcile what's actually on hand
to what the computer says is on hand, and, perhaps, fiddle with the database
to get it to work better. The real problem with any kind of inventory system
is human interference-- people in a hurry grabbing something they mean to
replace later, pilferage, the manager throwing some extra meals to the
waitstaff and not entering it into the computer, etc.

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Certified For Filemaker 8
Certified For Filemaker 7


"TKnTexas" <tkntexas55 (AT) aol (DOT) com> wrote

For someone whose native language is not English you did excellent. I
am printing this to study. But between the two examples I am getting
there.

Out of stock is not an issue for the phase that I working on.. just
setting up the master inventory records. If I have an inventory item
that will only be in one location EVER, does a record need to be in the
Join-Table. My ultimate tool is to print physical count sheets for all
locations by locations.

Most hotels and restaurants do not perform perpetual inventories for
the whole inventory. However, they may pick key items (6-8) that they
track. Beverage inventory is a little easier to track perpetuals on
.... from the main storeroom.

However, they do complete physical inventories at least twice a month,
some restaurants do it weekly. Having inventory sheets by location
would mean not having to flip back to another set of sheets to record
items.

After I complete the inventory, I will tackle the B&B application a
friend wants next year when he opens his.

Thanks again Marc-André
TK

Marc-André Paiement wrote:

John, thnks for that explanation. However, I do not know any
hospitality operation that carries their processes to that level.
Several years ago I was assigned a project to evaluate the software
used by Mrs. Field's Cookie Stores (written by her husband's company).


At the end of the month their system could tell you how much of the
food costs variances were wasted by ingredient. Even as a national
casual dining chain, we were not that interested in the level of
detail.

For most operations, the product purchased is charged to the
appropriate costs of goods sold account. At the end of the period
inventory on the balance sheet is adjusted to actuals based on the
physical counts.

My goal is to print count sheets by location. Currently I have had
each inventory item assigned to one location, not wanting to duplicate
the inventory item. But with a join-table I can have the item show
multiple times.

So when using a join-table between inventory items and locations all
locations are assigned in the join table?
TK

John Weinshel wrote:

Interesting-- I think you are saying -- correct me if I'm getting it wrong--
that only the really big brand name chains would get value from very
granular inventory tracking, but your company prefers to simply balance the
COGS against actual inventory counts, and not try to track incoming, either
by watching the vendor invoices or by taking stock as it's entered into
inventory. Is that close?

Even if I've got that right, I'm still confused about what you would, then,
track: what constitutes an inventory item? Is it pounds of beef? Gallons (or
bottles) of catsup?

Let's say an inventory item is a beef patty. I guess you could make a
'Count_Sheet' join table between a location and the inventory item, where
each 'Count_Sheet' would hold the information for the end of the period
stock on hand for that item for that location. So, it would have the
location ID, the inventory item ID, the date, the quantity, the quantity
attribute (each, pounds, dozen, gross, etc.), maybe an ID for who signed off
on the inventory. Combined with the COGS and sales data by inventory item
for each location, you should be able to build pretty good reporting about
what items are doing well where, what's costing too much, etc.

But if the sales data doesn't drill down to beef patties, dealing only with
items as they're shown on a customer check ('Burger Deluxe, no fries'), then
it's harder. You'd still get, from the inventory checks and Count_Sheet
records, your real margins, but you wouldn't get any data about non-sale
loss (waste, loss, theft, spoilage).

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Certified For Filemaker 8
Certified For Filemaker 7


"TKnTexas" <tkntexas55 (AT) aol (DOT) com> wrote

John, thnks for that explanation. However, I do not know any
hospitality operation that carries their processes to that level.
Several years ago I was assigned a project to evaluate the software
used by Mrs. Field's Cookie Stores (written by her husband's company).


At the end of the month their system could tell you how much of the
food costs variances were wasted by ingredient. Even as a national
casual dining chain, we were not that interested in the level of
detail.

For most operations, the product purchased is charged to the
appropriate costs of goods sold account. At the end of the period
inventory on the balance sheet is adjusted to actuals based on the
physical counts.

My goal is to print count sheets by location. Currently I have had
each inventory item assigned to one location, not wanting to duplicate
the inventory item. But with a join-table I can have the item show
multiple times.

So when using a join-table between inventory items and locations all
locations are assigned in the join table?
TK

John Weinshel wrote:

It becomes an issue of labor and cost value. In the national chain
where I was, the menus are standard across the nation. Pricing for the
food inventory items is the same with the exception of fresh produce.
There are six to eight regions in the concept, depending average costs
of produce items is used for units in a particular region. Based on
items sold from the POS system a theoretical food costs is calculated.
This is measured against the costs of food sold calculated by the
ever-true BEGINNING + PURCHASES - ENDING.

Each concept has determined for them what is an acceptable variance of
waste. For one concept with a greater number of inventory items and
higher volume of sales, 1.5% off theoretical is accepted. For the
smaller concept the percentage used was .5% off theoretical.

The smaller concept is a steak house. They purchased their steaks
precut. The kitchen manager counted the steaks four times a day
comparing totals from the POS to account for every steak purchased. A
mistake was not trashed til verified by the manager. The larger
concept implemented the steak count system but for three times

I was with this company for 10 years before returning to the lodging
industry as an assistant controller. When I suggested some of the
controls the Executive Chef through me out of his kitchen. Food costs
was budgeted at 31%, the actual was 27-28%. Since they were doing so
well they did not perceive the need to do more than they were doing.
To do more would have costs labor hours and they saw no reason to
justify.

I implemented a system for beverage costs, similar to the restaurant
chains food theoretical. This was for a bar with some food service.
The owners thought they were doing well to run a bev costs of good sold
percent that was at or slightly better than industry standard. When I
presented my analyses that theoretically they should be running 2.5%
percentage points better, I was first greeted with a little hostility
(that I would accuse them of mismanagement) and this was followed by
surprise. A computerized pour system was installed, two bartenders
quit the same day, and with the first two months of operations with
system we saw an improved bottom line of nearly 3%.

In addition to the theoretical costs system I put in (btw using
Filemaker Pro 3) I implemented a key item count (similar to that done
for the steaks). I counted only the bottle water. I did a physical
count of bottle water on each Monday. After four weeks I should the
owner the number of bottles of water he was missing. It was something
like 4 cases. He shrugged it off.. only a case a week.. only $0.53 a
bottle. As I left his office I reminded him that the internal controls
on water were the same as bottle beer.

The hospitality industry works off of very slim margins. The F&B Costs
Controller position was eliminated from most hotels (except the VERY
large) back in the '70s. The technological marvels of retail have not
worked their way to the hospitality industry. It is something I hope
to change in my small little world.
TK


John Weinshel wrote:

Thanks, TK-- an illuminating description. Sounds like losing those 2
bartenders was a good thing!

--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Certified For Filemaker 8
Certified For Filemaker 7


"TKnTexas" <tkntexas55 (AT) aol (DOT) com> wrote

It becomes an issue of labor and cost value. In the national chain
where I was, the menus are standard across the nation. Pricing for the
food inventory items is the same with the exception of fresh produce.
There are six to eight regions in the concept, depending average costs
of produce items is used for units in a particular region. Based on
items sold from the POS system a theoretical food costs is calculated.
This is measured against the costs of food sold calculated by the
ever-true BEGINNING + PURCHASES - ENDING.

Each concept has determined for them what is an acceptable variance of
waste. For one concept with a greater number of inventory items and
higher volume of sales, 1.5% off theoretical is accepted. For the
smaller concept the percentage used was .5% off theoretical.

The smaller concept is a steak house. They purchased their steaks
precut. The kitchen manager counted the steaks four times a day
comparing totals from the POS to account for every steak purchased. A
mistake was not trashed til verified by the manager. The larger
concept implemented the steak count system but for three times

I was with this company for 10 years before returning to the lodging
industry as an assistant controller. When I suggested some of the
controls the Executive Chef through me out of his kitchen. Food costs
was budgeted at 31%, the actual was 27-28%. Since they were doing so
well they did not perceive the need to do more than they were doing.
To do more would have costs labor hours and they saw no reason to
justify.

I implemented a system for beverage costs, similar to the restaurant
chains food theoretical. This was for a bar with some food service.
The owners thought they were doing well to run a bev costs of good sold
percent that was at or slightly better than industry standard. When I
presented my analyses that theoretically they should be running 2.5%
percentage points better, I was first greeted with a little hostility
(that I would accuse them of mismanagement) and this was followed by
surprise. A computerized pour system was installed, two bartenders
quit the same day, and with the first two months of operations with
system we saw an improved bottom line of nearly 3%.

In addition to the theoretical costs system I put in (btw using
Filemaker Pro 3) I implemented a key item count (similar to that done
for the steaks). I counted only the bottle water. I did a physical
count of bottle water on each Monday. After four weeks I should the
owner the number of bottles of water he was missing. It was something
like 4 cases. He shrugged it off.. only a case a week.. only $0.53 a
bottle. As I left his office I reminded him that the internal controls
on water were the same as bottle beer.

The hospitality industry works off of very slim margins. The F&B Costs
Controller position was eliminated from most hotels (except the VERY
large) back in the '70s. The technological marvels of retail have not
worked their way to the hospitality industry. It is something I hope
to change in my small little world.
TK


John Weinshel wrote: