I have a portal that displays data from a field in another db (of course!) that has "name", "type of activity", "activity group" fields.

I want to select data from the "type of activity field" depending on the "activity group", to be displayed in the portal.

Do I set up a new calculation field in the source database that matches data to the "type of activity" or can I set up a script to do the same in the master database?

TIA

The Novice...Stephen K

Hi, Stephen. Are you looking for a value list, or data in a portal? It looks to me like it's value list, so here goes--

Make a relationship from ActivityGroup in the master file to the same field in the foreign file. Create a new value list that gets its values from a field, and select the TypeOfActivity field from the foreign file. Attach that value list to the TypeOfActivity field in the master file, and i think you'll have what you're looking for.

HTH,

Jerry

Hi Jerry

No...it is data...I've been able to display the data matched by "activity group" be creating a relationship based on "activity group".

But this displays all records for the particular activity group.

I want to display by "activity group" by "name". I have an ID field in each DB but I can't work out this next step.

Eventually I want to print out a record for each person in the "name" field of their "type of activities" under an "activity group" heading.

Seems really simple...but!!!

Stephen

So you want to summarize and sort participants by ActivityName, e.g.,

Sports

more like...display by name by activity group (with activities listed)...eg

Stephen

Sporting

Participate athletics carnival

Represent school cricket

Civic

Volunteer Red Cross

Bob

Sporting

Beach volleyball

Civic

Student council

etc

Then be able to print out each student's record.

The typeactivity db has

ID

Studentname

Activity eg participate carnival

Activitygroup eg sporting

Eventually the db will contain 130 students, numerous activities but grouped into 4 to 5 activitygroups.

More eventually this Extracurricular page will be integrated into an existing student academic report that has an individual page per subject studied. This page will the first parents see after the title page.

Seems so simple,,,but I can't figure it out???

One more question: TypeActivity contains one record per student, or multiple? It looks like you may be getting into join table territory here, which is very common for many-to-many relationships. Look up join tables on the Forums, as there is a wealth of information out there from people more knowledgeable than i.

Whenever you get this set up with the proper structure, the result you are looking for will depend not on a portal but on a sub-summary structure and several calc fields. What you are looking for can be done with the structure i think you have, but (to rip a phrase from Vaughn) it will lead to tears.

J

There will be multiple records per student.

Each time a group of students carries out an activity this will be entered.

By the end of the reporting period each student should have carried out a number of extracurricular activities reported on a single page under the groupactivity headings...

Thanks for the help so far.

Yeah, i think you definitely want a join table. If you have multiple objects (students) associated with multiple objects (activities) a join table is best.

You'll basically have a third table, each record of which contains one studentID and one associated activityID (plus some other stuff i won't get into yet).

J

Thanks Jerry

I'll see what I can do

Stephen

Hey Jerry

How simple!!!

Set up Leading Sub-summary by activitygroup

Then Leading Sub-summary by name

Enter Preview mode

Then Sort by activitygroup then name (or do the sort before Preview mode)

Hey presto!!!

a student print out by name with activitygroup headings with each activity listed under the heading.

Your post about the sub-summaries gave me the idea.

I guess my point was that I needed a print out display rather than a database display...I didn't need the portal at all.

But thanks for the discussion...it got me thinking!!!

Stephen

The best solution is often the one you come up with yourself. Glad i could be of assistance.

If you haven't already, please do look into join tables. Even if you have something working now, it will be a good structural basis for down the road.

J