People Goal 5 - Part 9: Find specific data before running your report

Document created by Kedar on Feb 4, 2015Last modified by communitymanager on May 12, 2015
Version 13Show Document
  • View in full screen mode

 


In Goal 4, you used the Report Wizard to create a subsummary reporting layout and a script that automatically runs the report for you. The report summarizes secondary table data, and is performed on whatever records happen to be in the found set at the moment. Let’s modify the script to give the user a choice about which records they wish to summarize:


  • All records

  • Those from the past 90 days

  • Those from the past 30 days

 


If the user chooses “All records”, the script will show all records. Otherwise — assuming you use the Creation Date field in your find criteria — the script will try to find records from 30 days in the past or 90 days in the past and doublechecking that the find was successful. If the find was not successful, the script will stop without displaying the report. Otherwise, it will sort the records and display the report.



Nested If statements


This will involve placing one If statement inside another. Here’s the basic structure of the script:

sales activity report if structure.png



Date calculations


Displaying records from the past involves performing some simple calculations on a date field in your secondary table (either the Creation Date field or any other you think is appropriate), which introduces some important concepts regarding dates in FileMaker Pro: 

 

  • Date fields can be treated as numbers, where the date January 1, 0001 (1/1/0001) is equal to the number 1, the day after that is equal to the number 2, and so on.

  • You can subtract one date from another and get the difference between them in days.

  • You can also add a number X to a date, resulting in a date that is X days in the future, or subtract a number to get that many days in the past.

 


Four functions


The script will use four different functions:

 

  • Get ( LastMessageChoice )
    Returns a number corresponding to the button clicked by the user. You’ve used this function once already.

  • Choose ( test; result0 { ;result1;result2… } )
    Returns one of the result values, based on the value of test. If test is 0, result0 is returned. If test is 1, result1 is returned, and so on.

  • Get ( CurrentDate )
    Returns today’s date. You’ll use this to calculate the Start Date for your find operation.

  • Get ( LastError )
    Checks the error code associated with the most recently executed script step. A code of 0 means that no error was encountered. Anything else means that something went wrong. You’ll use this to check whether your find operation was successful.

 

 

Goal:

 

Give the user three choices about which secondary table records they wish to report on, find the records according to the user’s choice, and generate the subsummary report based on those records.

 

 

Steps:

 

Get the user's choice

 

  1. Duplicate your report script by selecting it in the left panel of the Script Workspace and then pressing Command-D (OS CX) or Control-D (Windows).

    In the example solution, the report script is called “Sales Activity Report”.

  2. Open the duplicated script.

  3. Change the name by replacing “copy” at the end with “ - with find”.

    The example solution’s script is named “Sales Activity Report - with find”.

  4. Click the layout name associated with the second script step:  "Activity Report" (Activities).

    A dropdown appears.

    go to layout dropdown.png

  5. Choose the Layout... option.

    A Specify Layout dialog window appears.

  6. Select the "Summary Only" version of your subsummary report layout.

    In the example solution, this is "Activity Report - Subsummary Only".
    select layout summary report.png

  7. Click the OK button to dismiss the Specify Layout dialog window.

    Leave your "Go To Layout" script step selected. This causes any new script steps to be inserted after it.

  8. Add a Show Custom Dialog script step.

  9. Click the blue gear to set the script step options.

    A "Show Custom Dialog" Options window appears.

  10. Click the Specify button in the Script Step Options area.

  11. Set the Title to: Reporting Time Period

  12. Set the Body to: Do you wish to report on all [Secondary Tablename], those from the past 90 days, or those from the past 30 days?

    The example solution uses “Do you wish to report on all activities…”

  13. Set the Default button to: “All”

  14. Set Button 2 to: “90 days”

  15. Set Button 3 to: “30 days”

  16. Click the OK button to dismiss the dialog window.

 


Find the records (or Show All)

  1. Add an If script step.

  2. Click the fx button.

    A Specify Calculation dialog window appears.

  3. In the large open box, enter the following expression: Get(LastMessageChoice) = 1

    This is true if the user clicked the “All” button.

  4. Click OK to dismiss the dialog window.

  5. Add a Show All Records script step.

    This changes the found set of records to include all records in the table.

  6. Add an Else script step from the Control group in the script step list.

    This portion of the script after the Else runs if the user did not click the “All” button.

  7. Add an Enter Find Mode script step.

  8. Click the blue gear to set the script step options.

  9. Uncheck the Pause option.

    When this option is checked, the script will pause in Find mode to wait for input from the user. The goal of this script is complete automation with no user interaction, so the Pause option should not be checked.

  10. Add a Set Variable script step from the Control group in the script step list.

  11. Click the blue gear to set the script step options.

    A "Set Variable" Options dialog window appears.


  12. Set the Name to: $DaysInPast

  13. Set the Value to:  If( Get(LastMessageChoice) = 2, 90, 30 )

    This variable is set to 90 if the user chose the “90 days” response. Otherwise it is set to 30.

  14. Click OK to dismiss the dialog window.

  15. Add a Set Field script step from the Fields group in the script step list.

    It might seem strange to set the field value. But remember that at this moment in time, the script is in Find mode. So when you enter a field value, it’s like the user entering find criteria.

  16. Click the blue gear to set the script step options.

    A popover appears.

  17. Select the Specify target field checkbox on the popover.

    A Specify Field dialog window appears.

  18. Change the table drop-down to your secondary table (in the example file, this is “Activities”).

  19. Select the date field that you want to involve in your Find operation.

    Use the "Creation Date" or any other date field in your secondary table. The example file uses “Activity Date”.

  20. Click the OK button to dismiss the dialog window.


    Next you need to specify the expression to be used in the Find operation:

  21. Click the second Specify button (“Calculated result:”) on the popover.

    A Specify Calculation window appears.

  22. In the large open box, enter the following expression: ( Get(CurrentDate) - $DaysInPast ) & “...”

    Here you are calculating the start date in the past. Adding “…” signifies that all dates after the start date should be found.

  23. Click the OK button to dismiss the dialog window.

  24. Add a Perform Find script step from the Found Sets group in the script step list.

    You don't need to set any options for this script step. (Note that this step is different from the Perform Find/Replace script step located in the Editing group.)



Check if the find was successful


  1. Add an If script step.

    This is the second If/End If, enclosed within the first one.

  2. Click the fx button.

    A Specify Calculation dialog window appears.


  3. In the large open box, enter the following expression: Get(LastError) > 0

    This expression returns a true result if the Perform Find operation encountered an error (such as not finding any records). A true result causes the script steps within the If statement to be performed.

  4. Click the OK button to dismiss the dialog window.

  5. Inside the If statement, add a Go To Layout script step.

  6. Make sure this script step specifies “Original Layout”.

    This returns the user to the layout they were viewing before the script began.

  7. Also inside the If statement, add an Exit Script script step, located in the Control group in the script step list.

    This causes the script to stop running.



Move one step

 

There’s two things to do before your script is finished:


  1. Delete the Enter Browse Mode script step at the beginning of the script.

    It’s not needed. The Perform Find script step always leaves the user in Browse mode.

  2. Move the first step (Go To Layout) below the Show Custom Dialog step.

    If you don’t move the step, the user will see the layout change before the dialog is displayed. It’s better that the user not see the layout change until the script is ready to show the report.

  3. Save the script by typing ⌘S (Mac) or Ctrl-S (Windows).

    The finished script should look similar to this:
    sales activity report complete.png



Add to report button and test


  1. View your Main List layout in Layout mode.

  2. Double-click your report button (in the right-hand portion of the header part).

    A Button Setup dialog window appears.

    In Goal 3, you weren't ready to choose the script. It’s time to do that now.

  3. Change the Action to "Perform Script".

    A Specify Script dialog window appears.


  4. Choose your new “Sales Activity Report - with find” script and click the OK button.

  5. Switch to Browse mode and click the report button.

    Does the custom dialog present you with three choices?

    Do each of the choices give the results you expected, based on the date field you chose? We recommend that you test this again in the future when you have more data to work with.



      

      

Attachments

    Outcomes