3 Replies Latest reply on May 9, 2013 10:24 PM by philmodjunk




      RED GREEN BLUE Totals


           I'm hoping for a little input and I'm not sure how to do this..

           We deal and inspect convience stores. This question is relative to tracking gas pumps.
           Here's what I'm hoping for:

           1. From a Table View or Portal.
               Each pump is rated on a point system 1-10.  (1 is low concern and 10 very High Concern / Needing attention)

                        LOCATIONS                                                           Concern TOTALS                              # Inspected     TOTAL

           Store#1  Chevon  Convience, Addr, Ph               RED###, ORANGE ###, Blue###, White###           x                 xxx
           Store#2  Shell #2 Convience, Addr, Ph               RED###, ORANGE ###, Blue###, White###          None            xxx
           Store#3  Texaco  Convience, Addr, Ph               RED###, ORANGE ###, Blue###, White###           xx                xxx 

           As we review the selected store, we would like to "Drill down to the details for each chosen store".
           Some stores have from 4 to as many as 24 gas pumps.

            Any thoughts on getting the totals in the DOTS within a Portal or Table  showing the  " # Concerns ".

           Hope this is clear enough.







        • 1. Re: RED GREEN BLUE Totals

               What do Red, Orange, Blue and White stand for? Different gas pumps? or ?

               What tables have you set up for this?

               I suggest at least one table for locations, one for each gas pump and probably you will need a third table for storing the details of each pump inspection.


               What version of FileMaker are you using? FileMaker 12 introduces the ExecuteSQL function which is one way to get your totals for each color.

          • 2. Re: RED GREEN BLUE Totals

                 Thanks for the question.

                 The colors designate the " Concern Levels"  that each pump would have.
                 Red circles with pump totals could mean the number of pumps needing immediate attention for service or replacement.
                 Orange would show Pumps that COULD use service or attention "Soon".
                 A Blue "Circle" with the total of pumps that would tell us that the pumps are coming up for routine maintenance.
                 White Circles with the number of pumps would show them as OK.

                 I have FMK11 and getting FMK12 soon.
                 I just want to be able to quickly identifiy per Store, the number of pumps: thoses that need immediate attention, those that are
                 soon to get service, those to receive routine maintenance and those that are OK.
                 So either a Portal with the Store #, address, city st, and ex. 40pumps, (17) are red, (18) are Orange, (10) are blue and the rest are white.
                 I can look at a table list of stored and see each store by color codes that need or don't need attention.

                 I am using a Excel page that has
                 Store#, Store Name, Last Visited, #of Pumps and Field names of " Need attention Now", "SHould have", "Coming up", "OK".
                 I then add the info under each column. I then manually total each column.  NOT so Good a method.
                 I also note for each pump the following:

                 Date last reviewed = xx/xx/xx
                 Mechanical Issue,  = 7 points
                 Programing Issue, = 3 points
                 Eteaches                = 3 points
                 Readability Display=4 points
                 Total                       = 17 points

                 Grade is 1-5 points = OK
                 Grade is 6-10 points= Routine Maint
                 Grade is 11-15 points= Needs Service Soon (within 30 days)
                 Grade is 16-20 points= Urgent need for service/ repair/ replacement

                 That's something along the line of getting the totals to show on the portal in the colored circles.
                 If there is a better or easier way of getting the calculated total to show, I would entertain the idea.










            • 3. Re: RED GREEN BLUE Totals

                   Yes, it sounds like you need the tables|relationships that I specified in my last post:


                   Locations::__pkLocationID = Pumps::_fkLocationID
                   Pumps::__pkPumpID = Inspections::_fkPumpID

                   I am assuming that your grade data comes from a single inspection of a single pump. And that the categories would be determined from the most recent inspection of that pump.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   If you set a sort order on Inspections in the Pumps---<Inspections relationship, a calculation field in pumps can use a case statement to assign a grade category by the total grade from the most recent inspection record:

                   Case ( Inspections::Grade < 6 ; "OK" ;
                              Inspections::Grade < 11; "Routine Maintenance" ;
                              Inspections::Grade < 16 ; "Needs Service Soon" ;
                              /* Else */ "Urgent" )

                   You can define a summary field in pumps that counts the __pkPumpID field.

                   Then you can set up a list view layout based on Locations with 4 1 row portals to pumps in the body of the layout. The filtered portal for the first could be pumps::GradeCategory = "OK", The fliter for the second could be Pumps::GradeCategory = "Routine Maintenance" and so forth. Put the summary field in each of these one row portals and you will have your counts of the number of pumps in each category for each location.