Matching Phone Area Codes to Region

Create 2 formula fields in your Leads table with folowing code:

Region:

let a := substr(Phone, 1, 3);
first(select 'Area Codes' where 'Area Code' = a).Region

Timezone:

let a := substr(Phone, 1, 3);
first(select 'Area Codes' where 'Area Code' = a).Timezone

Sample database:

Since I use formula fields, they are automatically filled. If you want to do this with static fields, you can use this in a Button or in the console to update all the static fields (here I use AreaStatic and TimezoneStatic for the two static fields):

for i in select Leads do
    let a := substr(i.Phone, index(i.Phone, "(") + 1, index(i.Phone, ")") - 1);
    i.(AreaStatic := first(select 'Area Codes' where 'Area Code' = a).Region);
    i.(TimezoneStatic := first(select 'Area Codes' where 'Area Code' = a).Timezone)
end

That's weird. Can you post a sample database? Or give some screenshots of your table? You may also invite me to your team (as admin) to have a look at it if you want.

Using a Select statement in a formula field can be problematic. It needs a reason to trigger and so won't be updated in your existing records.

Dave Irving said:
yeah, like I said.  The region and timezone fields only gets updated if it's a brand new record and I'm adding the phone number for the first time.  I am on their shared cloud.

 Looking at your DB, in the SPE Leads table under the all table view I see that most of the records with Letter of Intent status do not have a region.

When looking at all records I see that many do have Region and Time Zone filled out.

So something is wonky. Since there is only two lines:

let a := substr(Phone, index(Phone, "(") + 1, index(Phone, ")") - 1);
first(select 'Area Codes' where 'Area Code' = a).Region

It is easy to troubleshoot.

I took the first line and put it in a new formula field, that is at the end of the form view in a field called Formula

let a := substr(Phone, index(Phone, "(") + 1, index(Phone, ")") - 1);
a

You will see that the ones that do not have Region filled out the is because "a" returns bad information. It is returning the last ")" of the area code. Thus it can't find anything in your Area Code table that has a ")" in it.

With a bit of more investigation I find that there is blank space at the beginning of certain phone numbers. Once you remove the space the code can now run properly. The code did not take into account the possibility of a space at the beginning. If that is a common occurrence then it will have to be updated to account for it.

Dave Irving said:
Anyway, it can do the formula once, and permanently fill in another field to save on resources?

Ninox can do that. If we take your previous example of Region. The code looks like:

let a := substr(Phone, index(Phone, "(") + 1, index(Phone, ")") - 1);
first(select 'Area Codes' where 'Area Code' = a).Region

Let us create a new text field called Region. Since the above code is no longer in a formula field we need to find a place to put it so it gets triggered. Maybe one place is after the phone number is entered then the code is triggered to input data into the new Region field. Another way would be to use a button. Since we are testing let us create a button as that gives us control of the execution.

Put the following into a button:

let a := substr(Phone, index(Phone, "(") + 1, index(Phone, ")") - 1);
Region := first(select 'Area Codes' where 'Area Code' = a).Region

If that works as expected, then you can copy the code into the Trigger after update section of the Phone field.

Now every time a phone number is entered then the trigger will run and update the Region field.

Dave Irving said:
Now, I have never used the console before, so how do I convert this over to be used in the console for a one time deal to get the fields updated?

 I guess you can use the console, but I don't so I can't give any hints there.

You can use the Bulk Edit function. It is under the gear icon in your table view.

You find your field, then select Assign Calculated Value then you can put the part after the :=.