AnsweredAssumed Answered

Creating unique records based on dates and/or date range

Question asked by 709republic on Jun 11, 2010
Latest reply on Jun 15, 2010 by 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 -->












































Outcomes