SOLVED: Insert ID not set for subsequent transaction
I have a gallery program in which an image can have a title and description as well. The Image's name determined by a formula, so the flow is:
- Insert the Title & Description
- Upload file and rename based on a formula which includes the Insert ID and works.
- Update the gallery's Icon Image ID to be the Insert ID if the icon has not been set.
- Update the Image record to include the new file name.
What is strange is that the Insert ID is referenced in steps 2, 3, & 4, but works in 2 & 4 correctly and inserts the last used value in step 3.
In other words, if I just inserted image 7 into gallery 3; created gallery 4 (new so no icon set yet); and add an image to gallery 4, the image has the correct Insert ID in the name and the WHERE clause for step for is correct because the file name is updated correctly, BUT gallery 4 gets 7 assigned to the icon's ID foreign key field.
The order of the code is correct, so I cannot understand what is happening here.