2 Replies Latest reply on Feb 23, 2015 6:36 PM by FentonJones

    Advice on building a grocery database



      Advice on building a grocery database


      Hi this is my first post, excuse me if I am in the wrong place.


      I am trying to set up a database to track and compare grocery store purchases and prices. It is for my personal use only - meaning I will be the only one entering data and benefiting from this,,, maybe my wife too. There are many iApps out there already but none that I know of that run on my desktop.

      Since I am new to this I am having difficulty deciding on the structure, but I have some ideas. In order to keep data clean I have read I should use value lists and populate them manually during the design stage. To me that seems like a lot of extra work up front, i.e. putting every possible item and variety I could possibly purchase from  multiple grocery stores into a value list!

      Some fields I think should be included:

      store, department, item, house brand/branded, description, type, organic/inorganic, unit, qty, price, date

      Some of the fields would have few enough choices that a value list would be sane; store, department, organic/inorganic, unit.

      The others,,, how to build conditional value list for each of; item, description, and type without entering values up front, but as data is entered? Is this even necessary?

      Another idea is scanning receipts and using data in the receipts to build up the data. This would be great since we have a ScanSnap which scans receipts

      The next steps, charting the trend in prices for each store over season/time; calculating any savings from one store to the next. Shopping lists based on store? Factoring in membership costs?

      I would appreciate any input, advice, general guidance on what's necessary and what's not.


        • 1. Re: Advice on building a grocery database

          Hi, this Managing Inventory using a Transactions Ledger is place to start understanding what will be needed in you solution. It was very helpful in getting me started in understand how build a solution for things like items that will go in and out of your database/solution. See what you can take away from it, and then post more questions related to to what you want to do. The post's creator and other members will likely comment on your question you have.

          • 2. Re: Advice on building a grocery database

            The simplest way to get a Value List using entered values is the option: "Using values from field"; then choose the Table (actually its Table Occurrence name, which is on the Relationship Graph). Next, keep the default option below, (•) Include all values.

            Attach the VL to the field on the layout. You can then either chose an existing value in the list, or just click the field, and type a new value. It is very important to choose an existing value, if possible, to keep clear of "different name for the same darn item" (which makes many things not work as well as they should). Value List automatically shows a unique list.