10 Replies Latest reply on Apr 3, 2013 8:40 AM by philmodjunk

    Related field count returns unexpected values

    JeroenValkier

      Title

      Related field count returns unexpected values

      Post

           Hi,

           I'm using the folliwing for a calculated field "AantalDeelnemers" (to show the number of ID occurences in a related table): 

           Unstored, from Lessen, = Count ( LesDeelname::LesID )

           It does however not show the expected value for AantalDeelnemers in Lessen. For example: for LesID 145 I expect 8 occurences, instead of the 1.

           What am I doing wrong? I've attached the relationship graph, and data for clarification.

           Thanks, Jeroen

            

      Schermafbeelding_2013-03-30_om_08.25.57.png

        • 1. Re: Related field count returns unexpected values
          mickwilli

               Well if it's any consolation, from the information you've provided I can't spot what's wrong. When I throw it together in a test file, the basic principal seams to work, so I can only think there might be something simple out or there is something wrong with the relationship graph.

               As a side note, have you come across the Anchor/Buoy method for arrangeing the relationship graph? I've found that since implementing the Anchor/Buoy method most of my relationship problems have disapeared as it's much eaiser to correctly layout your relationship graph to better understand and follow what's going on in your database. Check out  Kevin Frank & Associates - Anchor/Buoy for details.

               If you like I'd be happy to take a quick look at your file and see if I can work out what's wrong with your count calculation, just PM me with the file or link.

          • 2. Re: Related field count returns unexpected values
            JeroenValkier

                 Thanks for the offer, but I'll have to pass.

                 Cheers,

                 Jeroen

            • 3. Re: Related field count returns unexpected values
              philmodjunk

                   Hmmm, your relationship matches by KlantID not by LesID. Thus, only one of your occurrences with Les ID = 145 has a value in KlantID that matches to the current record in Lessen as each has a different value in the KlantID field. A quick way to double check this is to place a portal to LesDeelname on your Lessen layout. The numer of records shown in the portal where Les ID is not empty should match the count returned by your unstored calculation field.

              • 4. Re: Related field count returns unexpected values
                JeroenValkier

                     So it appears you are right. The portal I placed on the Lessen layout shows no related records at all. But to fix this, I need to understand how you can tell that I configured the relationship incorrectly, and how to fix that, 'cause in the graph, the relationship is between Lessen::LesID and LesDeelname::LesID.

                • 5. Re: Related field count returns unexpected values
                  JeroenValkier

                       and here are the properties of that specific relationship...

                       any clue?

                  • 6. Re: Related field count returns unexpected values
                    philmodjunk

                         Apologies, but I mis-read your screen shot.

                         You do have a relationship matching by fields named LesID.

                         But if your portal to LesDeelName is empty when placed on a layout based on Lessen, then the value in Lessen::LesID does NOT match any record in LesDeelName even though a visual inspection of your data suggests that they do.

                         Possible reasons:

                         One of the two LesID fields is of type text and the other of type number.

                         The fields are of type text, but additional not visible text characters are present in one of the LesID fields such as a tab or space character and not the other LesID field in the related record. Then the fields will look like they have matching values when they do not actually have matching values.

                         The count function is defined in an auto-entered calculation instead of in a calculation field (not the case here)

                         The index for at least one of the two fields is damaged.

                         If you have ruled out all other possible issues, you can try rebuilding the indexes to your file to see if that makes a difference.

                         If you have FileMaker 11 or newer, you can use Advanced Recovery options to rebuild your file's indexes:

                           
                    1.           With the file closed, select Recover from the File Menu.
                    2.      
                    3.           Select "Use advanced Options"
                    4.      
                    5.           Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".
                    6.      
                    7.           The recovered copy of the file will be identical to the original copy except that it has completely rebuilt indexes.
                    • 7. Re: Related field count returns unexpected values
                      JeroenValkier

                            Thanks for sticking around!

                           Unfortunately, again, to no avail :)

                           Checking your suggestions I arrived at rebuilding the indexes. That made no difference as well.

                           What I did try however, is see if the Count works when implementing it in the other direction: Count ( Lessen::LesID ) And... that actually works! What could that indicate?

                      • 8. Re: Related field count returns unexpected values
                        JeroenValkier

                             Ok... this starts to look stupid. Again it was the table context that killed me. Changed it form Lessen 2 to Lessen. So it actually worked, but I didn't see it. Thanks for sticking around!

                        • 9. Re: Related field count returns unexpected values
                          mickwilli

                               I hate to sound like a stuck record, but this is one of the reasons that I suggested the Anchor/Buoy system for organising you relationship graph. I use to have these sort of issues regularly, but the Anchor/Buoy system helps to better control your relationship graph and table occurrences.

                               I highly suggest you read the presentation I linked previously.

                          • 10. Re: Related field count returns unexpected values
                            philmodjunk

                                 I also recommend Anchor Buoy. It's a very good way to break up a complex "web" of relationships into simpler, easier to use sub groups of relationships.