Custom Currency Calculations using Scripting Panel

Hi @Hunter_Reynolds,
Out of the box this script converts USD to other currencies so it needs to be changed around a little for it to work like you want

Your table will need 3 fields.
Local Currency - MultiSelect populated with the correct Currency codes for the API you are using.
Amount (Local) - Currency type (remove the $)
Amount (USD) - Currency type

Then replace your script with

let table = base.getTable('NAME OF TABLE');
let record = await input.recordAsync('Pick a record', table);
let currencyType = record.getCellValueAsString("Local Currency")
let apiResponse = await fetch(`https://api.exchangerate.host/latest?base=${currencyType}`);
let data = await apiResponse.json();
let conversionRate = data.rates.USD
let result = await table.selectRecordsAsync({fields: ['Amount (Local)']});
for (let record of result.records) {
    await table.updateRecordAsync(record, {
        // Change these names to fields in your base
        'Amount (USD)': record.getCellValue('Amount (Local)') * conversionRate,
    });
}

From here you should be able to modify the script (update the table name, change the select record input…)

let table = base.getTable('NAME OF TABLE');
let record = await input.recordAsync('Pick a record', table);
let currencyType = record.getCellValueAsString("Local Currency")
let apiResponse = await fetch(`https://api.exchangerate.host/latest?base=${currencyType}`);
let data = await apiResponse.json();
let conversionRate = data.rates.USD
let result = await table.selectRecordsAsync({fields: ['Amount (Local)']});
for (let record of result.records) {
    await table.updateRecordAsync(record, {
        // Change these names to fields in your base
        'Amount (USD)': record.getCellValue('Amount (Local)') * conversionRate,
    });
}

Thanks for your reply @Vivid-Squid. I manage to convert your script to automation (daily update of conversion rate). Sharing here in case anyone needs it. Replace first 2 lines to these.

//Record Input
let inputConfig = input.config();
let recordID = inputConfig.recordID;

//Tables


let table = base.getTable('Currency');
let mainQueryResult = await table.selectRecordsAsync();
let record = await mainQueryResult.getRecord(`${inputConfig.recordID}`);

Hey there @Vivid-Squid , thank you so much for your solution! Only one problem that I’m currently experiencing: It seems that when i select a record via “choose a record”, it uses that currency exchange rate for all of the other currencies in the database. In the example below, I selected Brisbane, AUS, as the record of choice - the calculations are correct for AUS, but the records below (NZD, GBP, EUR) are inheriting the same exchange rate, resulting in an incorrect calculation. Any thoughts? Thanks!

EDIT: @Vivid-Squid , it appears that in the screenshot you shared you’re experiencing the same issue as mine. The GBP is inheriting the exchange rate from the EUR in the field above.

//Record Input
let inputConfig = input.config();
let recordID = inputConfig.recordID;

//Tables


let table = base.getTable('Currency');
let mainQueryResult = await table.selectRecordsAsync();
let record = await mainQueryResult.getRecord(`${inputConfig.recordID}`);

@Hendrik_Yang , thanks for your suggestion. I’ve replaced the lines below. My console is giving an error saying “your script has invalid settings defined”. Screenshots below:

Hi all,

Novice here - I'm trying to include this script in my table within an automation that triggers when a separate date field matches 'today', In the script I have put the following: