Update Records with external API data with a validation step

[The_Focus_Forwa]

Hi there, I work for a prison education nonprofit that works with people facing federal crimes. We want to be able to send legal updates and resources to graduates of our classes, and use Airtable to track those graduates. We want to keep our records updated as to when graduates of our classes are transferred to a new federal prison, had their release date changed, or been released.

Anyone incarcerated in federal prison is given a register number. You can use that number to get release date and location data from this API. We’ve been using a combination of Python scripts to do this but it’s been clunky and buggy because I’m not that great at Python (frankly I’m not that great at coding, period, but I’m learning). I think we can do this more efficiently with a script in Airtable.

Here’s the one catch: every once in a while, someone puts in the wrong register number for our graduates. Is there a way to show values returned from an API side by side with the current values in the Airtable record and prompt the user to confirm that they want the script to overwrite the current Airtable value with the ones it pulled from the API? That way if there’s a typo in the register number, we’ll see it because it’s someone else’s name in the API data.

Thanks!

[Raminder_Singh · Accepted solution]

Hello @The_Focus_Forward_Pr,

I don’t fully know how you enter the register number but if you are trying to do it through a script here’s a skeleton of how you can do it:

let nameFromRecord = 'John Doe';//read name from table
let nameFromApi = 'Jane Doe';//read name from api

output.text(`Name in Airtable: ${nameFromRecord}`);
output.text(`Name from API: ${nameFromApi}`);

let updateRecord = await input.buttonsAsync('Update Record?', ['Yes', 'No']);

if (updateRecord === 'Yes') {
    //update the record here
    output.text('Updated Successfully');
} else {
    output.text('Did not update the record');
}

[The_Focus_Forwa]

Thank you, @Raminder_Singh! That all makes sense for the validation step, and I’ll use that as a starting point once I’ve got the data.

I’m actually looking for help with how to do the first step, too: that is pull the data in from an external API. I have the skeleton of code for it but not sure how to actually make it work. Here’s what I’ve got:

Iterate through records such that

1. If ‘Status’ equals “sentenced” (an option for a single-select field), and ‘Register Number’ is not empty then
2. let registerNumber = ‘Register Number’
3. get (I think, I’m not too good with API queries) ‘http://www.bop.gov/PublicInfo/execute/inmateloc?todo=query&output=json&inmateNumType=IRN&inmateNum=’ + registerNumber

That should return something including (among other data)

{
“InmateLocator”: [
{
“nameLast”: “STEVENS”,
“nameFirst”: “CONNOR”,
“nameMiddle”: “C”,
“inmateNum”: “57978-060”,
“inmateNumType”: “”,
“releaseCode”: “”,
“faclName”: “McKean”,
“faclType”: “FCI”,
“projRelDate”: “05/15/2019”,
“actRelDate”: “”,
}
],

I then want it to run through the following checks:

1. Does the API return a proRelDate equal to the value in the Airtable’s record for ‘Release Date’?
2. Does the record’s (single select) value for ‘Post-Sentence Prison’ equal what the API returns for faclType + faclName (for example, “FCI McKean”)
3. Does the API return the releaseCode as a null or empty value?

If the answers to any of the above is no, then first I want to do validation step for that record as much the way you described above. I can compare the nameFirst + nameLast from the api visually with what’s in the ‘Name’ field for the record in airtable using what you’ve put together (I don’t want to do this automatically - the BoP often misspells names) and if I click continue, then I’d like the script to do the following

1. If API returns releaseCode value of “R”, change ‘Status’ to “Released”
2. If API returns a new projRelDate, I want to write that date to ‘Release Date’
3. If API returns a new faclType + faclName, I want to write those values to ‘Post-Sentence Prison’.

Any advice on how to put this together?

[Raminder_Singh]

Here’s the solution fleshed out in a little more detail. I’ve left out the last part where you update the records so that you understand the script before starting to use it. Hope it helps.

const config = input.config({
    title: 'Update data script',
    description: 'A script that updates the data',
    items: [
        input.config.table('graduatesTable', {
            label: 'Graduates table',
            description: 'The table in which you track people facing federal crimes'
        }),
        input.config.field('statusField', {
            label: 'Status field',
            description: '',
            parentTable: 'graduatesTable',
        }),
        input.config.field('registerNumberField', {
            label: 'Register number field',
            description: '',
            parentTable: 'graduatesTable',
        }),
        input.config.field('releaseDateField', {
            label: 'Release date field',
            description: '',
            parentTable: 'graduatesTable',
        }),
        input.config.field('postSentencePrisonField', {
            label: 'Post sentence prison field',
            description: '',
            parentTable: 'graduatesTable',
        }),
        
        input.config.field('nameField', {
            label: 'Name field',
            description: '',
            parentTable: 'graduatesTable',
        }),
    ]
});

let table = base.getTable(config.graduatesTable.name);
let query = await table.selectRecordsAsync();

for (let record of query.records) {
    let status = record.getCellValueAsString(config.statusField.name);
    let registerNumber = record.getCellValueAsString(config.registerNumberField.name);
    let projectedReleaseDate = record.getCellValueAsString(config.releaseDateField.name);
    let postSentencePrison = record.getCellValueAsString(config.postSentencePrisonField.name);
    let name = record.getCellValueAsString(config.nameField.name);

    if (status == 'sentenced' && registerNumber) {
        let url = `https://www.bop.gov/PublicInfo/execute/inmateloc?todo=query&output=json&inmateNumType=IRN&inmateNum=${registerNumber}`;
        let response = await remoteFetchAsync(url);
        let apiResult = await response.json();

        if (apiResult && Array.isArray(apiResult.InmateLocator) && apiResult.InmateLocator.length === 1) {
            let inmate = apiResult.InmateLocator[0];
            if (inmate.projRelDate != projectedReleaseDate ||
                postSentencePrison != `${inmate.faclType} ${inmate.faclCode}`||
                inmate.releaseCode) {
                    output.text(`Name in Airtable: ${name}`);
                    output.text(`Name from API: ${inmate.nameFirst} ${inmate.nameLast}`);

                    let updateRecord = await input.buttonsAsync('Update Record?', ['Yes', 'No']);
                    if (updateRecord === 'Yes') {
                        //update the record here
                        //If API returns releaseCode value of “R”, change ‘Status’ to “Released”
                        //If API returns a new projRelDate, I want to write that date to ‘Release Date’
                        //If API returns a new faclType + faclName, I want to write those values to ‘Post-Sentence Prison’.
                        output.text('Updated Successfully');
                    } else {
                        output.text('Did not update the record');
                    }

                }
        } else {
            console.error(`Invalid object returned by api: ${apiResult}`);
        }
    }
}

Good luck :slightly_smiling_face:

[The_Focus_Forwa]

I took a shot at as much of this as I could easily figure out. I get an error message with line 1 :grinning_face_with_sweat: but at least it might be a start? Weirdly the part that totally stumped me was trying to figure out how to write a value to a cell in Airtable. Here’s what I’ve got:

let table = base.getTable('Participants');
let query = await table.selectRecordsAsync();
for (let record of query.records) {
   if (record.getCellValue('Status')='Sentenced' AND len.record.getCellValue('Register Number')>0) {
        let name = record.getCellValue('Name')
        let currentPrison = record.getCellValue('Post-Sentence Prison')
        let currentRelDate = record.getCellValue('Release Date')      
        let apiResponse = await fetch('http://www.bop.gov/PublicInfo/execute/inmateloc?todo=query&output=json&inmateNumType=IRN&inmateNum=’ + record.getCellValue('Register Number')');
        let data = await apiResponse.json();
        let bopName = data.InmateLocator.nameFirst +" "+ data.InmateLocator.nameLast;
        let newPrison = data.InmateLocator.faclType + " " +data.InmateLocator.faclName;
        let newRelDate = data.InmateLocator.projRelDate + data.InmateLocator.actRelDate
        //projRelDate and actRelDate are never both present, if one has data, the other will be empty
        let releaseCode = data.InmateLocator.releaseCode);
        if (currentPrison != newPrison OR currentRelDate != newRelDate OR releaseCode ='R'){
            output.text('New data found for register number: ' + record.getCellValue('Register Number'));
            output.text('Name in Airtable: ' name + ', Release Date in Airtable: ' currentRelDate + ', Prison in Airtable:' + currentPrison);
            output.text('Name in BOP Database: ' bopName + ", Release Date: " + newRelDate + ', Current Prison: ' + newPrison);
            if (releaseCode = 'R') {
                output.text('This person has been recorded by the BoP as released.');
            }
            let updateRecord = await input.buttonsAsync('Update this record?', ['Yes', 'No']);
            if (updateRecord === 'Yes'){
                if (newPrison != currentPrison){
                    //insert code to update post sentence prison
                    }
                if (newRelDate != currentRelDate){
                    //insert code to update release date
                }
                if (releaseCode = 'R'){
                    //inset code to change status to Released
                }
                }
            }
        }     
       }
   }

[The_Focus_Forwa]

Thank you! I’ll study this and try to integrate it into my efforts (above). The last step of actually writing the records is proving weirdly hard. I’m not seeing the command I need in my first glance at the API docs though I probably am just not looking for the right terms.

[Raminder_Singh]

Hey your solution is almost correct apart from minor syntactical errors. Regarding updating the fields you can use the updateRecordAsync function:

await table.updateRecordAsync(recordId, {
    "ReleaseDate": "07/19/2019",
});

If your fields are not single line text types then you might need to tweak it a bit. Refer to this page for details. Take a look under your field type’s Cell value write format section on that page. E.g. a single select field might be updated something like this:

await table.updateRecordAsync(recordId, {
    "Status": { name: "Released" },
});

because the Cell value write format section for single select field looks like this on that page:

TYPEDEF

{ id: string } | { name: string }

[The_Focus_Forwa]

Okay, I melded our code and before I start writing to the database, I want to debug a couple small things. The main one is that I’m getting a handful of false-positives (results for things that don’t actually have any changes) and I think it’s because of the string formatting of the the projRelDate and actRelDate data from the API. For example, when I have both that and the Release Date data from Airtable shown side-by-side, one will have leading zeros on single-digit months and days, and the other won’t (so if they’re evaluated as strings, they don’t match). Is there some kind of function I can use to store them as comparable dates rather than strings? Or do you think something else might be going on?

Here’s what I’ve got for the script:

    let table = base.getTable("Participants");
    let query = await table.selectRecordsAsync();

    for (let record of query.records) {
        let status = record.getCellValueAsString("Status");
        let registerNumber = record.getCellValueAsString("Register Number");
        let projectedReleaseDate = record.getCellValueAsString("Release Date");
        let postSentencePrison = record.getCellValueAsString("Post-Sentence Prison");
        let name = record.getCellValueAsString("Name");

        if (status == 'Sentenced' && registerNumber) {
            let url = `https://www.bop.gov/PublicInfo/execute/inmateloc?todo=query&output=json&inmateNumType=IRN&inmateNum=${registerNumber}`;
            let response = await remoteFetchAsync(url);
            let apiResult = await response.json();

              if (apiResult && Array.isArray(apiResult.InmateLocator) && apiResult.InmateLocator.length === 1) {
                let inmate = apiResult.InmateLocator[0];
                let newDate = inmate.projRelDate + inmate.actRelDate
                if (inmate.projRelDate != projectedReleaseDate ||
                    postSentencePrison != ` ${inmate.faclName} ${inmate.faclType}`||
                    inmate.releaseCode) {
                        output.markdown(`New data found for register number:** ${registerNumber}**`);
                        output.markdown(`Name in Airtable:** ${name}**, Release Date in Airtable:** ${projectedReleaseDate}**,  Prison in Airtable:** ${postSentencePrison}**`);
                        output.markdown(`Name in BOP Database: **${inmate.nameFirst} ${inmate.nameLast}**, Release Date: **${newDate}**, Current Prison: ** ${inmate.faclName} ${inmate.faclType}**`);
                        if (inmate.releaseCode){
                            output.markdown(`**This graduate has been marked as released by the BoP.**`);
                        }
                        let updateRecord = await input.buttonsAsync('Update Record?', ['Yes', 'No']);
                        if (updateRecord === 'Yes') {
                            //update the record here
                            //If API returns releaseCode value of “R”, change ‘Status’ to “Released”
                            //If API returns a new projRelDate, I want to write that date to ‘Release Date’
                            //If API returns a new faclType + faclName, I want to write those values to ‘Post-Sentence Prison’.
                            output.text('Updated Successfully');
                        } else {
                            output.text('Did not update the record');
                        }

                    }
            } else {
                console.error(`Invalid object returned by api: ${apiResult}`);

            }
        }
    }

And here’s a slightly modified example of the output:

New data found for register number: 999999-054

Name in Airtable: John Smith , Release Date in Airtable: 5/2/2036 , Prison in Airtable: Forrest City Medium FCI

Name in BOP Database: JON SMITH , Release Date: 05/02/2036 , Current Prison: Forrest City Medium FCI

[Raminder_Singh]

I think you are close. You should be able to use Date - JavaScript | MDN to parse strings into dates and then compare those. E.g:

let d1 = new Date("5/2/2036");
let d2 = new Date("05/02/2036");
let same = d1.getTime() == d2.getTime();

When your questions are Javascript specific, feel free to search on google instead of looking for answers only on the Airtable forums.

[The_Focus_Forwa]

That makes sense. Thank you. I was able to fix this and the next few problems by looking at JS documentation so we’re really, really close.

This might be the last roadblock: being able to write a new value to a single-select field. There are a lot of federal prisons and we don’t have all of them in our system. Right now if we try to write a new entry into a single select field that isn’t already an existing option, then we get an error:

Can’t set cell values: invalid cell value for field ‘Post-Sentence Prison’.
Could not find a choice with that ID or name

Is there a way to have the script create a new option with the name I provide?

Thanks again!

let table = base.getTable("Participants");
let query = await table.selectRecordsAsync();

for (let record of query.records) {
    let status = record.getCellValueAsString("Status");
    let registerNumber = record.getCellValueAsString("Register Number");
    let projectedReleaseDate = record.getCellValueAsString("Release Date");
    let postSentencePrison = record.getCellValueAsString("Post-Sentence Prison");
    let name = record.getCellValueAsString("Name");

if (status == 'Sentenced' && registerNumber) {
    let url = `https://www.bop.gov/PublicInfo/execute/inmateloc?todo=query&output=json&inmateNumType=IRN&inmateNum=${registerNumber}`;
    let response = await remoteFetchAsync(url);
    let apiResult = await response.json();

      if (apiResult && Array.isArray(apiResult.InmateLocator) && apiResult.InmateLocator.length === 1) {
        let inmate = apiResult.InmateLocator[0];
        if (inmate.projRelDate == 'UNKNOWN'){
            continue;
        };
        if (inmate.faclName == 'IN TRANSIT'){
            continue;
        }
        let newDate = inmate.projRelDate + inmate.actRelDate
        //projRelDate and actRelDate are mutually exclusive, if one is present the other will be empty
        let parsedNewDate = new Date(newDate);
        let parsedOldDate = new Date(projectedReleaseDate);
        let newPrison = `${inmate.faclName} ${inmate.faclType}`;
        if (parsedNewDate.valueOf() != parsedOldDate.valueOf() ||
            postSentencePrison != newPrison||
            inmate.releaseCode) {
                output.markdown(`New data found for register number:** ${registerNumber}**`);
                output.markdown(`Name in Airtable:** ${name}**, Release Date in Airtable:** ${projectedReleaseDate}**,  Prison in Airtable:** ${postSentencePrison}**`);
                output.markdown(`Name in BOP Database: **${inmate.nameFirst} ${inmate.nameLast}**, Release Date: **${newDate}**, Current Prison: ** ${newPrison}**`);
                if (inmate.releaseCode){
                    output.markdown(`**This graduate has been marked as released by the BoP.**`);
                };
                let updateRecord = await input.buttonsAsync('Update Record?', ['Yes', 'No']);
                if (updateRecord === 'Yes') {
                    if (parsedNewDate.valueOf() != parsedOldDate.valueOf()){
                        output.markdown(`*Updating release date of ${name} from ${projectedReleaseDate} to ${newDate}*`);
                        await table.updateRecordAsync(record, {
                            "Release Date": parsedNewDate,
                        });
                    };
                    if (inmate.releaseCode) {
                        output.markdown(`*Marking ${name} as released.*`);
                        await table.updateRecordAsync(record, {
                            "Status": { name: "Released"},
                        });
                    continue;
                    };
                    if (postSentencePrison != newPrison){
                        output.markdown(`*Updating location of ${name} from ${postSentencePrison} to ${newPrison}*`)
                        await table.updateRecordAsync(record, {
                            "Post-Sentence Prison": { name: newPrison},
                        });
                    }
                } else {
                    output.text('Record not updated');
                }

            }
    } else {
        console.error(`Invalid object returned by api: ${apiResult}`);

    }
}

}