Report number of fields within a date range

[Matthew Bloomfield]

Hi, 

 

I made a Filemaker Database a couple of years ago for a correspondence school, now I'm building the same thing for another school. The database has a record for each student and a field for each possible lesson they can be sent. When a lesson is sent the date goes in the appropriate field.

 

For reporting I had no idea what I was doing, so I just made a table with a field for each lesson and a script that would search within a date range, take a record count of the returned records take that number and put it in my report table. The date range is set by global fields for the start date and the end date. The script would repeat for the 200 or so lessons that we had available. I would then have a report of the number of each lesson that was sent in a date range. 

 

I guessing that there must be a better way, I have never used a summary report, is that something that would work? If someone could point me in the right direction I would be very very grateful.

 

Thanks heaps in advance for any help or pointers. Using Filemaker 16.

[doughemi]

200 fields for lessons in each student record? Oh, no no no!

Create a table of lessons, one record for each lesson. You already have a table for students. Add another table for StudentLessons. Each StudentLessons record will define one lesson for one student. This is called a join table. It will have a field for StudentID, LessonID, DateSent, DateReturned, and Grade (plus whatever other information you need to record about that student taking that lesson).

The StudentLessons table will be related to Students by Students::ID = StudentLessons::StudentID and to Lessons by StudentLessons::LessonID = Lessons::ID.

Now you have a structure that will allow you to report on just about anything related to students or lessons.

We will need some more information about what you want to report to help you create your reports, but first you need to get your structure right.

[Matthew Bloomfield]

Thanks for the quick reply! And thorough advice. This is very timely as I have to make some major adjustments to the first database (it is getting merged with another) so I will fix that one next (it has 15000 records so might take some time).

I think I understand what you are saying, I have attached an image of what it looks like, does it look like I have the right idea? I have added a few extra things like:

• Study series - there are about 20 lessons in each series
• Offers - We also send out free offers from a TV show
• Donations

Would I still be able to have a page like the attached where the user can see all the items sent to a student? How would I make a field show a date for a particular study?

The things we want to make reports for are things such as:

• Number of lessons/certificates sent in a week/particular time frame
• Donation amount in a particular time frame

Thanks again for your quick reply. This is a bit of a revelation for me.

[doughemi]

Overall, your diagram looks good. You will need a separate Table Occurrence (TO) of the Study Series table to connect to Student Certificates to prevent the circular relationship.

You may also need separate relationships with new TOs of some of your tables (using global date fields) to generate your report page. The individual study series data would be in portals to the relevant TO.

You might want to do internet searches for "FileMaker Anchor Buoy" to help you develop this aspect.

[Lee Smith]

Hi Matthew,

I haven't analyzed your ERD, however you might find these Free ERD sites helpful for this or future projects.

https://www.draw.io

and

http://www.databaseanswers.org/data_models/

[Matthew Bloomfield]

Thank you Doug and Lee, that's all very helpful.

I had a couple of posts in the meantime but I managed to answer the questions myself so I deleted them.

I have set up the database, and made my main layout using portals for each series of studies (pic attached). Do I need a unique serial number for studentlessons?

I have been able to set up a report for number of certificates by series in a time period, and should be able to do the same for lessons and offers. Is there any way to combine all of those into one report or would it be easier to just do seperate reports and export them appended together as a PDF?