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.
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.
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.