AnsweredAssumed Answered

Help Understanding this Script

Question asked by sccardais on Aug 7, 2014
Latest reply on Aug 8, 2014 by philmodjunk

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

Outcomes