1 Reply Latest reply on Apr 23, 2009 12:58 AM by Orlando

    Help with count or calculation of records with entries in a pop up menu field



      Help with count or calculation of records with entries in a pop up menu field


      I have a FMP 8.5 DB for an annual car show.  One of the fields is called "Division."  The field setup is set to Pop-up Menu with values from a value list "division".  The division are 20's, 30's, 40's, etc. 


      I am trying to create a report or script which will give me an update to date number of entries in each division.  If I create a field such as "count 20's" and code it's type to 'summary', I can get a total count of the division entries, but can't break it down to specific divisions.  I have also tried to write a script.  I have been able to write the script to print/show a report of all entries in each division, however as time goes on it will be necessary to be able to see just the # of entries in each division, and I haven't been successful if figuring this out.


      Any help will be greatly appreciated.

        • 1. Re: Help with count or calculation of records with entries in a pop up menu field

          Hi mlopez911 and welcome to the forum,


          You have two options here, first using the Summary field you already have and creating a new layout that will have sub summary parts to group the data and show you the breakdown by division's


          So in Layout Mode you need to create a new layout and have your summary field on that layout. you shoudl have a layout with a Header, Body and Footer. What you want to do now is change the Body Part of the layout to be a Sub Summary and sort it by your Summary field, to do this simply double click on the Body tab and when the dialog pops up select 'Sub-Summary when sorted by' and then from the list of fields on the right select your Summary field and click 'OK'.


          That is the layout setup so now depending on the version of FileMaker you are running sub summary reports can be activated/viewed in two ways, pre 10 you need to sort the records by the fields in your sub summary part, in your case Week, and then go into Preview Mode. In FileMaker 10 you can do the same, but you will also see the report summarized if you sort the data and are in List view. 


          The Second is only if you don't see you list of Divisions changing, and this required you to add, similar to what you have tried already, a flag for each division and then an additional summary totaling each flag, this will then give you the ability to run a report with a breakdown of divisions for you found set, but the downside is two additional records for each Division. 


          The Flag field will be a calculation and for 20's lets call it AlertDivision20 and the calculation result should be set to number:

          Division = "20's"

          And then setup the Summary field, TotalDivision20 and for the summary settings make it 'Total of' and select 'AlertDivision20' and click 'OK'


          Then repeat this for each value you have for division, 30s, 40s etc.


          They you setup your report with these total fields on, which you can then summarise by other values, similar to the first option, to give you more detailed reporting.


          I guess you choice will depend on the level of reporting you want to perform.