AnsweredAssumed Answered

layouts and DB structure for daily sales category tracking

Question asked by yahbai on Dec 30, 2014
Latest reply on Jan 12, 2015 by 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...)

Outcomes