Hello, I think I need a help of Airtable community even though there is built in AI helper in the formula field. Here is what I am trying to do and have no success for some unknown for me reason. I'm creating an automation that with the click of the button will send an sms text message to my customer. I've recently experienced a problem with Twillio campaign approval (they are giving a hard time), but I've got an approval with TextMagic. So, Text Magic accepts sms as an email, then converts it to text and then sends it to my customer. I have a dedicated Airtable field for the phone number. Based on this, I've created another formula field that adds "@textmagic.com" after the phone number, the formula looks like this:
CONCATENATE(Phone,'@textmagic.com')
Everything looked good, so I started with my automation, but my automation failed at the end due the format in the formula field. My formula adds the ending, but it keeps original Airtable fortmat of the phone number with the brackets and spaces and automation is saying that in order for this to work, the phone number should look like a plain text. I asked AI assistant to generate the formula where it first transforms the phone number into plain text and then adds the textmagic.com, here is the formula that AI provided me:
CONCATENATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Phone}, "(", ""), ")", ""), " ", ""), "-", ""), "@textmagic.com")
But for some reason it does not work and instead of the text, I've got an message #ERROR! in that field.
Any ideas why the formula does not work or any suggestions for another formula? Thank you in advance