Correcting data in a linked (related table)

[ekz]

Hey all.. experienced SQL/Web Dev here, with minimal FM experience

I have the task of correcting data in a related table -- In Contacts, I have

fields OldID and NewID. My Donations table is linked to the Contacts table via

Donations:ContactID <--> Contacts::OldID. Now I want to achieve the following:

For each record in Contacts,

- Use oldID to find the related record in Donations,

- Update Donations:ContactID to Contacts:NewID"

In SQL i'd do an update query, but dont know how to do it in FMaker!

Any help or direction would be appreciated!

thanks

-ekz

[[email protected]]

[MoonShadow]

Back up first. I take it you've set your new ContactIDs in Contacts::NewID field? If so:

Go to your related Donations table. Create a new field in Donations called TempContactID (I know, but you need a field to hold this new ID because you can't write to your Donaltions::ContactID because it's currently being used in the relationship to write this transfer). You use your old relationship (Donations::ContactID to Contacts::OldID) to set your new Donations::TempContactID field.

Show All Records

Go To Record/Request/Page [ First ]

Loop

Set Field [ Donations::TempContactID; Contacts::NewID ]

Go to Record/Request [ Exit after Last; Next ]

End Loop

Then set your 'good' Donations::ContactID field with this temp key as:

Show All Records

Go To Record/Request/Page [ First ]

Loop

Set Field [ Donations::ContactID; Donations::TempContactID ]

Go to Record/Request [ Exit after Last; Next ]

End Loop

Reconnect Contacts::NewID to Donations::ContactID and delete your Contacts::OldID and your Donations::TempContactID after verifying the transfer.