1 Reply Latest reply on Sep 20, 2010 10:55 AM by philmodjunk

    creating report by date



      creating report by date


      This is my script which creates a report with all the last order date of each company from the database. I want to create a neat report which separates them into categories of; More than 1 month since last order, more than 3 months since last order, and more than 6 months since last order. Also right now the perform find simply omits all records before jan 1 2008. However, I would like it to find all records within 2 years of the current date, and omit all records after current date.

      Also I wanted to create a button which would go to the order in another layout pased off the purchase order number.



        • 1. Re: creating report by date

          To find the records you want:

          Enter Find Mode[]
          Set Field [YourTable::Date ; "> " & Date ( 1 ; 1 ; Year ( Get (CurrentDate) ) - 2 ) ]
          Set Error Capture [on]
          Perform Find[]
          If [Get ( FoundCount ) > 0 ]
             Sort [Restore ; No dialog ]
             Show Custom Dialog ["No records in this date range were found..."]
          End If

          To group your records as described, you can create a summary report and the sort step in the above script can be set to sort the records in an order that supports the structure of the summary report.

          The Summary Field, sLastOrder defined as Maximum of your record's order date will return the date of a given customer's most recent order if the records are sorted by Customer ID.

          Then the following calculation, cOrderAgeCategory can compute a value you can use to group the records by elapsed time in days since last order:

          Let ( Age = GetSummary ( sLastOrder ; CustomerID ) - OrderDateField ; 
                  Case ( age < 30 ; 1 ;
                              age < 90 ; 3 ;
                              age < 120 ; 6 ;
                              7 ) /*case */
                ) /* let */

          You would sort the above records first by Customer ID, then cOrderAgeCategory. You can add sub summary layout parts when sorted by Customer ID to put in a heading for each customer group of orders and you can then include below it a sub summary part when sorted by cOrderAgeCategory to show a breakdown of orders by age.

          If summary reports are a new concept, you may want to read this thread:  Creating Filemaker Pro summary reports--Tutorial