Hey, I have just started to use Filemaker recently, and I would like to solve a task efficiently.
The task is:
I have a People table (people_id, name, property1, property2, ...) and I have another table with People's Exams (exam_id, people_id, exam_name, exam_result, exam_property1, ...) and results.
I would need a Report/Listview that lists ALL people with their exam results. It should lists those who did not took an exam, but if someone took for example 4 exams, then it shoud show it in 4 rows.
So report should look like:
People_id, People_name. property1, exam_name, exam:result, exam_property1
1, John, class A, NULL, NULL, NULL
2, Lisa, Class B, English, 75%, expert
3, Mary, Class C, English, 65%, expert
3, Mary, Class C, French, 45%, beginner
3, Mary, Class C, Russian, 65%, expert
4, Emil, Class D, English, 75%, beginner
5, John, class F, NULL, NULL, NULL
6, John, class E, NULL, NULL, NULL
I used the following approach:
- Created a new "Report" table for the report
- Created a Script that executes a LEFT JOIN, and then goes through the results set - inserts every row into the table.
- Listview Layout for Report table.
I had/have 2 issues:
- After adding a new exam (or removing an old one) I had to regenerate the table (truncate and execute the script)
- It was too slow (generating the table), and with many People, and many exams the process will be slower.
For the second issue I have used PSOS solution - with this the current execution time is 15 seconds, but this value will grow as we will load the exams, so issue 1 is still a problem.
I was wondering that it would be a solution without using the regeneration of the table (eliminating the loop that goes through all resultset rows) - using some "relationship solution".
Based on my result example, the issue is that Mary should be in 3 rows as she has 3 exams.
Maybe you know the way I could solve this out?