Deleted parent reverts child?

[LaRetta]

I’ve completed a migration step where I need to find transfer records in an Accounts table (it holds a lot more than transfers) and import them into a new Transfers table so they become the many side. I then save only the first ‘parent’ transfer record (one each unique by Contract/AcctNum) and it must be the first AccountID of each ‘set’ because it holds history and we want the sequencing of that AccountID where it is within the Accounts table. I delete all the remaining same Contract/AccountNum records out of Accounts and zero the amount but I first must point the new transfer records back to their NEW parent (the first) by reassigning the AccountID in the transfer table to the variable I specify. I then delete the ‘dup’ parent.

I am not posting because I need help with any of this; the process is done and I did not (could not) create aux relationships to make it happen for migration so this is how I solved it. I’m sharing this part so you understand the context of what you might view in the demo (if you choose to assist). And now the reason for my posting:

We know we can set a related key field to something else, thus breaking the relationship and that’s fine (which is what I need to do here) but, because I then delete its original parent (one-to-one based upon AccountID), the AccountID I just set in Transfers reverts back to the ‘dead’ parent’s AccountID! The AccountID I assigned won’t stick.

I have provided a raw table with only this example. You can watch it assign the AccountID I hold in the variable. You can see how many ‘same’ Contract/Account Numbers there are and you know the Transfer table should end up with two AccountID 2, three AccountID 6 etc. But that is not what happens.

This reversion after setting the Transfer::AccountID and deleting the parent is unexpected behavior (to me). When I include a Commit between the steps, it works fine. I realize it must have to do with the parent/child relationship but I can’t nail down why it reverts.

Can anyone help me understand the theory on what is actually happening here? BTW, I wasted several hours before I identified why it broke. It worked manually and even when stepping through debug, it showed it set the related AccountID properly. :crazy2:

CreateTransfers.zip

Edited October 6, 2010 by Guest
I replaced the demo. The one I had uploaded had the Commit enabled. I disabled it for the test.

[comment]

I think what your file shows is this:

When you delete an uncommitted record, it is deleted as such (i.e. without committing it first). Therefore any modifications of related records are reverted when the local record is deleted.

Note that deleting the local record also unlocks the related records.

[Raybaudi]

From Help On Line:

[color:green]-----------------------------------------------

• When you change related data (such as related records displayed in a portal), these changes are not committed until you commit the record that is displaying the related data.

Data is committed when you:

• select another record [color:red](*)

• click anywhere outside of the current field

• Windows: press Enter on the numeric keypad, or Ctrl+Enter on computers without a numeric keypad

• Mac OS: press Enter (not Return)

• switch to another mode [color:red](**)

[color:green]-----------------------------------------------

So, to obtain the same effect of Commit Record [ ]:

[color:red](*) You could use: Go to Record [ No dialog ; Get ( RecordNumber ) ]

[color:red](**) You could use: Enter Find Mode [ ] > Enter Browse Mode [ ]

BTW: there is a [color:red]bug in your script that deletes even a record that must not be deleted ( 33|399 )

You could put an Exit Loop If [ Get ( RecordNumber ) = Get ( FoundCount ) ] after the Delete record script step.

[comment]

One could argue that when you delete the current record, you must be ipso facto selecting another record... However, as we can see, that is not so.

[LaRetta]

Hi Daniele,

The script was written in hurry to provide a base for observing the oddity in this file only; I've been working 70-hour weeks. I even mentioned that I didn't need the issue critiqued - only to observe the behavior. I even DO have an Exit Loop [] in the real migration file which protects properly.

Hi Michael,

Well, I understand I suppose. But are you saying you would expect this to happen? I'm not the only one that wouldn't have expected this (from what I'm hearing from others now). I assumed it was because of the Commit (which is why I ended up adding it), but it still caught me off guard particularly since I watched the ID change and it didn't throw an error.

Once I changed the related ID, it should have been related to the other parent which wasn't deleted and was committed. I see the logic behind it but it is fine line and certainly could catch someone if not careful.

UPDATE: Yes, a delete seems like a commit to me. ROFLMAO!

Edited October 6, 2010 by Guest

[comment]

are you saying you would expect this to happen?

It's a close call but I think eventually it's a better choice than the alternative. Consider someone editing portal records, then changing their mind and deleting the entire parent record. Should this make the edits permanent?

[Raybaudi]

I even mentioned that I didn't need the issue critiqued

... and I didn't criticized in any way, I just tried to give my contribution.

[LaRetta]

Well said and a perfect example. This is what I'll hang onto to understand the meat of this issue. Thank you!!