9 Replies Latest reply on Feb 3, 2015 12:07 PM by ezellkendrick

    Script Wording for PatternCount List Comparison is Not Working

    ezellkendrick

      Title

      Script Wording for PatternCount List Comparison is Not Working

      Post

      Hello,

      The following script is to compare a name in Table 1 to a list of names in Table 2 to ensure that a duplicate name is not being added to Table 2. When the Id in Table 1 is compared to a record with the same Id In Table 2 it works correctly, but when the Name is compared with the an Else If statement it doesn't find matching names. I have my relationship set as follows:

      Table 1 Id = Table 2 Id_Fk

      and

      Table 1 First Name  = Table 2 First Name

      and

      Table 1 Last Name  = Table 2 Last Name

       

      Set Variable [ $Name; Value:PrimaryList::Name_First ]


      Set Variable [ $Id; Value:PrimaryList::Id_Primary ]


      If [ PatternCount ( List ( NamesList::Id_ Fk) ;$Id) ]

      Show Custom Dialog [ Title: "Name Exists Exit Script"; Message: "Name Exists "; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]

      Exit Script [ ]


      Else If [ PatternCount ( List ( NamesList::Name_First) ;$Name) ]

      Show Custom Dialog [ Title: "Name Exists Exit Script"; Message: "Name Exists "; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]

      Go to Related Record [ From table: “NamesList”; Using layout: “Names Pick List” (NamesList) ] [ Show only related records; New window ]

      Show/Hide Toolbars [ Toggle ]


      Pause/Resume Script [ Indefinitely ]

      End If

        • 1. Re: Script Wording for PatternCount List Comparison is Not Working
          philmodjunk

          This relationship:

          Table 1 Id = Table 2 Id_Fk and
          Table 1 First Name  = Table 2 First Name and
          Table 1 Last Name  = Table 1 Last Name

          Makes no sense. You should only be matching records by the first pair of fields: Table 1 Id = Table 2 Id_Fk

          It looks like you need two relationships to two different Table Occurrences of Table 2, One that matches by ID and one that matches by name.

          Here's a more reliable way to check to see if a given value is a member of a list of values:

          Not IsEmpty ( FilterValues ( List ( RelatedTable::Field ) ; LayoutTable::Field ) )

          Pattern count will produce a match when your name in the layout field is "Fred" and the name in the related table is "Freddy". FilterValues will avoid that limitation.

          • 2. Re: Script Wording for PatternCount List Comparison is Not Working
            ezellkendrick

            Hello Phil,

            Thanks for the pointer. I couldn't get FilterValues to work for me. The only solution that seemed to work was going to the RelatedTable from LayoutTable using "Go To Layout" and then doing a "Find" on the name in question. From there I used:

            If Get ( FoundCount ) >0 

            Show Custom Dialog [ Title: "Name Exists Exit Script"; Message: "Name Exists "; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]

            Go to Related Record [ From table: “NamesList”; Using layout: “Names Pick List” (AllNamesList) ] [ Show only related records; New window ]

            Show/Hide Toolbars [ Toggle ]


            Pause/Resume Script [ Indefinitely ]

            End If

            I'm sure there is a better way, this method seems clunky and archaic to me, but this script issue has me stumped. I usually can figure these things out...especially with help. However, I must be misunderstanding some basics in scripting terminology and functioning.

            • 3. Re: Script Wording for PatternCount List Comparison is Not Working
              meinhardt

              Pattern count syntax does not appear to be complete in the script as illustrated above.

               

              Pattern count function returns a numeric value. The if statement pattern count needs to be followed with ">0" to function properly as shown below.

               

              If [ PatternCount ( List ( NamesList::Id_ Fk) ;$Id) >0 ]

              • 4. Re: Script Wording for PatternCount List Comparison is Not Working
                philmodjunk

                If [ PatternCount ( List ( NamesList::Id_ Fk) ;$Id) >0 ]

                and

                If [ PatternCount ( List ( NamesList::Id_ Fk) ;$Id) ]

                Produce identical results. Test it for yourself and see. But it's prone to mismatches due to the way that it works.

                FilterValues is not working for you because, as I have already noted, your relationships are incorrect for what you are trying to do.

                Do you want to search all records in the table for a particular name? Or only those records related by ID to your current record for a particular name? (in order to check uniqueness)

                If you want to check a table to see if a particular name is in any record of that table, use:

                YourLayoutTable::NameField = RelatedTable::NameField

                Then this expression:

                Not IsEmpty ( RelatedTable::NameField )

                tells you that at least one instance of the exact text found in YourLayoutTable::NameField exists in RelatedTable::NameField.

                • 5. Re: Script Wording for PatternCount List Comparison is Not Working
                  meinhardt

                  I agree on the mismatch issue, your assessment, and subsequent solution.

                  However to clarify my position, using Pattern count stand alone will return the total number found on that search string within the list potentially 2, 3, 10, 100, whereas if patterncount > 0  will only ever return a binary value, 0, or 1.

                  So although in this instance the values may be the same, using a patterncount within the if statement seems to be lacking a quantifier from my perspective.

                  For example where the patterncount value returned = 6 and the same set is used with if patterncount >0 the value return of the first statement would be 6 and the second statement would be 1.

                  • 6. Re: Script Wording for PatternCount List Comparison is Not Working
                    philmodjunk

                    However to clarify my position, using Pattern count stand alone will return the total number found on that search string within the list potentially 2, 3, 10, 100, whereas if patterncount > 0  will only ever return a binary value, 0, or 1.

                    All true but irrelevant. You still get identical results when the script step executes. Any calculation that produces a numeric result other than zero or Null will evaluate as True. Any result of 0 or null evaluates as False. So there is no need to include the > 0 part of the expression, 1, 5, 33, 33,000,000 all evaluate as True, but including the inequality is harmless. It's just not a factor in why the original set up was failing. And I will repeat my statement that this function should not be used at all here as it can return a false positive match in some cases.

                    • 7. Re: Script Wording for PatternCount List Comparison is Not Working
                      ezellkendrick

                      Thank You gentlemen.

                      I do  want to check a table to see if a particular name is in any record of that table. 

                      I am comparing one table to another and wanting to see the related record(s) in another window to mark or exclude the matching record.

                      I changed the relationships previously. Basically, when I import records into my Main Table the script that runs adds names and all other record data to that table then it adds all names that have been imported to another table (Names Table) that is just a list of all unique Fullnames in the database. At that time the script is suppose to compare all new names from the Main Table that are being added to the Names Table. When a duplicate name(s) is found I expect to see the matching name(s) in a related record window to determine if this is a duplicate name or someone already in the database or the same name but a different person. At that point, I will mark the name based on what it is.

                      • 8. Re: Script Wording for PatternCount List Comparison is Not Working
                        philmodjunk

                        Then you need a relationship that matches names by the names field. A portal to that related table, based on that relationship will show you what you are looking for.

                        I will point out that you can set up more than one relationship between two tables if you use multiple Tutorial: What are Table Occurrences? of one of the tables.

                        So you can match records by ID fields via one relationship and match records by name via a different relationship.

                        • 9. Re: Script Wording for PatternCount List Comparison is Not Working
                          ezellkendrick

                          Thanks Phil,

                           

                          I do have those multiple occurrences, matching fields and relationships setup. I will try doing this through portals.