AnsweredAssumed Answered

Can a New Related Record Be Edited Twice in a Single Database Transaction?

Question asked by Reid Larson on Jan 19, 2019
Latest reply on Jan 19, 2019 by Reid Larson

I'm experimenting with using database transactions to be able to undo changes if an error occurs. I've run into a stumbling block in a specific situation:

  1. Create a new related record.
  2. Store the related record's primary key in a variable.
  3. Create a second new related record.
  4. Using the first related record's primary key, attempt to edit that record again.

The way my script is set up, this results in a third related record being created with the same primary key as the first. With unique values validation being set for the field, error 504 occurs when the transaction is committed.

 

This is the relationship setup:

Screen Shot 2019-01-19 at 18.14.11.png

Here is the script:

Set Error Capture [ On ]
#
# Error trapping loop
Loop
# Start transaction
New Window [ Style: Document ; Using layout: “Transaction” (Transaction) ]
Exit Loop If [ Let ( $errorCode = Get(LastError) ; $errorCode ) ]
Enter Browse Mode [ Pause: Off ]
Exit Loop If [ Let ( $errorCode = Get(LastError) ; $errorCode ) ]
New Record/Request
Exit Loop If [ Let ( $errorCode = Get(LastError) ; $errorCode ) ]
#
# Create first related record
Set Field [ Related::Number ; 1000 ]
Exit Loop If [ Let ( $errorCode = Get(LastError) ; $errorCode ) ]
Set Variable [ $RelatedKey1 ; Value: Related::PrimaryKey ]
#
# Create second related record
Set Field [ Transaction::RelatedForeignKey ; "" ]
Exit Loop If [ Let ( $errorCode = Get(LastError) ; $errorCode ) ]
Set Field [ Related::Number ; 2000 ]
Exit Loop If [ Let ( $errorCode = Get(LastError) ; $errorCode ) ]
#
# Attempt to edit first related record
Set Field [ Transaction::RelatedForeignKey ; $RelatedKey1 ]
Exit Loop If [ Let ( $errorCode = Get(LastError) ; $errorCode ) ]
Set Field [ Related::Number ; 1001 ]
Exit Loop If [ Let ( $errorCode = Get(LastError) ; $errorCode ) ]
#
# End transaction
Commit Records/Requests [ With dialog: Off ]
Exit Loop If [ Let ( $errorCode = Get(LastError) ; $errorCode ) ]
Close Window [ Current Window ]
Exit Loop If [ Let ( $errorCode = Get(LastError) ; $errorCode ) ]
#
Exit Loop If [ True ]
End Loop
If [ $errorCode ]
Revert Record/Request [ With dialog: Off ]
Close Window [ Current Window ]
Show Custom Dialog [ "Error" ; "Error Code: " & $errorCode ]
Exit Script [ Text Result: ]
End If

 

Line 25 is where I expect to reconnect to the first new record. Instead line 27 creates a new related record with the same primary key. The commit record step on line 31 throws error 504 because of the two related records sharing a primary key.

 

Is there a way to change my setup to allow for arbitrarily changing the foreign key in the Transaction table and using the relationship for never-been-committed related records?

 

Feel free to step through the script in the attached demo file.

Attachments

Outcomes