Skip to main content

I support an organization fundraising in a variety of currencies (USD, GBP, EUR, CAD, ILS). To simplify the process for the person maintaining the list, I have a single number field for the donation amount and a single linked field to select the appropriate currency (this is opposed to having 5-6 different currency fields on the intake form and needing to choose the correct currency field each time). We found that it was easier and faster to use a linked filed as opposed to a single select for currency.

I would like to keep a running total of all the donations' values in both USD and EUR so I can use rollup fields to display overall amounts on a dashboard (i.e. my 100 donations in a variety of currencies are worth a total of USD 10,000 or EUR 9,500).

Exchange rates are "static" in that if on the day of the donation, the ILS:USD rate is 4:1, it should stay calculated at that rate forever, even if in a year from now the rate is different. Exchange rates will be updated once a year based on an average rate--no need for daily updates.

I cannot figure out how to create an automation (or other system) that looks for a new donation entry and makes the correct currency conversions. I'm open to using formulas, scripting (though I don't know it well yet), automations, changing to a single select field, Make, etc., but do not want to purchase another add-on.

Maybe I'm overthinking and the best solution is simply to have separate currency fields instead of trying to do this complicated conversion on the backend. 

Thanks for any suggestions?

Assuming your tables are set up like this:



Then I'd suggest stamping in the exchange rate when the Donation record is created with an automation like so:

Link to base

   

Assuming your tables are set up like this:



Then I'd suggest stamping in the exchange rate when the Donation record is created with an automation like so:

Link to base

   


Thanks Adam. I ultimately went a little further and used ChatGPT to write a script (which, by the way, is amazing). I'm posting the code below just in case it helps someone else in the future. I've done a bunch of testing and it seems to work well, but maybe there is something I'm missing. Anyway....

I have the user enter a number amount and select a linked field currency. I then display the combined value using a simple formula field, but the script copies the amount into the correct currency field and also does a conversion into two fields that are used for the running total. The formula runs via automations (record create and record update) and watches the relevant number (amount)/linked (currency) fields.

The one thing it does NOT do currently is let me have a continuously updated exchange rate. A future version would probably involve creating a free account at one of the currency API providers and weekly (or daily) pulling currency conversion rates and doing the conversion based on more updated rates, while recording the date the rates were pulled and the actual rates themselves. (I also know there isn't error correction, etc., but this is currently fine for my purposes.)

 

// Script written by ChatGPT

// Define the table name

let table = base.getTable("Grants");



// Get the record ID from the automation trigger

let inputConfig = input.config();

let recordId = inputConfig.recordId;



if (recordId) {

// Fetch the specific record that was created or updated

let record = await table.selectRecordAsync(recordId);



if (record) {

// Get the values of Request, Award, -> USD, -> NIS

let request = record.getCellValue("Request Amount");

let award = record.getCellValue("Award Amount");

let reqToUSD = record.getCellValue("Req -> USD");

let reqToNIS = record.getCellValue("Req -> NIS");

let awardToUSD = record.getCellValue("Award -> USD");

let awardToNIS = record.getCellValue("Award -> NIS");



// Get the values of Request Currency and Award Currency

let requestCurrency = record.getCellValue("Request Currency");

let awardCurrency = record.getCellValue("Award Currency");



// Initialize reset object

let reset = {

"Request in USD": 0,

"Award in USD": 0,

"Request in NIS": 0,

"Award in NIS": 0,

"Request in EUR": 0,

"Award in EUR": 0,

"Request in GBP": 0,

"Award in GBP": 0,

"Request in CAD": 0,

"Award in CAD": 0,

"Request in AUD": 0,

"Award in AUD": 0,

"For Running Total Requests USD": 0,

"For Running Total Requests NIS": 0,

"For Running Total Awards USD": 0,

"For Running Total Awards NIS": 0

};



// Reset all currency fields to 0

await table.updateRecordAsync(recordId, reset);



// Re-initialize update object for new values

let update = {};



// Handle Request Currency

if (request != null && requestCurrency) {

switch (requestCurrencyr0].name) {

case "USD":

updateu"Request in USD"] = request;

break;

case "NIS":

updateu"Request in NIS"] = request;

break;

case "EUR":

updateu"Request in EUR"] = request;

break;

case "GBP":

updateu"Request in GBP"] = request;

break;

case "CAD":

updateu"Request in CAD"] = request;

break;

case "AUD":

updateu"Request in AUD"] = request;

break;

default:

break;

}

}



// Handle Award Currency

if (award != null && awardCurrency) {

switch (awardCurrencyr0].name) {

case "USD":

updateu"Award in USD"] = award;

break;

case "NIS":

updateu"Award in NIS"] = award;

break;

case "EUR":

updateu"Award in EUR"] = award;

break;

case "GBP":

updateu"Award in GBP"] = award;

break;

case "CAD":

updateu"Award in CAD"] = award;

break;

case "AUD":

updateu"Award in AUD"] = award;

break;

default:

break;

}

}



// Perform currency conversions for Request and update running totals

if (request != null) {

if (requestCurrencyr0].name !== "USD") {

updateu"For Running Total Requests USD"] = request * reqToUSD;

} else {

updateu"For Running Total Requests USD"] = request;

}

if (requestCurrencyr0].name !== "NIS") {

updateu"For Running Total Requests NIS"] = request * reqToNIS;

} else {

updateu"For Running Total Requests NIS"] = request;

}

}



// Perform currency conversions for Award and update running totals

if (award != null) {

if (awardCurrencyr0].name !== "USD") {

updateu"For Running Total Awards USD"] = award * awardToUSD;

} else {

updateu"For Running Total Awards USD"] = award;

}

if (awardCurrencyr0].name !== "NIS") {

updateu"For Running Total Awards NIS"] = award * awardToNIS;

} else {

updateu"For Running Total Awards NIS"] = award;

}

}



// Update the record with the new values

await table.updateRecordAsync(recordId, update);

}

}

 

Thanks Adam. I ultimately went a little further and used ChatGPT to write a script (which, by the way, is amazing). I'm posting the code below just in case it helps someone else in the future. I've done a bunch of testing and it seems to work well, but maybe there is something I'm missing. Anyway....

I have the user enter a number amount and select a linked field currency. I then display the combined value using a simple formula field, but the script copies the amount into the correct currency field and also does a conversion into two fields that are used for the running total. The formula runs via automations (record create and record update) and watches the relevant number (amount)/linked (currency) fields.

The one thing it does NOT do currently is let me have a continuously updated exchange rate. A future version would probably involve creating a free account at one of the currency API providers and weekly (or daily) pulling currency conversion rates and doing the conversion based on more updated rates, while recording the date the rates were pulled and the actual rates themselves. (I also know there isn't error correction, etc., but this is currently fine for my purposes.)

 

// Script written by ChatGPT

// Define the table name

let table = base.getTable("Grants");



// Get the record ID from the automation trigger

let inputConfig = input.config();

let recordId = inputConfig.recordId;



if (recordId) {

// Fetch the specific record that was created or updated

let record = await table.selectRecordAsync(recordId);



if (record) {

// Get the values of Request, Award, -> USD, -> NIS

let request = record.getCellValue("Request Amount");

let award = record.getCellValue("Award Amount");

let reqToUSD = record.getCellValue("Req -> USD");

let reqToNIS = record.getCellValue("Req -> NIS");

let awardToUSD = record.getCellValue("Award -> USD");

let awardToNIS = record.getCellValue("Award -> NIS");



// Get the values of Request Currency and Award Currency

let requestCurrency = record.getCellValue("Request Currency");

let awardCurrency = record.getCellValue("Award Currency");



// Initialize reset object

let reset = {

"Request in USD": 0,

"Award in USD": 0,

"Request in NIS": 0,

"Award in NIS": 0,

"Request in EUR": 0,

"Award in EUR": 0,

"Request in GBP": 0,

"Award in GBP": 0,

"Request in CAD": 0,

"Award in CAD": 0,

"Request in AUD": 0,

"Award in AUD": 0,

"For Running Total Requests USD": 0,

"For Running Total Requests NIS": 0,

"For Running Total Awards USD": 0,

"For Running Total Awards NIS": 0

};



// Reset all currency fields to 0

await table.updateRecordAsync(recordId, reset);



// Re-initialize update object for new values

let update = {};



// Handle Request Currency

if (request != null && requestCurrency) {

switch (requestCurrency[0].name) {

case "USD":

update["Request in USD"] = request;

break;

case "NIS":

update["Request in NIS"] = request;

break;

case "EUR":

update["Request in EUR"] = request;

break;

case "GBP":

update["Request in GBP"] = request;

break;

case "CAD":

update["Request in CAD"] = request;

break;

case "AUD":

update["Request in AUD"] = request;

break;

default:

break;

}

}



// Handle Award Currency

if (award != null && awardCurrency) {

switch (awardCurrency[0].name) {

case "USD":

update["Award in USD"] = award;

break;

case "NIS":

update["Award in NIS"] = award;

break;

case "EUR":

update["Award in EUR"] = award;

break;

case "GBP":

update["Award in GBP"] = award;

break;

case "CAD":

update["Award in CAD"] = award;

break;

case "AUD":

update["Award in AUD"] = award;

break;

default:

break;

}

}



// Perform currency conversions for Request and update running totals

if (request != null) {

if (requestCurrency[0].name !== "USD") {

update["For Running Total Requests USD"] = request * reqToUSD;

} else {

update["For Running Total Requests USD"] = request;

}

if (requestCurrency[0].name !== "NIS") {

update["For Running Total Requests NIS"] = request * reqToNIS;

} else {

update["For Running Total Requests NIS"] = request;

}

}



// Perform currency conversions for Award and update running totals

if (award != null) {

if (awardCurrency[0].name !== "USD") {

update["For Running Total Awards USD"] = award * awardToUSD;

} else {

update["For Running Total Awards USD"] = award;

}

if (awardCurrency[0].name !== "NIS") {

update["For Running Total Awards NIS"] = award * awardToNIS;

} else {

update["For Running Total Awards NIS"] = award;

}

}



// Update the record with the new values

await table.updateRecordAsync(recordId, update);

}

}

 


I don't know if anyone will ever look at this or find it useful, but the need to update the currency on a regular basis arose and so ChatGPT was once again helpful with this code. I used freecurrencyapi.com as it has a plentiful (5k/month) limit. The automation is set to run once a week and I track the date by having the date (key) field in the table default to "today." The script has all the console.log parts for debugging and I'm sure it's not perfect, but here it is:

// Script written by ChatGPT

// Script to fetch currency conversion rates from freecurrencyapi.com and update Airtable



// Define Airtable table and field names

let table = base.getTable("Conversion Rates");



// Define fields to be reset

let fieldsToReset = [

"USD->ILS", "USD->EUR", "USD->GBP", "USD->CAD", "USD->AUD",

"ILS->USD", "ILS->EUR", "ILS->GBP", "ILS->CAD", "ILS->AUD",

"EUR->USD", "EUR->ILS", "EUR->GBP", "EUR->CAD", "EUR->AUD",

"GBP->USD", "GBP->ILS", "GBP->EUR", "GBP->CAD", "GBP->AUD",

"CAD->USD", "CAD->ILS", "CAD->EUR", "CAD->GBP", "CAD->AUD",

"AUD->USD", "AUD->ILS", "AUD->EUR", "AUD->GBP", "AUD->CAD"

];



// Define API key and base URL

const apiKey = "YOUR-API-KEY-GOES-HERE";

const baseUrl = "https://api.freecurrencyapi.com/v1/latest?apikey=" + apiKey;



// Define the source URL

const sourceUrl = baseUrl;



// Define currencies and conversions to be fetched

const conversions = [

{ base: "USD", targets: ["ILS", "EUR", "GBP", "CAD", "AUD"] },

{ base: "ILS", targets: ["USD", "EUR", "GBP", "CAD", "AUD"] },

{ base: "EUR", targets: ["USD", "ILS", "GBP", "CAD", "AUD"] },

{ base: "GBP", targets: ["USD", "ILS", "EUR", "CAD", "AUD"] },

{ base: "CAD", targets: ["USD", "ILS", "EUR", "GBP", "AUD"] },

{ base: "AUD", targets: ["USD", "ILS", "EUR", "GBP", "CAD"] }

];



// Function to fetch conversion rates and update Airtable

async function fetchAndUpdateRates(recordId) {

console.log("Starting fetchAndUpdateRates");

let updateData = {};



for (let conversion of conversions) {

let url = `${baseUrl}&currencies=${conversion.targets.join(",")}`;

if (conversion.base !== "USD") {

url += `&base_currency=${conversion.base}`;

}



try {

console.log(`Fetching rates for base: ${conversion.base}`);

let response = await fetch(url, {

headers: {

"apikey": apiKey

}

});



if (!response.ok) {

throw new Error(`Failed to fetch rates for ${conversion.base}: ${response.statusText}`);

}



let data = await response.json();

console.log(`Received data for base: ${conversion.base}`, data);

for (let target of conversion.targets) {

let rate = data.data[target];

let fieldName = `${conversion.base}->${target}`;

updateData[fieldName] = rate;

}



} catch (error) {

console.log("Error fetching rates:", error);

}

}



// Add the source URL to the update data

updateData["Source"] = sourceUrl;



// Log update data for debugging

console.log("Update Data: ", updateData);



// Update Airtable record

console.log("Updating Airtable record");

await table.updateRecordAsync(recordId, updateData);

}



// Main function to create a new record and update it

async function main() {

// Reset fields to 0 before fetching new rates

let resetData = {};

for (let field of fieldsToReset) {

resetData[field] = 0;

}



// Add the source URL to the reset data

resetData["Source"] = sourceUrl;



// Log reset data for debugging

console.log("Reset Data: ", resetData);



// Create a new record and get its ID

let newRecordId = await table.createRecordAsync(resetData);



// Fetch and update rates

console.log("Fetching and updating rates");

await fetchAndUpdateRates(newRecordId);

}



// Run the main function

main();

 

Reply


Terms & Conditions