1 Reply Latest reply on Sep 4, 2012 11:07 AM by philmodjunk

    Summaries based on values in Text field



      Summaries based on values in Text field


      I have a small tourism rental and manage my bookings in FM11. I want to use the data to learn more about what advertising works and what not, as well as comparing year to year.

      The interesting Table here is Bookings, which among others have the fields Price and Source. (Source is where the booking comes from, entered from a Value List)

      To start with I would like to list Income (sPrice) and number of bookings for each value of Source, preferably sorted descending. If I manage that I hav a few more ideas...

      Can someone point me in the right direction?



        • 1. Re: Summaries based on values in Text field

          What you describe is called a summary report. It requires several elements: a summary field to compute both grand and sub totals, a Sub summary layout part(or perhaps several of them) and a sort order that groups the data correctly for sub totals and then a special sort option that re-orders the groups in descending order.

          Step 1: define a summary field that computes the total of Price. I think you have this already with the field you call sPrice so I will refer to this summary field as sPrice in this example.

          Step 2: Set up a list view layout based on your bookings table. Add a sub summary layout part to your layout "when sorted by" the Source field. Put the Source and sPrice fields inside this sub summary layout part.

          Step 3: Find your records and sort them by Source. In the Sort Records dialog, once you have selected this sort order, select the "reorder based on summary field" option and select sPrice as the summary field.

          At this point, when you sort your records you'll see groups of booking records with the total value listed either above or below each group of booking records in the sub summary part. If you only want to see the subtotals--not the individual booking records, return to layout mode and remove the body layout part (click the part label and press delete.)

          Note that step 3 can be automated by creating a script that finds and sorts your records for you.