3 Replies Latest reply on Aug 30, 2011 1:12 PM by MichaelLane

    Filemaker Pro 11 Reports

    MichaelLane

      Title

      Filemaker Pro 11 Reports

      Post

       Greetings. The table feeding the report contains 4 fields:

       [Date], [Job_ID] [Client] & [Amount_Charged].

      I would like the program to ask for a date range ([Start_Date] and [End_Date] and then have the report show the records in that date range and total the [Amount_Charged] field for each Job.

      There could be multiple entries for one job with either the same date or a different date.

      I have tried writing a script to do this but have failed miserably. If there is anyone out there who can help me I would very much appreciate it.

        • 1. Re: Filemaker Pro 11 Reports
          philmodjunk

          What you describe is called a summary report. I'll include a link to a tutorial on the subject at the bottom of this post that you can investigate if interested. It walks you through some interesting variations that you may find useful.

          First, let's define three more fields to use with this report:

          gStart_Date, gEnd_Date. Make these date fields with global storage. You can define them in any table in your file. I'll keep them in the same table here for simplicity, but in my own systems, I usually put all such global fields in their own table for easier management.

          sTotalCharged. Make this a field of type summary that computes the "Total of Amount_Charged".

          Now set up a list view layout based on your table. (I'll call it "YourTable" in the script below)

          Put your four fields in the body part in a single row and pull the bottom boundary of the body layout part up to the smallest height possible.

          Use Part Setup, to create a sub summary part when sorted by Job_ID. Specify "Print Below".

          Put "Total charged: " and the sTotalCharged field in this new part and align the summary field beneath the Amount_charged field.

          Now return to browse mode and sort your records by Job_ID.

          You should see all your records grouped by Job_ID with a sub total for each. You can add this same summary field to a trailing grand summary or the footer if you want to see a grand total.

          Now to set up a script so that you can specify a date range and see only the records within that range of dates:

          Put gStart_Date and gEnd_Date on a different layout. Put a button next to them that performs this script:

          Go to Layout [//select your new report layout here]
          Enter find mode [] //clear the pause check box
          Set Field [YourTable::Date ; If ( YourTable::gDate2 ; YourTable::gDate1 & "..." & YourTable::gDate2 ; " ≥ " & YourTable::gDate1 ]
          Set Error capture [on] //keeps "no recors found" dialog from interrupting the script
          Perform Find []
          Sort Records [No dialog ; Restore ] //sort records by Job_ID or sub totals will not be visible

          Note 1: there are several ways your script can be set to work with two dates in the two global fields. I'm using an approach that allows you to enter a single date to get all records from that date or later if you enter a single date in gStart_Date and which finds the range of dates if two dates are entered. Other options are also possible.

          Note 2: If you do not specify global storage for the two date fields, this script will not work.

          Note 3: you can use get ( foundcount ) or Get ( LastError ) in an if step to detect when no records where found and can then use Show Custom Dialog to let the user know why their report screen is blank.

          Here's the tutorial: Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Filemaker Pro 11 Reports
            MichaelLane

             What can I say !!

            I have only just joined the forum and posted my first question and within 30 minutes I receive a comprehensive answer.

            I will get on with carrying out your coding and report back.

            Very many thanks.

            • 3. Re: Filemaker Pro 11 Reports
              MichaelLane

              Coding complete and it works like a dream.

              Once again - many thanks.