stock control database, pelase help

[portarello]

I am attempting to create a database for stock control at a venue. The venue contains multiple bars and multiple storage areas for the stock. Not every item is stored at every storage area, as the different bars offer different menus.

Creating the table of stocked items is straight forward enough, here's the problem: We want to use the database for regular stocktake, whereby a new record in a related table is created for the stocktake at each storage area on a particular date. Essentially, we want to pull the 'item name', say, into the stocktake table, and be able to add quantity information for each item which is unique to the new record. The resulting stocktake records will then be used to generate orders, track sales, etc. Is this possible, and how best to proceed?

Ideally, only the items available at a given storage area would show on the stocktake record. This is easy enough to achieve using a portal filtered by some location code, but am I then able to add the quantity information? It seems to me that the only way to do this would be to import the 'item name' entries as field names in the new record, in which case all records would have to show all items, if this is even possible.

I hope the problem is clear, any suggestions would be most appreciated.

[portarello]

I might add that I have considered the approach of creating a separate table for each storage area, containing only the items available at the associated bar/s, whereby each item is a new record and the record contains a quantity field. It would then be a simple matter of overwriting these 'quantity' values during stocktake, and generating a consolidated stocktake report, order, or whatever. However surely there is a more elegant solution, which would not, for example, require multiple entries of a new item, and where the individual stocktakes may be archived as a record rather than a report. Ok, thanks.

[efen]

I don't fully understand how you want this organised but I would suggest that you need several more tables for what you want to do than the two[?] that you mention in your post.

You don't need a separate table for each location - just one table of locations with each location as a record. But are bars and storage areas separate units or do the bars each have their own storage area?

[eos]

If I understand you corrrectly, you need, in addition to the (I assume) existing Item table, to create one table each for StorageArea and Bar.

Then create two join tables:

1) ItemInStorage, with foreign keys ItemID and StorageAreaID, and a field for quantity. Each record tells you which item is available where and

in which quantity.

This table is comparatively “static”; once you have created a record for every item in every storage area, you only add new records when

you have new items or new storage areas (or a new combination, should not all items be initially stocked in all areas), but for the record with

matching ItemID and StorageAreaID, the quantity field will be updated with every new stocktake, i.e. a new record in the next join table.

2) Stocktakes, with FKs ItemID, StorageAreaID and BarID, and fields for quantity and date.

This table is a transaction table and therefore “dynamic” - which means that every single stocktake will create a new record in this table,

but once it's created, it remains unchanged.

With a relationship to the other join table you know if a given item is in stock in the required storage area, and you always have current

stock numbers for each item, in total as well as per storage area. (This would allow for an automated order system, based on treshold and

target values for items).

This should be enough granularity to create all required reports and summaries. Extending this structure in a similar manner is easy,

e.g. to assign storage areas to bars.

[portarello]

Thankyou for your responses. Join tables, eh? Well, this seems more like it. However, this approach requires a new record for every item at every storage area every time we count, yes? Even with drop down lists, this will make stocktake extremely time consuming. We have some 150+ items, 6 storage areas and do ordering every 2-3 days when the venue is operating. Am I missing something?

The individual bars need not be considered at this stage, as the amount of stock in them is insignificant. It seems I can use the above approach with only the first join table, where I just overwrite the quantity data during stocktake. That way, once the records are added, I can just scroll through and punch in numbers, which is what I'm going for. With the addition of sorting and reporting scripts this may be sufficient. Other suggestions, however, would still be appreciated.

Thanks a bunch!

[eos]

Stock management seems to be an important part of your business, so isn't there anyone dedicated (semi-)exclusively to this task?

Anyway, here are some ideas for speeding up data-entry in a StockTakes join table: Either use selection portals instead of pop-ups. If orders are similar,

you could set up pre-defined “packages“, each one consisting of a list of items and their quantity. Swap in an entire package for a storage area, set a flag

for all contained items to be Unconfirmed, then work through the list using your (paper) manifest, adding/removing items and adjusting quantities

according to the actual numbers. When you're finished, set all items of the list to Confirmed.

This may sound like a lot of preparation work, but if your business does profit (directly or indirectly) from recording these data, it will pay off in the long run.

Also, as noted above, with the proper programming you'd not have to make manually adjustments in the first join table, since the numbers can be

derived from summarizing the second join table (or setting quantities via script).

[brian rich]

I set up a system for bar stocking many years ago (not in Filemaker as it happens) on a small portable suitcase computer - about 1982 if I remember correctly

The approach I took was to have a list of the products in each bar (or bar cellar). Another table contained a list of stocktaking events. Between these two tables was a join table to sotcktake quantities.

When you wanted to do a stocktake, you created a new stocktake event in the stocktake table, and then created a new blank entry in the join table for every item in the product list. This list was supplied to the stocktaker on the computer, and he walked around the bar and entered what he found in these empty entries.

To ensure that the numbers entered at the time could be easily checked, I also held the figures from the previous stocktake, the selling price of the bar products, any new stock deliveries to that bar and the cash on hand at the last stocktake. This meant that the stocktaker could run a reconciliation that checked the numbers entered this time were 'reasonable' given the last stock situation and stock additions and also that the sales looked reasonable based on the current cash on hand and bankings. This gave the opportunity to recheck the stocktake if anything seemed to be out of the ordinary.

Once the stocktake was 'reconciled' it could be used to update the master for the next stocktake event.

These days this would be a nice exercise for Filemaker and Filemaker Go on an iPad for the stocktaker.

HTH

Brian Rich

Helpful Systems

Wariwckshire

UK

[portarello]

Ok, this is coming along nicely. eos, how can I access the last (most recent) matching record in the Stocktakes table to update the ItemInStore table? You mention using a summary but there does not seem to be an option to achieve this. If I use a calculation field it accesses the first matching record. Surely I don't need to write a script.

Thanks for all the help!

[eos]

I did not say anything about a summary You create a StockIntake record either by script or with a portal; regardless of the method, at the time of creation you use a relationship to the ItemInStock table

to decrease the item's stock number by the appropriate amount, so you always have the correct numbers.

I have a solution where in my version of the Items table, there is a script to Replace Field Contents for all current records (yes, it's a single-user solution …). I can add StockIntake records manually or as a batch,

and then go to all matching Item records (one or several) and call this script to update the stock numbers (which is faster than looping).

Would you care to post a sample file?

[portarello]

My apologies, you said 'summarizing the second join table' and I misunderstood. Yes I would care to post a sample file (despite the fact that it is hardly fit for public consumption) but it says i'm not allowed. ?.

[eos]

but it says i'm not allowed. ?.

You need to zip your file to upload it.

[portarello]

Oh ok, thanks.

Stock Control v2.zip