1 2 Previous Next 19 Replies Latest reply on Mar 31, 2013 8:25 PM by BowdenData

    PatternCount() function  Possible bug

    BruceHerbach

      I'm trying to look for an ID in a variable containing a list of record IDs. All of the IDs are UUIDs created with the get(UUID) function. So they look like 87199D05-ACB2-479F-BD5B-13F98162206F with the - character. With the - character in the string Patterncount fails to find a matching ID. If I use the substitute function and remove the - character from the values in the variable list and from the value I am comparing, Patterncount finds the matching value.

       

      Has anyone else seen this? Should I report it as a bug?

      ---------

      The original script ( one that fails ) looks like this:

       

      Set Variable[$IDlst; List($IDlst; table::ID)]

      .

      .

      if[patterncount($IDlst; $newID) = 0]

      new record

      set field...

      end if

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

      The functioning version added substitute so it looks like:

       

      Set Variable[$IDlst; List($IDlst; substitute(table::ID; "-'; ""))]

      .

      .

      if[patterncount($IDlst; substitute($newID; "-"; "")) = 0]

      new record

      set field...

      end if

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

      Thanks

      Bruce

        • 1. Re: PatternCount() function  Possible bug
          Stephen Huston

          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.

          1 of 1 people found this helpful
          • 2. Re: PatternCount() function  Possible bug
            BowdenData

            Bruce,

             

            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 & "¶" )

             

            HTH.

             

            Doug

            • 3. Re: PatternCount() function  Possible bug
              Oliver_Reid

              I tested this in the data viewer

               

              Let($x="D7B6AB0E-A587-4D2C-A677-71645E378DD8";

              PatternCount(List("B2F4ABFA-F7F4-437A-8814-15663CB22495";"D7B6AB0E-A587-4D2C-A677-71645E378DD8";"83806477-6B14-4A32-BE0D-7A875A904953");$x))

               

              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.

              • 4. Re: PatternCount() function  Possible bug
                BruceHerbach

                Oliver,

                 

                You may have a point..  I tried this in the data viewer and it worked as well:

                Let([

                $x = get(UUID);

                $a = get(UUID);

                $b = get(UUID);

                $c = get(UUID);

                $ylst = list($x; $a; $b; $c)];

                PatternCount($ylst;$x))

                 

                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...

                 

                Bruce

                • 5. Re: PatternCount() function  Possible bug
                  itraining

                  Hi Bruce

                   

                  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.

                  http://en.wikipedia.org/wiki/Sage_ACT!

                   

                  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.

                  http://www.filemaker.com/help/html/non_toc.46.2.html

                   

                  Michael Richards

                  Brisbane (Australia)

                  • 6. Re: PatternCount() function  Possible bug

                    Hi Bruce,

                     

                    "if[patterncount($IDlst; $newID) = 0]

                      new record"

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

                     

                    I believe that you are getting false positives.  For example, if you have a list with:

                     

                    99199D05-ACB2-479F-BD5B-13F98162206F

                    88199D05-ACB2-479F-BD5B-13F98162206F

                    77199D05-ACB2-479F-BD5B-13F98162206F

                     

                    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 ) ) ]

                    New Record


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

                     

                    I don't think it a bug.

                     

                    Message was edited by: LaRetta ... removed a sentence which isn't applicable.

                    • 7. Re: PatternCount() function  Possible bug

                      Hi Michael,

                       

                      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.

                      • 8. Re: PatternCount() function  Possible bug

                        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. 

                        • 9. Re: PatternCount() function  Possible bug

                          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.

                          • 10. Re: PatternCount() function  Possible bug
                            BruceHerbach

                            Hi LaRetta,

                             

                            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...

                            • 11. Re: PatternCount() function  Possible bug
                              keywords

                              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.

                              • 12. Re: PatternCount() function  Possible bug

                                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). 

                                 

                                99199D05-ACB2-479F-BD5B-13F98162206F

                                88199D05-ACB2-479F-BD5B-13F98162206F

                                77199D05-ACB2-479F-BD5B-13F98162206F

                                 

                                -13F98162206F

                                77199D05

                                 

                                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.

                                • 13. Re: PatternCount() function  Possible bug
                                  keywords

                                  LaRetta, can you post your list of exceptions again please? It only shows as an empty box.

                                   

                                  All very interesting.

                                  • 14. Re: PatternCount() function  Possible bug

                                    My apology.  Here is the list again.

                                    SnapShot.jpg

                                    1 2 Previous Next