Using RegEx for transformation of Phone to the right format

[Vadim_Ciobanu]

Hello there!

Who can help me with a script that will transform the phone field into the right international format? Example: I have a base with client phones. Most of them are in the national format 069xxxxxx, 068xxxxxx, or without zero 69xxxxxx, 68xxxxxx. I need to transform them all to the +37369xxxxxx, +37368xxxxxx.

How to do this?

[Bill_French]

Welcome back to the community @Vadim_Ciobanu!

Vadim_Ciobanu:

a script that will transform the phone field into the right international format?

You could grab a copy of Field Tweaker which was intended to be extended for things like this. Then modify it to perform the transformation.

The area of the modification would be in this area of the code:

 // to title case
 case "toTitleCase":
    var targetStr = toTitleCase(sourceStr);
    break;

You would simply add another section like this using a regEx for validating international phone numbers (like this😞

 // normalize phone number
 case "fixphone":
    var targetStr = <regEx of sourceStr here>;
    break;

Then run the Field Tweaker script and type “fixphone” as the transformation type.

[Stephen_Suen]

Here’s one way you can do this (partially based on our email validator example):

// Get the data we need (you can rename these or use table/field ids).
let clientsTable = base.getTable('Clients');
let clientsQuery = await clientsTable.selectRecordsAsync();
let phoneField = clientsTable.getField('Phone');
let countryCode = '+373';

// This regex has two capture groups (enclosed in parentheses):
// (1) Either +373 or 0 (optional)
// (2) 8 digits
let regex = new RegExp(`(\${countryCode}|0)?([0-9]{8})`);

// Create an array of record updates.
let updates = [];
for (let record of clientsQuery.records) {
    let phone = record.getCellValue(phoneField);
    // If there is a cell value in the phone field, check it.
    if (phone) {
        let groups = phone.match(regex);
        // If the cell value matches the regex, transform it if needed.
        // groups is either null (no match) or an array containing:
        // (1) the matching substring
        // (2) capture group 1 — either +373, 0, or undefined
        // (3) capture group 2 — the remaining 8 digits
        if (groups && groups[1] !== countryCode) {
            updates.push({
                id: record.id,
                fields: {
                    // If capture group 1 isn't +373, make a new phone
                    // number from the country code and the contents of
                    // capture group 2.
                    [phoneField.id]: `${countryCode}${groups[2]}`,
                }
            });
        }
    }
}

// Only up to 50 updates are allowed at one time, so do it in batches.
while (updates.length > 0) {
    await table.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}
output.text('Phone numbers updated!');

[Vadim_Ciobanu]

I got this error:

TypeError: clientsTable.selectAsync is not a function
at main on line 3

[Bill_French]

Vadim_Ciobanu:

TypeError: clientsTable.selectAsync is not a function
at main on line 3

Two things -

1. You need to share more about the code when seeking help about code. Without the actual code and line numbers, we don’t really know what this error message stems from.

2. I suspect the example provided by @Stephen_Suen cannot be run in your environment without first modifying it to use your table and field names. It’s likely this error is caused because there is no table named “Clients” in the base.

[Stephen_Suen]

My bad, I had a typo in there — that function should be clientsTable.selectRecordsAsync, apologies!