AnsweredAssumed Answered

LEFT JOIN without execute SQL

Question asked by tamashajdu on Aug 6, 2018

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:

  1. Created a new "Report" table for the report
  2. Created a Script that executes a LEFT JOIN, and then goes through the results set - inserts every row into the table.
  3. Listview Layout for Report table.

 

I had/have 2 issues:

  1. After adding a new exam (or removing an old one) I had to regenerate the table (truncate and execute the script)
  2. 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?

 

Thank you,

Outcomes