4 Replies Latest reply on Dec 6, 2011 7:27 AM by dansan500

    Relationship - Create a new file in a different table



      Relationship - Create a new file in a different table


      These are the two tables I'm working with.

      Table - Supplier

      Table - Contact_Names

      I would like the user to enter an infinet number of contacts from the Supplier layout.

      I can't figure out the relationship, I could use the script trigger to launch a script when I figure it out.

      How do I set the Company Namein the Contact Names table portal from the Supplier table




        • 1. Re: Relationship - Create a new file in a different table

          And what should happen when you get two contacts, each working for a different company, named "Joe"?

          What you show at the bottom should be a portal to a related table. That way you can list multiple contacts here and not hit any limits as to how many you choose to add.

          Another question:

          In the upper tab, you show "Rack Express".

          In the lower Contacts tab, you show "Rack Express" again. Why show the same data twice? Won't all contacts listed on the contacts tab for this current record be from "Rack Express"? Aren't these all contacts for the current Supplier record?

          Could the same contact be associated with more than one supplier? (I can recall cases where a previous employer sometimes dealt with brokers that represented more than one potential supplier.)

          • 2. Re: Relationship - Create a new file in a different table

            All contacts entered from this portal will be working for the Supplier (Company Name) in the Supplier Table.

            It is not really necessary to show the Company in the bottom portal, I didn't remove it because, well, I was just trying to get it to work, and wanted to see if it populated the field.

            I was trying to get the script to create a new record and enter the Company Name in the Contact_Name table, but then I couldn't think of a way to set the relationship.

            A contact could be associated with two companies, but then there would be a new Supplier Company under that name. I didn't want to get that deep until I figured this one out.

            • 3. Re: Relationship - Create a new file in a different table

              Well, linking one contact to more than one company makes this a many to many relationship--which requires adding an additional table so that a company can list multiple contacts and a contact can list multiple companies.

              first the basics, a one company to many contacts system:

              define a serial number field, __pk_companyID field in the Companies table. If you have existing records, use Replace field contents to update this field with serial numbers. define a matching field: _fk_companyID in contacts. (pk stands for primary key, fk stands for foreign key, this is just a naming convention you can use if you want).

              In manage | database | Relationships, set up this relationship:

              Companies::__pk_CompanyID = Contacts::_fk_CompanyID

              Doubleclick the relationship liine linking these two tables and select "allow creation of records via this relationship" for the contacts table.

              Now, assuming that your Suppliers layout is based on Companies, put a portal to Contacts on that lower tab panel. You can add new related contacts by typing data into the bottom blank row of this portal and it will automatically be linked by CompanyID to the current company record shown on your layout.

              When you are ready to go from One company --> Many contacts to ManyCompanies--->Manycontacts, report back here and I'll tell you how and include a demo file you can download and examine.