Relationships are the basis of relational database design. When set up properly, table relationships will ensure your database runs like a well oiled machine. However, sometimes we run into systems where relationship keys are open to the user, exposing data to the risk of human error if a relationship is changed improperly. It’s sometimes desirable to refactor these relationships to use a proper artificial key relationships to connect tables, a laborious task depending on the size of your database. This post discusses a convenient trick to populate Primary Keys, a reminder about mismatched data types, and a modular script to help populate foreign keys when refactoring a key relationship.

So you have a relational database that you want to change from an exposed key relationship to a hidden one with UUIDs or serial entered values. Creating a new Primary Key field and setting it up to populate keys on record creation is easy, but now you have to populate all existing records in the database. This can also be easy using a script or menu action to “Replace Field Contents” and “Get ( UUID )” for all records in the database. This may not be the best method, however, since it’s susceptible to record locking and it will trigger any modification auto-enter values. There is a way to avoid this by using the following steps:

change the key field to a calculation with “Get ( UUID )”

1. make sure to index the calculation so the values are stored.

2. this will populate all records with a key value.

1. Save these changes by exiting the manage database dialogue.

2. change the key field back to a text field with an auto enter value to complete the process.

With this method, since no scripts or users are actually accessing and changing the data in any of the records, the database won’t trigger any modification auto-enter updates. Since this isn’t performed on a layout or in any fields, you won’t have to worry about script triggers either.

While working on a project to refactor a relationship, we came across an interesting quirk when we ran into a relationship made with mismatched data types. A text field in the parent table was being matched to a number field in the child table. From one side of the relationship, this works just fine, but from the opposite direction the records were linking in unintended ways. When evaluating the relationship from the text field, it would filter out all non-numeric characters and evaluate the relationship based on the numeric characters in the number field. Conversely, from the number field, it would take the entire contents of the field and compare it to the entire contents of the text field and look for an exact match. For instance, a text field with the value “A123” would find a relationship with any number fields with the value “123” in it (eg A123, 123B, 1C23, etc.) while the number field of “A123” would only have a matching relationship with the text field with “A123” in it. This is why you should always make sure your data types match in relationships.

Lastly, I have a modular script designed to populate foreign keys of child records with the new primary keys of their parent records.

All you need to do is pass it a JSON object with:

•  a layout on the parent table (“parent_layout”)

• a layout on the child table (“child_layout”)

• the former primary key field on the parent table (“oldpk”)

• the former foreign key field on the child table (“oldfk”)

• the new primary key field in the parent table (“pkfield”)

• and the new foreign key field in the child table (“fkfield”). 

Your script parameter should look something like this:

JSONSetElement ( "" ;
[ "parent_layout" ; "Table_A" ; "JSONString" ] ;
[ "child_layout" ; "Table_B" ; "JSONString" ] ;
[ "oldpk" ; "Table_A::_pk_parent_ID" ; "JSONString" ] ;
[ "oldfk" ; "Table_B::_fk_child_ID" ; "JSONString" ] ;
[ "pkfield" ; "Table_A::_pk_parent_ID_new" ; "JSONString" ] ;
[ "fkfield" ; "Table_B::_fk_child_ID_new" ; "JSONString" ]
)

Just replace the blue text with the layouts and fields you want used in the script, all of them are required. The script is also designed with server compatibility, so any errors are returned as a global variable ( $$KeyError ). Most of these errors would occur if the layouts or fields provided in the initial JSON object are not found, or if the new primary key field has missing or duplicate values. It will also return a list of child records that fail to be written (likely due to record lock) with their parent’s old pk value and the record number in the found group of children.

Here’s a look at the script, you can also find it in a demo file below:

#Uses: used to update the key relationships of child records for all parent records in a given layout's table
#Parameters: JSON object with the keys: "parent_layout" ; "child_layout" ; "pkfield" ; "fkfield" ; "oldpk" ; "oldfk"
#Returns: $KeyError global variable with information on what caused the error

#Get script parameters
Set Variable [ $KeyError; Value:"" ]
Set Variable [ $JSON; Value:JSONFormatElements ( Get ( ScriptParameter ) ) ]
Set Variable [ $ParentLayout; Value:JSONGetElement ( $JSON ; "parent_layout" ) ]
Set Variable [ $ChildLayout; Value:JSONGetElement ( $JSON ; "child_layout" ) ]
Set Variable [ $FieldPK; Value:JSONGetElement ( $JSON ; "pkfield" ) ]
Set Variable [ $FieldFK; Value:JSONGetElement ( $JSON ; "fkfield" ) ]
Set Variable [ $OldFieldPK; Value:JSONGetElement ( $JSON ; "oldpk" ) ]
Set Variable [ $OldFieldFK; Value:JSONGetElement ( $JSON ; "oldfk" ) ]

Set Error Capture [ On ]
#Error capture is enabled for the entire script

#goto parent layout
Go to Layout [ $ParentLayout ]

#check that parent layout was properly navigated to
If [ Get ( LastError ) ≠ 0 ]

   #exit script
   Set Variable [ $KeyError; Value:"Failed to navigate to parent layout: " & $ParentLayout ]
   Exit Script [ Result: $KeyError ]

End If

#check for records are missing a new PK value
Enter Find Mode [Pause:Off]
Set Field By Name [ $FieldPK; "=" ]

#check for field missing error
If [ Get ( LastError ) ≠ 0 ]
   Set Variable [ $KeyError; Value:"The PK Field: " & $FieldPK & " was not found." ]

   #exit script
   Enter Browse Mode
   Exit Script [ Result: $KeyError ]

End If
Perform Find []

#exit script if missing new PK values are found
If [ Get ( LastError ) = 0 or Get ( FoundCount) > 0 ]
   Set Variable [ $KeyError; Value:Get ( FoundCount ) & " record(s) were found without a value in the PK field: " & $pkfield ]

   #exit script
   Exit Script [ Result: $KeyError ]

End If

#check for duplicate PK values
Enter Find Mode [ Pause:OFF ]
Set Field By Name [ $FieldPK; "!" ]
Perform Find []

#exit script if duplicate PK values are found
If [ Get ( LastError ) = 0 or Get ( FoundCount) > 0 ]
   Set Variable [ $KeyError; Value:Get ( FoundCount ) & " record(s) were found with duplicate values in the PK field: " & $pkfield ]

   #exit script
   Exit Script [ Result: $KeyError ]

End If

#find parent records with an old pk value
Enter Find Mode [ Pause:Off ]
Set Field By Name [ $OldFieldPK; "=" ]

#check for field missing error
If [ Get ( LastError ) ≠ 0 ]
   Set Variable [ $KeyError; Value:"The Old PK Field: " & $OldFieldPK & " was not found." ]

   #exit script
   Enter Browse Mode
   Exit Script [ Result: $KeyError ]

End If
Omit Record
Perform Find []

#exit script if no old PK values are found
If [ Get ( LastError ) ≠ 0 or Get ( FoundCount) < 1 ]
   Set Variable [ $KeyError; Value:"No records were found with a primary key value in the old pk field: " & $OldFieldPK ]

   #exit script
   Exit Script [ Result: $KeyError ]

End If

#goto first record
Go to Record/Request/Page [ First ]

#loop through parent records
Set Variable [ $ParentCount; Value:Get ( FoundCount ) ]
Loop
   Set Variable [ $ParentLoop; Value:$ParentLoop + 1 ]

   #get pk
   Set Variable [ $_oldpk; Value:GetField ( $OldFieldPK ) ]
   Set Variable [ $_pk; Value:GetField ( $FieldPK ) ]

   #find all related child records
   Go to Layout [ $ChildLayout ]

   #check that layout was properly navigated to
   If [ $ParentLoop = 1 and Get ( LastError ) ≠ 0 ]
        Set Variable [ $KeyError; Value:"Failed to navigate to child layout: " & $ChildLayout ]
        #exit script
        Exit Script [ Result: $KeyError ]

   End If

   Enter Find Mode []
   Set Field By Name [ $OldFieldFK; "==" &$_oldpk ]

   #check for field missing error
   If [ $ParentLoop = 1 and Get ( LastError ) ≠ 0 ]
        Set Variable [ $KeyError; Value:"The Old FK Field: " & $OldFieldFK & " was not found." ]
        #exit script
        Enter Browse Mode
        Exit Script [ Result: $KeyError ]

   End If
   Perform Find [ ]

   #check for records
   If [ Get ( LastError ) = 0 and Get ( FoundCount ) > 0 ]
        Go to Record/Request/Page [ First ]
        Set Variable [ $ChildCount; Value:Get ( FoundCount ) ]

        Loop
            Set Variable [ $ChildLoop; Value:$ChildLoop + 1 ]

             #then update fk if empty
             If [ GetField ( $FieldFK ) ≠ $_pk ]
                  Set Field By Name [ $FieldFK; $_pk ]

             End If

             #check for failed replace due to record lock or field missing
             If [ Get ( LastError ) ≠ 0 ]
                  If [ $ParentLoop = 1 and Get ( LastError ) = 102 ]
                    Set Variable [ $KeyError; Value:"The FK Field: " & $FieldFK & " was not found." ]
                    #exit script
                     Enter Browse Mode
                     Exit Script [ Result: $KeyError ]

                End If

                Set Variable [ $KeyError; Value:$KeyError & "¶Parent Key: " & $_oldpk & " ; Record number: " & $ChildLoop & " ; failed to write to child record." ]

               End If
               Exit Loop If [ $ChildLoop ≥ $ChildCount ]
               Go to Record/Request/Page [ Next ]

          End Loop
          Set Variable [ $ChildCount; Value:"" ]
        Set Variable [ $ChildLoop; Value:"" ]

   End If

   #return to parent layout, check for exit loop
   Go to Layout [ $ParentLayout ]
   Exit Loop If [ $ParentLoop ≥ $ParentCount ]

   #goto next
   Go to Record/Request/Page [ Next; Exit after last ]
End Loop

Set Error Capture [ Off ]
Exit Script [ Result: $KeyError ]

If you have any questions about this topic or have issues implementing the functions in this post, leave a comment below or contact us.