9 Replies Latest reply on Aug 9, 2017 2:40 PM by philmodjunk

    Related records

    haider2016

      Hi all,

       

      I have two tables, Student and subjects. the relationship between the two tables is one to many.

      The subject table contains the subject's name and all the tests results (marks) of that subject. 

      How can I get all students that are failed in a specific subject?

       

      Thanks in advance

        • 1. Re: Related records
          philmodjunk

          There are many ways:

           

          Perform a find on a layout based on Subjects where you specify both the subject and the grade as find criteria.

           

          You might set up a portal to Subjects on your Student layout but use a portal filter to specify the grade.

           

          You could add a grade field to the Student layout and include that field as an additional match field to the grade field in Subjects. This can use a new occurrence of the subjects table in your relationship graph to keep the current relationship unchanged.

          • 2. Re: Related records
            philmodjunk

            And I left out ExecuteSQL, which can be used in more than one way to get what you want here.

            • 3. Re: Related records
              haider2016

              Thanks phil, I wil play around with that. concerning with ExecuteSQL, it works with me with a single table, but when Joining another table it always result question mark ?

              • 4. Re: Related records
                CICT

                If you are considering using joins in Execute SQL I highly recommend Taylor Sharp's posting here: JOINS in ExecuteSQL

                 

                In my view the best thread in this forum for a long time.

                 

                Regards

                 

                Andy

                2 of 2 people found this helpful
                • 5. Re: Related records
                  keywords

                  haider2016 wrote:

                  "Student and subjects"

                  "the relationship between the two tables is one to many"

                  "get all students that are failed in a specific subject"

                  Begging your pardon, but this does not sound like "one to many" from the very wording of your question. Note in text I have quoted: in first line subjects is plural; in third line students is plural. That implies many to many, so needs a join table—unless of course Subjects is already a join connecting students to, say, Subjects Available.

                  • 6. Re: Related records
                    philmodjunk

                    In this particular case, you don't really have to use a join. You can use WHERE to specify subject and Grade to get the data you want directly from Subjects.

                     

                    You might also find the ExecuteSQL query tool that I built into the following teaching file of interest. It allows you to easily build a basic Query, then transform it into an expanded calculation that:

                     

                    a) Avoids referencing any field or table occurrence name as quoted text. This keeps the query from failing should you open Manage | Database and rename something in the future.

                     

                    b) Keeps the original query unmodified by quotes and ampersands so that the query is easier to read.

                     

                    See the last example of:

                     

                    Adventures in FileMaking #2-enhanced value selection

                    2 of 2 people found this helpful
                    • 7. Re: Related records
                      haider2016

                      good point Keywords and thank you. you are right.

                      • 8. Re: Related records
                        haider2016

                        Thanks Andy

                        • 9. Re: Related records
                          philmodjunk

                          It's one to many.

                           

                          If you find all subject records where the grade is "F", the relationship back to students will then provide all the info you need on each student. This can be a basic summary report grouped by subject where you see a list of the students that failed in each group. The same student may appear in multiple parts of such a report.

                           

                          And it's still "one to many" but now it would be more accurate, given the change in context to say "many to one".

                           

                          To me it's a table name issue. I'd call subjects, something different such as "grades" and link grades to a subjects table to list info for each subject. but that assumes that you need more info about each subject than the name of the subject. If all you need is a single field to describe each subject, then the current model works just fine as a one to many relationship.

                          1 of 1 people found this helpful