Excel or Filemaker?

[zradosevich71]

I have been developing a solution for a couple of months now that will eventually be for registering for athletic events online. The information that this is based off of comes from an excel spreadsheet that lists the event number, the event name and competitor eligibility. When it was done the old way (on paper) competitors were expected to know their own gender, age and skill level - then submit their reg. through the mail. This was a logistical nightmare for those of us sorting through it in the end and correcting errors. I have the solution set up so that a pull-down menu appears under event registration that so far will only allow competitors to register for events for the gender they said they were, and the skill level that they claimed to be. However, they still have to sort though a plethora of age groups to find the correct age bracket. I would like to limit the value list to age as well. The problem is, each division number ORIGINALLY included many age possibilities, both genders and all skill levels. I manually broke-down, say Event BD-123 which would allow males and female ages 17 and under to compete as advanced, intermediate or novice into an individual row in excel for each option. What started out as one line item on a registration form became 6 rows to accommodate each option. The original sheet had 100+ divisions, and now broken into just those two options, the spreadsheet is 600+ rows. (I'm trying to explain this as clearly as possible!) I guess I'm not feeling very ambitious to get started breaking each division manually into separate ages, as I'm guessing that there could be thousands of rows designating each event option by the time I'm done. Does anyone know of a way to get filemaker to validate age with, say, a calculation? If a person's age falls between 1 and 17, then only that event pops up for them, or if they are in the 11-12 year old bracket and they are 12, then that becomes available? Please advise. Thank you!

[Ender]

I think you were on the right track before. You'll need to do the filtering via a relationship, and that means having records that describe the conditions of who's allowed for each event.

 

There's a couple directions you could consider:

 

1. Break all the conditions into the most granular bits as records in a big Event-EventEligiblityCriteria join table. You'd have to specify explicitly which types of competitors would be able to see each Event.

 

2. Use a flat structure for the criteria, where a few fields in the Event table define the eligiblity criteria, each with multiple choices (like checkboxes).

 

3. Use a hybrid approach, using the join table for significant categories or groups, and using multiple choices (like checkboxes) for sub-categories.

 

Either way, the relationships will be similar, relating from the registration interface to where you have the eligibility criteria specified, using a multi-criteria relationship.

 

For example, using the join table the relationship might look something like:

 

Registration Event_EventEligiblityCriteria =

Registration::Division = Event_EventEligiblityCriteria::Division

AND Registration::Age ≥ Event_EventEligiblityCriteria::MinAge

AND Registration::Age ≤ Event_EventEligiblityCriteria::MaxAge

AND Registration::Skill Level = Event_EventEligiblityCriteria::Skill Level

 

 

Using the join table, you'll have more flexibility than the flat structure. The hybrid approach can save some data-entry effort over the granular join table alone.

[zradosevich71]

Thank you, I'm trying your suggestion using a join table, but I've got another issue. The TO that I have relating skill level and gender are as follows:

 

EventTABLE::Gender = CompetitorTABLE::Gender AND EventTABLE::Skill_Level = CompetitorTABLE::Skill_Level

 

And this part works. However, the EventTABLE is the expanded version that I mentioned earlier that has the 671 rows from the Excel spreadsheet that I had to expand out. Is it possible to create an association on the CompetitorTABLE TO to the SimpleEventTABLE that is not expanded, but to which I created fields for min and max ages- and have this still work in the current pull-down? Or do I need to create these age range fields in the expanded EventTABLE?

 

The pull-down is displayed using the event number from the extended EventTABLE and a second, calculated field (displaying gender, skill level and age range) that also comes from the EventTABLE (called event description).

 

Thanks, let me know if you need more information.

[zradosevich71]

Appreciate all your help... I answered my own question...

 

Used the association with the table occurrence and everything works.

 

Thanks a bunch!