1 2 Previous Next 16 Replies Latest reply on Sep 12, 2012 12:01 AM by comment

    Create a value list from 2 different tables

    noushie

      I Know that I'm missing something simple, but I cannot seem to find an example that deals with this. I would like to be able to create a list of names compiled from 2 different tables. It woudl be great to be able to create a valu elist based on 2 different tables, and I'm sure there is a way to do this, but I just cannot do it.

      Eg. I have a table that has the main contact (ie Primary_Contact's FirstName_Surname). I then have a table joined to this table which details some additional contacts (Additional_Contact First& Surname). There are multiple additional Contacts connected to the Primary Contact. I want to be able to create a list of names that includes the Primary Contact and all of the additional Contacts connect to them. Is there a way to create a calculation field that creates a list of these?

        • 1. Re: Create a value list from 2 different tables
          comment

          No, I am afraid it's not simple at all. Why don't you put all your contacts in a single table (using a self-joins, if necessary)? It will probably solve other problems that you haven't encountered yet.

          • 2. Re: Create a value list from 2 different tables
            noushie

            If I do that, how woudl I designate that one contact is the primary contact? Is there a way to designate one contact as the primary contact, and the others as additional? Then use that designation to use the primary contact for letter heads etc?

            • 3. Re: Create a value list from 2 different tables
              nickchapin

              Michael is correct, you shoud have all of these contacts in one table.

               

              You would want to create another field that is a flag field to designate the contact's role. If you have only the two types of contacts, primary and secondary, then a simple boolean flag would do. So, your flag field, ContactType, could have a zero if it's a primary and a one if it's a secondary (or zero and null, or 1 and null, you get the idea). When deciding whether to include a name as the primary contact for the leter head you would want to ensure you've got the contacvt with the "primary" designation.

              • 4. Re: Create a value list from 2 different tables
                noushie

                I have the primary contact, secondary contact and then additional contacts. How would you suggest i do that?

                • 5. Re: Create a value list from 2 different tables
                  noushie

                  If I have contacts designated as Primary and secondary and then additional. What kind of calculation woudl I use to contatenate the names of those designated as Primary & Secondary names together?

                  • 6. Re: Create a value list from 2 different tables
                    comment

                    field

                    noushie wrote:

                     

                    If I do that, how woudl I designate that one contact is the primary contact? Is there a way to designate one contact as the primary contact, and the others as additional? Then use that designation to use the primary contact for letter heads etc?

                     

                    This would be a lot easier to answer if we had a better understanding of what those things mean in real life. As an example, you could use a ParentContactID field to assign a secondary contact to a primary one, or a tertiary contact to a secondary one - with no limit on the number of levels you can have. But I am not at all sure this is what you require.

                    • 7. Re: Create a value list from 2 different tables
                      noushie

                      Sorry, I'm not quite sure what you mean. Here's an example: Just say you have a database that is collecting details of contacts. So, you record the details of many companies. Each company would have a unique identifier (the Parent), connected to the company, there are any employees, so under that company you'd record the details of each employee as a child. Then, what you also want to be able to do is to desingate one employees as the primary contact and another as a secondary contact, then all others as additional contacts. How would you contatenate the primary and secondary contacts names together, so if you send out a letter to the primary and seondary contacts, their names would read Dear Mr P Keeper & Ms F Lefters.

                      • 8. Re: Create a value list from 2 different tables
                        comment

                        I see (hopefully). The thing is, in a "one company has many contacts" relationship, the identity of the primary contact is an attribute of the company - not of the employee. So just add a PrimaryContactID field to the Companies table and populate it with the ContactID of the selected contact. Then define a new relationship between the tables (using another occurrence of Contacts) as:

                         

                        Companies::PrimaryContactID = Contacts 2::ContactID

                         

                        Do the same for the secondary contact and use the two relationships to fetch the names of the selected contacts from their respective records.

                         

                         

                        ---

                        Note: this is assuming there is a difference between a primary contact and a secondary one - otherwise you'd simply mark a few employees  as the contacts for the company, and this would be handled in the Contacts table.

                        • 9. Re: Create a value list from 2 different tables
                          pincus321

                          You need a seperate relationship like Company with as many contacts as you want associated with that company.  the prime contact is the first contact in the list.  So you might have a key number to sort the list the first person in the list is the primary contact

                          • 10. Re: Create a value list from 2 different tables
                            noushie

                            I have already created this relationship Companies::PrimaryContactID = Contacts 2::ContactID, and am trying to write a calculation that combines the primary contact & secondary contacts names together. How woudl i go about writing this calculation - ie 'fetch' these names. I'm thinking an if calc, but am stuck on how to do it

                            • 11. Re: Create a value list from 2 different tables
                              comment

                              Probably something like:

                               

                              TrimAll ( 
                              Contacts 2::Title & " " & Contacts 2::Initial & " " & Contacts 2::LastName
                              & 
                              Case ( 
                              not IsEmpty ( SecondaryContactID ) ; 
                              " & " & Contacts 3::Title & " " & Contacts 3::Initial & " " & Contacts 3::LastName
                              ) ; 0 ; 0 )
                              
                              • 12. Re: Create a value list from 2 different tables
                                noushie

                                I think that is nearly there, however, I want the calculation to only combine the contact designated as the Primary Contact and the Secondary contact. At the moment I have a drop down box that asks the user to designate the contact as either a primary contact, secondary contact or an additional contact. I think I need an if calc, so if contact designator = 'Primary" then calc trim (contacts name); if there is also a secondary contact then add that name to the calc as well.

                                • 13. Re: Create a value list from 2 different tables
                                  comment

                                  noushie wrote:

                                   

                                  At the moment I have a drop down box that asks the user to designate the contact as either a primary contact, secondary contact or an additional contact.

                                   

                                  That's not at all what I suggested.

                                  • 14. Re: Create a value list from 2 different tables
                                    pincus321

                                    So the last time I did this I used a number example 10000 for primary 11000 for secondary and 11100 for all others so all of the names are in one database with a customer details. For the company which is a seperate database you need  to include an ID in the customer database that relates to company and what kind of contact they are primary or secondary etc two fields, but then you made up a unique ID for each company just in case there are two companies called jones.

                                     

                                    So whenever you need the company and its primary person you do a search with company ID and primary person in a script.  To reduce the effort in the company database you can have a a field that includes the primary id so in the returned layout for the company you reference the customer database with key 11000 and company ID so you then only get the primary user.  If you want the secondary user you do the same but change the static key but use the original company ID.

                                     

                                    I had to do this in Filemaker 4 it was a way to get back certain information about the customer example his Credit card, whether he was a supplier, reseller, casual purchase that sort of thing so essentially you inbed a non changing field that is like a subsearch on the one database for each name.  So when you create the name in customers you have to decide what he is Secondary and who the company is. Plugging the company id into a record in focus is trivial so you could.  Create a new record for a company you could include in the layout a button that creates the customer record, all of the fields like Primary and company id can be scripted without you having to be aware of it.

                                     

                                    Hope this helps Keep it simple

                                    1 2 Previous Next