9 Replies Latest reply on Aug 3, 2016 4:48 AM by beverly

    Problem with setting up relationship

    macc

      I'm trying to set up proper relationships.

       

      Student – common table for all three groups (with id, names, dates of births etc).

      Former – extension of Student table, but only for former students who has resigned (with info about cause of leaving)

      Graduate – extension of Student table, but only for graduates (diplomaNumber, ifPassedExam, etc.)

      Present – extension of Student table but only for present students

       

      It looks like those ralationships should be one-to-one.

      Now it's my question: how should i link them?

       

      Student –––– Former

      Student::id = Former::id AND

      Student::status = Former::key_FORMERSTATUS

       

      Students::status is text field (controlled by pop-up menu with value list of status)

      Former::key_FORMERSTATUS (is global text field (pop-up menu with value list of status) – as all the records in Former table should concern former students)

       

      When I have tried that, first it looked fine. But when I changed value (for testing) in Former::key_FORMERSTATUS to graduate, on layout based on Student i can see info in related fields from Former table (lookse like second parameter of relationship is not working). But when I put portal on Former layout I see no related records.

       

      Any help will be appreciated.

        • 1. Re: Problem with setting up relationship
          Johan Hedman

          Is your field Status and key_FORMERSTATUS same format (text/numeric)?

          • 2. Re: Problem with setting up relationship
            macc

            Yes, both are text fields.

            Also Student::status is indexed field.

            • 3. Re: Problem with setting up relationship
              Johan Hedman

              What information do you store in those fields?

               

               

              Johan Hedman

              Atatiki AB

               

              3 aug. 2016 kl. 12:16 skrev macc <noreply@filemaker.com>:

               

              Problem with setting up relationship

              svar från macc i Discussions - Visa hela diskussionen

               

              Yes, both are text fields.

               

              Svara på detta meddelande genom att svara på detta e-post, eller gå till meddelandet på FileMaker Community

              Starta en ny diskussion i Discussions via e-post eller på FileMaker Community

              Följer Problem with setting up relationship i dessa flöden: Inkorg

              Manage your email preferences

               

              FileMaker Developer Conference 2016 • Las Vegas, Nevada • July 18-21 • www.filemaker.com/devcon

               

              • 4. Re: Problem with setting up relationship
                macc

                // Changing key_FORMERSTATUS to not global field has fixed the relationship. Why, did I use global field incorrectly?

                 

                In Student::status is text field that determines if Student is prestent student, former student, or graduate.

                That text field on layout is controlled by pop-up with this value list:

                present

                former

                graduate

                 

                key_FORMERSTATUS is global field that stores value "former", text field.

                • 5. Re: Problem with setting up relationship
                  Johan Hedman

                  You can only go from table with the global field to a related table with text field, not the other way around.

                   

                  Sounds more like you want to use a Portal Filter then a relationship.

                   

                  Keep the relationship to just the two key fields. Then on your layout, use your global field in the Filter calculation to match your Status field and you will only see records with the same status as you pick in your pop-up

                  • 6. Re: Problem with setting up relationship
                    macc

                    Johan Hedman wrote:

                     

                    You can only go from table with the global field to a related table with text field, not the other way around.

                    Thank you for explanation.

                    One last question and let's close this thread.

                    In some good practices one said that every table should have an id – even if you won't use it. It seems reasonable to me in one-to-many realationships. But in one-to-one relationship – can't the tables be related only with their primary keys directly?

                    What is your opinion on that subject?

                    • 7. Re: Problem with setting up relationship
                      Johan Hedman

                      Always have a unique ID in every table. It could be a running number or what I use is UUID.

                       

                      When it comes to relationships there are common ways to use one-to-many like Order to OrderRows. But many times you only use one-to-one like Order to Customer. In those case you save the ID for the Customer in a field id_Customer in your Orders table and relate those two tables.

                      With one-to-many like Order to OrderRow you have a id_Order field in OrderRows table where you store the ID for the Order that will give you what you are asking for.

                      2 of 2 people found this helpful
                      • 8. Re: Problem with setting up relationship
                        macc

                        Thanks for all the info.

                        • 9. Re: Problem with setting up relationship
                          beverly

                          +1, emphasis on the ALWAYS! every data table should have a primary key, whether FileMaker or SQL or ?? even if it's a lookup table with 2 columns otherwise (such as States and State Abbreviations). The table can have other keys, but that unique field is so very important in many uses for the data. Especially when communicating with any other applications.

                          beverly

                          1 of 1 people found this helpful