9 Replies Latest reply on Feb 4, 2015 12:37 PM by DanielJenkins

    Report

    DanielJenkins

      Title

      Report

      Post

      I have created a report which displays data from my booking table.
      It displays Booking ID, Accommodation Type, Arrival Date, Departure Date and Customer ID.
      It sections the data by accommodation type.
      In addition to this, I've create a script which runs when I click a button on my main menu, the script is to open this report and only show bookings within 'Winter'. I have also created a report for bookings within 'Summer'.

      The script i've used is:
      Enter Browse Mode []
      Go to Layout ["Winter Bookings" (Customers)]
      Perform Find [Restore]
      Sort Records [Restore; No dialog]

      The script for the find is:
      Field: Bookings::Arrival Date 
      Criteria: <=30/09/2015
      AND >=01/04/2015

      Currently, it is not performing this find correctly.
      Can anyone offer any advice as to what I need to do? 
      Also how can I display all the records on one form? Currently one set of records is displayed per form.
      I have attached a screenshot illustrating what is currently displayed

      Thanks, Daniel.

       

      Screen_Shot_2015-02-03_at_19.21.49.png

        • 1. Re: Report
          philmodjunk

          To find records in a range of dates, use:  .01/04/2015...0/09/2015

          And I recommend scripting your finds using the examples found Scripted Find Examples so that a user can perform the same find, but use different dates for the date range without having to rewrite your script.

          how can I display all the records on one form?

          Set up your layout to use List View instead of Form view. And you don't actually have to use a different layout for your winter and summer reports. You could use the same layout for both--even if you want to use different graphics for the two reports.

           

          • 2. Re: Report
            DanielJenkins

            Thanks for that, when I press the button in browse mode and it shows the report with the bookings for either summer/winter depending on which button I pressed, it goes into sort mode. If I go to bookings where I should be able to view all bookings, I cannot, I can only view the bookings which were present in the report. To undo this I have to manually click "Show All" on filmmakers menu. Is there any way around this? 
            I have a home button located on every layout which directs the user to the main menu layout, would it be best to add to the script of this button that it unsorts the records? so that all of them can be viewed again?

            Also, How would I deal with the winter bookings, because the range would be the dates outside of 01/04 - 30/09. I'd want the range to include dates from 01/01 - 31/03 and 01/10 - 31/12. 
            In regards to the range, Is there anyway for the year not to be included? So if i pressed this button next year, 2016, it would find the correct records? Currently 2015 is automatically added to the date range within the find record action in the script.

            Thanks, Daniel.

            • 3. Re: Report
              philmodjunk

              ctrl - J (Command - J on macs) is the keyboard short cut for show all records. Using that shortcut is a pretty common thing to do with Filemaker layouts.

              The reason for what you are getting is that layouts that share the same table occurrence designation in Layout Setup | Show Records from also share the same found set. Searches and sorts on one table automatically affect what is seen on all layouts that share the same table occurrence context.

              Ways to work around that:

              On Layout Enter can perform a script to show all records each time you return to your original layout.

              You can open a new window for the report before performing the find. The found set produced in a different window is separate from found sets in a different FileMaker window.

              You can go into manage | Database | Relationships and make a duplicate of the Bookings table occurrence box. Then base your report layouts on one of these and your original layout on a different one. Even though they are still based on records from the same table, using a different Tutorial: What are Table Occurrences? as the layout context results in separate found sets of records.

              I'd want the range to include dates from 01/01 - 31/03 and 01/10 - 31/12. 

              01/10/2014 ... 31/03/2015 is the range that would do that.

              In regards to the range, Is there anyway for the year not to be included?

              When specifying a date range, you will need the year, but your script can take the date produced by Get ( currentDate ) and calculate the year to use for each of your scripts. You can find examples of set field steps used to specify calculated search criteria in the link of find examples that I recommended.

               

              • 4. Re: Report
                DanielJenkins

                Where would I enter the Get (current date) in the script? I cannot find it on the left hand side menu when editing the script
                Thanks, Daniel

                 

                • 5. Re: Report
                  philmodjunk

                  Get ( CurrentDate ) is something that you would enter into the Specify Calculation dialog when you set up the calculated result part of the set field step.

                  When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                  • 6. Re: Report
                    DanielJenkins

                    But how would I get the Get (current date) for the year to apply to the perform find criteria?

                    Thanks, Daniel.

                    • 7. Re: Report
                      philmodjunk

                      What I had in mind for your spring/summer season would look like this:

                      Set Field [ Bookings::DateField ; //see calculation below ]

                      Let ( y = Year ( Get ( CurrentDate ) ) ;
                              "01/04/" & y & "..." & "01/09/" & y
                            )

                      But I just ran a test with a manual find and found that I could specify a date range without using a year. Thus, "01/04...01/09" should also work and be much simpler.

                      • 8. Re: Report
                        DanielJenkins

                        I tried 01/04 ... 30/09 but when I enter that criteria it stores it as 01/04/2015...30/09/2015 automatically

                        If i used that set field, wouldn't it change the stored field within a booking and not set the field of the perform find? within the script

                        Thanks, Daniel.

                        • 9. Re: Report
                          DanielJenkins

                          Also I used a duplicate table for my reports and it still leaves all records in sorted when I leave that report. Meaning if i returned to the bookings I could only view the ones present in the report