4 Replies Latest reply on Jan 30, 2010 5:14 AM by daveealex

    Foreign key problem and linked drop downs from 2 diffrent databases into a third database.

    daveealex

      Title

      Foreign key problem and linked drop downs from 2 diffrent databases into a third database.

      Post

      I have 3 databases. 1. Business Info(anagraficaAzienda) it has address, phone, email, etc. 2. The Contact from the business(referente) it has telephone, email, fax, etc. 3. the work request(RichiestaLavoro). This is where all the data comes together. The anagraficaAzienda table is connect to the RichiestaLavoro by the Ragione Sociale. The RichiestaLavoro is connected to the referente by the cognome(last name) field. On the work request form when you select the Business name(Ragione Sociale) ,from a drop down thats is populated from the AnagraficAzienda(Business Info) tables' Ragione Sociale(business) field, it automatically pulls the data from the anagraficaAzienda table. Like i want. Also the when the cognome is selected from a drop down list, populated from the referente table, it too pulls data correctly. I see 2 problems, the Ragione Sociale and the last name are not always unique. I have created foreign keys(fk) and the do not pull the data unless i use the key fields specifically. It will be hard to remember all the fk's, and it is a waste of time to always have to look up the key every time. I am worried about errors that could arise. A business will have many requests. Most of the time it will be the same contact person, but if the person leaves, changes position, etc I have to give the operatores a way to store the new contact info and select it for the work request.

       

       

      I would like to have the Cognome(last name) list populated dynamically after the Ragione Sociale is selected. I have put the Ragione Sociale field in the referente database (Contact person) which is populated from the AnagraficaAzienda(Business info) Ragione Sociale(Business name) field. I can dynamically link 3 fields from one db to another but i can not figure out how to dynamically link two fields from seperates databases in another database. I just can't wrap my head around how to link them.Thank you in advance

       

      (I wrote it and I am not sure that i understand)

        • 1. Re: Foreign key problem and linked drop downs from 2 diffrent databases into a third database.
          philmodjunk
            

          Let's deal with problem one first: "I have created foreign keys(fk) and the do not pull the data unless i use the key fields specifically. It will be hard to remember all the fk's, and it is a waste of time to always have to look up the key every time."

           

          Make your value list a two column value list. Put the key value in column 1 and the name value in column 2. You can place the name field from the related table next to the drop down field so that the name appears when you select the ID and Name from the value list.

           

          You can also build a "name search" field and script that allows you to use filemaker's auto-complete to search a table for matching names and then put the ID of the found record into your key field. If multiple matches are found, the script pops up a list of names with additional data (so you can determine which record is the one you want) for the user to select from.

          • 2. Re: Foreign key problem and linked drop downs from 2 diffrent databases into a third database.
            daveealex
               Thanks Phil, the first part is done. I knew that is what you were going to say that. I just thought there was maybe another way. The search script is something i will have to figure out. I will wait for the second part.
            • 3. Re: Foreign key problem and linked drop downs from 2 diffrent databases into a third database.
              philmodjunk
                

              I'm not sure I follow your description for problem 2. I think you are describing a conditional value list. This is how I read your description:

               

              You want to be able to select a business (Ragione Sociale) and see only the names (Cognome) that belong to that business. Correct?

              To make it more challenging, you say you have three tables involved, but I can only identify 2 for the fields involved: the layout's (work request), referente (for Ragione Sociale), and referente (for cognome).

               

              Can you try to describe this more fully, perhaps showing the relationships involved? (tableOccurrenceName::FieldName = TableOccurrenceName::FieldName is a simple way to post relationships.)

               

              If this is a conditional value list problem, have you seen this thread?
              Custom Value List?

              • 4. Re: Foreign key problem and linked drop downs from 2 diffrent databases into a third database.
                daveealex
                  

                Phil,

                 

                I believe I have worked it out. Yes it was a conditional value list. The conditional value list I was making out harder than it was. I think I was a little confused by trying to link three tables together. About the first part foreign keys, To make it easy I created a Calculation field with the last and first name. So when a person selects the business name then they go to the contact person it only lists the contacts connected to the business name. They see fk - lastname - firstname, very easy to find who they are looking for. Thank you for all your help!!