4 Replies Latest reply on Feb 11, 2013 4:05 AM by MortenJoergensen

    Display companies on another table

    MortenJoergensen

      Title

      Display companies on another table

      Post

           Hi there.

           So I created a database containing almost 200 contacts on one table. Each contact has a company associated. I want to make another table with just the companies, so the same company doesn't appear twice. Also, I want it to bo automatic so when i create a new record on the contact table and associate it with a company, it automatically updates the company table with a new company, is that possible?

           The other thing is, when i create a new record on the contact table and want to associate it with an already existing company, how can i get FileMaker to auto-fill the data so i just need to put e.g. the company name?

           With regards

           Morten

        • 1. Re: Display companies on another table
          philmodjunk

               Define your new Company Table. Create fields for each item of data that is specific to a company and not specific to a contact. To this set of fields, add __pkCompanyID. Define it as an auto-entered serial number field.

               In Contacts, add a number field named: _fkCompanyID

               Use the relationships tab in Manage | Database to link the two tables like this:

               Companies-----<Contacts

               Companies::__pkCompanyID = Contacts::_fkCompanyID

               If this notation is unfamiliar, see the first post of: Common Forum Relationship and Field Notations Explained

               Put _fkCompanyID on your Contacts layout and format it with a drop down list or pop up menu with a value list that lists values from Companies. Select __pkComanyID as the first field and the Company Name field as the second.

               When you select a company in _fkCompanyID, you link that contact record to an existing Company. Remove any fields from Contacts that contain the company name or other data specific to the company. Replace them with fields from the Companies table and they will automatically display data from the Companies table when you select a company in _fkCompanyID.

               You can place a "New Company" button on your layout that performs this script:

               Freeze Window
               Go to Layout ["Companies" (Company)]
               New Record/Request
               Set Variable [$CompID ; value: Companies::__pkCompanyID ]
               Go to Layout [original layout]
               Set Field [Contacts::_fkCompanyID  ; $CompID]

          • 2. Re: Display companies on another table
            MortenJoergensen

                 Perfect.

                 This was very usefull and helped me alot, so thank you.

                 Now another question. I need a field to show both first name and last name on the company table, which is in two different fields on the individual table, is that possible?

                 Many thanks,

                 Morten

            • 3. Re: Display companies on another table
              philmodjunk

                   Since there are many individual records for any given company record, from which related individual record would you display these names?

                   And why would you need a field for this?

                   If you put two merge fields from individual side by side on your layout.

                   <<Individual::FirstName>> <<Individual::LastName>>

                   It will look like one field even though you have two.

                   A calculation field could combine two fields from a related table:

                   Individual::FirstName & " " & Individual::LastName

                   but I don't see why that would be a good idea.

              • 4. Re: Display companies on another table
                MortenJoergensen

                     Thank you for your help.

                     I Just created two fields with first name and last name, so i figuered it out.