Custom Currency Calculations using Scripting Panel

[Hunter_Reynolds]

Good afternoon everyone! I’m trying to create custom currency conversions for a project I’m involved with. We’re traveling around the world selling concert tickets at the door of the venues, and each country has a different currency. I want to be able to take the ticket price for each country’s currency and convert to USD which will then be displayed on that record.

I’ve seen this script is available to convert currencies, but I don’t know how to make the currency type dynamic as it only seems to show a single currency in the example.

How can I edit line 7, where is says “GBP” to take the currency abbreviation from a field that exists within the record (ex, EUR,NZD,AUS). Any help is appreciated!!

[Vivid-Squid]

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…)

[Hendrik_Yang]

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}`);

[Hunter_Reynolds]

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.

[Hunter_Reynolds]

@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:

[Vivid-Squid · Accepted solution]

Dang, im sorry i did even look at that part.

Here you go:

let table = base.getTable("MY TABLE NAME");
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.updateRecordAsync(record.id, {'Amount (USD)': record.getCellValue('Amount (Local)') * conversionRate});

I removed the loop that is updating all the other records, this will only update the record you pick.

re. @Hendrik_Yang’s post. You have seen that you can add a scripting extension to run scripts, but you can also run them in Automations. They run a little differently than in the sidebar extension in that you define your record variables and then you call them in the script. Ultimately you most likely want this as an automation instead of selecting the record each time as in my script.

[Richard_Plews]

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:

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

//Tables


let table = base.getTable('Processes');
let mainQueryResult = await table.selectRecordsAsync();
let record = await mainQueryResult.getRecord(`${inputConfig.recordID}`);
let currencyType = record.getCellValueAsString("Fee Currency");
let apiResponse = await fetch(`https://api.exchangerate.host/latest?base=${currencyType}`);
let data = await apiResponse.json();
let conversionRate = data.rates.GBP;
let result = await table.updateRecordAsync(record.id, {'Fee (GBP)': record.getCellValue('Fee (Local)') * conversionRate});

When I try testing the script I get "Error: No record matching "undefined" found in query at main on line 10". It doesn't seem to like the selectRecordsAsync() on line 9 either as it's 'deprecated'.

Any tips?