3 Replies Latest reply on Aug 8, 2014 12:36 PM by philmodjunk

    Help Understanding this Script

    sccardais

      Title

      Help Understanding this Script

      Post

           The script below identifies a way to mark the 1st occurrence of a record with a specific value in a field that appears in a group of imported records. e.g. If multiple records contain a field with "Ray", this script will identify the first imported record with "Ray" in the field being monitored. I've included the entire script but highlighted in blue the portion that makes the relationship and calc field work 

           It uses a self join and a calculated field to set the value of a field c_FirstAppearance based on the RecordID of the records after import.

           The script works but I cannot understand why. The calc field enters a 1 into c_FirstAppearance if the RecordID in Table 1 = the Record ID in table 2. Since this is a self join, aren’t these values always the same? Why does this relationship work?

      Script steps

           #Prompt user
           Show Custom Dialog [ Title: "Import"; Message: "This script imports a table of values and identifies unique values in a selected column (the \"Target\"). Continue?"; Default Button: “OK”, Commit: “Yes”; Button 2: “Cancel”, Commit: “No” ]
           If [ Get ( LastMessageChoice ) = 2 ]
      #User cancels
           Exit Script [ ]
           End If
      #Initialise environment
           Set Error Capture [ On ]
           Freeze Window
      #Set names for results windows
           Set Variable [ $ResultsWindow1; Value:"First appearance of value" ]
           Set Variable [ $ResultsWindow2; Value:"Subsequent appearances of value" ]
      #Empty the import table
           Go to Layout [ “import” (import) ]
           If [ Get ( LastError ) > 0 ]
      #Check for an error browsing to the target table’s layout. If the layout has been deleted and this check isn’t performed, then the upcoming ‘Delete All Records’ step could potentially delete all records in the wrong table.
           Show Custom Dialog [ Title: "Import"; Message: "Error going to the import layout. Script can't continue."; Default Button: “OK”, Commit: “Yes” ]
           Go to Layout [ original layout ]
           Exit Script [ ]
           End If
      #Present the import dialogue
           Import Records [ Target: “import”; Method: Add; Character Set: “Windows ANSI”; Field Mapping: Source field 1 import to import::Import target field here Source field 2 import to import::Optional field 1 Source field 3 import to import::Optional field 2 Source field 4 import to import::Optional field 3 ]
           Set Variable [ $Error; Value:Get ( LastError ) ]
      #Check if the user cancelled (error 1) or for another error during import
           If [ $Error > 0 ]
           If [ $Error > 1 ]
           Show Custom Dialog [ Title: "Import"; Message: "Error during import. Error code: " & $Error; Default Button: “OK”, Commit: “Yes” ]
           End If
           Go to Layout [ original layout ]
           Exit Script [ ]
           End If
      #Delete any records from the previous import
           Show Omitted Only
           Delete All Records [ No dialog ]
      #Delete records with no value in the target field
           Perform Find [ Specified Find Requests: Omit Records; Criteria: import::Import target field here: “*” ] [ Restore ]
           Delete All Records [ No dialog ]
      #Check if any records remain
           Show All Records
           If [ Get ( FoundCount ) = 0 ]
           Show Custom Dialog [ Title: "Import"; Message: "Nothing imported into the target field."; Default Button: “OK”, Commit: “Yes” ]
           Go to Layout [ original layout ]
           Exit Script [ ]
                End If
      #Number records
      Replace Field Contents [ import::RecordNumber; Replace with serial numbers: Entry option values; Initial value: 1; Increment value: 1 ] [ No dialog ]
      #Window management: 1. Create results1 window if not already created 2. Close results2 window if open 3. Minimise original window if it wasn’t the results1 window
           Set Variable [ $Window; Value:Get ( WindowName ) ]
           Select Window [ Name: $ResultsWindow1; Current file ]
           If [ Get ( LastError ) > 0 ]
           New Window [ Name: $ResultsWindow1; Style: Document; Close: “Yes”; Minimize: “Yes”; Maximize: “Yes”; Zoom Control Area: “Yes”; Resize: “Yes” ]
           End If
           Select Window [ Name: $ResultsWindow2; Current file ]
           If [ Get ( LastError ) = 0 ]
           Close Window [ Current Window ]
           End If
           If [ $Window ≠ $ResultsWindow1 ]
           Select Window [ Name: $Window; Current file ]
           Adjust Window [ Minimize ]
           End If
           Select Window [ Name: $ResultsWindow1; Current file ]
           Adjust Window [ Maximize ]
      #Find records where the target field value makes its first appearance in the table.
           Perform Find [ Specified Find Requests: Find Records; Criteria: import::c_FirstAppearance: “1” ] [ Restore ]
           If [ Get ( FoundCount ) = Get ( TotalRecordCount ) ]
      #All records contain unique values in the target field
           Refresh Window
           Show Custom Dialog [ Title: "Import"; Message: "All values in the target field are unique."; Default Button: “OK”, Commit: “Yes” ]
           Else
      #Find and display duplicates in another window
           Select Window [ Name: $ResultsWindow2; Current file ]
           If [ Get ( LastError ) > 0 ]
           New Window [ Name: $ResultsWindow2; Style: Document; Close: “Yes”; Minimize: “Yes”; Maximize: “Yes”; Zoom Control Area: “Yes”; Resize: “Yes” ]
           End If
           Perform Find [ Specified Find Requests: Find Records; Criteria: import::c_FirstAppearance: “0” ] [ Restore ]
      #Arrange windows
           Arrange All Windows [ Tile Vertically ]
           Refresh Window
           Show Custom Dialog [ Title: "Import"; Message: "Number of duplicates found: " & Get (FoundCount ); Default Button: “OK”, Commit: “Yes” ]
           End If
            
            

      First_Appearance.png

        • 1. Re: Help Understanding this Script
          philmodjunk
               

                    The script works but I cannot understand why. The calc field enters a 1 into c_FirstAppearance if the RecordID in Table 1 = the Record ID in table 2. Since this is a self join, aren’t these values always the same? Why does this relationship work?

               Yes, you have a self join. No the values will not always be the same. The records match by the field being checked for duplicates, import target field here. So if you have 3 records where this field = "Ray", any field in Import matches to all 3 records with "Ray" in Import 2.

               Your calculation field, c_FirstAppearance, however only refers to the first related record in Import 2. So if you had these three records:

               import target field here    |     c_recordID
               Ray                                      |     3
               Ray                                      |     7
               Ray                                      |     9

               from all three records, this expression from inside the If function: Import 2::C_RecordID, returns the value 3, the recordID value of the first record with this matching value. Thus, the if function will only return a 1, when it is evaluated for the first instance of Ray.

          • 2. Re: Help Understanding this Script
            sccardais

                 I'm still struggling to understand this but I think it's important to understand.

                 Even though all three of the records are related because they all have "Ray" in the match field, the calc "c_First Appearance" only enters a 1 in the first matching record and not the rest of the matching records?

                 Is that how this works?

                  

            • 3. Re: Help Understanding this Script
              philmodjunk

                   Correct.

                   Try putting a portal to Import 2 on a layout based on Import. Then put the REcord ID calculation field from Import 2 on your layout, but outside of the portal

                   On all three records with "Ray", you'll see the same records listed in the portal in the same order. And the same Record Id in the field outside the portal. The field outside the portal will always match the first row of your portal.

                   But, just like the field outside of the portal, this calculation only refers to the Record ID of the first record listed in the portal to import 2, And that value will only match the record ID of one of the three records shown on your Import layout and this will be the first record to have this duplicate value.