Nov 10, 2020 11:54 AM
Hey Community!
Here’s my problem: I have users adding phone numbers to records. Problem is, the way the are inputting records doesn’t allow me to have Data validation. I need the number they input in a specific format, and I was hoping there might be a formula that could do this.
Format we need: 1 (XXX) XXX-XXXX
Inputs we might get (because users make mistakes) =
What is important for us is there is the area code, aka, a “1” at the front of every number. I was thinking about creating a formula that adds a “1” at the start, but in the circumstances where a user does what we want, and adds a “1”, I don’t want to add it as it’d break it.
Does anyone know how to put a formula together that’ll do this?
PS,
Bonus points if you can ensure it looks “pretty” by formatting it in this way: 1 (XXX) XXX-XXXX
Solved! Go to Solution.
Nov 10, 2020 01:21 PM
Right, there isn’t unfortunately and if you try to build a formula-only approach, it gets pretty ugly and convoluted. I think I needed three fields to achieve it when I tried it a few years ago. Perhaps there’s a formula geek amongst us that can whip one up.
COMMENTARY: Airtable should make it possible to perform data validation by supporting an option trigger to call into a script block and return the proper format or an error popup…
To get around this, I help clients perform a lot of data cleansing using the new script automation and script block features. Like this …
Script:
/*
***********************************************************
ScriptBloqs - Phone Number Validation Logic
Copyright (c) 2020 by Global Technologies Corporation
ALL RIGHTS RESERVED
***********************************************************
*/
output.markdown('# Formatting Phone Numbers');
// get the name of the table
let table = base.getTable("Airdrop");
// get the records from this table
let records = await table.selectRecordsAsync();
// Update the records
for (let record of records.records) {
if (record.getCellValue("Phone Number"))
{
//
// apply the format
//
let thisValue = record.getCellValue("Phone Number");
// output.inspect(thisValue);
// remove any + signs if they exist
thisValue = thisValue.replace("+", "");
// if the first character is a one, do nothing, otherwise append one (this is north america after all)
thisValue = (thisValue.toString()[0] === "1") ? thisValue : "1 " + thisValue;
// regex the number
thisValue = formatPhoneNumber(thisValue);
// per the client's request, eliminate the + sign that the regex added
thisValue = thisValue.replace("+1 ", "1 ");
await table.updateRecordAsync(record, {
"Phone Number Formatted" : thisValue
});
}
}
//
// format phone number
//
function formatPhoneNumber(phoneNumberString) {
var cleaned = ('' + phoneNumberString).replace(/\D/g, '')
var match = cleaned.match(/^(1|)?(\d{3})(\d{3})(\d{4})$/)
if (match) {
var intlCode = (match[1] ? '+1 ' : '')
return [intlCode, '(', match[2], ') ', match[3], '-', match[4]].join('')
}
return null
}
Nov 10, 2020 01:21 PM
Right, there isn’t unfortunately and if you try to build a formula-only approach, it gets pretty ugly and convoluted. I think I needed three fields to achieve it when I tried it a few years ago. Perhaps there’s a formula geek amongst us that can whip one up.
COMMENTARY: Airtable should make it possible to perform data validation by supporting an option trigger to call into a script block and return the proper format or an error popup…
To get around this, I help clients perform a lot of data cleansing using the new script automation and script block features. Like this …
Script:
/*
***********************************************************
ScriptBloqs - Phone Number Validation Logic
Copyright (c) 2020 by Global Technologies Corporation
ALL RIGHTS RESERVED
***********************************************************
*/
output.markdown('# Formatting Phone Numbers');
// get the name of the table
let table = base.getTable("Airdrop");
// get the records from this table
let records = await table.selectRecordsAsync();
// Update the records
for (let record of records.records) {
if (record.getCellValue("Phone Number"))
{
//
// apply the format
//
let thisValue = record.getCellValue("Phone Number");
// output.inspect(thisValue);
// remove any + signs if they exist
thisValue = thisValue.replace("+", "");
// if the first character is a one, do nothing, otherwise append one (this is north america after all)
thisValue = (thisValue.toString()[0] === "1") ? thisValue : "1 " + thisValue;
// regex the number
thisValue = formatPhoneNumber(thisValue);
// per the client's request, eliminate the + sign that the regex added
thisValue = thisValue.replace("+1 ", "1 ");
await table.updateRecordAsync(record, {
"Phone Number Formatted" : thisValue
});
}
}
//
// format phone number
//
function formatPhoneNumber(phoneNumberString) {
var cleaned = ('' + phoneNumberString).replace(/\D/g, '')
var match = cleaned.match(/^(1|)?(\d{3})(\d{3})(\d{4})$/)
if (match) {
var intlCode = (match[1] ? '+1 ' : '')
return [intlCode, '(', match[2], ') ', match[3], '-', match[4]].join('')
}
return null
}
Nov 13, 2020 11:19 AM
Thank you so much for putting this together. This will be incredibly helpful for ensuring our phone number data is usable.
Thank you so much for putting this together!
Oct 07, 2024 08:49 AM
You can set up a regex pattern rule in the DataGuard extension to prevent users from entering an incorrect telephone format and keep your data clean.