11 Replies Latest reply on May 16, 2011 12:45 PM by philmodjunk

    Creating records through a relationship

    Sylvain

      Title

      Creating records through a relationship

      Post

      Hi,

      I have two tables: Company and Contacts. Company can have many contacts.

      From the relationship I built (see attached), I can bring in up to two existing contact records in the company table, but I cannot create any new contact records from the company table? Am I doing this the wrong way?

      Any help would be greatly appreciated.

      Screen_shot_2011-05-16_at_11.55.47_AM.png

        • 1. Re: Creating records through a relationship
          philmodjunk

          How did you design your Company Layout?

          What problem do you solve by having two different relationships to Contacts?

          Given that a company can have many contacts, it would seem that linking the records by __pk_CompanyID would be the better way to go here. Then a portal to a singe occurrence of Contacts could list as many contacts as you need for a given company record.

          • 2. Re: Creating records through a relationship
            Sylvain

            Well, actually all I'm trying to do is to have many different contacts linked to a company, but sometimes a contact might be used for more than one company... That's why I'm not using a portal to bring the same companies.

            I'm not sure how to send another image for the layout company on the forum... but basically, on the company layout there is all the fields from both the contact and contact 2 tables. I bring the information from the "_fk_contactsID" and "_fk_contacts2ID" fields, values from name fields of both contact tables and using popup menus.

            • 3. Re: Creating records through a relationship
              aammondd

              You would need an intermidary table that just contains the keys to the two tables that  way a single contact could apply to multiple companies and a company can have a multiple contacts.

              In addition there is a check box on the relationship dialog that allows you to create related records through the relationship.

               

              • 4. Re: Creating records through a relationship
                Sylvain

                Thanks for helping out guys, much appreciated.

                "In addition there is a check box on the relationship dialog that allows you to create related records through the relationship."

                 Yes, I'm aware, does'nt seem to be working the way I have things setup though for some reason.

                The idea of using a third table in between is interesting... I'll try to experiment on that a bit.

                Any other suggestions is more than welcome. :-)

                • 5. Re: Creating records through a relationship
                  aammondd

                  Just making sure you covered all the base :)

                   

                  • 6. Re: Creating records through a relationship
                    philmodjunk

                    Yes, If you are going to link a contact to more than one company and a company to more than one contact, you have a many to many relationshiop and you should have a join table to link them:

                    Companies::__pk_CompanyID = Company_Contact::_fk_CompanyID
                    Companies---<Company_Contact>------Contacts

                    Contacts::__pk_ContactID = Company_Contact::_fk_ContactID
                    You'd then place a portal to Company_Contact on your contacts layout to list all companies for that contact and a portal to Company_Contact on your companies layout would list all contacts and this allows for more than two contacts for a given company should you need them.

                    In both cases, you can add additional fields from the other related table to fill in details about the related company or contact.

                    Adding new contact records from the company layout, however, can be a challenge. Some developers use a script so that clicking the button takes the user to the contact layout where a new record is created and a matching new record is added to the join table to link this new record to the current company record. The user then fills out the needed fields and clicks yet another button to return to the original layout.

                    Other developers enable allow creation of records via this relationship for Companies to Company_Contact and from Company_Contact to Contacts. Then they can place a portal to Contacts on the company layout and can add new records to the Contacts table just by entering data into the bottom blank row of the portal. The allow "creation..." options will automatically create the linking record in Company_Contact.

                    A key difference between the two approaches: Deleting a portal row from teh Company_Contact portal removes the link between a company record and a contact record, but leaves the records in the other two tables in place unless you set up a delete option to automatically delete records (and this would not be a good idea here.) Deleting a portal row from the Contacts portal deletes that contact record and the linking record in Company_Contact will be left as an "orphan" record unless you have also enabled the correct delete option in the relationship to delete this related record (which would be a good option even if you don't use this method.)

                    • 7. Re: Creating records through a relationship
                      Sylvain

                      So I did what you guys suggested (a third table) and it works as expected. Thanks for the help :-)

                      However, I still wish I could do the same without having to use portals. Having to keep the information in line is a bit of a pain, since There is lots of information to be shown (name, title, 2 email, 2 phone#, 2 Fax).

                      But until I find a solution, I will be using the portals.

                      Thanks again :-)

                      • 8. Re: Creating records through a relationship
                        aammondd

                        You can increase the size of the protal line (occurance) spacing and put multiple fields in whatever arrangement you want.

                        You could place  the name addresslines stacked horizontially within the occurance add the phones and email to the right of that.

                         

                         

                        • 9. Re: Creating records through a relationship
                          philmodjunk

                          Yes, the "table like view" you get with a portal is just the default arrangement. You can resize and reposition to get a "mini-form" look if you prefer.

                          • 10. Re: Creating records through a relationship
                            aammondd

                            Can you slide within the portal occurance (just asking off the top of my head.)

                            • 11. Re: Creating records through a relationship
                              philmodjunk

                              If you mean, can a portal row field "slide up", then no. The entire portal can "slide" to remove space taken up by unused portal rows, but that's it. That's a key reason not to print from a layout with a portal if you can print from a layout based on the portal table instead.