4 Replies Latest reply on Apr 27, 2009 3:16 PM by keycoachjohn

    Suggestions to set up a utilization tracking system FM10pro

    keycoachjohn

      Title

      Suggestions to set up a utilization tracking system FM10pro

      Post

      Hi there,

      Am trying to create a tracking db that incorporates monthly utilization (hours) for several serialized components (A through L) installed on similar types of serialized products upwards totaling around 40.   My initial idea uses a table for each serialized component type (12  tables) , a new record for each month of usage per table, fields per record each record have a relationship back to a basic “entry” screen where a user enters utilization data.  Upon entry, the previous month’s numbers get automatically updated across each field.  After which we can then run reports and summary sheets. 

       

      Have tossed around the ideas of lookups and scripts to create new records and calculations, but before heading down that path wanted to probe whether there’s a better approach to this. Regards, John

        • 1. Re: Suggestions to set up a utilization tracking system FM10pro
          philmodjunk
            

          It's always a good idea to map out your basic tables and relationships before you start. It sounds like you need the following tables:

           

          Products

          Components

          InventoryProd

          InventoryComp

          Log

           

          In products you list each type of product you plan to track, with as many fields as you need to describe each type of product.

          In components, you do the same for each type of component

          Your two inventory tables describe actual physical pieces of equipment. (You might have one "Fork lift" record in products and 5 "fork lift" records in InventoryProd, one for each machine being used in your plant.)

          Your log record records one entry for one specific utilization record for one specific component.

           

          How you link the tables and what fields you need in each table will be determined by the information you have and how you want to organize it in your tables.

          You may be able to get by with fewer tables, you may decide you need more.

           

          This is just a broad outline, but maybe it'll get you started in the right direction

          • 2. Re: Suggestions to set up a utilization tracking system FM10pro
            keycoachjohn
              

            Thanks for the very quick reply!  Using your suggestion below, I presume the cumulative hours on one of the components would then be calculated via a report page?  Is it impractical to consider maintaining the cumulative hours on a new record per component each time the entry took place; for traceability purpses?

             

            Part of my analysis paralysis is that I get stuck in the initial planning stage...partly due to the fact that some of the components amongst the inventory are moveable from one product to another, and such accumulate time on different "fork lifts". 

            • 3. Re: Suggestions to set up a utilization tracking system FM10pro
              philmodjunk
                

              "I presume the cumulative hours on one of the components would then be calculated via a report page?  Is it impractical to consider maintaining the cumulative hours on a new record per component each time the entry took place; for traceability purposes?"

              You have multiple options. In FMP you can define calculation fields that reach across relationships to sum total information. Thus, a calculation field in InventoryComp could calculate a sum based on all matching records in Log (all utilization records for component xyz). Alternatively, You can create a summary field in Log that gives you the same number. With summary fields, you can create a very nice usage log report where each of the component's usage records are grouped under a sub summary heading that reports the total usage for a specific component.

               

              "Part of my analysis paralysis is that I get stuck in the initial planning stage...partly due to the fact that some of the components amongst the inventory are movable from one product to another, and such accumulate time on different "fork lifts". "

              I'd log each component's usage data in log by unique component ID. I'd then record the ID of the Product it was part of in a different field of the same log. I could then compute usage stats for that component to get either an overall total or the total while it was attached to a particular Product.

               

              It sounds like you may be fairly new to database design and Filemaker Pro. If so, I'd suggest either investing some study time with a good book on Filemaker, some tutorials and/or possibly hiring a consultant to design your DB.

               

               

               

              • 4. Re: Suggestions to set up a utilization tracking system FM10pro
                keycoachjohn
                  

                Thanks for the tips, will give those a try.  Currently have worked through the basic tutorials included with the s/w, and am dabbling with "Missing Manual" tutorials.  Open to other recommended books if you have suggestions. 

                  BRds-John