2 Replies Latest reply on Jun 21, 2010 9:46 AM by Luc

    tracking inventory in a wine cellar



      tracking inventory in a wine cellar


      I am using FMP 10 on a Mac OS X v. 10.? (latest). Curently, application runs on standalone computer, not on a network.

      I am not very experienced with FMP. Used to work with Access relatively simple applications.

      I am developing a database to track inventory in a sizeable wine cellar. I have defined various tables, which can be categorised in wine related tables, purchases, sales, consumption/usage/breakage and locations in cellar. Every bottle needs to have an unique location in the cellar.

      I do not understand how to develop the data base to enter a purchase (or sale/consumption) of multiple bottles and at the same time allocate each bottle individually to a location in the cellar.

      Cna anyone help to explain how this should be done?



        • 1. Re: tracking inventory in a wine cellar

          Basically, it would be a Loop(s) in a script. However, there is no way to do it until you decide how you're going to number and label the racks that the wine goes into. 


          The first thing is this: Each bottle gets an auto-entered non-changeable incrementing serial ID, generated by FileMaker, via the auto-enter options. This is NOT its "location" or anything else. It is NEVER changed. Nor is any other data added to it. It is what is used internally to identify that particular bottle, now and forever. 


          Now, the racks, that's a different story. The "location" of the bottle is based on the way you number/label the racks. It is a separate field from the above ID field. I really don't know how you'd label the racks. It could be like a giant spreadsheet, with Rack# (if separate), then Columns and Rows. Or just columns and rows. If the the "rack height" is 26 or less, you could use that for the Rows (opposite of a spreadsheet), allowing unlimited columns; or just go with the usual A1, A2,...AA1, AA2, etc..


          Considering that you would be putting a batch of new bottles into unbroken open spaces at the end, you'd need a loop that would fill down or across, until it reached the edge, then started again on the next column or row. So it would be a "loop within a loop". Not too hard, unless the racks are separate and different widths/heights (in which case you'd need a Racks table with that data; may want it anyway). 


          Basically the number/labelling of the location depends upon the physical layout of the racks. It would be changeable; but a bit of a PITA to do a lot of that later.

          • 2. Re: tracking inventory in a wine cellar

            Thank you Fenton. I probably did not explain it properly or I do not understand your reply. I have sorted the locations in the cellar out. Let's say there are 2,000 unique locations which I can put in a table or a value list. 48 bottles of Chateau X are bought. The wine is in the database already. So in principle I could enter bottle by bottle and add a (free) location for each bottle. However, I would like to enter 48 bottles as one purchase and then link bottle by bottle to a location. Once a location is taken, it should not be available for another wine until it is free again after the bottle has been sold/consumed.