10 Replies Latest reply on Mar 22, 2012 2:41 PM by dont

    Relationship not working

    dont

      I'm developing a project management/reporting system for a client. I want to report various acitivies at a 'project' level, and also at a 'program' (ie several projects under one program) level. I have a series of tables relating to a 'project' report, and want to also relate these at the 'program report' level.

       

      However, I can't get the program-level relationships to work, despite setting up almost identical relationships, but using the 'Program ID' as the primary key. I've done this many times before and even within the same database solution have set up the same set of relationships at a 'lower' level in the hierarchy, and they're working fine. But I've tried all I can think of and just can't get this higher level reporting to work!

       

      I'm starting to suspect there's an issue somewhere 'deeper' in Filemaker!

       

      Has anyone had a similar experience - and do you know of a solution?.

        • 1. Re: Relationship not working
          timwhisenant

          Hi,

           

          I suspect the problem lies in the layout focus. What table occurrence is the layout based on?

           

          Maybe it’s not the relationship, but the way you are trying to view it.

           

           

           

          Without more information, I can only give you questions.

           

          Tim

          • 2. Re: Relationship not working
            RayCologon

            dont wrote:

            but using the 'Program ID' as the primary key.

             

            Hi dont,

             

            You've said that something's not working, but you haven't said what and in what way it's failing.

             

            But that aside, it's not clear why you'd need a separate set of relationships based on ProgramID. Assuming the Project table is related to the Program table, then all the tables related to Project are already also related to Program through the Project table, and you should be able to aggregate and report on their data without requiring a separate set of replicant relationships.

             

            Having said that, the answer to your actual question is no. That is, no, I've never seen a set of relationships fail for no reason, nor a relationship setup that works in one implementation fail to work in a comparable and correctly implemented implementation. If it were the case, I doubt it would be a 'deeper issue' for long - as it would have risen to the surface many times on lists such as this.

             

            Regards,

            Ray

            ------------------------------------------------

            R J Cologon, Ph.D.

            FileMaker Certified Developer

            Author, FileMaker Pro 10 Bible

            NightWing Enterprises, Melbourne, Australia

            http://www.nightwingenterprises.com

            ------------------------------------------------

            • 3. Re: Relationship not working
              dont

              Thanks Tim,

              The layout is based on the 'Program Report' layout, which is related by a 'Program ID' and the 'Program ID' in each of the respective tables from which I want to draw data.  I've set up exactly the same kind of relationships and layout for the 'project' level reporting and it's working fine.

               

              Being my first posting on this forum, I can see I've got to provide far more detail about the nature of the problem... So sorry everyone about that.  I'll try to provide some additional info soon, if I still can't get this to work!

               

              Thanks for your understanding

              • 4. Re: Relationship not working
                dont

                Thanks Ray,

                My comment on the 'deeper issue' was an exasperation late last night when I resorted to trying out this Forum (which I've not used before)!

                 

                It's just got me stumped as to why it's not working. I've been using Filemaker for a couple of decades myself and have always managed to get these kinds of things to work, but not this particular instance.

                 

                I appreciate I wasn't too detailed in my explanation either!  I'll put together a better explanation later today if I can't resolve it myself in the mean time.

                 

                Thanks for responding!

                 

                Cheers

                 

                Don

                • 5. Re: Relationship not working
                  RayCologon

                  dont wrote:

                  ..I'll put together a better explanation later today if I can't resolve it myself in the mean time.

                   

                  Hi Don,

                   

                  No problem. If you do manage to resolve it, it would be good if you would report back what the cause was in case anyone else reading the forum finds themselves confronted by a similar issue.

                   

                  All the best,

                  Ray

                  ------------------------------------------------

                  R J Cologon, Ph.D.

                  FileMaker Certified Developer

                  Author, FileMaker Pro 10 Bible

                  NightWing Enterprises, Melbourne, Australia

                  http://www.nightwingenterprises.com

                  ------------------------------------------------

                  • 6. Re: Relationship not working
                    dont

                    OK I will.  I suspect it's something to do with a conflict in the relationships as there are several table occurences within the solution that relate to the tables in use at this 'program' level.  I occasionally get a message: "This operation cannot be performed because one or more of the relationships between these tables are invalid" but there's no reason why the direct relationship between the table occurences I'm using in this case shouldn't work... Even when I cut it back to a basic one-field relationship (ie cut out the date ranges, for example).  Do you have any hints as to the best way to track down WHERE these validity problems are occuring? That's probably the simplest approach!

                     

                    Cheers

                     

                    Don

                    • 7. Re: Relationship not working
                      RayCologon

                      dont wrote:

                      Do you have any hints as to the best way to track down WHERE these validity problems are occuring? That's probably the simplest approach!

                       

                      Hi Don,

                       

                      The first thing I'd be looking at is what the indexing settings on the key fields for the 'problem' relationship/s are. If any of them are unstored calcs (which by definition can't be indexed) or have indexing turned off, then that will render the relationship invalid. Corrupt index data can also be a problem that can cause relationship mis-match or "missed match" problems (though it rarely results in an 'invalid relationship' message, in my experience).

                       

                      Regards,

                      Ray

                      ------------------------------------------------

                      R J Cologon, Ph.D.

                      FileMaker Certified Developer

                      Author, FileMaker Pro 10 Bible

                      NightWing Enterprises, Melbourne, Australia

                      http://www.nightwingenterprises.com

                      ------------------------------------------------

                      • 8. Re: Relationship not working
                        dont

                        OK, I've checked the indexing and they're all indexed.  Tell me more about corrupted index data - because I've now gone and tried to create another relationship to the 'projects' report that was working, and that new relationship won't work - I get the 'invalid relationship message'!  Is there a way to clear the index data? Should I 'save as' a clone and re-start data entry.

                         

                        Thanks,

                         

                        Don

                        • 9. Re: Relationship not working
                          RayCologon

                          dont wrote:

                          ...Tell me more about corrupted index data - because I've now gone and tried to create another relationship to the 'projects' report that was working, and that new relationship won't work - I get the 'invalid relationship message'!  Is there a way to clear the index data? Should I 'save as' a clone and re-start data entry.

                           

                          Hi Don,

                           

                          Saving as a clone and re-entering the data would certainly create new indexes, but there are a couple of other options.

                           

                          The first thing I'd suggest you do to explore whether the issue may be to do with indexing is to use the advanced recover options to rebuild the indexes. When you select a copy of the file to recover, if you then choose the Use Advanced Options checkbox, in the resulting dialog you'll be able to turn off all options other than rebuilding the indexes. If you run this process and find that expected relationship behavior is restored in the resulting file, you'll know that the issue was/is related to indexing. If the issues you've been seeing are still present in a file with rebuilt indexes, you will be able to rule that out and look for other causes.

                           

                          Regards,

                          Ray

                          ------------------------------------------------

                          R J Cologon, Ph.D.

                          FileMaker Certified Developer

                          Author, FileMaker Pro 10 Bible

                          NightWing Enterprises, Melbourne, Australia

                          Email: cologon@nightwingenterprises.com

                          http://www.nightwingenterprises.com

                          ------------------------------------------------

                          • 10. Re: Relationship not working
                            dont

                            Thanks all - this is now resolved.  Turned out to be an indexing problem.  It's all fixed now and thanks to those who put the time in to helping out!