- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 22, 2021 01:32 AM
Hello!
I’m populating my base with data from an external API and would like to update it regularly as new results come in without creating new duplicate records. I’ve read a couple of other topics that discuss that (like this one and this one), but am still struggling to figure it out.
I’m using the [uri] field in the base records and trying to match them to the array of [uri]s from the api. If there’s a match, the record would update, and if not, a new one would be created.
FWIW my knowledge of js is survivalist at best! Here’s what I have so far:
// The loop for each result in the API
for(let i = 0, l = data.results.length; i < l; i++) {
// ids for current records
let table = base.getTable("Observation");
let queryResult = await table.selectRecordsAsync();
const records = queryResult.records;
for(let i = 0, r = queryResult.records.length; i < r; i++) {
// grab cell value uri for reference against the api
let record = queryResult.records[i];
let cellUri = record.getCellValue("uri");
if (cellUri && data.results[i].uri) { // compare field and api
// update record
} else {
// create record
}
}
I’m getting way too many results for the update record function because of the double-loop, and nothing for the create record function, which means I’m probably setting this up wrong. Can anyone help me correct my code?
Thank you!
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 23, 2021 12:46 AM
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");
existingRecords[uri] = 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: [{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 = existingRecords[result.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 existingRecords[uri] = uri;
to existingRecords[uri] = 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 = existingRecords[result.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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 22, 2021 03:32 AM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 23, 2021 12:06 AM
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([
{
id: // What should go here? ,
fields: {
"name" : result.user.name,
// etc. etc.
},
},
]);
} else {
console.log(`Create new record with uri ${result.uri}`);
}
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 23, 2021 12:46 AM
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");
existingRecords[uri] = 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: [{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 = existingRecords[result.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 existingRecords[uri] = uri;
to existingRecords[uri] = 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 = existingRecords[result.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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 23, 2021 12:59 AM
@Raminder_Singh , you have saved me DAYS of work. Thank you so much—it works perfectly :blush: