Jump to content
  • 0

TASK to Link Records After Import


Becca37
 Share

Question

I'm migrating  an application from ACCESS to CASPIO. In ACCESS the formatting being used for record ID could potentially result in duplicate ID values, though it is currently unique. This record ID is used to link the multiple child tables to the parent Client table so we're moving to have a unique ID created for each table via auto-number.

After I import the data for each table, I have to go back and link child tables by inserting the NEW auto-numbered Client Record ID into the child tables.  I will do this via TASKs that I will run on demand after import of the Client table data and each applicable child table data.

However, I'm having difficulty crafting the TASK to update the child tables properly. It successfully matches some child records and writes the correct new Client  Record ID to the child table, but ignores other child records that have matches. 

So, instead of saying all the things I've tried, etc., etc., let me just ask: what's the best way to write the TASK to do the equivalent of this:

UPDATE mn
SET mn.ClientRID = mc.RID
FROM [Notes] mn
INNER JOIN Clients mc
ON mn.OLD_Record_ID_for_Migration = mc.OLD_Record_ID_for_Migration

EDIT:  {doh!} As it turns out, the users were allowed to DELETE Client records, so not all child records have parent Client records anymore. {thunks self on head} 

So, if anyone's curious, this is what I ended with as my task after adding a count field to my export and wanting to only update if (a) not already updated and (b) has a matching record:

2019-04-01_15-15-24.thumb.png.14827e5b58265416f0903ac48ddb836f.png

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...