1 2 Previous Next 21 Replies Latest reply on Oct 9, 2015 12:34 PM by richardsrussell

    Relationships

    Ben_B94

      If I have two tables, Contacts and Calls, both with a primary key field and Calls contains a foreign key field also.

       

      How do I get it so that I can display all information about a contact and also the information about calls relating to only that contact. Do I need to make another table occurrence of calls and join it to the contacts table? If so what join do I use.

       

      I have my two layouts with various fields on them and created a 3rd layout and based it on the contacts table then tried using a portal to show the details of the calls but when I do that the portal is empty. If I use another table occurrence I don't understand what join to use. If I use x that displays all the calls in the portal relating to everyone but I guess that's the whole point of a Cartesian join?

       

       

      Also is there anywhere I can get a beginners tutorial on relationships I understand the need for it but it is displaying the data and on what layout that confuses me.

        • 1. Re: Relationships
          maestrodevelopment

          Let's say Contacts primary key is 100 and a Call related to that has a primary key of 200.  You need a field in the Call record 200 that stores the 100 as well.  For this we always have a record key (e.g. 100 and 200) then we have a Link (e.g your foreign key) on the child record.  So in this case, Call 200 would have a Link field which has 100 - presumably you add then when you create the Call record.  Then you create a relationship that matches Contact to Call Link.  Make sure that the Call Link can be indexed and make sure both are the same type (e.g. number or text).

          • 2. Re: Relationships
            erolst

            BenBrownfield wrote:

            Do I need to make another table occurrence of calls and join it to the contacts table? If so what join do I use.

             

            You already have one TO of Calls (from when you created the table), so for now simply use that one. Create this relationship:

             

            Contact::id = Call::id_contact

             

            i.e. primary key equals foreign key.

             

            and put a portal for Calls onto the Contacts layout; you should now see all calls related to the current contact.

             

            If in the Relationship Definition you check the “Allow creation of related records via this relationship” option, you can use the portal to create Call records that are automatically related to the current contact.

            BenBrownfield wrote:

            Also is there anywhere I can get a beginners tutorial on relationships I understand the need for it but it is displaying the data and on what layout that confuses me.

             

            Study the FileMaker Training Series; you could also start out with the Help system of FM that offers an introduction on designing relational databases (which, it seems, no one bothers to read).

            • 3. Re: Relationships
              Ben_B94

              I have the primary key in contacts, contact_id linked to the foreign key called the same thing in the calls table. The portal however is displaying nothing. Double checked I have data in both tables, have them both as auto enter serial, and reset them both back to 1 but still the portal is empty?

              • 4. Re: Relationships
                erolst

                Care to post your database file?

                 

                (zip the file, create a new reply, click “Advanced”, then “Attach” to … well, attach the zip to the post.)

                • 5. Re: Relationships
                  richardsrussell

                  As usual, erolst gives good advice. A little additional consideration from me:

                   

                  Your portal into "Calls" will appear on your "Contacts" layout, and it will show only the calls linked to each particular contact. However, as the call records start to pile up, the more recent ones will be added to the bottom of the list, and gradually they'll reach the bottom of the portal and then beyond it, so you'll have to scroll to see them. Wouldn't it be handy if the most recent calls were right up at the top, where they're easy to see?

                   

                  Voila! FileMaker to the rescue! Go into your "Relationships" diagram and double-click on the box between your 2 tables:

                   

                  Relationship Box.jpeg

                   

                  That will open up the "Edit Relationship" dialog, where you can specify the sort order for the calls:

                   

                  Specify Sort.jpeg

                   

                  Sort them in descending order based on their auto-entered sequence number within "Calls":

                   

                  Descending Order.jpeg

                   

                  PS: While you're in the "Edit Relationships" dialog, you might also want to consider whether you want to make use of those other 2 checkable options as well. From your description of what you're doing, I think they'd probably be helpful.

                  • 6. Re: Relationships

                    I'm trying to get a hold a copy of the file off the server but the admin-console is endlessly slow. I'll upload it when it decides to co-operate

                    • 7. Re: Relationships
                      Ben_B94

                      Hey, thanks ill put the sort order to use! Currently though it's my portal isn't displaying any related records it is likely something simple but I can't figure it out. I've made the relationship, put the portal on a layout based on the parent table, linked  ID to foreign key, have data in the tables..

                       

                      • 8. Re: Relationships
                        richardsrussell

                        Please don't take this as condescending, because it's hard to know over this impersonal medium what somebody's level of expertise is, but did you put actual related fields inside the portal? These would be the fields from "Calls" that you want to be visible in "Contacts", presumably the date, possibly a time, perhaps a subject. Simply creating a blank portal won't show you anything.

                        • 9. Re: Relationships
                          Ben_B94

                          Some pics

                          • 10. Re: Relationships
                            Ben_B94

                            I think I have but now i'm starting to doubt myself, im a complete beginner to Filemaker , I have posted a document there of some screen shots that may help.

                            • 11. Re: Relationships
                              erolst

                              Richard S. Russell wrote:

                              did you put actual related fields inside the portal?

                               

                              Well, that question crossed my mind too …

                               

                              There is always a number of sanity checks to perform, and one thing that greater expertise gives you is to think of these on autopilot …

                              • 12. Re: Relationships
                                richardsrussell

                                Hmpf. Everything sure looks OK.

                                 

                                Try this: In the "Relationships" box between your 2 tables, check (turn on) the feature "Allow creation of records in this table via this relationship", then see if you can enter a new call directly in the portal.

                                 

                                Here's one other thing that has occurred before as a known glitch in FileMaker. Select any of the fields in your portal and arrow down a couple of clicks, then look at how it's displayed in Browse mode. If it's visible, go back into Layout mode and arrow it back up a click at a time until it disappears. Then you'll know the last point at which it was visible, and set all the rest of the fields at that level as well.

                                • 13. Re: Relationships
                                  Ben_B94

                                  After allowing creation of records from relationship,

                                   

                                  Entering data into the portal does create new records in the table.

                                   

                                  But still creating records in the table(from calls layout) does not display them in the portal.

                                  • 14. Re: Relationships
                                    erolst

                                    BenBrownfield wrote:

                                    But still creating records in the table(from calls layout) does not display them in the portal.

                                     

                                    I guess there lies the source of your misconception:

                                     

                                    If you just create a new record in the Calls table – without setting the/a foreign key – that record isn't related to any Contact record, and consequently won't show up in your portal.

                                     

                                    Creating a record through a portal will automatically make it a related record (to the current contact) by filling in the key fields involved in the definition of the relationship that underlies the portal.

                                     

                                    You could of course create the records in a Calls context and set a foreign key with e.g. a value list / popup, but especially data like Calls are usually entered and (mostly) viewed in the context of their parent record; so utilizing a portal for record creation and call listing should work fine.

                                    1 2 Previous Next