You'll need to tell us a lot more about your database before we can provide a suggested solution. How your tables are linked to each other in relationships will be crucial to devising the best solution.
Also, please let us know what version of FileMaker you are using. FileMaker 12 offers some new options that use ExecuteSQL to pull data from other tables without needing relationships to do so and in formats not easily produced in earlier versions of this software.
I am using FileMaker 11 (but considering upgrading, especially if you suggest it would make this easier).
It is a pretty simple database. The Bookings table is the parent, kp_ bookingID joined = to kf_bookingID in 2 link tables for presenters and schools,
kf_presenterID and kf_schoolID = kp_presenterID and kp_schoolID in resepctive child tables.
The link tables are populated via portals in a layout based on Bookings table. So for each booking, I add a school and a selection of presenters.
I interpret that to mean that you have these relationships:
Schools::kp_SchoolID = Booking_School::kf_SchoolID
Bookings::kp_BookingID = Booking_School::kf_BookingID
Presenter::kp_PresenterID = Booking_Presenter::kf_PresenterID
Bookings::kp_BookingID = Booking_Presenter::kf_BookingID
While the join (link) table names may be different, does this structure match what you have here?
I only need the schools and presenters listed once, even though often a school is visited more than once, and a presenter often does multiple presentations.
If you try to create an example of your report with paper and pencil, you'll find that this is not possible as you have many presenters presenting at many schools. Your report can either list each presenter once or each school once--not both.
I think that you want a report that looks like this:
Bookings For Jefferson High School
Joe Smith (include any summary data for this booking here)
Is that correct?
And for any given record in "booking" can their be more than one presenter booked? And for that one booking, can they be booked at more than one school?
I ask that because it looks to me like you can simplify your relationship structure to be:
and if that can be made to work, your report becomes much simpler to set up.
You have interpreted the relationships exactly right.
Any booking will be with one school, but there is a team of around 4 presenters for each booking, normally with a different team for each booking. In answer to your question, presenters cannot be booked for more than one school for that one booking. A booking is specific to the one school.
The problem I have is that the layout and content of the term report has been determined externally by the parent charity, so is not flexible.
The report I am required to produce is more like this:
- No. Schools visited this term
- List of names of schools visited (named once even if visited more than once)
- Presenters active this term (list of names)
- Plus other summary stats (from the booking table, I know how to do this part)
If I try a report with subsummary layout sorted by term, the summary stats are correct, but it only pulls out the first school name and the team who visited on that first booking. Same if I put a portal on this report, or if I use a calculated value list.
To start, you can dispense with the "link" table between schools and bookings. If a given booking record never links to more than one school, there is no need for that link table as you have a one to many relationship from school to bookings instead of a many to many relationship.You do need the link on the other side of Bookings to facilitate the many to many relationship with presenters.
This report would indeed be much simpler in FileMaker 12 using ExecuteSQL to pull up a list of schools and a list of presenters using the DISTINCT key word to drop out duplicates. But FileMaker 12 represents a significant change in many areas and even with three "bug fix" updates, there still are a number of issues with it. You might download the 30 day free trial and see how your Database looks and functions after conversion to the new file format before deciding whether or not to buy.
The parts of your report are more easily produced as separate items on different layouts.
The first two can be achieved by performing a find on the schools table specifying a date range or term in a field in the related bookings table. This finds all schools record with at least one related bookings record that meets the time interval criterion. That lists the schools and the number of records found is the number of schools.
After finding all bookings records, you can get a list of active presnters by using Go To Related Records with the Match Found set option to pull up a list of Presenters on a Presenters layout.
So one option here is to use a script to do exactly that, but then copy this data into Large, sliding text fields on a report layout based on your Bookings table.
Copy All Records can be used to copy a return separated list of school names from a layout based on Schools if that layout only has the school name field on it. This can then be pasted into a text field and ValueCount ( SchoolListField ) would then tell you the number of schools in that list. The same trick can be used to get a list of presenters.
Note: depending on what data from Bookings is needed, you may be able to not do this for Schools and just do it for Presenters. If you define a list view layout based on Bookings where you remove the body layout part and replace it with a sub summary part "when sorted by Schools::__pkSchoolID", you can get a list of all schools booked in the specified time period and each school will be listed only once. This does, however require using the "How to count the number of unique occurences in field." method for getting a count of the number of schools in that list, however.
Thanks that's very helpful.
Could you possibly also give me an idea of the script "using ExecuteSQL to pull up a list of schools and a list of presenters using the DISTINCT key word to drop out duplicates"?
It's not a script, it's a calcualtion field. The results from your query would be displayed in a single, large text field set to slide up/resize enclosing part.
See this thread:
Thanks, that's so much easier than in FM11,
I haven't come across any bugs in my database so far, so looks like it makes sense to upgrade
Really appreciate your time