3 Replies Latest reply on Apr 1, 2015 12:43 PM by RobertT

    create a report sorting by year



      create a report sorting by year


      I have a list of patients with 3 different date ( two different years 2014,2015)  Im trying to calculate the total number of scripts written (different amounts per patient per record).  

      my problem is I can calculate the year(dateservice=2014)   and separate field calculation year(dateservice=2015) and use it as subsummary for numberscripts field... but i am getting boolean values and not the total number of scripts per given year.     i have a total 159 scripts (  random number scripts written in a "scripts"  field)  and 3 records per patient with the patient having 3 different visits  where I have a "date service"  field  ...   any guidance would be appreciated





        • 1. Re: create a report sorting by year


          Is a Boolean expression comparing the value of DateService to the constant value 2014. The result will be 1 (True) if the value of dateservice is 2014 and 0 (False) if it is not.

          I could be wrong as there is a lot of detail missing from your post, but it sounds like you could use a summary report. Summary fields can be defined to compute counts of, totals of (or other aggregate values) specific fields in your table. A calculation field can be defined as: Year ( DateService ) to return the year if that field is a field of type date. If you sort your records on such a calculation field, your records are grouped into groups of records with the same year. If you then set up a list view layout and add one or more sub summary layout parts "when sorted by" your year calculation field, you can put your summary field(s) into this sub summary layout part to show a sub total based on all records for the same year. Put those same summary fields into a grand summary layout part and you get a grand total value based on all the records in your found set.

          The typical way to set up such a report is to perform a find for the records that you want for your report and then sort them using the "sorted by" fields specified for your sub summary layout parts. If you leave a sub summary layout part's "sorted by" field out of the sort order, that sub summary part disappears--which can sometimes be a useful way to get different reports from the same layout just by changing how the records are sorted.

          Here's a tutorial you can read and do to learn more about summary reports: Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: create a report sorting by year

            Yeah the i read the tutorial still doesn't make much sense...   If I have two different years in a field..  and i want to separate the total number of scripts by the year (2014, and 2015)  do I need to create a separate calculation for each year or  one calculation of  year(dateofservice).. i tried that  in a subpart summary of field (number of scripts)   and a sub summary field  (number scripts) sorted by year(date service)  calculation and don't get a total summary of scripts that were written for a record in year 2014, and years 2015

            • 3. Re: create a report sorting by year

              hah now i figured it out thanks :)