Creating a Related Record Set based on dates

[709]

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 will be generating 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 the dates as 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 have to 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, it would be greatly appreciated ?

Regards,

Steve

[Fitch]

What is the question?

[709]

The question is, what would be the best approach to create a record for each day in the (see output concept) Itinerary table for each tripmain_id date range through a script ? Or if there is another method that would accomplish the same thing.

Hope that makes sense.....

[709]

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

[Fitch]

Sounds good, glad I could be of help.

Seriously, it seems like you're on the right track. If I could add anything, it might be that you should be able to update the day numbers and dates as you create the records, so you wouldn't have to loop through them again or do a replace.