3 Replies Latest reply on Feb 1, 2012 9:58 AM by flacus

    "Elementary, my dear Watson!"



      "Elementary, my dear Watson!"


      Hi all,

      I sell apples and organic apple juices over my site and deliver them to my customers. I would like to track down everything. And the problem is, I am not quite sure in what table should I put some fields. Data modeling isn't my stronger side.

      This is how I put things, so bare with me and if you can, help me!

      I need these information from users:
      Name, Last Name, Username, Address, City, ZIP, email, cell phone, telephone, date of registration

      This is pretty obvious, so I made a table USERS with these fields + UserID which is primary key for relationships. Right?

      Next thing is my own products. This is a little bit tricky. I sell apples in boxes of 10kg. Currently I have three apple sorts I offer. Beside that, I have apple juices bag in box 5L two types of juices. Here, I don't know how should I form a table.

      - I can have more apple sorts. 3 is just beginning
      - I could have other packaging except 10kg ones. 5kg, 3kg, 20kg,
      - Apple juice currently comes in 0,2 Liters and 5 liters. Currently in two flavors, but more to come soon.
      - and price is not constant

      And for orders, I need the following:
      OrderID, Order number, Which user ordered, order date, delivery date, which product

      In the end, I want to take the following info out:
      - how many kilos of apple is sold
      - how many boxes of apple is sold
      - which apple sort is most sold
      - how many bag in box 5 liters are sold
      - how many 0,2 liters juices are sold
      - summary - how many liters is sold
      - summary - how much have I earned
      - and this list goes on...

      Every information is important to me, so I would like someone to help me with constructing tables or at least, helping me understand the relationships.

      Please forgive me if something isn't clear because english is not my primary language.

      Thank you in advance!

        • 1. Re: "Elementary, my dear Watson!"

          What you want is really a classic sales orders database design.  I would recommend starting by modifying one of the starter solutions that comes in filemakers business productivity pack but some of the designs concepts they use are not very good.  If you are looking for a quick fix that may be your best bet.  If you want to expand your filemaker skills, follow along with the rest of my post.

          You do need a customers table like you stated in your question.  You also need the following tables/fields




           -orderTotal  calculation sum (LineItems::extendedPrice)






           -itemDecription  lookup from Inventory Table

           -itemCategory  lookup from Inventory Table

           -itemPrice  lookup from Inventory Table


           -extendedPrice calculation itemPrice * itemQuantity





           -ItemCategory  Value List of product cadegories.  ie apple juce, 



          Customers --> Orders

          Orders --> Line Items

          Inventory --> Line Items


          Hook everything up and you are off to the races.  On you Orders layout you will need a portal showing line items.  Be sure to allow the creation and deleation of records from the portal.

          • 2. Re: "Elementary, my dear Watson!"

            Here's a simpler demo file than the starter solution: http://fmforums.com/forum/showpost.php?post/309136/

            It uses the same basic structure and method for printing invoices so you may find that after studying this demo file a while, the starter solution may become easier to understand.

            Here's a tutorial on summary reports--a basic methd for producing the reports you've sketched out here: Creating Filemaker Pro summary reports--Tutorial

            The tutorial thread is old enough that comments/questions posted to it will not pop the thread back up in Recent Items. If you have questions about it feel free to post those questions here or start a new thread and include a link to the tutorial thread in your first post.

            • 3. Re: "Elementary, my dear Watson!"

              Thank you very much guys! I will get back to you, soon as I check everything out.

              I have to prepare so I can get back with fresh questions :)