Script to copy records into secondary table - id mapping

[corb]

Hi - I’m not great with scripting but trying to learn. All I want to do is copy a set of data - really just one column, from tableA to tableB, monthly via automation. The column is a linked field in the destination table and so would need to be an array of recordIds when passed. I’m not sure how to cleanly do that and am not familiar enough with scripting to fix it. The below are things that I’ve borrowed or taken from elsewhere & tried to adapt, but I’m not quite there. Can someone point me to my issues? I know for example that I am passing only the first element with “[0]” but I don’t know quite how to cycle through all the elements cleanly.

I welcome any code cleanup or suggestions.

let sourcetable = base.getTable("Projects");
let sourceview = sourcetable.getView("ProjectView1");
let destination_table_name = "DestinationTableName";
let destination_table = base.getTable(destination_table_name);

let query = await sourceview.selectRecordsAsync({fields: ["Project Name"]});
let records = query.records;
let recordsToCreate = new Array;

//I think I begin to have trouble here, and I don't know how to properly loop through and push the query results into the array.

for (let projids of query.recordIds){
    recordsToCreate.push({
        fields:{
            "Project" : [{id: query.recordIds[0]}]
        }
    })
}

//is this right? I think I am probably messing this up too
recordsToCreate = query.records.map(record => record.id);
let recordsCreated = await batchAnd ('Create',destination_table,recordsToCreate);

//this is a function I borrowed from others here on the forums for batch updating >50
// ********************
async function batchAnd(action, table, records) {
  let recordsActedOn = records.length;

  switch (action) {
      case 'Update':
          while (records.length > 0) {
              await table.updateRecordsAsync(records.slice(0, 50));
              records = records.slice(50);
          };
          break;
      
      case 'Create':
          while (records.length > 0) {
              await table.createRecordsAsync(records.slice(0, 50));
              records = records.slice(50);
          };
          break;

      case 'Delete':
          while (records.length > 0) {
              await table.deleteRecordsAsync(records.slice(0, 50));
              records = records.slice(50);
          }
          break;

      default:
          output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
          recordsActedOn = null;
  }
  return recordsActedOn;
}

[Alexey_Gusev]

Hi,

indeed, your goal is not clear.
You have source table linked to some other table, and destination linked to the same other table and you want to copy links? Why do you need to query “Project Name” then?

or you just want each new record of destination table to be linked to a single record of your source table?

You need first 5 lines of your code (in fact, 4, let destination_table ="DestinationTableName" is enough)

//this is a function I borrowed
in your case it’s a bit “over-engineering”
while (records.length) is enough for statement,
function splice cuts 50 records from array ( records = records.slice(50) part ) and return these 50 record to the caller function (records.slice(0, 50)).

so, you need to clarify your goal - update or create, and choose one of these:

while (upd.length) await table.updateRecordsAsync(upd.splice(0,50))

while (crt.length) await table.createRecordsAsync(crt.splice(0,50))

You can also avoid for loop and create/push array.
Your goal is to transform array ‘query.records’ (or even query.recordIds) into array of ‘updates’ or ‘create’ (i guess you need Create)

[corb]

Alexey_Gusev:

you just want each new record of destination table to be linked to a single record of your source table

Yep - this.

I agree it’s a bit overengineering, or more naive/clueless engineering haha. I don’t quite know what I’m doing so in borrowing things from various other scripts I am sure there are plenty of errors.

Yes - the goal is to insert a set of records into Table2. Which records? All records which appear in View1 (which is already filtered), which is based on Table1. Each new row in Table2 should be linked to Table1 by the linked column information I want to get out of View1.

I would love to avoid the loop & push. Let me see if I can understand your message further and give it a try.