3 Replies Latest reply on Apr 15, 2015 10:15 AM by philmodjunk

    Self Join Relationship Confusion



      Self Join Relationship Confusion


      Hey guys,

      I think i have misunderstood how self join relationship work.

      Lets say for example i have a table called 'Animals', with the fields;
      animal - text
      amount - number
      cat_match - text global of 'cat' as the data

      Then i had 4 records with animal as 'dog', 1 record with animal as 'cat', and any numbers in their amount fields.

      Then i create a self join relationship of the table, calling it 'cat_only', setting 'animal' = 'cat_match'

      I thought doing this would then make the 'cat_only' table have only the one record where 'animal' was set to cat. Also giving me the ability to do a calculation such as sum, but to limit the result of only adding up the cat's amounts.

      I hope that is clear enough. Do i have this completely wrong, and they are not meant to work that way? How would i go about creating a system like that?



        • 1. Re: Self Join Relationship Confusion

          What you describe sounds correct and should work, though it's not the only way to get this result. So you need to check details and take a look at the "context" in which you expect to see only cat records:

          What you describe is having only one table: Animals (sometimes called a Base table or data source table)

          But two table occurrences, Animals, cat_only. That's two "boxes" in manage | database | relationships, but when you hover the mouse over the arrow in the upper left hand corner, you see that both show the same table name as the data source table.

          You've then set up these match fields:

          Animals::Cat_Match = cat_only::animal

          animal and cat_match are defined as text fields. cat_match has global storage specified in field options.

          In this setup, if you put a portal to cat_only on a layout based on Animals, the only animals listed in the portal would be those with "cat" in the animal field provided that the current value of Cat_Match is also "cat". Change the value of Cat_Match to "dog" and the same portal would then show only dog records from the animal table.

          A calculation field defined in Animals as Sum ( cat_only::amount ) would show the total of amount calculated from only those records that appear in this portal. A summary field defined in Animals that computes the total of Amount would then provide a second way to show the same total. Add this table to your layout, but select it from cat_only in the specify fields or field picker dialogs and you will see the same total--one based on just the records that appear in the portal.

          But please note that if you create a layout based on cat_only, you will see all records for all kinds of animals, the layout will not be limited to only one type of animal.

          • 2. Re: Self Join Relationship Confusion

            Thank you,

            Yes that does work. For this test i was only using the table view, and expected only the cat records to show if i was showing records from cat_only.

            The reason i wanted to do this little test relates to a much bigger and more complicated database that i was having issues with. It wasn't really for portals, but for summary fields.

            To describe the issue there, say i had another table called 'results'. (connected by another field like 'project') In that table i had two sum fields to calculate the total of 'amount', one for animals and one for cat_only. It was here that i was getting the same result for both.

            Just now, i figured out instead of having a self join for animals, and doing the calculations for one 'results' table. I should create another instance of the 'results' table and have a 'cat_match' field in there to get it to work, which it does.

            Does that make sense and a better way to do it?

            Oh and Thank you for your fast reply!

            • 3. Re: Self Join Relationship Confusion

              There are a number of different approaches for computing and displaying sub totals and other aggregate data in FileMaker. You might also consider using a summary report with sub summary layout parts where you perform a find for the desired set of records on which to base your report and then sort your records to group them into groups where each then appears with it's own sub summary layout part and a sub total displayed in that sub summary layout part.

              Here's a tutorial on that type of report: Creating Filemaker Pro summary reports--Tutorial