8 Replies Latest reply on Jun 15, 2010 7:32 AM by 709republic

    Creating unique records based on dates and/or date range

    709republic

      Title

      Creating unique records based on dates and/or date range

      Post

      Hi,

      Hopefully this will make sense, as I have a customer with FileMaker database that holds Hotel confirmations and costings. The data is fairly straight forward.

      maintrip_id
      tripdetail_id
      hotel_id
      checkin_date
      checkout_date
      etc.

      The customer would like to generate travel itineraries for their clients based on each of the days client is traveling. The key date is based on the check in day in a particular hotel.

      The itineraries will contain ad hoc text of things the clients can do while they are in a particular location for each given day.

      What I can not get sorted out in my head and this is a specific customer request - they want to refer to each of the travel intinerary enteries as a day number.

      For example:

      Client checks into "Hotel A" on January 1st and checks out on January 2nd this should be referenced as DAY 1.

      On January 2nd client checks into "Hotel B" and checks out on January 5th.

      These dates look like this:

      Jan 2 = Day 2
      Jan 3 = Day 3
      Jan 4 = Day 4
      Jan 5 = N/A


      Records in [TripDetail] table look like this:

      [TripDetail]
      maintrip_id=100
      tripdetail_id=1
      hotel_id=2
      checkin_date=01/01/2010
      checkout_date=01/02/2010
      numberofnights=1

      [TripDetail]
      maintrip_id=100
      tripdetail_id=2
      hotel_id=3
      checkin_date=01/02/2010
      checkout_date=01/05/2010
      numberofnights=3


      The customer will create itinerary information (text) for each date/Day:

      This process is completed after all of the hotel arrangements have been created and confirmed.

      What I am visualizing is a script that will go through the above referenced table and create a day record for each date and place it in a related table. The customer then can recall the related record(s) from the particular hotel record and input the required travel text for each day.

      Output Concept

      [Itinerary]
      itin_id=1
      tripdetail_id=1
      referencedate=01/01/2010
      daynum=1
      traveltext={TEXT}

      itin_id=2
      tripdetail_id=2
      referencedate=01/02/2010
      daynum=2
      traveltext={TEXT}

      itin_id=3
      tripdetail_id=2
      referencedate=01/03/2010
      daynum=3
      traveltext={TEXT}

      itin_id=4
      tripdetail_id=2
      referencedate=01/04/2010
      daynum=4
      traveltext={TEXT}

      If anybody has any thoughts or insight on this, or some advise on possibly a better way of doing this. any help would be greatly appreciated.

      Regards,

      Steve


      <!-- POLLS --><!-- FILES --><!-- SIGNATURE -->












































        • 1. Re: Creating unique records based on dates and/or date range
          philmodjunk

          I can think of an approach that works, but first a basic question needs an answer:

           

          It seems that you assume that your itinerary records represent 1 day events and should be so identified with no gaps in the sequence.

           

          What happens when this is not the case? (Say an item on an itinerary is a 2 day seminar that doesn't start until the last two days of the person's trip...)

           

          • 2. Re: Creating unique records based on dates and/or date range
            709republic

            Hi Paul,

             

            Thank you for replying to my query.

             

            You bring up a good point, however, in this case the customer is providing the client with a printed itinerary of suggested sites to visit for a particular day. After confirming with the customer, they have indicated that the scenario you have presented never happens.

             

             

            Regards,

             

            Steve

            • 3. Re: Creating unique records based on dates and/or date range
              philmodjunk

              Hmm, never is a dangerous word sometimes....:smileywink:

               

              There are a number of variations on the same basic idea, but here's the general outline.

               

              Find all Trip Detail records of the same Main Trip ID and sort in ascending order by Trip Detail ID.

              Loop through these records

                 In each pass through the loop:

                 Go to the Itinerary layout and find all Itinerary records with the same Trip Detail ID using a scripted Find or a Go To Related Records step

                 Use replace field contents or loop to put a serial number starting with one in each records Day field

                 Return to your Trip detail record

                 Go to next trip detail record

              End Loop

              • 4. Re: Creating unique records based on dates and/or date range
                709republic

                Hi Paul,

                 

                Thanks again for your response. 

                 

                In your solution, in particular:

                 

                Go to the Itinerary layout and find all Itinerary records with the same Trip Detail ID using a scripted Find or a Go To Related Records step

                 

                I might have missed, or misunderstood something as the itinerary records have not yet been created. I suspect I would have to determine the actual number of days from the date range (min & max) of the [tripdetails] table for each maintrip_id and create records for the actual number of days. Then process the above??

                 

                Of course its Friday, sunny and hot out. So my brain not be working to capacity.  ;)

                 

                Regards,

                 

                Steve

                • 5. Re: Creating unique records based on dates and/or date range
                  philmodjunk

                  I assumed you would input or import this data and then run the script to assign the "day" numbers. It assumes that itinerary records are consecutive with no gaps between start to finish for a particular location. (That's why I asked the question I did at the beginning.)

                   

                  Note that with some displays, you wouldn't need to do this at all. If you display only the records for a particular location via a find or in a portal, you can simply number them in this fashiion by placing @@ as layout text in the body or portal row to show the record numbering.

                  • 6. Re: Creating unique records based on dates and/or date range
                    709republic

                    Hi Phil,

                     

                    I guess what I really want to do is automate the creation of a set of related records based on the minimum startdate and maximum enddate.

                     

                    My thoughts are:

                     

                    1) determine the actual number of days from the min of start date - max of end date, this will be = X

                    2) create X (from above) related records in the itinerary table 

                    3) loop through the newly created related records and insert day number starting at 1, then 2 etc into the appropriate field until there are no more to update

                    4) loop through the records again (itinerary table) and add the appropriate  date to the day number ( min date = day 1) until each record has a date.

                     

                     

                    Does the logic above make sense or do you have any additional thoughts on creating a script to accomplish such a task ?

                     

                     

                    Regards,

                     

                    Steve

                     

                     

                     

                     

                     

                    • 7. Re: Creating unique records based on dates and/or date range
                      philmodjunk

                      That can be done. There's no need to loop through the records twice. You can assign dates and day numbers in the same loop, though I'd probably not assign day numbers if I have the actual dates in each itinerary record, a calculation field can subtract the itinerary date from the start date in the related detail record to compute the day number. This has the added advantage of automatically updating the day number if you modify either date field.

                      • 8. Re: Creating unique records based on dates and/or date range
                        709republic

                        Hi Phil,

                         

                        Thanks again for your help. I utilized a part of solutions you gave to someone else to come to a final solution.

                         

                        http://forum-en.filemaker.com/t5/Using-FileMaker-Pro/Automatically-insert-delete-multiple-records-in-related-table/m-p/63339/highlight/true#M49264

                         

                        Regards,

                         

                        Steve