display the result of joining three objects (tables)

Imad

Dear support team

Please could you tell me how to display the result of joining three objects (tables) and display their attributes in on table?
Example:

Student (FirstName, LastName, Course)
Course (CoursName,CoursDate, Lecturer)
Lecturer (L_FirstName, L_LastName)

I would like to show the result in the below order and in one table as:

FirstName , LastName, CourseName, CourseDate, L_FirstName, L_LastName

Second request please:
How I can create a form to the above query if this is possible or how to set the course name for a specific value such as CourseDate = 12/12/2006 and display the result as the above.

Thank you,

aware_support2

Imad,

What is the relationship between Student and Course, is it many-to-many? I presume a course would have many students registered for it. What about a student, can a student be linked to many courses, or just one?

Imad

The relationship between Student and Course is many-to-many. But could you please show me also if the relationship is many-to-one or one-to-many.

Thank you for the support

aware_support2

Imad,

Let's start with many-to-one relationships where a student can have only one course and a course has one lecturer. In this case you could define a few shortcut attributes on Student to point to related information in Course and Lecturer objects:

Student.CourseName: shortcut to Student.Course.CourseName
Student.CourseDate: shortcut to Student.Course.CourseDate
Student.LecturerFirstName: shortcut to Student.Course.Lecturer.L_FirstName
Student.LecturerLastName: shortcut to Student.Course.Lecturer.L_LastName

Next, you could define a query to find all students who take a course with a given name on a given date:

FIND Student WHERE EXISTS Course WHERE (Course.CourseName CONTAINS ?'Course name' AND Course.CourseDate = ?'Course date ') ORDER BY Student.CourseName, Student.LastName

'Course name' and 'Course date' in this query are the parameters that the user can enter before the query is executed. The query will list all students taking the first course, then all students taking the second course, etc. All you would have to do now is to specify the following attributes (including the shortcut attributes) of Student to be displayed for the query: FirstName, LastName, CourseName, CourseDate, LecturerFirstName, LecturerLastName.

If the relationship between Student and Course is many-to-many you cannot create shortcut attributes on Student pointing to Course because a shortcut would not know to which related Course instance it should point. Therefore the approach to query will be different. Basically, the idea is to show all courses of interest with a list of students under each course. The query would then search for courses:

FIND Course WHERE Course.CourseName CONTAINS ?'Course name' AND Course.CourseDate = ?'Course date ' ORDER BY Course.CourseDate, Course.CourseName

The next step is to design a presentation for object Course (see the Aware IM User Guide for details on designing object presentations). It should show course name, date, and lecturer name in the Details section as well as a list of student names. The student list can be shown using a sub-presentation designed for object Student with the following query:

FIND Student WHERE Student.Course = Course ORDER BY Student.LastName

The last step is to select the newly designed presentation for object Course to display the results of the query on Course.

Please refer to report 'Member details' in the Library sample application for an example of using sub-reports (reports and presentations are very similar).

Imad

Thank you for the reply.

I was thinking of the possibility of solving Many-To-Many relationship in a similar way when you have many to many tables in Access for example. Is it possible here to create an object that behave as a linker or bridge between the two other objects, then object A and B have Many-To-One relationship and object B and C have One-To-Many relationship. The result will be A and C have Many-To-Many relationship that can be searched and displayed on a one table. Is this example possible to apply on AwareIM?

Best regards,

aware_support2

Imad,

> Is this example possible to apply on AwareIM?
Absolutely. In some cases it may be even necessary to implement object relationships this way. For example, the Loan object in the Library sample application registers the fact of borrowing a single Item by a single Member. A member can borrow many items and an item can be borrowed by many members. Object Loan also keeps some loan-related data like the borrowing and due-back dates that cannot be kept on either Item or Member.

If the intermediate object fits well into your object model (i.e. it is not there purely for technical reasons), such a design will give you more flexibility, including implementation of queries.

Imad

Thank you very much. It is working fine.

Best wishes