Value list to inventory?

[Steve Gunther]

I need some pointers. My shop sells gelato. There are 80 possible flavours. I am creating a db which tracks production and inventory. I created a list with all flavours, and then every day the worker can check the flavours they produce. I have a field which then counts total number created, time taken etc.

But I also want two more things. I want to generate a report at the end of the week, of the number of each flavour created.

And, I also want to track inventory levels of each flavour, preferable displayed in a table so the worker coming on can at a glance see what is left of what flavour and what needs to be made.

So, is there some decent way to convert my simple checkbox list into an inventory process?

Or should I just create a field for every flavour, and have them enter directly into that the production for the day into a new daily record. I would then have to create total fields for each flavour. Lots of field creation.

If I do have to go down this track, can I create some kind of portal which lists all flavours vertically, instead of just pasting them horizontally in a layout?

I tried fiddling with scripts to translate from the checkbox list to something else, but it all started getting too complex.

- Steve

Edited December 25, 2007 by Guest

[Ocean West]

YUMMM, Gelato!

I would recommend instead of using FileMaker's built in value list is to create a separate table for your flavors in a portal. This will give you the most flexibility, to generate reports.

the table/portal should also include a production date. then you could filter that portal by date range and find all flavors produced in a date or in a range.

However to view "negative" info like say you didn't produce strawberry cheesecake on a certain day you should still create a record for that date and put a quantity of 0 pint (or what ever measurement) so that you can see what wasn't produced - if that makes sense.

Typically inventory systems should be "transactional" think like your check/cheque book. You make deposits and withdraws, and on occasion you have adjustments (due to spoilage). Your inventory should be made in this fashion. You create a deposit when you produce the gelato and put it in the freezer for sale. + xx pints. Then when you sell it you need to create another record to deduct - xx pints sold. The SUM of debts and credits would give you the total on hand. This assumes you only track whole units.

hope that helps.