1 2 Previous Next 22 Replies Latest reply on Mar 22, 2013 1:18 PM by philmodjunk

    How to use a contacts table

    Walkabout

      Title

      How to use a contacts table

      Post

           I'm a new fm user. I thought I understood relational tables but I guess not. I'm looking for help on something that seems to me should be simple-using a shared contacts table to track contacts in 4 other tables for the purpose of documenting what was sent to whom. I don't understand how to set up these related fields to enable me to record which of the contracts was sent something. I assume its like a "vegetable" order form....I want to reference a series of "vegetables" from a shared table of "vegetables". It sounds so simple but it sure is stumping me.

        • 1. Re: How to use a contacts table
          philmodjunk

               I suggest that you pick one of those 4 other tables. Describe it in terms of how it needs to "connect" to your contacts table and how you need to use it. We can then give you specific advice that may enable you to also figure out how to get the other 3 tables correctly linked in and working as well.

          • 2. Re: How to use a contacts table
            Walkabout

                 OK. I have a table of records called "Submittals" each which documents a submission from a third party. These are then sent out to different individuals for review, they come back, and then are returned to the original third party. I need to track the distribution of each Submittal record utilising a shared group of Contacts. Each Contact may be included on a number of Submittals and likewise each submittal may (will) be sent to a number of contacts. ?A many to many relationship? There are 3 other talbes that have identical schemas. So I thought a Contacts table was the right approach.

                 I've tried connecting this relationship with a one to many (submitall to contacts) and a many to many (using a join table). In both cases I get stumped in how to get the submittal record  to allow me to select one or more records from the contacts table to document which contact is sent the information. When I set thme up I know its wrong because I can't even tab to the related Contact field.

                 Maybe this is the wrong approachentirely? 

            • 3. Re: How to use a contacts table
              philmodjunk

                   What you have is a many to many relationship since one contact can be linked to many submittals and a given submittal is linked to more than one contact.

                   Contacts----<Contact_Submittal>-------Submittals

                   Contacts::__pkContactID = Contact_Submittal::_fkContactID
                   Submittals::__pkSubmittalID = Contact_SubMittal::_fkSubmittalID

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   With this setup you can place a portal to contact_submittal, the join table, on your contacts layout and include fields as needed from Submittals in the portal row to list all submittals linked to that contact. You can also put a portal to Contact_Submittal on your Submittals layout to list all contacts associated with that submittal.

                   Here's a demo file that you may find helpful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                   If you are using FileMaker 12, open this file from the File menu to get a copy converted to the .fmp12 file format so that you can open and examine it.

              • 4. Re: How to use a contacts table
                Walkabout

                     Phil

                     This is pretty much what I've been doing. The trouble is I cannot get the fields in the portal to accept input. Im baffled.

                     Im attaching ER shot. Will send two more separately. See if they offer insights into what I'm missing.

                • 5. Re: How to use a contacts table
                  Walkabout

                       Dist. Fields in Portal under Submittals, no data will enter.

                  • 6. Re: How to use a contacts table
                    Walkabout

                         Submittal tab

                    • 7. Re: How to use a contacts table
                      philmodjunk

                           You should have a portal to the join table, not to contacts. "Allow creation of records via this relationship" should also be enabled for the join table.

                           Please take a closer look at the demo file for examples of this and note how you can edit data in the portals found in this demo file.

                      • 8. Re: How to use a contacts table
                        Walkabout

                             I've remade the portal to the join table, copied from your example the "Allow creation...", setup identical drop down value lists per your example but still no joy. I cannot enter data in the portal. Any thoughts were I can find the mistake?

                        • 9. Re: How to use a contacts table
                          philmodjunk

                               What you show is a layout that would be used to select existing contacts in order to link them to the current Submitttal record. Is that how you would use this layout if it worked for you?

                               Check each field in the portal row to make sure that they refer to the correct table occurrence. From your screen shot of your relationships, The left hand drop down should refer to _fkContactID from Submittals_Join_Contacts. The fields to the right of it should be from Contacts.Normally, you would not use this portal to edit fields from contacts except in two possible situations:

                               1) You've used a script to create and link in a new contact record. (See the buttons with a plus sign in the demo file for an example.) This option may or may not be practical for your database solution.

                               2) You are correcting an error in the contact record's data.

                               Under other circumstances, the data from contacts automatically appear when you use the drop down to select the contact. Thus, the data is already present in the other fields.

                               One more thing that you can check:

                               In browse mode, if you create a new submittal record on this layout, what do you then see in the portal? if "allow creation" for Submittals_Join_Contacts in the submittals to Submittals_Join_Contacts relationships has been selected, you should see asingle blank row appear in the top of the portal. If you don't see this "add" row appear, you have not correctly selected the "Allow creation..." option for the portal's table occurrence.

                          • 10. Re: How to use a contacts table
                            Walkabout

                                 Phil

                                 Yes, the layout will be used to select existing records from the contacts table.

                                 I did not see the add row appear, I checked "Allow creation.. " option which was set to the Submittal side of the reationship, I reversed it, and presto, data now appears! Thanks very much. I need to go back over my books and tutorials on this point as I don't recall any instructions regarding those check boxes. In any case that solves my issue.

                            • 11. Re: How to use a contacts table
                              Walkabout

                                   Phil

                                   Now that I have one table working (Submittal) I plan to add three other tables, all of which will need similar distribut from the Contacts table. I understand that to do this I set up separate join tables for each linking to the one Contacts table. As opposed to needing to use new occurances of the Contacts table.

                              • 12. Re: How to use a contacts table
                                philmodjunk

                                     Whether or not you link them all to the same occurrence of Contacts will depend on the design of your system. You would definitely use the same occurrence of Contacts if you want all the portals to the join tables to be located on the same layout for contacts Submittals.

                                     As you work with your database, you may find this article on Anchor Buoy helpful: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/

                                • 13. Re: How to use a contacts table
                                  Walkabout

                                       Phil

                                       So now I have made another table, similar to Submittals, called RFIs. I set up an identical join table and have tried linking to the same Contacts table as well as a second occurance of the Contacts table. The goal is to be able to generate a list of contacts to whom a record is distributed to. In both cases, I cannot get the second setup to work. Even though every setting seems to be the same. The first one using Submittals continues to work. Any thoughts about how this needs to work?

                                  • 14. Re: How to use a contacts table
                                    philmodjunk

                                         "cannot get this to work" does not give me much to go on.

                                         How do your efforts fail? (that may give me a clue...)

                                    1 2 Previous Next