2 Replies Latest reply on Jan 12, 2015 10:42 AM by yahbai

    layouts and DB structure for daily sales category tracking

    yahbai

      (this is a repeat of a post a couple of hours ago, because I didn't originally put it in any categories, then couldn't add categories after posting...)

       

      Hi all,

       

      I've got a question on how to setup layouts and best DB design.  I had a quick google but couldn't find anything that really fits.

       

      (I'm using FM 13 advanced)

       

      I thought this problem might be kind of generic and somewhat common, so I thought I'd ask here in a complete fashion so others could look later.

       

      Here's my scenario:

       

      • I want to have a simple sales report system, where I track daily sales amounts of products (in this case a coffee shop).
      • I would like to keep track of not just total sales, but also sales broken down by product categories (and sub-categories etc)
      • The total number of categories/sub/sub-sub is probably not greater than 10-15
      • The number of product categories and sub-categories can change over time.  (new ones added, maybe old ones retired etc)
      • Our shop manager would enter the daily sales number by category.
      • For ease of use and speed, do not want to have the user to select from dropdown lists etc for each category - would like a simple one-page layout that lists all categories with entry fields that can be simply tabbed between. 

        See the mockup example below (black is time 0, maybe later the red "flower tea" category is retired from sales, show don't show it in the UI, while maybe later pizza food is added, while maybe even later we start selling ducks.  The "____" are the data entry fields in the UI for the user to simply tab between and enter the value

       

       

      Date:     15th Dec

      Enter sales data below

       

           coffees

                standard          _____

                speciality         _____

           teas

                standard         _____

                (flower)           _____

                milk                 _____

           food

                cakes                _____

                sandwiches     _____

                (pizza)             _____

           ducks

                red                  _____

                green              _____

       

       

      I would like a daily sales entry layout that can automatically handle the change in the categories and sub-categories (and maybe sub-sub etc) over time.

      (for instance we have a category "food" that might start with "sandwiches", "cakes" as sub-categories, but later have "pizza" added as a new category.  I would like a layout that automatically allows creation of a new entry field based on traversing the category/sub/sub-sub list at the time it is displayed).

       

       

      So my questions are:

       

      A) best DB structure for category hierarchy?  (not really a problem, just looking for peoples best suggestions/practices...)

      1.      Given we're not talking more than a few 10's of categories, a hard-coded setup of "category" "sub-category" "sub-sub-category" tables is KISS and allows use of portals, automatic lists if needed
      2.      Or use an adjacency list model table would also work well and be extensible, but the UI would need some more coding I'm presuming (the number of categories etc doesn't need anything more efficient)

      B) best DB structure to track the daily sales numbers?

      Given the categories will vary over time, a fixed record structure daily sales is out, and I'm presuming a "daily category sales" model where each record has a category_ID, date and sales number.  (similiar to "invoice" and "invoice line item" model)

       

      C) how to do a layout for the daily data entry in filemaker?

       

      OK this one's got me a bit stumped! 

      Its easy to come up with some form of list hierarchy etc for the categories however they are stored.  This is easy to do a layout for if the user is selecting from a list per category, but that's not what I want.

       

      Then I want a variable layout that displays the categories, sub-cat etc that are valid and active at the time.

       

      Can you do this with portals???  (but if with portals, does that restrict you to having fixed category/sub-cat/sub-sub-cat tables instead of something like adjacency list model etc).  I don't right now how to see how to do this with portals, because this isn't entering data into one of the category/sub-category tables/lists directly, just using that as a label for available info.

       

       

      thanks all,

       

      Richard

       

      (PS a quick sorry - I posted a question just before the change in the website and didn't get time to say thanks to the people that replied.  After the change I couldn't find my old posts to do that...)

        • 1. Re: layouts and DB structure for daily sales category tracking
          DavidJondreau

          Are you using FileMaker for the actual sales or just the reporting? If the sales, then you'd have tables for Products, Invoices, and Line Items. Line Items would look up a bunch of data from Products, including Category, sub-Category, etc. You wouldn't need to enter summary data for sales (though you could and you may want to), you could simply build a reporting layout that uses sub-summary parts.

           

          If you're not using FM for the actual sales themselves, but say, some Point of Sale system or just ringing them up in a register, then you'd want tables for Products and Daily Product Sales (or pick your own name!). Each record in DPS would be a unique combination of a Product ID and a Date.

           

          I'd script record creation in the Daily Product Sale table. Click a button at the end of the day (or enter a date in a global field and click a button). The script would find all the Products with their Status field of "Active"; collect those IDs; go to Daily Product Sales and create a record (with a Product ID and date) for each active Product; fields in DPS look up their Categories from Products; and you end up in a data entry layout allowing entry for quantity for each Product. That layout has subsummary fields for Category.

           

          I'd go with the KISS of Category, Sub-category, sub-sub. Managing all that in separate tables is super annoying.

          • 2. Re: layouts and DB structure for daily sales category tracking
            yahbai

            Thanks David,

             

            sorry for the late thanks.  working in china right now, and filemaker (why who knows) is blocked half the time from here