1 of 1 people found this helpful
I urge you to report it as a bug.
FM's own generated UUIDs should not need to be stripped of dashes to work in the calc engine, and, without knowing all of the details of how a UUID is actually generated, it seems possible that stripping out the dashes could eventually result in a UUID not really staying unique when missing that character from the string.
What is the field type of the UUID field? Should be Text.
Suggest you try using the FilterValues ( textToFilter ; filterValues ) function instead of PatternCount. If this doesn't work, try going a little bit further and and surround your list and value to check with return "¶" characters.
FilterValues ( "¶" & textToFilter & "¶" ; "¶" & filterValues & "¶" )
I tested this in the data viewer
And it works. So something else is going on.
Perhaps there is a length limit to the first argument in Patterncount -- Filtervalues is prabably a better choice- as Doun suggests.
You may have a point.. I tried this in the data viewer and it worked as well:
$x = get(UUID);
$a = get(UUID);
$b = get(UUID);
$c = get(UUID);
$ylst = list($x; $a; $b; $c)];
In script I am pulling the new ID value using an ExecuteSQL script step. When I looked at it in the data viewer the values look correct. I can't explain why removing the - character resolved the issue in the first place.
So I updated the script and went back to the original setup with out Substitute, using the original ID from the executeSQL step and the script worked perfectly... I may be spending to much time scripting.
Anyway thanks for doing this and getting me to take another look... Don't I feel foolish...
Have you tried using the ID fields as primary/foreign keys in a relationship?
It may be the hyphenated data in the field that is the issue and the PatternCount function is OK.
Last week I was helping a client move their data from a Sage ACT database into their new FileMaker Pro database. The recordID fields in ACT were populated with 36 character (hyphenated) keys as per your example.
Relationships in FMP12 did NOT work using the ACT recordID fields. The ACT recordID fields are Text and changing the Storage options from Default to Unicode did not work. We used the Substitute function to remove the hyphens and the relationships became valid and finally worked.
"if[patterncount($IDlst; $newID) = 0]
I believe that you are getting false positives. For example, if you have a list with:
And you enter the ID with just the characters in red, you will be told that ID already exists. And since you can get false positives and be told you have a match when you don't, it will not create your new record.
I suggest instead that you use a function made specifically for values and lists, along the lines that Doug introduced. Prior to xValues, we had to begin and end with carriage returns on both the list and the ID but any more all we need is:
If [ IsEmpty ( FilterValues ( ListOfIDs ; NewID ) ) ]
I don't think it a bug.
Message was edited by: LaRetta ... removed a sentence which isn't applicable.
I am very interested in your results! FM can index 100 characters per word and each dash is a word separator so each dash starts the 100-count over. I just tested and it works consistently as expected in 12.0v3 (on Windows XP3) and in relationship with up to 300 characters (with and without dashes) . I would be most interested in getting my hands on anything to test because we can learn a great deal from unexpected behaviors.
Still, for the current issue, I would doubt that FMI would produce UUIDs which exceeded their indexable limit or couldn't be used for relationships.
Your example is perfect, Oliver. And it DOES work when testing if the value IS there. But the script is failing on the other end ... when the value ISN'T there ... it is seeing a record which doesn't exist so it will not create a new record.
Ah, the old days, huh Doug? We've sure been handed some cool tools these past 10 years. I could not imagine working without them now. It would be nice if forum questions asked the OP for their FM version & OS version. It would sure help us design our solution to their need.
To be honest, I'm not sure I follow your logic for the false positive. Since both the values in the list variable and the comparison ID will have the same number of characters, I don't see how a partial match on the last 4 sections would generate a false positive. Not to mention that since these are values generated by Get(UUID) function, it is unlikey that there will be that much similarity in the values stored in the list variable. Ok, that stated, I think your method has merit.
Still don't know what caused my initial problem, but now it seems to work correctly and I can't make it fail. So no it's not a bug... unless the bug is stored between the ears of the programmer...
This reminds me of a Find issue I once had when searching an ISBN field, where the presence in the string of numerals of either spaces or hyphens caused multiple matches if a sequence of numerals was repeated within the string (eg. 123-456789-45-6) and only the last three numerals were unique. In that instance, the solution was to use the exact match operator (==) instead of equals (=); the difference is that FM matches the whole string using == when the presence of the hyphen causes FM to look at the elements of the string as separate sequences. It seems to me that this behaviour is much the same as LaRetta is describing, though used in a calc instead of a Find.
Bruce said, "I'm not sure I follow your logic for the false positive."
I understand and if you are sure the IDs are always consistently same length, data type of text (did you ever verify that for Doug?), no spaces before or after the IDs and no errant carriage returns then I agree it should be working. I cannot make your calculation fail according to the specs you've given us so I, like Oliver, suspect something else. PatternCount() is a non-discrimination pattern-seeker and I have never seen it used on a list without the additional protections such as Doug points out. Ray taught me back in version 6 before xValues:
If [ PatternCount ( ¶ & listIDs & ¶ ; ¶ & currentID & ¶ ]
.... otherwise it could match by combining pieces of two different lines (below).
Your calculation will tell you that the blue broken ID already exists in the string of IDs from your list. I have no idea why it is breaking for you, Bruce and the examples I present are problem not the reason but I believe PatternCount(0 is being predictable even if we can't pin the break yet. These are incredible learning opportunities - those juicy bits that will stick with you forever when you nail them. If you wish to share with me back-channel then I would be most pleased to help you figure it out. No cost; heck, I'd even be tempted to pay you just so I can identify it.
BTW, keywords, yes, word-breaks did enter my mind as well although if the list and IDs are clean single lines as indicated it shouldn't be an issue. The issues you talking about are the exceptions to FileMaker's normal word-break rules. The exceptions are:
It is a list that Michael Horak (Comment) and I worked on quite some time back and I keep it close at hand. Some of these rules had changed in 9 (I think). But, knowing the dashes are word-breaks which fall into the exceptions, and glancing over the UUIDs and the number/text/dash combinations, and knowing how PatternCount() does not discriminate, my unease in using PatternCount() here only increased.
LaRetta, can you post your list of exceptions again please? It only shows as an empty box.
All very interesting.