7 Replies Latest reply on Jan 19, 2011 12:26 PM by philmodjunk

    Table Occurrence from found set

    DanNoble

      Title

      Table Occurrence from found set

      Post

      I have a table 'Contacts' that contains fields related to people with different (multiple) roles.  I have y/n fields set up to quickly find those that fit a role.

      Example: Table 'Contacts' has 100 records, 20 records are people that are researchers and are flagged in a field ynResearcher.

      I would like to create (what I believe are called) Table Occurrences to use in the existing FMP solution as well as reference externally in other FM files and for value lists.  With MS Access, I could create a table from a query that filtered for the value of 'yes' on the role field I was trying to isolate.  How do I do this in FMP.

      New table would contain the following fields filtered for only Researchers

      Contacts_ID
      Name fields

      Thanks in advance for any help you can provide.

      Development on FMPA11 on Win7
      Files hosted on FMAdv Server 11

        • 1. Re: Table Occurrence from found set
          philmodjunk

          As you've discovered, FileMaker doesn't do things the same way Access does. Wink

          In Manage | Database | Relationships, The very far left button at the bottom and the button with two green plus signs can be used to create as many table occurrences as you need to support multiple relationships between the same pair of data source tables.

          If you want a relationship that matches to only researchers, do this:

          Define a field in one table that always returns "Yes" (assuming that's the value entered in ynReasearcher when you click that control). We'll call that field, cYesKey. Then this relationship will filter for just contacts where Yes has been selected in the ynResearcher field:

          Table::cYesKey = ResearcherContacts::ynResearcher

          ResearcherContacts is a new table occurrence of Contacts and Table can be an occurrence of whatever table you choose (even contacts). Typically, you'd set up Table as the table occurrrence referred to in either Layout or Portal setup... so as to have access to a list of all researchers.

          Please keep in mind that this is not the only way to get a list of all researchers. You can perform a find on the contacts table to pull up a found set of all such contacts records. This is closer in many ways to "a query that filtered for the value of 'yes' on the role field"

          In FileMaker 11, you can also set up a portal to contacts and use a filter expression to only display contact records with yes in a particular role field. This can avoid having to create so many different table occurrences where the only difference is the specific role field serving as the filter and the same portal can even be set up so that the specific role is selectable.

          • 2. Re: Table Occurrence from found set
            DanNoble

            Thanks for the quick reply PhilModJunk, just a few things I need to clear up (???)

            Main table is called: Contacts

            TO of Contacts is called: TO_Contacts_Researcher

            New field defined in Contacts: cYesKey (Field type: ???)
            I have it set as a calculation that is ="Yes", is this correct or should it be a different field type?

            So based on that the relationship would be

            Contacts:cYesKey = TO_Contacts_Researcher:ynResearcher

            • 3. Re: Table Occurrence from found set
              philmodjunk

              You've got the basic idea. The field type would be calculation, it's return type, as specified in the drop down in the specify calculation dialog, would be text.

              Some developers just specify the field as type text and make "Yes" its auto-entered calculation. Can't see where one is better than the other in this situation, but just to give you all the options...

              • 4. Re: Table Occurrence from found set
                DanNoble

                OK, not really sure where I am going wrong but I cannot make it work as expected. Frown

                After setting up a new TO as defined in my earlier post (screenshot below) does not display the filtered list in a new layout using the TO as the data source.  It displays ALL records from the source table when viewed in a newly created layout using the TO as the data source.

                Edit Relationship

                • 5. Re: Table Occurrence from found set
                  philmodjunk

                  Your prior experience with SQL and Access is leading you astray. In access, each form or report has a specified RecordSource that is defined by a SQL expression. Each such form is "locked" to that record set unless some VB code specifically modifies and re-evaluates the record source.

                  In FileMaker, you have a much more fluid situation where any given layout refers to whatever records are currently part of the found set for that Layout's TO. While the TO does identify the underlying data source table and what records are accessible in other related tables, it does not, by itself determine what records may be part of its found set. Any number of actions by the user can modify that found set by searching for a different group of records or by choosing Show All Records or Show Omitted Only. All without andy scripting done by the developer.

                  Remember that I stated that there are more ways to get a filtered list of records than by using a TO? The TO is not a query, it's one half of a relationship.

                  If you display the records from TO_Contacts_PI in a portal on a layout based on Contacts, you will see the filtered list you want. In that context, the relationship between the TO of the layout (Contacts) and the TO of the portal (TO_Contacts_PI) can filter the values shown in the portal.

                  When you base a layout on TO_Contacts_PI, you only have one half of that relationship. There's nothing there to "tell" FileMaker that you want only the contacts with "Yes" in the ynResearcher field.

                  To get what you want, you can use a script that performs a find for a records with "Yes" in this field or you can use a Go To Related Records script step to do this if you base your layout on Contacts. You can automate this process with script triggers to keep user actions from pulling up other contact records.

                  This script, for example:

                  Enter Find Mode[]
                  Set Field [Contacts::ynResearcher ; "Yes"]
                  Set Error Capture[on]
                  Constrain Found Set []

                  Can be used to with a mode enter (browse) script trigger to filter the results of any user's find request to omit any that aren't researchers. Show All Records and Show Omitted only are a bigger challenge, but you can use FileMaker advanced to put a custom menu in place that puts a modified script in place of the standard actions or, if you don't have advanced, you can use Manage Security and passwords to make the other records inaccessible to the user.

                  Navigation buttons that take the user to this layout can perform scripts that also include similar steps, but with Perform Find in place of Constrain Found Set to insure that only the desired recors are visible.

                  Definitely not how you'd do this with SQL and Access, but that's how it works in FileMaker.

                  • 6. Re: Table Occurrence from found set
                    DanNoble

                    PhilModJunk wrote
                    "When you base a layout on TO_Contacts_PI, you only have one half of that  relationship. There's nothing there to "tell" FileMaker that you want  only the contacts with "Yes" in the ynResearcher field."

                    I thought that by relating the calculation field (calYesKey) with ynResearcher was 'telling' FileMaker what I wanted.  I was able to verify that a filtered set is displayed thru a portal as you described.

                    My motivations for creating (what I thought) were filtered subsets of table data included creation of value lists, external access to filtered set from other FM files and through PHP or IWP.

                    Do you have any suggestions that will provide a table-like reference containing this filtered data set?  My thinking is to create a separate table called Researchers and populate that table via scripts whenever ynResearchers is modified in the Contacts table.  Does that sound like a FileMaker way to do the voodoo that I am trying to do?

                    • 7. Re: Table Occurrence from found set
                      philmodjunk

                      I thought that by relating the calculation field (calYesKey) with ynResearcher was 'telling' FileMaker what I wanted.  I was able to verify that a filtered set is displayed thru a portal as you described.

                      I realize that's what you thought, but when you simply base a layout on this TO, You've only referred to one TO. The portal works because it's based on the relationship between the two TOs as the layout is based on one in Layout Setup... and the portal is based on the other in Portal Setup...

                      My motivations for creating (what I thought) were filtered subsets of table data included creation of value lists, external access to filtered set from other FM files and through PHP or IWP.

                      Value lists need not have a separate table. Conditional value lists that list values from a subset of the entire table can be set up fairly easily. 

                      Custom Value List?
                      http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                      For referencing subsets of data from another FileMaker file or via PHP, you'd build the query logic into the link that accesses the data. In FileMaker, tables in external files can be linked just like you would link it to an internal table.