1 Reply Latest reply on May 17, 2009 10:22 AM by LaRetta_1

    Finding records in two different tables using two different fields



      Finding records in two different tables using two different fields


      I am the first one to admit that I am new to FileMaker Pro version 10 but I am also the first one to admit that I have done programming with other database programs. With that said I have been beating my head against a stone wall trying to solve a problem and hope that the brain trust of this forum can help me.

      I have set up a FileMaker Pro file with two tables (Address List and Donations).


      In the Address List table I have created a field called Contact_ID and made it as a serial number that is automatically generated. I use this field as a relationship to the Donations table with a corresponding field. What I am trying to do in the Donations layout is to link the address information of the person or business making the donation so that it can be properly displayed. In the Donations layout I am only entering in the amount and date of the donation which is what goes into the Donations table.


      My problem comes when I am trying to have my users find the address of the person making the donation. My user can either locate the address of a "business" or a "person". These are two different fields in the Address List table. I would like to be able to use a drop-down list (alphabetically sorted) for both of these fields and when I click on the appropriate business or name have their corresponding address show up on the screen. The business name or the person's name is only being displayed and will not be allowed to have changed in the Donations layout (they will have to go to the Address List layout to make any changes). I can then enter in the donation and to date and have that correspond with the appropriate business/person who made the donation.

      My confusion comes from how do I go about searching the Address List table and linking the address and associated information by either one of these fields (business or name).


      If I enter the Contract_ID I have no difficulty in getting the associated address and information but that number is meaningless to the users of my program. There must be a simpler way than using the Find button and having all of those magnifying glasses show up on my fields in the layout. I would like to be able to go to my Donations layout click on a field called business or name and have a drop-down list show, allow the user to choose the business or name, and have the appropriate address and associated information displayed.


      I am sure that I am misunderstanding the relationship link between the two tables and I am afraid that my medical insurance will not pay for the time in a mental institution that I'm heading for.


      Could someone please point me in the right direction.


      Thank you,


        • 1. Re: Finding records in two different tables using two different fields

          Hi Gary,


          I suggest that you create a new calculation (result of text) in your address table which will hold either the Business or the Person name.  It would be called cContactName and be something similar to:  Case ( not IsEmpty ( BusinessField ) ; BusinessField ; PersonName )


          Then create a value list based upon the addresses table and this new cContactName calc.  When specifying 'use values from field', specify your Addresses table in the upper pop-up and right below, select the ContactID.  Then to the right, click 'also display values from second field' also in the Addresses table and select the cContactName field.


          Then go to your Donations ContactID field and attach a value list (field control > setup) and select a pop-up using this  new value list.  You must use pop-up because drop-down is not made to use for ID selection.  When you use pop-up, it will display the Business or Person name for the User to select from but it will insert the ContacID into the field.  Using a drop-down won't work that way.  Using pop-up also protects from entering invalid information into the field.


          See how that goes for you.