Using ValueListItems gives me all field index not related

[meglome]

I have two tables...

Schools List

Courses/teachers

I check if the ID of the school matches the ID with the course/teacher. From there I print out reports to each school showing the complete list of what courses each teacher is going to using portals. No problems.

But... The last report I did was four pages for each school, on page three I needed to show all of the teachers in the school not just ones which were allocated courses. Also since some teachers are in the system more than once becuase they are going to more than one course I needed only unique values. So I used ValueListItems (Get(FileName); "Value List") from the schools table to index the field. The value list is based on the contents of the Full Name field which adds the teachers first name and surname on the courses/teachers table. The first time I did this it indexed only the teachers that were associated with each school. So each school record had just the teachers related to that school in this index field. Now when I try it I get all teachers in each index field. Which is not what I'm looking for, aside for the file now being ten times bigger.

I get the feeling I must have done something differently the first time but I can't figure what that is. Any help is much appreciated as my back is to the wall on this.

If you have another way of getting only unique items for each school selected only from the teachers associated with each school I'd be very glad to hear it.

(sorry this is a double post but as I mentioned above I really have to get this sorted, so I hope i'll be forgiven)

[meglome]

I have two tables...

Schools List

Courses/teachers

I check if the ID of the school matches the ID with the course/teacher. From there I print out reports to each school showing the complete list of what courses each teacher is going to using portals. No problems.

But... The last report I did was four pages for each school, on page three I needed to show all of the teachers in the school not just ones which were allocated courses. Also since some teachers are in the system more than once becuase they are going to more than one course I needed only unique values. So I used ValueListItems (Get(FileName); "Value List") from the schools table to index the field. The value list is based on the contents of the Full Name field which adds the teachers first name and surname on the courses/teachers table. The first time I did this it indexed only the teachers that were associated with each school. So each school record had just the teachers related to that school in this index field. Now when I try it I get all teachers in each index field. Which is not what I'm looking for, aside for the file now being ten times bigger.

I get the feeling I must have done something differently the first time but I can't figure what that is. Any help is much appreciated as my back is to the wall on this.

If you have another way of getting only unique items for each school selected only from the teachers associated with each school I'd be very glad to hear it.

(sorry this is a double post but as I mentioned above I really have to get this sorted, so I hope i'll be forgiven)

[jscooper]

I think I understand what you're looking for: a list of schools, for each school a list of teachers and, if that teacher has any courses, a list of those. If that's right...

Try changing the layout to show records from the courses table occurance rather then the schools. Change the body part to a sumsummary when sorted by teacher. Then add another subsummary part sorted by school. On the shool's subsummary put the school name and info. Into the other subsummary, put the teacher info including a field containing all courses the teacher is taking (look at the LIST function for this).

When you print the report, sort the records by teacher then by school. This should have a list of teachers print out (each listed once) under school names.

Jeff

[meglome]

Thnaks for that. But I think I need to clarify what I'm trying to do.

I've attached a file with 3 layouts.

Schools(Table)

Teachers(Table)

4 Page Letter (Based on Schools Table)

I'm sending a letter to each school that has a course running, which is simple and works perfectly. But on page 3 of the letter I need to show all the teachers that are related to the particular school. So I'm using ValueListItems (Get(FileName); "Full Name") to get the index values from the Full Name field. The first time I did it I got only the teachers related to each school in my index field called cNameOnce. Now I'm getting all teachers.

Can you please take a look and see where I went wrong this time.

teacher_letters.zip

[meglome]

I think I need to clarify what I'm trying to do.

I've attached a file with 3 layouts.

Schools(Table)

Teachers(Table)

4 Page Letter (Based on Schools Table)

I'm sending a letter to each school that has a course running, which is simple and works perfectly. But on page 3 of the letter I need to show all the teachers that are related to the particular school. So I'm using ValueListItems (Get(FileName); "Full Name") to get the index values from the Full Name field. The first time I did it I got only the teachers related to each school in my index field called cNameOnce. Now I'm getting all teachers.

Can you please take a look and see where I went wrong this time.

teacher_letters.zip

[mr_vodka]

Why would you not set this up for the courses and the teachers assigned to those courses to be as seperate related tables?

I would recommend that there be at least 4 tables. School, Teachers, Courses, and a join table TeacherCourses.

[mr_vodka]

Please Do Not Double Post.

Link to Other thread

[comment]

Change your value list to show only related values, starting from Schools.

And please do NOT double post!!!

Edited December 12, 2006 by Guest

[meglome]

Easy fix in the end. Thanks a million for the help.

In fairness I did state from the outset that I was double posting. I'm in Europe so I was worried that no one would respond until the American users came online. Anyways...