3 Replies Latest reply on Apr 24, 2010 6:32 PM by caz3000

    Table instances - I have no clue what I'm doing

    caz3000

      Title

      Table instances - I have no clue what I'm doing

      Post

      Hi,

       

      I'm pretty new to Filemaker.  I'm using Filemaker Pro 11 on MacOSX 10.5.

       

      The problem I'm having probably has a simple answer but I can't for the life of me work it out.

       

      I'm creating a database to track investigations.  Some of the tables I'm using are:

       

      PERSON

      ALLEGATION

      DOCUMENT

       

      Each of these has a many-to-many relationship with each other (eg. one person may be relevant to multiple allegations, and each allegation may involve multiple persons; one document may have been seen by multiple persons and each person may have seen multiple documents; one document may be evidence for multiple allegations and each allegation may have multiple related documents).  I have therefore created the following linking tables:

       

      ALLEGATION PERSON

      ALLEGATION DOC RELATIONSHIP

      PERSON DOC RELATIONSHIP

       

      The relationships are defined as follows:

       

      PERSON::__kp_PersonID = ALLEGATION PERSON::_kf_PersonID

      ALLEGATION PERSON::_kf_AllegationID = ALLEGATION::__kp_AllegationID

      ALLEGATION::__kp_AllegationID = ALLEGATION DOC RELATIONSHIP::_kf_AllegationID

      ALLEGATION DOC RELATIONSHIP::_kf_DocumentID = DOCUMENT::__kp_DocumentID

      DOCUMENT::__kp_DocumentID = PERSON DOC RELATIONSHIP::_kf_DocumentID

       

      In the DOCUMENT record, I have a portal to display records from PERSON DOC RELATIONSHIP, which is supposed to display a list of all the persons connected to that document.  This contains the following fields:

       

      PERSON DOC RELATIONSHIP::_kf_PersonID

      PERSON::NameCombined

       

      and some other fields specifying the nature of the relationship.

       

      The problem is that no matter what is in _kf_PersonID, the Name displayed is the name of the person in the first PERSON record in the database, not the name of the person whose ID is in the PERSON DOC RELATIONSHIP record.  I'm guessing this is because Filemaker is looking up all the persons associated with all the allegations through the chain of relationships, not just the persons associated with the document.  I'm also guessing I'm supposed to solve the problem with table instances, but can't work out what I'm supposed to do because I don't really understand table instances.

       

      Can anyone help?

       

      (NB: I'm aware that I could use a calculation to fill in a field with the person's name in the Person Doc Relationship record, but then this would not update if changes / corrections were made to the Person record.)

        • 1. Re: Table instances - I have no clue what I'm doing
          caz3000

          After a bit more googling, I found this:

           

          Key Concepts in Filemaker 7

           

          and now I think that I have to create a separate group of 'table occurrences' (or 'table instances?'  are the terms interchangeable?) for each main kind of record (eg. a group for PERSON records; a group for ALLEGATIONS etc.).  Then I have to make sure the main layout for that type of record and all the portals in that layout are referenced to the table occurrences for that group.

           

          Does this sound right?

          • 2. Re: Table instances - I have no clue what I'm doing
            FentonJones

            The simple answer is "join tables". Whenever you have a many-to-many relationship (which you do) it is likely you should have a join table. You must create it as a real table, then put a table occurrence of it between the two "entity" tables which it is joining. Here is an old blog post about it:

             

            http://fmprelationships.blogspot.com/2007/10/example-join-relationship.html

             

            A join table has at least 2 ID fields, foreign keys of the 2 entity tables it is joining. It usually has other fields also, such as creation date (or timestamp). Sometimes it requires a unique primary ID of its own. In that case I try and use an auto-entered UID (so I do not have to update it during an import into a cloned file). 

             

            It would have a record for every unique combo of IDs. For example, each line is a record in the Person|Allegation join table (or Allegation|Person as the name, whichever way you prefer; functionality works both directions).

             

            Person A, Allegation 1

            Person B, Allegation 1

            Person A, Allegation 2

             

            So you'd have 3 join tables.

             

            [ I toyed with the idea of only 1 join table, with 3 IDs, in order to have it all in one table, in order to do reports. It would also work. But reporting would still have a problem, unless you required separate records for each, and never allow all 3 IDs to have a value on one record. Because otherwise a record could only show once in the Body of a summarized report. It could not appear under a "Allegations" subsummary and also under a "People" subsummary. But this could be accomplished fairly easily, by simply not allowing entry to the 3rd ID in portals, and/or validating each field, that the other 2 are not already filled. 

             

            Alternatively, you could add a 4th table, "Reports", which would be populated by script from the other 3, to have what I said above. That's probably less messy overall (as the 1-table method's relationships would need to block other records from its portals).

             

            But the simplest is likely 3 tables to start with. There are other ways of collecting data for reports, especially if you have FileMaker Pro Advanced, to make use of a Custom Function to aggregate records. ]

             

            Regarding "Key Concepts in FileMaker 7". This is a good read (as I remember). It is describing the need for (use of) what is called the "anchor buoy" method of Relationship Graph organization. It is a useful method whenever there is a great deal going on with any of your main entities (some of which has nothing to do with the other main entities). It allows you to keep some of their TOs (table occurrences) only in the entity's TOG (table occurrence group). 

             

            For example, a Person's "children" may only matter to Person, so no need to be available to Documents. You end up with more table occurrences, as many require an instance in each TOG (Person for example).

             

            But really I do not see that you need separate TOGs (yet),* unless there's a lot more going on than is apparent. It is partly a "scale" thing, how much is going on, how many odd little table occurrences you need for specialized things, like filtered value lists, calculations, etc.; now and in the future. 

             

            * You can later "split" a graph into separate TOGs. But it has some glitches. You should split/move the main existing table occurrence, to use as the new "anchor", NOT a new table occurrence. Because:

            1. Calculations' default evaluation context is the 1st table occurrence created for that base table. If you move to anchor buoy, but create a new "main" table occurrence for an entity, the default evaluation context will be the old one (in the original TOG). Which won't matter for local evaluations; but will for relational ones. It will bite you at least once later. Don't ask how I know :-]

            2. Your main layout for your entity will also be incorrect.

            So, disconnect and move the existing table occurrence. Create a new one to take its place in the existing TOG.

             

            • 3. Re: Table instances - I have no clue what I'm doing
              caz3000

              Thanks for the detailed answer, Fenton.

               

              I already had join tables.  ALLEGATION PERSON, ALLEGATION DOC RELATIONSHIP, PERSON DOC RELATIONSHIP are the join tables I was using (but I described them as linking tables - I'm still new to the terminology).  The problem was:

               

                      PERSON A may be related to only DOCUMENTS B and C

              and PERSON A may be involved in ALLEGATION X and Y

              but  ALLEGATION X is related to DOCUMENTS A and B

              and ALLEGATION Y is related to DOCUMENTS C and D

              and ALLEGATION Z is related to DOCUMENTS A, B, and D

               

              so when I was, say, in a PERSON record, and I wanted to portal in fields from related DOCUMENT records using the relevant join table, Filemaker did not know whether I mean the DOCUMENTS directly related to that PERSON or all the DOCUMENTS related to the ALLEGATION records related to that PERSON.  I initially thought you were supposed to solve the problem through filtering, but that didn't seem to work - I would end up with a portal displaying the exact same related join table record twice or three times or not at all - that sort of thing!

               

              However, the good news is that using multiple TOCs fixes the problem.  I can now 'drill down' and get the exact right relationship for the portal on the particular table layout I'm concerned with.  It took me about 4 hours yesterday to re-do all the relationships using multiple TOCs (the whole database is actually a bit more complicated than the part of it I've set out here).  Doing that has actually made the whole structure of the database a lot more easier for me to follow, so that's also good.

               

              What made it so hard for me to work out how to use TOCs was that I didn't realise you had to associate each layout with the particular TOC you wanted to use to define the relationships for that layout.  I would create a new TOC in the Relationships map and then wonder why when I created a portal on a layout referencing the old TOC, the new TOC wasn't a 'related table' for that portal.

               

              Unfortunately, I learned your good piece of advice:

               

              You can later "split" a graph into separate TOGs. But it has some glitches. You should split/move the main existing table occurrence, to use as the new "anchor", NOT a new table occurrence.

               

              the hard way!