Confused at setting up relationships for Pet Hotel Solution

[Brisprad]

Hi,

I am working on a solution for the pet hotel I work at and am confused at setting up part of the relationships. First let me explain what I am trying to do. Then, I'll explain what I've done so far.

The solution is designed so that it has Pets related to Clients. You can then add a Reservation for any of the Pets. For each Reservation, a Pet can be scheduled for various Activities throughout their stay (like walks and playtimes). At check-in, Charges are created based on the days of the Reservation and Activities added. At Check-Out, an Invoice is printed.

Here's what I have. Pets are related to Clients. Reservations are related to Pets. Activities are related to Reservations and are also attached to a separate table with the names of the Activities (so the Activities names can easily be changed/added to later). Charges are related to Reservations. Charges would include line items for non-Activity services/products. Graphically it could be written:

Clients->Pets->Reservations->Activities->Charges

Looking at it, I know this should work but I can't figure out how for sure I can add the charges for the Activities based on what is on the Reservation. The hard part (for me) is knowing that each Activity might not be every day-it might be every other day or even every 3 days. I am looking to print a report for each Activity each day. Add this to how I want it to look on screen and I'm very confused!

Hopefully this is clear enough. I've tried to do as much on my own (and by reading this awesome forum!) but I'm stuck. Any suggestions would be greatly appreciated.

Thank you in advance!

Brian

[CoZiMan]

Wow, you've got a great project there.

A suggestion that might help from what you describe:

'Look for the unique data.'

For instance you have several unique data types with which you can build relationships.

Pets -unique (each one is itself) but Client Info may be a requirement to correctly identify 'spot' I suggest a concatenated field of 'spot' and client phone number. The odds of someone having the same phone number and the same named pet are negligable. This creates a definitely unique Record ID for each creature.

I would suggest a unique entry for 'Reservation' as well and this could be a 'statement or invoice' number.

This Record ID (Pet-Phone) now allows you to use the other unique data , such as Client, Reservation and Activity, to be related specifically to this creature for reporting.

Using a lokup for a 'Charges' entry field makes sense so that any price changes don't affect historical (closed) invoices.

So you'll need several tables to tie this all together.

If you need more specific help this is the place to get it.

[Søren Dyhr]

You should investigate Rays template:

http://www.nightwing.com.au/FileMaker/demos7/demo705.html

--sd

[Brisprad]

Thanks to you both, CoziMan and Soren.

I'm sure I can get it if I just think it through. I'm trying to get a prototype you could say together to get approval for the bigger project. It needs to be a little functional to show off the ideas I have and to show that I can do it.

I have found great information here. A while ago, I read how to do a conditional lookup. I had in idea how to do it and was glad to find out that I wasn't far off.

Thanks again.

-Brian