Searching in a Range

[Jeremy Curry]

Hey there,

I'm pretty new to FileMaker, but I have a fair understanding of relational databases.

I am in the midst of try to restructure a database that has all of it's data in one table... over 450 fields. I didn't make it.

The database is about 5 years old, and it's for a weekly children's program. They keep track of the kids attendance in fields attached to the main table. In a testing file, I've rebuilt the database so that the attendance information is in a separate table. The new table consists of a date, the child's unique id, and an "attendance" boolean.

This information is then also displayed in a portal so that it is easy to add an additional attendance row. The date options are in a drop down from a separate table that defines which days the program happens.

What I want to be able to do is search the last 'x' weeks, and see which children attended 'y' times or less/more with in those weeks.

Normally, I would know how to program a front end how to do this, but have no idea how to do it through FileMaker...

I would normally search for the Child ID before date 1 and after date 2 and see how many rows returned and return all the children that had less/more than 'y' rows returned..

[Fenton]

There is a WeekOfYear (date) function, in the Dates section. From the Help:

Returns the number of weeks after January 1 of the year of date. Fractions of weeks occurring at the beginning or end of the year count as full weeks, so the WeekOfYear function returns values 1 through 54.

[Jeremy Curry]

Sorry, but I'm not quite sure how that's useful?

I need to get rows in the attendance table between date 1, and date 2 – at the very least I need to figure out how to find out how many entries exist between two dates.

[comment]

To find out how many entries exist between two dates, do a search in the attendance table for dates within the range. The result is given by the number of records in the found set - which can be returned by Get (FoundCount) or by a summary field counting the records' serial ID (or any other field that cannot be empty).

To find the children with a given number of attendances within the range is more difficult. You cannot do this by find alone, because the aggregate information is not (yet) anywhere in the system to search for. You need to define a new relationship between Children and another occurrence of Attendance, filtered by a date range:

Children::ChildID = Attendance 2::ChildID

AND

Children::gStartDate ≤ Attendance 2::Date

AND

Children::gEndDate ≥ Attendance 2::Date

gStartDate and gEndDate are global date fields in the Children table.

Once you have that, you can define a calculation field in Children as =

Sum ( Attendance 2::Attendance )

This will return the number of times the child has attended within the date range. Now you can search for a number in this field.

---

Note: in a properly normalized system, absences need not to be recorded, and it would be sufficient to count the attendance records.

[Jeremy Curry]

Thanks for your help... but I'm still new to scripting and everything in FileMaker, and not quite sure how to do what you are proposing.

[comment]

Uhm... what I wrote is pretty much a step-by-step. If you know how to define fields and relationships, you should be able to follow it. If there's some specific part that's not clear, please point to it.

Note that I am assuming that you already have two tables, Children and Attendance, and that they are related using ChildID as the matchfield.