9 Replies Latest reply on Sep 1, 2015 4:55 AM by Extensitech

    search based on difficult relationship


      My database has tables for clients and contacts. This is a 1 to many relationship.  My client wants to enter a contact name (first name, last name, and email) and see a layout where both the client and contact information are editable. The hard part is, the client also wants to see all of the other contacts for that client and be able to see a layout where that contact and the client information is editable. Data will be entered while the selected client is upon the phone.


      In essence, the user enters a contact name, the system gets the ClientID (a foreign key in the contact table), and extends the found set with all other contacts with the same client ID. It then uses the client ID to gather client information


      From the search form,  name and email values are linked to global fields in the globals table, which in turns links to the contacts table. (The globals table will be cleared when the phone session with the contact is ended.)  I don't know how to take the next step, getting all contacts associated with the client, to work. I think it could be done with a self-join based on client ID, or done be saving the client ID as a variable in a script and using it to run a second search.


      So far, I can't get it to work. The failing is not getting additional contacts to show when needed.


      I'd like suggestions on how to link the table involved in this process, how to extent the found set with additional contact for a client, and how do display the client editable fields and contact editable fields on one form, while maintaining the ability to easily jump to another contact within the same client.

        • 1. Re: search based on difficult relationship

          Do you really need a found set? This sounds like a portal should work. This seems a little confusing with "my client" and "the client", but I think you want to look at a filtered portal instead of a found set via a find.


          You should be able to add a portal from the client or contactss table and filter by 'ClientID = $$global_client_id'. This would also work with a self join. This will allow for editing as well it setup properly. This will also allow you to GoToRelatedRecord via the portal and that solves your link issue. Be careful about the target record table in a GTRR as sometimes you get the correct record and sometimes you get the first record in the filtered set. The FM documentation details this.


          You can also filter a portal with ExecuteSQL if you think the relationship is complicated.

          • 2. Re: search based on difficult relationship

            Why not simply use a Master-Detail approach on the Client layout itself? Add a non-filtered portal of related Contacts to display all contacts (and a few fields), and a filtered version of that portal for editing a selected contact (displaying all (pertinent) fields).


            You can still let the user search for a contact name by performing the find in the related Contact::name field from the Client context.

            • 3. Re: search based on difficult relationship

              If you have fm13 and up... also consider using a popover to show "editable" fields.  That way you can seperate the display and the entering of data.

              • 4. Re: search based on difficult relationship

                If you use a portal with a relationship to a global you can fill that global with the id's you need.


                You can collect the id's in various ways (ExecuteSQL, a find or another relationship). 


                A "source" id can have multiple ids and FileMaker wil display the records associated with those keys in portal for example.


                You can also get inspired by the Selector Connector model by Todd Geist and gang.


                Happy Coding!

                • 5. Re: search based on difficult relationship

                  We don't know the client until we find the contact. The clients may be just

                  an informal group who always buy tickets together but it helps my client to

                  know that. We make up a name like Mary and Sue and friends but we never use

                  it with the client.


                  I could do this easily wig a union query but I don't think FM supports it.


                  I don't know how to set up the relationship table to use the filtered

                  query. I tried using a second TO of contacts joined on clientID. Didn't

                  work. Thinking of trying Find Matching Record to extend the found set.


                  Thanks for reply. It has given me ideas and I might be able to make the

                  filters portal work.

                  • 6. Re: search based on difficult relationship

                    FileMaker does support UNION. https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf


                    For usability you really should consider a portal. Seems like all you need is a simple layout that uses a global field for the search and displays results in a portal and a popover to edit any data.


                    The Selector Connector was mentioned and it is very useful.

                    • 7. Re: search based on difficult relationship

                      I'll give it a try tomorrow. Thanks for your help.

                      • 8. Re: search based on difficult relationship

                        One way to do it is :


                        Create a global field in a table z_ClientIDs  (maybe you have an interface table, or use the table the layout in question is based on)


                        Create a relationship from this global field z_ClientIDs to ClientID in your Client Table.


                        Create a portal with that relationship.


                        Then with an ExecuteSQL either with or without union fill the z_ClientIDs field as a list and all the clients you want will appear in the portal.


                        The z_ClientIDs field (being a global) is just a placeholder that FileMaker uses to fetch the related records.  In other database systems you would the result of the query into an array and display that.  Now you just fetch the keys and use a relationship to fetch the records.  FileMaker supports using a list of source keys which makes this possible :-)


                        As is normal with FileMaker many different options, but none like other programming environments hihi :-)


                        Here a sample you can fiddle with...

                        • 9. Re: search based on difficult relationship

                          It looks as though you already have plenty of good ideas to try, but just an additional thought:


                          What about creating a self-relationship between contacts, where client id matches? You could use fields from that relationship (first, last, email) as your search fields, and pull up all contacts where one or more contacts for the same client have those attributes.


                          You'd want to take into account jumping to the particular contact once you've found the set (lots of methods for that), and you may need to account for contacts that don't have a client, if those exist.


                          This relationship could also be used to "easily jump to another contact within the same client".


                          Chris Cain