Date range formula help...

[jschroeder68]

Very much a newbie, thanks for any help you might be able to give.

I'm building DB in Filemaker 9 for a youth athletic league. 2000 kids, and I want to automatically determine what age division (7-8=pee wee, 9-10=bantam, 11-12=intermediate, 13-14=senior) they play in by referencing their dob and display it in their record. I designed another table with starting date, ending date, and division name, and tried to have the "division" field do a lookup to see which date range their dob fell into and return that division name to their division field in their record.

Alas, I'm a moron, and this seemingly simple calculation evades me. Maybe a script would be easier? I figured the table would be better because we use the same calendar date every year to determine eligibility, and it might be easier to change the year only in the table.

Any advice is greatly appreciated. Thank you.

[comment]

An important question here would be: age as of when? Presumably, if someone is almost 13, they would be classified as "intermediate" until... well, that's the question.

[jschroeder68]

We determine age eligibility from how old the kid is on August 31st of every year for all programs occurring that year. So if a child is 8 as of Aug 31, he's a pee wee, whereas if his/her dob was aug 31, he/she would be considered a bantam.

I figured I'd do it as a table with a lookup just for that reason so I could just build a new simple table each calendar year instead of having to dig into a script or formulas.

Thank you so much for helping - I really appreciate it.

[Lee Smith]

This topic has come up before.

Start with this Thread, Link

Edited February 11, 2008 by Guest
corrected link to Thread

[comment]

See if this can get you started.

DivisionByAge.fp7.zip

[comment]

I was in a hurry before, but this is a little more complex than it seems, and some notes are in order:

The 'correct' way to do this is to have a record for each division/year combination (4 new records each year). In these records, the cut-off birthdate can be stored - so that a bi-directional relationship is possible to the Players table. Otherwise there has to be a global year in one of the tables, and then either the division cannot "see" its players (as in my example), or the players cannot see for which division they are eligible.

Strictly speaking, there should be also another table of Registrations, so that a player registers each year for a division.

[jschroeder68]

THanks so much. It's going to take me a little time to digest that, but I'm a little satisfied I wasn't missing a simple answer.

The crazy thing is, if I had a table with every single birthdate (2918 records?) listed between the eligible years (7-14) and a second field with their division, this would be easy - like a zipcode lookup.

That just seems like such an idiotic way to do it, but it might be the simplest. Part of the problem is that we're importing text records from our registration software.

Thank you so much, and I'm sorry to the other guy that I wasn't able to find the solution or topic on my own.

[Lee Smith]

Click on the word [color:red]Link in my other Post, if I'm the "Other Guy" you are referring to.

[comment]

if I had a table with every single birthdate (2918 records?) listed between the eligible years (7-14) and a second field with their division, this would be easy

It would be the same as having only the starting date and ending date - only more complex. Filemaker (since version 7) allows you to define a relationship based on range. Even in earlier versions, a lookup could achieve a very similar result. Because this is not truly about range, but about upper limit. So in fact, you only need to know the actual birthdate of each player, and the earliest birthdate that is allowed for each division in a particular year.

[jschroeder68]

OK, another silly question - In your "players" table there is a field named "-". What am I missing - isn't this something that shouldn't be done?

I created fields in my existing file that matched your "player" table, and used your "divisionbyage" table and linked to it, but it's not happening (at least without the "-" field).

I've attached the file if you feel like looking at it, but I understand if you don't have time. Thanks again.

master_contacts.fp7.zip

[comment]

The field named "-" is purely cosmetic (separates data from calculations).

I cannot see how you defined your relationship, since the other file is missing. I'd suggest you do it from scratch in a single file - AFTER you have understood how and why it's supposed to work.

[jschroeder68]

Great - thanks so much. I'll update you on my progress this week. I really appreciate your help.

[Lee Smith]

Somehow I linked the wrong thread above, sorry about that.

I have updated it to show the Thread I meant, although the Thread and the files date back, take a look at the one I posted, and the the one by CobaltSky for additional ideas on how to work this out.

Lee

[jschroeder68]

OK, here's what I ended up with. A friend pointed me in the right direction, and it was along the lines of what I was initially trying to do.

If ( Month ( dob ) ≤ 8 ; If ( age ≥ 7 and age ≤ 9 ; "Pee Wee" ; If ( age > 9 and age ≤ 11 ; "Bantam" ; If ( age > 11 and age ≤ 13 ; "Intermediate" ; If ( age > 13 and age ≤ 15 ; "Senior" ; "Alumni" ) ) ) ) ; If ( age ≥ 7 and age ≤ 8 ; "Pee Wee" ; If ( age ≥ 9 and age ≤ 10 ; "Bantam" ; If ( age ≥ 11 and age ≤ 12 ; "Intermediate" ; If ( age ≥ 13 and age ≤ 14; "Senior" ; "Alumni" ) ) ) ) )

Thanks again. You guys are really cool for helping knuckleheads like me out with this. Cheers.

[Søren Dyhr]

You are hopefully aware how indigestible your calc appears. In order to grasp the reasoning behind should you turn to this page:

http://www.aptworks.com/cgi-bin/calc_formatter_2v5.cgi

Which is a calc'-formatter, which BTW would show your calc this way:

If(

    Month( dob ) ≤ 8;

    If(

        age ≥ 7 and age ≤ 9;

        "Pee Wee";

        If(

            age > 9 and age ≤ 11;

            "Bantam";

            If(

                age > 11 and age ≤ 13;

                "Intermediate";

                If( age > 13 and age ≤ 15; "Senior"; "Alumni" )

            )

        )

    );

    If(

        age ≥ 7 and age ≤ 8;

        "Pee Wee";

        If(

            age ≥ 9 and age ≤ 10;

            "Bantam";

            If(

                age ≥ 11 and age ≤ 12;

                "Intermediate";

                If( age ≥ 13 and age ≤ 14; "Senior"; "Alumni" )

            )

        )

    )

)

Which reveals to me at least.... that the same could be written this way:

Let(

    tt = Month( dob )  >  8;

    Case(

        age ≥ 7 and age ≤ 9 - tt;

        "Pee Wee";

        age  ≥  10 -tt and age ≤ 11 - tt;

        "Bantam";

        age  ≥  12-tt and age ≤ 13 - tt;

        "Intermediate";

        age  ≥  14-tt and age ≤ 15 - tt;

        "Senior";

        "Alumni"

    ))

--sd

[comment]

Actually, if one wanted to go with a calculated result, it could be written as:

Case (

age > 14 ; "Alumni" ;

age > 12 ; "Senior" ;

age > 10 ; "Intermediate" ;

age > 8 ; "Bantam" ;

age > 6 ; "Pee Wee"

)

I'm not sure what role Month ( dob ) ≤ 8 plays here, since that should be included in the age calculation. The fact that there needs to be an age calculation, presumably unstored and depending on the current year, is the week point of this approach - there is no trail of who was eligible for what when.

[Søren Dyhr]

Indeed.... wonder why I didn't make the connection, since I usually would solve such matters with:

http://www.filemaker.com/help/FunctionsRef-316.html

--sd