Hi @Ulu_Mills,
Something like this might work for you:
//We first put existing records' URIs into a map named existingRecords
let table = base.getTable("Observation");
let queryResult = await table.selectRecordsAsync();
let existingRecords = {};
for (let record of queryResult.records) {
let uri = record.getCellValue("uri");
existingRecords[uri] = uri;
}
//Then we fetch the data from the api (I have hardcoded these values here just to test, you will of course need to fetch them from your API)
let data = {
results: [{uri: "https://news.ycombinator.com/"}, {uri: "https://news.ycombinator.com/4"}]
}
//And finally we test which URIs exist in the map we created earlier.
//If the URI exists in the map we need to update the record, other we need to create a new record.
for (let result of data.results) {
if (result.uri in existingRecords) {
console.log(`Update existing record with uri ${result.uri}`);
} else {
console.log(`Create new record with uri ${result.uri}`);
}
}
Hope this helps,
-Raminder
@Raminder_Singh
Raminder!! Thank you so so much for your help. I’m almost there! One more question:
For the update function, how do I get the record ID for the record with the uri being referenced? Building off of what you wrote:
for (let result of data.results) {
if (result.uri in existingRecords) {
let table = base.getTable("Observation"); //Do I need this?
let query = await table.selectRecordsAsync(); //Do I need this?
let records = query.records; //Do I need this?
await table.updateRecordsAsync(c
{
id: // What should go here? ,
fields: {
"name" : result.user.name,
// etc. etc.
},
},
]);
} else {
console.log(`Create new record with uri ${result.uri}`);
}
@Raminder_Singh
Raminder!! Thank you so so much for your help. I’m almost there! One more question:
For the update function, how do I get the record ID for the record with the uri being referenced? Building off of what you wrote:
for (let result of data.results) {
if (result.uri in existingRecords) {
let table = base.getTable("Observation"); //Do I need this?
let query = await table.selectRecordsAsync(); //Do I need this?
let records = query.records; //Do I need this?
await table.updateRecordsAsync(c
{
id: // What should go here? ,
fields: {
"name" : result.user.name,
// etc. etc.
},
},
]);
} else {
console.log(`Create new record with uri ${result.uri}`);
}
Hi @Ulu_Mills,
I’ve tweaked my script a bit like this:
//We first put existing records' URIs into a map named existingRecords
let table = base.getTable("Observation");
let queryResult = await table.selectRecordsAsync();
let existingRecords = {};
for (let record of queryResult.records) {
let uri = record.getCellValue("uri");
existingRecordsouri] = record;
}
//Then we fetch the data from the api (I have hardcoded these values here just to test, you will of course need to fetch them from your API)
let data = {
results: t{uri: "https://news.ycombinator.com/"}, {uri: "https://news.ycombinator.com/4"}]
}
//And finally we test which URIs exist in the map we created earlier.
//If the URI exists in the map we need to update the record, other we need to create a new record.
for (let result of data.results) {
if (result.uri in existingRecords) {
let existingRecord = existingRecordsoresult.uri];
await table.updateRecordAsync(existingRecord, {"Status": "Done"});
console.log(`Updated existing record with uri ${result.uri}`);
} else {
let newRecordId = await table.createRecordAsync({
"Status": "Todo",
"uri": result.uri,
});
console.log(`Created new record with id ${newRecordId}`);
}
}
First, I have changed existingRecordsouri] = uri;
to existingRecordsouri] = record;
. This will ensure that when we update the record later we can access the existing record. The code to update a record is like this:
let existingRecord = existingRecordsoresult.uri];
await table.updateRecordAsync(existingRecord, {"Status": "Done"});
Here I’m updating my Status
field to a value Done
. In my case the Status
fields was of type Single line text. If your field is of a different type you might have to tweak this part a bit. See this page for reference.
The code to create a new record is similar:
let newRecordId = await table.createRecordAsync({
"Status": "Todo",
"uri": result.uri,
});
Regarding your Do I need this?
questions, you don’t need those as you can reference the table
variable as I did in my script.
Good luck :grinning_face_with_smiling_eyes:
-Raminder
@Raminder_Singh , you have saved me DAYS of work. Thank you so much—it works perfectly