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.
Thanks for the offer, but I'll have to pass.
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.
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.
and here are the properties of that specific relationship...
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.
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:
- With the file closed, select Recover from the File Menu.
- Select "Use advanced Options"
- Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".
- The recovered copy of the file will be identical to the original copy except that it has completely rebuilt indexes.
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?
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!
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.