relationships defined by more than one field

[kazza]

ok , I've only been using filemaker for about 2 weeks so sorry if this question is stupid.

i have 2 files, one where the user enters data about classroom bookings and one where there is an overview of the all the rooms in a day.For each day i need to be able to show all the rooms and have what activity is booked for that room. Any help would really be good. Or any suggestions of doing this whole thing diferently.

[CobaltSky]

Hello kazza,

If your description is correct:

"For each day i need to be able to show all the rooms and have what activity is booked for that room"

...then it would seem that you need to relate the files based on both date and roomID.

Since you are using FileMaker 6, you will need to create a calculation field in each file which puts the two values together into a single string, then define your relationship to match the calculation fields to each other.

The calculation formulae will be along the lines of:

YourRoomIDfield & YourDateField

and the calculation result for both calc fields should be set to text.

As part of this set-up, you might find it convenient to define the date field in the overview file as a global field. That way you'll be able to change the date in one place and all the room records will update accordingly.

You might also find it useful to display the booking details (from the other file) in a portal within the overview file.

If you're not sure about any of the terms or details of what I've suggested, post back and I'm sure one of us here will be happy to elaborate. :P

[kazza]

Your an absolute LEGEND!

Thankyou so so much it worked perfectly.

Now i just have to figure out the rest of this thing .

[bbinca]

Your an absolute LEGEND!

LOL!This is true! Another Zorro!

So you've learned quite a bit in your first two weeks.

[kazza]

A fair bit but nowhere near enough to get this database working!! I dont suppose your excellence could answer this for me....

How do i get a total of how many new schools were entered into the system for a month? I've got a list of all schools with how many kids came in each and a total of that, but now i need the number of schools that are new.

I believe in you zorro!!!

[CobaltSky]

erm - well I'm not feeling particularly zorrowful, but I'll try to help...

I guess that perhaps, as part of your solution, you have a FileMaker 6 file into which you are entering the schools? If so, then one way to solve your current problem would be to:

1) Create a date field called GenDate and set it up to auto-enter the creation date.

2) Create a calculation called cMonthID which returns a text result, with the formula: Month(GenDate) & "/" & Year(GenDate)

3) Create a global text field called gSelectedMonth

4) Create a relationship called SelectedMonthSchools which joins the file to itself and matches the gSelectedMonth field to the cMonthID field

5) Create a second calculation field called cNewSchools which returns a number result, with the formula Count(SelectedMonthSchools:: GenDate)

Once the above is in place, if you enter a month and year in the format 12/2005 into the gSelectedMonth global field, the cNewSchools field will show the number of new schools added in that month. :