4 Replies Latest reply on Dec 6, 2011 8:53 AM by philmodjunk

    Table Occurrences - Do not make sense - Advice Please



      Table Occurrences - Do not make sense - Advice Please


      Dear All,

      Obviously very new to FMP and attempting to find my way round. The results I am getting just do not make sense to me at all.

      Here is an example:

      I am looking at a table occurrence that which has a relationship between two tables one defines and stores all invoice records for a customer the other defines and stores the categorization of those invoices. The 'match' fields are correctly specified.

      When I open the layout which displays this table occurrence all the records are listed not the sub set which matches the relationship specified.

      I have looked at several other table occurrences constructed in a similar manner. All of them display all the records not the subset I am expecting.

      When I use a portal to effectively display the same data on a test layout using exactly the same relationship parameters I do see the expected sub set.

      This makes absolutely no sense to me what so ever. When I display the table occurrence in a layout I only expect to see the records which satisfy the relationship criteria.

      Could someone please explain. From reading the documentation on table occurrence I think that my expectation is correct. Yet what I see make me think I am completely misunderstanding what this documentation is telling me.



        • 1. Re: Table Occurrences - Do not make sense - Advice Please

          Use Go to related record checking Show only related records. Use the FM help for more details.

          • 2. Re: Table Occurrences - Do not make sense - Advice Please

            It's the way FileMaker works. A relationship, such as that used to set up a portal requires TWO table occurrences. In the case of your portal, it's the table occurrence (and it's current record) specified in Layout Setup | Show Records From and the Occurrence specified in Portal Setup | Show Related Records From.

            When you access a layout directly with just Go to Layout or by selecting a layout from the layout drop down, there's only one Table Occurrence in play and thus FileMaker has no way to identify the relationship you want to use and you are then presented with access to all the records in that table. Each table occurrence has its own found set, current record and sort order--which you access from a layout that specifies that table occurrence. You can thus pull up any group of records you want for that table by performing a find directly on that layout.

            Also, as Juan cardona has suggested, you can use Go To Related Records to pull up a set of records on your layout. This works because the Go To Related Records step enables FileMaker to use the current record on your original layout, the layout's table occurrence and then the table occurrence you select in the Go To Related Records step to identify the needed relationship to pull up a set of records on the target layout that you specify in this step.

            To learn more about table occurrences, you may want to work with this tutorial on the concept: Tutorial: What are Table Occurrences?.

            • 3. Re: Table Occurrences - Do not make sense - Advice Please

              Dear PhilModJunk and Juan,

              Thank you both for replying. The Tutorial is indeed useful. What I do not understand is why this is omitted from the documentation on the subject whether it be in the video tutorials or the help entries. As this is such a counter intuitive feature of FMP I would have expected some kind of health warning - basically you are not going completely mad when no matter what you do FMP apparently ignores the criteria you have specified for the Table Occurrence.

              For example the video tutorial in Lynda.com (which by the way I think is excellent as are the FMP videos Tutorials) explains the Go To Related Records very well in the section on using relationships. However it makes absolutely no mention of the fact that the entire dataset will be visible through a layout based on the Table Occurrence.

              Can I suggest that this feature of the system is very clearly documented on the FMP help and some how added to the Lynda.com and FMP video tutorials.

              I feel sure that many users new to FMP will be expecting the Table Occurrence to behave something like a SQL query set as implied by the current documentation and like me will spend many hours trying to find out why it is not behaving as one would expect it to when the behaviour is actually a feature of FMP. Indeed it is a fundamental of FMP therefore it's omission is truly baffling.


              • 4. Re: Table Occurrences - Do not make sense - Advice Please

                FMP apparently ignores the criteria you have specified for the Table Occurrence.

                You cannot specifiy criteria for a table occurrence in manage | database | relationships. That's why this doesn't work for you. You can only specify match fields and operators for pairs of table occurrences and a layout only refers directly to one occurrence, not a pair. If you've ever worked with SQL expressions, the pairing of a FileMaker layout to its table occurrence is much like a Select * query listing all the joins but omitting the Where and Order by clauses. These are then supplied by the user or a script by when they perfom a find and/or sort the records.

                Go To Related Records (GTRR) refers to two table occurrences that of the current layout and then the one specified as a parameter in the GTRR step. That's why this works to pull up just the records in the other layout's table that are linked to the current record of the current layout--provided you specify "show only related records/Match current record" Even here, it's not your only option.