Automatically converting between multiple currencies

[mbeifield]

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?

[TheTimeSavingCo]

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

   

[mbeifield · Accepted solution]

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

[mbeifield]

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();