1 Reply Latest reply on Sep 9, 2014 9:50 AM by philmodjunk

    Data Validation via Script; compare single field (many words)  to Multiple records.

    digitalemu

      Title

      Data Validation via Script; compare single field (many words)  to Multiple records.

      Post

      Hi guys. 
      This is an interesting one and one I it may have come up before, however I am either searching the wrong terms or it hasn't been posted yet. 

      Here is what I am trying to achieve; 

      Table 1 : Activity Table. 
      Field needing valiation : "Details" (text)
      Example of Details : 
      "Called the customer today and informed him of changes"

      Table 2 : validation table. 
      Multiple records in 2 fields : Entry and Relation
      example of data in validation records;

                                                                                                                                                                                                                                                                                               
      entryrelation
      callPhone Contact
      emailEmail Contact
      attendAttended
      bookBooked Attendance

      What I want to do: 

      I would like to run a script that compares the "Details" to all "entry" data in the validation table... 
      I.E: If "Details" contains any of the records in "Entry", show dialogue box with options. 

      In this case: 
      "Details" contains "call" (called)
      dialogue box "Mark this activity a Phone Contact?"

      I realise I may be missing something very simple here, but I am at a loss. 

      Cheers in advance for any advice. 

      Jamie.

        • 1. Re: Data Validation via Script; compare single field (many words)  to Multiple records.
          philmodjunk

          It is a far from simple issue. Particularly since in your example, Details does not contain the word "call", it contains the word "called" so even if we parsed the data in details into a return separated list of key words, there would be no match to any record in your validation table.

          Best solution that I can think of is to use a looping script that searches the field for one such entry value at a time;

          The following assumes a relationship between activities and validation based on the Catesian join operator so that any record in activities matches to all records in Validation. (You could also use ExecuteSQL to generate this list in the variable without any relationship to Validation)

          You would also have a field named "ActionToTake" in Validation that stores the text associated with each entry value such as: "Mark this activity a Phone Contact?"

          Set Variable [$EntryList ; value: List ( Validation::Entry ) ]
          Set Variable [$ActionList ; value: List ( Validation::ActionToTake ) ]
          Loop
              Set Variable [$K ; $K + 1]
              Exit Loop If [ $K > Valuecount ( $EntryList ) ]
              If [ PatternCount ( Activity::Details ; GetValue ( $EntryList ; $K ) ) // entry found in details text ]
                  Show Custom Dialog [ GetValue ( $ActionList ; $K ) ]
              End If
            End Loop