1 Reply Latest reply on Oct 31, 2008 2:39 AM by Sorbsbuster

    Creating A Report

    krzyivan

      Title

      Creating A Report

      Post

      I have a wonderful db that I converted from an old dos program called file pro, and what I have created in FM9 is great and the people at the Sheriffs department love it, accept for one thing, they want to do a report et the end of the month. What I need to figure out is, how do I make this do the following:

       

      1. Click the tab I have designed, which in turn will pop up and ask them which date range they want.

      2. After that I want it to automatically sort acccording to the date range they have chosen, from oldest date range to newest date range they chose.

      3. Then show up in a report and under the amount recvd column, I want the total dollar amount to show up as well.

      4. There are 4 columns total, Amount Recvd, Recvd Date, Paid For First Name, Paid For Last Name.

      5. I can get it to show me on the report, all of the above columns perfectly, with the exception that it shows me all 10000 records (which I dont want). Doesnt give me the total dollar amount (which I do want)  and shows me every date from 1993 to 2008 (which I dont want).

      6. And I cant get #1,2, or 3 to work, so I'm kinda stuck.

       

      Any Ideas would be greatly appreacited...Tony 

        • 1. Re: Creating A Report
          Sorbsbuster
            

          Hi krzyivan,

           

          Do you mind if we do these steps manually, first, to make sure my understanding is correct?

           

          First, create the total dollar amount first of all:

          - Define a new field, SumAmountRecvd, and make it type 'Summary'.  Set it to be the summary of AmountRecvd.

           

          Now make your report have the correct part:

          - In Layout Mode, click the 'Part...' button on the Status Bar (LHS), drag-and-drop it into the Footer area, and select 'Trailing Grand Summary'

          - Clcik the 'Field...' button and drag-and-drop it into the new Grand Summary area.  Set the field to be the new summary field, SumAmountRecvd.

           

          (Strictly, you didn't need to create the new report part, as the summary field will  display perfectly if you place it in the footer, but this is a more elegant way.)

           

          Now find the correct records:

          - Enter 'Find' mode, click into the Recvd Date field and type '1/10/2008...31/10/2008'.  (No inverted commas.  I've used UK format, but I assume you'll use US. The 3 dots tell FM to search for a range.)

          - Click 'Find (Status Bar) or hit 'Enter'.

           

          Now you've found the right records, we can sort them:

          - Right-click (was DOS based, so using a PC?) on the field 'Recvd Date' and select 'Sort Ascending.

          or

          - Choose  'Records -> Sort...' (or 'Control-S') and in the left box find the field 'Recvd Date' and pop it over to the right box.

          - Click 'Sort'.

           

          If all of that works the way you want (you can format the summary field to be currency, no decimals, for instance) then we can script all of that perfectly to offer the user a data-entry box that already defaults to the date range for last month (say), allows them to change it or leave it, and does everything else automatically.

           

          Hope this helps,

          Alan.