3 Replies Latest reply on Dec 19, 2011 2:00 AM by MatthewKingham

    Newbie Question about Date Ranges

    MatthewKingham

      Title

      Newbie Question about Date Ranges

      Post

      Hi, I'm relatively new at this but I just want to create a simple function in my database and I'm struggling when it comes to which way would be the best way to go about it.

      I have 2 tables - Customers and Bookings. 'Bookings' has a date field.

      In the Customers table there are 2 fields (among others) called Winter and Summer. If the booking date is in a winter date range I want the Winter field to be ticked or say 'Yes' or something, and the same for the Summer.

      The tables are related, I'm just new when it comes to scripts and calculations etc...

      Am I right in thinking that the Winter and Summer fields should have auto-enter calculations which would say 'Yes' when a script finds a date in the date range?

      Thanks for your help.

        • 1. Re: Newbie Question about Date Ranges
          philmodjunk

          Am I right in thinking that the Winter and Summer fields should have auto-enter calculations which would say 'Yes' when a script finds a date in the date range?

          That's one option. It can also just be a calculation field.

          With either option, the "Summer" field's calculation might work like this if all dates from June through August count as summer dates:

          Let ( m = Month ( bookingDateField ) ; If ( m > 5 and m < 9 ; "Yes" ) )

          • 2. Re: Newbie Question about Date Ranges
            LaRetta_1

            "In the Customers table there are 2 fields (among others) called Winter and Summer. If the booking date is in a winter date range I want the Winter field to be ticked or say 'Yes' or something, and the same for the Summer."

            From the perspective of Customers, you will only see the first related Booking record.  I would assume that, if you have two bookings for a customer (one summer and one winter) then you would want both fields to produce 'yes'?

            You have not indicated your FM version but if you use vs. 11 then you can use filtered one-row portal and eliminate your two fields in Customer altogether (see link) for example.  Note specifically the use of the custom formatting applied to the dates in the portals to produce the yes values. I have colored the portals yellow.  You can make the portal transparent and you can turn off entry to the fields.  I left it open so it is easier to pull it apart.

            http://www.directlinesolutions.com/downloads/Periods.zip

            Note that, if you have a lot of related booking records (more than 3,000 or so) for any one customer then the filtered portal might begin to suffer performance issues.  If this might be the case then it is best to use a filtered relationship instead. :-)

            • 3. Re: Newbie Question about Date Ranges
              MatthewKingham

              Thank you, I think I understand it a bit better now - this has really helped. Laughing