I attached a file that counts duplicates through the relationship graph.
That still does not count correctly like the problem you described. (duplicate - table occurence 2)
Then I figured you could list the hash numbers backward and at least compare the ends, but how it is indexed or not indexed (or something wrong) it can't read the end of the hash numbers.
So you will see the RightDuplicateLeft paired with the table occurence 4 that counts the last 50, which can be changed, to see if the end numbers match.
If you could possibly combine the duplicate field connected to table occurence 2 and the RightDuplicateLeft table occurence 4 some how, then maybe it will help with finding actual duplicates.
The total file path is an unstored recursive calculation.
Why does it need to be unstored, and why recursive? Isn't t just a path?
Storing and indexing will likely solve many speed and searching issues.
The calculation is unstored because it calculates the complete path over at least one relation - so it cannot be stored.
The whole idea is that you do not use the complete, original path of a file or folder, but this unstored calculation, so that when a folder is renamed, you only have to rename one record, and the paths of all folders and files beneath this record are correct again. The same if a folder with content is moved: only change the link to the folder or server above the folder record, and the path of the folder is correct again, and so are the paths of folders and files in the moved folder.
Thanks for your suggestion. In the meantime we have solved the problem in a different way. Each record has a simple serial number as ID. Concatenating these ID's in the same recursive way as the original unstored file path calculation results in a fairly short number (unstored), unique for each path. Comparison of these short numbers happens to work without a problem.
This solution however doesn't solve the original problem of FMP.
the number of characters considered when finding duplicates is 100 in FMP 11 and is documented in the FileMaker Help (do a search for "Finding duplicate values").