Is there any way to change the key column in ...

[Marc_Dillon]

Is there any way to change the key column in a table, and have all references to those records still be intact? For example, in one table my key column is a concatenation of several other columns. I want to add an additional column to the concatenation because I now need an additional level of specification to avoid duplicate key values, but a quick test showed that it destroyed all the old references (hundreds of them, so hard to go back and fix manually).

[RezaRaoofi]

If your data is on a RDBMS, like SQL Server, MySQL, etc. you could use Cascade Update which automatically updates all foreign keys on the many side upon change of the key value on the one side.

However if you are using spreadsheet as data source, I think the only solutions left are either directly manipulating data on the spreadsheet by building new computed key values using formulas and copy/pasting, or perhaps if there are not too many effected rows you could use action; the one that has these parameters:

[Marc_Dillon]

Yah, spreadsheet. I figured I was pretty much out of luck. I think I’m gonna have to convert to uniqueid() key columns, and change everything over. It’s in google sheets, so I should be able to write a script to basically do a bunch ’ find and replace’s.

[Levent_KULACOGL]

@Marc_Dillon

Goto Tools > Script Editor in your gSheet, paste this script and run it. It will bulk change your key column values as desired and set in the script

function replaceID() {

var sSht = SpreadsheetApp.openById(“YourSpreadsheetIDHere”);

var sht = sSht.getSheetByName(“YourSheetNameHere”);

var data = sht.getRange(2, 1, sht.getLastRow(), sht.getLastColumn()).getValues();

var newID = [];

for (var i = 0; i < data.length; i++) {

var constructNewID = data[i][0] + data[i][1] + data[i][2] //[0], [1] and [2] denotes to Column A, B and C. First index in an array is always 0. You can add more parameters with +data[i][ColumnIndex]. Depends on which columns you want to concatenate for your ID

newID.push([constructNewID])

}

sht.getRange(2, startColumnNrHere, newID.length, 1).setValues(newID) // sht.getRange(2, 1, newID.length, 1).setValues(newID) }

[Levent_KULACOGL]

@Marc_Dillon

If you want to use a deliminator between your column concatenation, you can change this line:

var constructNewID = data[i][0] + data[i][1] + data[i][2]

to

var constructNewID = data[i][0] + “-” + data[i][1] + “-” + data[i][2]

which will create a concatenated string like:

AA-BB-CC

[Marc_Dillon]

Thanks @Levent_KULACOGLU, that should save me some time.

[Marc_Dillon]

I have had success! I did not use Levent’s script, not that I didn’t appreciate it, I absolutely did, but I just figured I already knew exactly what I needed to do, no need to waste time modifying his script for my use.

Basically what I did in my google script was: Loop through the rows of the parent table: - re-create the old key values (concatenate 3 columns together, with correct spaces and extra string) -generate new random unique id -Loop through child tables ----search for old key ----replace with newly generated id key

[Levent_KULACOGL]

Well done @Marc_Dillon

Good to hear that you have solved the problem