5 Replies Latest reply on Dec 22, 2014 9:34 PM by mchancevet@gmail.com

    Searching, saving and displaying 1...n contacts and their affiliations



      Searching, saving and displaying 1...n contacts and their affiliations


      I am using FilemakerPro 12 to develop a database for a performing arts organization to track event attendance, donations and volunteer time.

      The database will track contacts and their affiliations. There may be one or more contacts per affiliation. The affiliation may be a couple, church, school or some other grouping. 

      Affiliations will be stored in the AFFILIATIONS Table. The primary key will be AFFILIATION_ID 

      The contact information will be in the CONTACTS table. The primary key will be CONTACT_ID.  The table will have a foreign key AFFILIATION_ID.

      Question 1. What control should I use to display 1 or more contacts?

      Question 2: Can I use the same layout for searching for existing contacts and creating new contacts. The behavior I want is for a user to enter a name, and if it is found, show all the contacts in the affiliation. If no contact is found, a new affiliation and contact will be created.

      The found or new contact needs to be selected for recording of donations, reservations, or volunteer hours elsewhere in the UI.



        • 1. Re: Searching, saving and displaying 1...n contacts and their affiliations

          Can the same contact have more than one affiliation? If so, you have a many to many relationship and you need another table to serve as the Join table linking Contacts to Affiliations. If not, then a different data model with a one to many relationship linking Affiliations to Contacts by affiliationID can be used as the data model for you solution.

          • 2. Re: Searching, saving and displaying 1...n contacts and their affiliations

            Hi Brian,

            Q1 - You could use a layout based on Affiliations table and have a portal with related contact records.

            Or you could use a list view of contact records accessed via a script which performs a find for only those records related to any given affiliation record. In this scenario you could have some affiliations field data displayed in the header in fields or merge fields if you needed it.

            Q2 - More than one way to skin a cat. One solution might be to have a contacts list layout with a global 'search' field {Home::gSearch} in the header (often on a seperate home or resources or system table). A button on the layout could call a script that enters find mode and places the contents of your global field Home::gSearch, as the  specification for a Set field step prior to performing the find.

            In this sort of scenario it can be helpful to plan for user error and display a list of potential correct records with a way for a user to select the one that is correct - I provide a button on the list layout that takes them to a form view layout (as above)  for the single record they have selected.

            In my contexts this is helpful because a single spelling mistake can mean the same organisation is in the system twice and at least a user can see and actively choose the one they want and intervene if there has been data entry error. Also for contacts handled in this way some names will legitimately occur multiple times and if you have provided more info (organisation name etc) they can make informed choices about who they are after and select the correct one. A scripted process (depending on how it is scripted) may just take a user to the first record in a found set of 'John Smiths'  which may not be the record they thought they were getting.

            Hope that makes sense and is helpful,



            • 3. Re: Searching, saving and displaying 1...n contacts and their affiliations

              Another Point ,

              Beware of spaces. I found a colleague was placing a space after every name and in time I found this was interfering with searches and allowing for lots of duplicate records and other errors. Some of the text calculation functions and search operators (from FTS 1-31) in searches can be very useful for adjusting your searches to account for this kind of thing.


              • 4. Re: Searching, saving and displaying 1...n contacts and their affiliations




                Thanks. I hadn't thought of that. The user has said it won't happen. I'll use a one to many for now, or I may fix it anyway, because we all know users lie.


                Question 3: I need about 10 fields for each contact (2 name, 2 address, CSZ, phone, 2nd phone, email) so the usual table format in a portal doesn't work very well for data entry or editing. Is there a way to force things into a more attractive format in 5 or 6 lines. 

                Question 4: If the answer to question 3 is Yes, then can I create a second portal and bind it to the second line in the results, when there are two contacts for the affiliation? Is there some control I could put in the portal to handle this for me. I have learned that I cannot put a tab set in a portal. Could I put portals in a tab set, and bind each to a separate row in the results.

                I understand that FMP 13 allows controls be be dynamically show or hidden.

                Question 5: I would like the entry boxes for the contact search to be the same as the entry boxes or a new contact, rather than having  the user jump between screens. Can I use 3 portals on a layout bound to the affiliation table, A for search and the first contact, B for the 2nd contact and C for 3rd contact. B and C would normally be hidden. When a search yields 1 affiliation and 1 contact, portal A will continue to be visible.  When a search yields 2 or three contacts, Portals B and C could be shown. This all assumes that a contact will have only 1 affiliation, which can be enforced on data entry.

                Question6.  I have a pretty good command of SQL. Would all of this be easier if I upgraded to FMP 13. I would have do that anyway to get the dynamic control display capability. My history is mostly VB or C# against Oracle. I like the idea of binding files to tables, but sometimes it is a bit restricting when I want to develop a slick user interface.



                • 5. Re: Searching, saving and displaying 1...n contacts and their affiliations

                   Hi Brian,

                  Question 3: Perhaps consider a display only portal with a button on the bottom for "add record" which creates a new related record and takes you to a form view with as many fields as you need.

                  I recently found a nice article on attractive button behavior on portals it might be helpful.


                  Q 4: If I understand you correctly you could try button in the portal which takes you to a form for that record. You can also make all or any set of the fields in the portal act as buttons. you can do the same thing in a list view layout.

                  Use the inspector in layout mode, Data tab - you can define conditions for hiding an object on a layout.

                  Q5 I would suggest using global fields as I suggested for Q2 for find or new contact details. Then you can have 2 or more different buttons that launch different scripts that either find or create records. You could also have some contextual determinant for the outcome of a single button if that is suitable.

                  I'm not sure I quite follow the question re multiple portals but yes you can have multiple portals. If you create multiple table occurrences TOs (select then duplicate a table) in your relationship graph you can define the relationship between your acquinatance table and each of the TOs differently you can then assign each portal to a different TO. You can then Hide the portals using the conditional hide as above.

                  Q 6 - This ones a bit out of my league. I think you should be able to do all of this in FM12. Check with Phil,

                  By the way Phil I just found your conditional value list resource/education file. WOW thank you so much, awesome.