4 Replies Latest reply on May 16, 2017 7:09 PM by rjalex

    Help setting up a purchase / inventory / sales database please

    rjalex

      Dear friends,

      I am learning Filemaker to help wife setup a stationery shop.

      Read through many database schemas and tutorials but still have lots of confusion and really want to build a valid durable schema before I develop.

       

      Here is my first "problem": I have the following tables Supplier PurchaseOrder PurchaseOrderLine Product Deliverable

       

      Supplier is easy :-) Product describes the product including it's barcode/UPC/EAN as a universal identifier

       

      Deliverable maps a Product to a specific Supplier. A product can be carried by multiple suppliers. A "Deliverable" is a product carried by a specific supplier that will have the supplier SKU (the code to order that product from that specific supplier) and a price (for now let's forget prices changing in time and order units).

       

      If I get this right I'd be able to do the following: when I need to order a product, list all suppliers that carry it sorted for price from low to high.

       

      Does the above make sense?

       

      When I am building an order I might find the product, if not I need to enter the new product. Ideas on how to do this in a user friendly way?

       

      I still have to understand two main processes:

       

      a) How do I handle orders so that when goods arrive I am going to be able to handle any discrepancy in quantity or price between what I've ordered and what I've received

       

      b) How do I handle price changes. Would I set prices on Products or on Deliverables ? On products it would make my life easier when handling sales but as each supplier might sell me the same product at different prices I'd lose the capability of knowing my net value right ?

       

      Thank you for any help

        • 2. Re: Help setting up a purchase / inventory / sales database please
          philmodjunk

          "When I am building an order I might find the product, if not I need to enter the new product. Ideas on how to do this in a user friendly way?"

           

          You simply add the new record(s) to product and/or deliverables. That can be as simple as manually changing layouts to layouts based on those tables, creating records and filling in the fields or you can set something more sophisticated such as a popover with global fields and a button. You enter the needed data into the global fields, then click the button to a) use the data in the global fields to create the new record and b) add the reference to the new deliverable to a line item for your current purchase order.

           

          "How do I handle orders so that when goods arrive I am going to be able to handle any discrepancy in quantity or price between what I've ordered and what I've received"

           

          You'll need more tables related to PurchaseOrder where you document what is received as compared to what was ordered.

           

          "How do I handle price changes. Would I set prices on Products or on Deliverables ? On products it would make my life easier when handling sales but as each supplier might sell me the same product at different prices I'd lose the capability of knowing my net value right ?"

           

          you've answered that question yourself. You can't put in products as it won't work given different prices from different suppliers of the same product.

          1 of 1 people found this helpful
          • 3. Re: Help setting up a purchase / inventory / sales database please
            DanielShanahan

            Take a look at FileMaker Inventory Resources.  There are articles and download files that may be helpful.

             

            a) How do I handle orders so that when goods arrive I am going to be able to handle any discrepancy in quantity or price between what I've ordered and what I've received

             

            Take a look at the Receiving Goods article at FileMaker Inventor Resources.

            There is also an accompanying demo file that may be helpful.  A couple of supporting YouTube video are also available: one on the process and a longer one “under the hood” that takes a look at the tables, fields, and scripts.

             

            b) How do I handle price changes. Would I set prices on Products or on Deliverables ? On products it would make my life easier when handling sales but as each supplier might sell me the same product at different prices I'd lose the capability of knowing my net value right ?

             

            Here’s one way to handle this:

             

            1. For each SKU have a primary supplier.  This can be marked in the Deliverable table but you need to make sure there is only one primary supplier per product.

             

            2. Once you have the primary supplier for each SKU you can use that price for your Sales Order Line.

             

            3. I recommend the following fields for your Deliverable table:

              a. Cost.  This is the amount of money the supplier charges you.

              b. Markup.  This is the % you markup each SKU.

              c. Price.  Cost + Markup.

              d. Lead Time.  Number of days it takes from the time you order the SKU until you receive it into stock.

             

            Regarding price changes, do you need to see the changes?  If no, then simply update the value as needed.  For your cost, that will likely be when you receive the invoice from your supplier.  For price, that’s up to you (using the definitions for cost and price above).  If you don’t need to store the history of cost and price changes, just overwrite the existing data.  If you want to track the history of cost and price fluctuation for an SKU then create a Price Log table with the Product ID, Supplier ID, Date Changed, Existing Cost, New Cost, Existing Markup, New Markup, Existing Price, New Price.

            2 of 2 people found this helpful
            • 4. Re: Help setting up a purchase / inventory / sales database please
              rjalex

              Thank you all very interesting input and food for thought.

               

              Daniel a special thank you for the very clear material you took the time and pain to build and share with all of us.

               

              I think I set my sights on too much of a task as I'm really new to all of this and struggling.

               

              Ciao from Rome