1 Reply Latest reply on Aug 1, 2012 10:01 PM by philmodjunk

    Writing a script to perform a filtered result in a report

      Title

      Writing a script to perform a filtered result in a report

      Post

      I have a report containing the following fields:

      1. Indent Date
      2. Supplier’s Name
      3. Salesperson
      4. Plus other fields with specific data

      I have created two Global fields:

      1. Begin Date
      2. End Date

      I want to create a script where, when the report is opened, the user can:

      1. Enter a Begin Date and an End Date, then
      2. Click on Button 1, which, not only finds the records by the above dates but also sorts them by Supplier

      I then want to create Button 2, which when clicked on asks the user to enter the Salesperson for which the report is required. In other words shows only the records related to that salesperson.

      The final report needs to show the records

      1. Between (and including) the two Begin Date and End Date (These are Indent Dates)
      2. Sorted by Supplier
      3. Filtered to only show records for the selected Salesperson.

      Can anyone please help me write the scripts for the above?

      Many thanks

      David Cutlan

       

      Commission_Projected_Report.JPG

        • 1. Re: Writing a script to perform a filtered result in a report
          philmodjunk

          There are several options for the user to enter the dates and other criteria. Show Custom Dialog is easiest to script in most respects, but if you use New Window, you can pop up a small floating window where the date fields have pop up calendar controls and a field for salesperson can be formatted with a value list of sales personnel. The basic script will be the same, so I will set up a simplified version with show custom dialog as a starting point.

          First, define one more global field for SalesPerson. I'll name these fields: Globals::BeginDate, Globals::EndDate, Globals::SalesPerson.

          This script also assumes that you have FileMaker 12. If you have an older version, the script will need some changes as Show Custom Dialog in older versions only commits data from input fields if button 1 is clicked.

          Set Field [Globals::BeginDate ; "" ]
          Set Field [Globals::EndDate ; "" ]
          Set Field [Globals::SalesPerson ; "" ]
          Show Custom Dialog  [ "Enter Date Range:" ] // Specify BeginDate and EndDate as the input fields.
          If [Get ( LastMessageChoice ) = 2 // "S Person" clicked ]
              Show Custom Dialog [ "Enter SalesPerson' ] // Specify Globals::SalesPerson as input field.
          Else If [ Get ( LastMessageChoice ) = 3 // Cancel clicked ]
               Exit Script []
          End If
          Go to Layout [Report layout]
          Enter Find Mode [] //clear the pause check box
          Set Field [ YourTable::indentDate ; Globals::BeginDate & "..." & Globals::EndDate ]
          Set Field [ YourTable::SalesPerson ; Globals::SalesPerson ]
          Set Error Capture [on]
          Perform Find []
          Sort [no dialog ; Restore ]