I need some experienced opinions. I have a DB that collects info on what
activities a student is interested in. The Challenges are:

1) The list of activities is long. Ever seen one of those
warranty/questionnaire cards with every activity/hobby under the sun? Our
form is like that.

2) The specific activities the client wants to see listed on the Student
Detail layout changes from year to year. One year they add "Golf", the next
year they decide they don't want to see "Golf."

2a) Even if the client changes what activities they want to see, I think it
prudent to save that activity data in case they change their mind (again)
and want it back.

Now I can think of three possible solutions:

A) An individual field and individual Value List per activity;

B) An individual field per activity, each using the same value field ("Yes"
or "1" for example)

C) Use a value list that just keep growing with additions.

Solutions A & B have the advantage of keeping all data regardless of whether
it's seen on the Profile layout or not. The advantage to A is that the
activity name can export out, but I'm not sure I'll ever need to worry about
that. I'm a little bothered by the bloat of fields, but don't see any way
around that.

A is a lot of initial work. B is half the work.

If the client ever wants reports from activities I'll need to create a new
field for any solution (either a Summary field for A & B, or a Calc field
for C).

Any other options or ideas? Any advantage to having a separate Activities
table? (All the students have activities, but would this be easier to
maintain as a separate table?)

I'm using FM 7.

Thanks,
Steve

The best approach is:

- A separate "Activities" table.
- Value list based on that table
- Addition of field to mark whether an activity is shown or not

e.g.:

Activities
==========
Activity_ID
Activity_Name
Category
Type
Appears (1/0)
etc.

Relationships can control which activities are shown where, and can suppress
values where Appears=0.

Bill

"Steve McGillivray" <stevemcgillivray (AT) cox (DOT) net> wrote

Bill, I love it. Simple, elegant, easy to use.

But a question: What relationship?

I created the Activities table. Then create a Value List based on the
"Activity_Name" field. Then created an "Activity/Interest" field in the
Student table and formatted it using the "Activity Name" Value List.

And I got the whole list of activities. I saw no way to filter based on the
Appears field. There is no Relationship this way.

Did I miss something?

What I found I could do was go back to the Activities table, create a calc
field called "Display_Activity_Name" that displays the "Activity_Name" if
Appears=1. Then use that field as the basis of the Value List. The only
downside is I was hoping to sort this list by Type, then alphabetic. Instead
it's only alphabetic.

Any suggestions?


On 12/2/05 7:36 AM, in article m-adnWTq9bVJxg3enZ2dnUVZ_sydnZ2d (AT) comcast (DOT) com,
"Bill Marriott" <wjm (AT) wjm (DOT) org> wrote: