8 Replies Latest reply on Mar 11, 2013 11:11 AM by philmodjunk

    Filter a table thru relationship



      Filter a table thru relationship


           I have a table of Contacts.

           These contacts may be Jobseekers, Employers, Donors or any combination of thoses.

           I also have a related ContactType table with at least one record per contact, identifying which Type(s) each contact is.


           I tried to set up a TOG with Employer_ContactType = Employer and Employer_ContactID = Employer_ContactType ContactID

           I assumed that if I used Employer_Contact from the TOG it would only have Contacts who were employers, but that is not working.


           All data in the tables and the calculation (cEmployer) is correct...am Ioff base here?

           Better way to do it?


        • 1. Re: Filter a table thru relationship

               "TOG" stands for "Table Occurrence Group"?

               I tried to set up a TOG with

               Employer_ContactType = Employer and
               Employer_ContactID = Employer_ContactType ContactID

               Employer is what in this relationship?

               Did you try it this way:

               Employer::ContactType = Employer_ContactType::ContactType and
               Employer::ContactID = Employer_ContactType::ContactID

               With Contact type either a text field with "Employer" in it or defined as a calcualtion field with "employer" the value it calculates?

               If you then had these relationships:


               Employer__ContactType::_fkContactID = employer_ContactType_Contacts::__pkContactID

               this should work to display a list of records from employer_ContactType_Contacts in a portal on your Employer layout.

          • 2. Re: Filter a table thru relationship

                 Yes, TOG of Employer_Contact and Employer_ContactType

                 Employer_Contact contains PKContactID, details, and cEmployer a calculated field containing "Employer".

                 Employer_ContactType contains FKContactID and ContactType. ContactType may contain a value of "Employer" or "Donor" or "Jobseeker"

                 The relationship is Employer_Contact::PKContactID = Employer_Contact::FKContactID


                 Employer_Contact::cEmployer = Employer_ContactType::ContactType.

                 If I create a layout for Employer_Contact it contains ALL records, not just records that = Employer_ContactType::ContactType of "Employer".

                 When I add the related ContactType field to the Employer_Contact layout it contains blank except when it = "Employer".

                 I am not using a portal: I assumed the layout would now be retricted to just Employer_Contact of Employer.

                 I also need to be able to refer to pull-down list of just Employer_Contacts in other layouts.

                 Sorry that my diagramming skills are not up to par: I work mostly in Access.


            • 3. Re: Filter a table thru relationship

                   I should clarify that I don't really need this layout: but I was using it to prove the filtering works.

                   What I really need is a drop-down list of Employers, for which I hoped to use Employer_Contacts as containing only Employers.

              • 4. Re: Filter a table thru relationship

                     A portal (think of it as a subForm or SubReport--what they call it in acces) allows you to see multiple related records. If you just put a field from the related table on your layout, you only see data from one record the "first" related record of those that match.

                     Try using a portal to get a better picture of whether or not your relationship is working. What you have done, while not the only way to do this, should work. In future posts, you might note that you can do a screen capture of Manage|Database|Relationships and upload that screen shot using the Upload an image controls found below the Post A Answer box.

                     Here are some links on conditional value lists that you may find useful:

                Forum Tutorial: Custom Value List?

                Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                Hierarchical Conditional Value lists: Conditional Value List Question

                Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

                • 5. Re: Filter a table thru relationship

                       I have studied all four of these posts/demos.

                       They make sense but I am unable to apply to my problem, which seems ludicous. This should be simple.

                       I am posting screen shots to be sure communication is clear on what I have done.

                       I hace a Contacts table, and a ContactType table. Contacs may be of on or more Types: the ContactType table serves only to idenify which types any one Contact is.

                       I need to use the ContactTypes table to restrict my view of Contacts to a given Type, in drop-down lists, all sorts of reporting etc.

                       In testing I cannot make a Value List of one Type work, nor can I populate a layout with Contacts of a single Type.

                       BTW I do have separate FMP12 files for the Layouts vs the Data. The Value Lists and the Table Occurances are specified in the Layouts file.

                  • 6. Re: Filter a table thru relationship

                         We need to determine the "table occurrence context" or "starting point" for your value list.

                         Oh what layout do you need to see a value list of all employer contacts?

                         On what table is that layout based?

                    • 7. Re: Filter a table thru relationship

                           Contact and ContactType are the specific tables.

                           Employer_Contact and Employer_Contact type are table occurences.

                           The drop-down I need is in an OpenJobs table, primary key PKOPenJobID, and incremented numeric.

                           FKContactID within that table is intended to be an Employer from the Contact Table.

                           In the Open Jobs layout it is a drop-down list of PFContactID and cFullName both from Employer_Contact, which I expected to be limited to "Employer" rows.


                           However, I am going to need to use this kind on relationship in various repoorts and counters.

                      • 8. Re: Filter a table thru relationship

                             Using the calculation field as a match field like you have and using the tables/relationships that you have, the filtering calculation field should be defined in the OpenJobs table:


                             OpenJobs::cEmployer = ContactType::ContactType
                             ContactType::FkContactID = Contacts::PKContactID

                             Using the names from my example in this post, your value list would list data from Contacts, Include only related values starting from OpenJobs. To implement this in your solution, you'll need to use the relevant table occurrence names specific to your database design.

                             This is not the only option, see option 1 in the tutorial link I shared earlier for a method that does not require a relationship and thus avoids some of the issues you may encounter setting this up for multiple table occurrence contexts.