3 Replies Latest reply on Nov 29, 2014 8:42 PM by gilcano

    Developing a tracking system for Maintenance Request

    gilcano

      Hi guys, I'm new to FileMaker but I'd been reading a lot and also watching some videos about FileMaker. I had some experience with Data Bases programs in the past such as DBase III, Foxpro and also developed a few application in Clipper which was originally created as a compiler for DBase III language.. Presently I'm the Director of Maintenance of a Non-for-profit organization that provide low-incomem housing for Senior Citizens and I would like to develop an application to keep track of the work performed in the units as well as an inventory of the mechanical equipment. The organization has 24 buildings and about 3600 units of housing. We handle everything with forms and I'm getting overwhelm with the amount of paper work. I'm acquaring FileMaker Pro 13 Advanced version next week. Besides I have to conduct many mandated inspections which again we keep on forms. When a form is misplaced or lost you can imagine the chaos.

       

      Here are some of the tables I think I'll need to create my application. Please note that I'm not an expert and I’m learning FileMaker Pro. Any advice will be appreciated.

       

      Table: Buildings (This table will hold information of each building name, address, phone, fax, etc.)

       

      _pk_Bldg_ID

      Bldg_Name

      Address

      City

      State

      Telephone#

      Fax#

       

      Table: Tenants (This table will hold the information of the tenants such as unit number, tenant name, spouse name, phone number, emergency contact, move-in date or move-out date, if tenant pass away record will be moved to a history file as well as when a tenant move-out)

       

      _fk_Bldg_ID

      _pk_Unit_NO

      First_Name

      Last_Name

      Spouse_First

      Spouse_Last

      Telephone_NO

      Emerg_NO

      Move_IN

      Move_Out

      Decease

       

      Table: Work Orders (This table will hold the information of all repairs done in an specific unit with detail information such as category (plumbing, electrical, heat, hardware, etc.), work done, work assigned to, etc.

       

      _pk_WO_NO

      _fk_Unit_NO

      Date

      Time

      Category plumbing, electrical, heat, hardware, etc.

      Requested_service It can be a combo box with different type of problems, i.e. toilet stopped up, etc.

      Entry_Permission radio button (Yes-No)

      Assigned_to Employee doing the work

      Time_entry

      Time_Leaving

      Work_Done

      Materials_used

      Notes

       

      When the work is done the Work Order is checked as completed, otherwise it need to be checked as follow: 1-parts on order, 2-repaired temporarily, 3-outside contractor called.

       

      Table: Appliances (this table will hold the information of the refrigerator, range, and a/c in the apartment with a date installed and history of service)

       

      _fk_Unit_NO

      Appl_type Refrigerator, Range or A/C

      Manufactures

      Model_NO

      Serial_NO

       

       

      I’m a little confused with the equipment inventory because I want to be able to input the information of a name-plate of fans, pumps and motors. For example, every building have an average of 30 Roof Exhaust fans with a manufacturer, model number and serial number but also have a “motor” with his own data, manufacturer, model number, serial number, HP and a whole lot of other information. The same with the “PUMPS”, I thought about the following:

       

      Table: Equipment

       

      _fk_Bldg_ID

      _pk_Equip_ID

      Equip_Type (Fan, Pump, HVAC, Boilers, etc.)

      Equip_Description

      Location

      Manufacturer

      Model_NO

      Serial_NO

      C.F.M.

      Belt_Description

      Pump_Capacity

      Ft_Head

       

      Table: Motors

       

      _fk_Equip_ID

      Manufacturer

      Model_NO

      Serial_NO

      H.P.

      Amps

      P/N

      Voltage

      Hertz

      RPM

      Insulation

      Frame

      Date_Production

      Variable_Speed

      Thermal_Protected

      Pict_Equip Container

      Notes

       

      Table: Service Detail this table will hold the equipment service history

       

      _fk_Equip_ID

      Equip_type

      Date_Serviced

      Time_serviced

      WO_NO

      Work_Performed

      Work_By

        • 1. Re: Developing a tracking system for Maintenance Request
          lavendt

          First; welcome on the platform !

           

          May I suggest that you start drawing your current workflows on paper, with boxes and lines.

          Then take a look at the workflows and try to see if you could optimize them.

          e.g. if you were using iPads to see workorders and fill out information during maintenance jobs, would that affect your workflow ?

          What are your goals for developing a FileMaker solution for this ?

           

          When you have your workflows in place and maybe even optimized them, then you can start looking at the entities you need in your solution.

          After that, you can start looking at the fields, that would be needed.

          However, in my experience, it is a good idea to continue drawing screens, that support each workflow.

          E.g. when a worker needs to perform maintenance, the first thing needed, is a list of the jobs he needs to perform. How should that list look like ? Which information should he be seeing on that list, that enables him to go directly to the location and know which type of job he should perform ?

          Should the list be sorted by job type ? or by locations that are close to each other physically ?

          Then he needs to see details for the job, he needs to perform. What, how, who etc.

          Maybe he needs to be able to see a list of appliances that he needs to bring. Could that be displayed in a popover for the job on the list ? Should there be an appliance list for what he needs to bring for the jobs, he needs to perform today ?

           

          By drawing the screens and thinking in workflows, you will find out which fields is needed for the screens and then you will also know wich fields should be in the tables.

           

          When you do it this way, you don't get "lost" in features, fields, tables and scripts, but creates a great plan for how you should create your solution. The great thing is that ideas on how you can optimize your workflows and make you work more efficiently, will show themself in this process.

          After your planning and drawings, you will be able to create your solution much faster and also be able to utilize external help for the things and details, that you are not yet experienced enough in.

          • 2. Re: Developing a tracking system for Maintenance Request
            gilcano

            Claus Lavendt


            Yes I think you’re right but I’m so excited about this FileMaker Platform that I’m forgetting some steps. I remember back in the days of Clipper and Dbase III when one had to design the screens and everything on paper before programming anything.

            I’m going to do the workflow on paper as you suggested

            Thank you for your comments

             

             

            E.g. when a worker needs to perform maintenance, the first thing needed, is a list of the jobs he needs to perform. How should that list look like ? Which information should he be seeing on that list, that enables him to go directly to the location and know which type of job he should perform ?

            Should the list be sorted by job type ? or by locations that are close to each other physically ?

            Then he needs to see details for the job, he needs to perform. What, how, who etc.

            Maybe he needs to be able to see a list of appliances that he needs to bring. Could that be displayed in a popover for the job on the list ? Should there be an appliance list for what he needs to bring for the jobs, he needs to perform today ?

             

            By drawing the screens and thinking in workflows, you will find out which fields is needed for the screens and then you will also know wich fields should be in the tables.

            • 3. Re: Developing a tracking system for Maintenance Request
              gilcano

              Working on the workflow but wanted to answer some questions.

              Claus Lavendt wrote:  "What are your Goals for developing a FileMaker solution for this"

               

              My Goals for developing a FileMaker solution are:

               

              1. Retrieve Information

                  Besides the flexibility of FileMaker to run on Windows and Macintosh it can be also run on iPads and iPhones.  On the other hand I believe FileMaker is a robust platform for developers to create applications that will help any organization to "get rid of paper-based processes, but my main goal is to keep track of information easily.  When I talk about keeping track information I mean to be able to open any WO in a "desktop" or and "iPad" and see what job was requested?, what was the solution?, who performed the repairs (employee or outside contractor)?, which materials (if any) where needed to made the repairs?; If the job is not completed I would like to see "WHY IS NOT COMPLETED", an outside contractor is needed?, some parts needs to be ordered to complete the work?, etc.

               

              2. Inspections and PM's

                  To keep track of relevant information such as the cause of the problem (if any), downtime involved (if any), and recommendations for future actions.  To record data about equipment, including maintenance activities, specifications, purchased date, installations date, expected lifetime, warranty information (PDF, container field?), service contracts (PDF, container field?), service history, spare parts and anything else that might be of help to Management or Maintenance Supervisors.  Management of permits and other information required by City Authorities such as equipment permits ( Oil tanks, Boilers, HVAC, etc.).  These permits could be stored as PDF's with their respective equipment information and date of expiration.

               

              3. Reports

                  I would like to print many reports, specially the WO report.  I want to be able to print reports by "Buildings", then by "category" inside buildings (plumbing, electrical, hardware, heat, etc.) and finally by units.  With reports, one can pinpoint problems, for example a tenant could be clogging a bathtub too often.  With this information on hand you can take actions, is this tenant been negligent? is there a problem with the waste line or drain? Are the employees using the right tools or materials? Do we need an outside contractor?, etc. I'll try to "customize" this application for our needs, many commercial CMMS have many modules that we don't use.

               

               

              Claus Lavendt wrote:   "When a worker needs to perform maintenance, the first thing needed is a list of the jobs he needs to perform"

               

              Not necessarily, that's true if the worker is the one writing thw WO but this is not the case, the WO will be written by the Property Manager or the Maintenance Supervisor, at this time the "JOB REQUESTED" will be designated and the WO will be scheduled.  When the worker receives the WO he/she will have an idea of what materials and tools will be needed to resolve the problem.  I thought of create the following tables:

               

               

               

              TABLE CATEGORY

               

              Cat_ID
              Cat_Desc
              01Plumbing
              02Electrical
              03Heat
              04Hardware
              05Doors
              06Windows
              07Floor
              08Paint/Plaster
              09Light bulbs
              10PM

               

              TABLE JOB_REQUESTED

               

              Cat_Desc
              Job_Requested
              PlumbingFaucet Leaking (Kitchen or Bathroom)
              Electrical Light fixture does not work
              WindowWindow won't open
              FloorFloor tile lifting/cracked (wood-vinyl-ceramic)
              HardwareDoor lock jammed
              PlumbingToilet stopped up
              PlumbingLeak under sink (Kitchen or Bathroom)
              Paint/PlasterDamaged Wall/Ceiling
              WindowWindow lock missing
              DoorCloset door won't close/jam
              HeatNot enough heat
              WindowWindow glass cracked
              Paint/PlasterCracked ceramic tile in bathroom
              PlumbibgTub stopped up
              EtcEtc
              EtcEtc
              EtcEtc
              EtcEtc
              EtcEtc
              EtcEtc

               

              Thanks