Matching Phone Area Codes to Region
So, for a lot of Data I receive, I don't have any location material besides one's phone area code. I know that the area code is not the most accurate in todays cell-phone age, however it gives me an idea. So, I already have a Phone field that is autoformatting my phone #'s to (xxx) xxx-xxxx format. I have also created a separate table for Area Codes with their matching region and time zone. Now I need a formula field that will check the (xxx) in my phone field, cross reference it with my area code table and input the corresponding region. Also, same thing for a formula field that will display time zone. Any help would be greatly appreciated. My main table is Leads. Phone field is Leads.Phone. 'Area Codes' is a separate table with 'Area Code' being the xxx field, 'Region' obviously being the region field, and 'Timezone" being the timezone field.
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 :=.
Bulk editing has worked to some degree. However, I'm finding that some area codes are auto-populating the fields. It's so weird. The two common ones are (785) and (620).
I thought you were bulk editing zip codes. What does area codes have to do with anything?
Looking around I see you have the similar triggers on Phone and Time Zone fields. Are you really manually filling in Time Zone?
The code for you Zip trigger can be simplified:
let a := Zip; City := first(select 'Zip Codes' where 'Zip Code' = a).City; let b := Zip; State := first(select 'Zip Codes' where 'Zip Code' = b).State
let a := Zip; let b := first(select 'Zip Codes' where 'Zip Code' = a) City := b.City; State := b.State
Since your select statements are exactly the same and only reference a different field, you can the lighten the load on Ninox by only doing only 1 select statement by putting it in a variable. Select statements are labor intensive to Ninox and should be used sparingly.
You can see that since I put the results from the select into a variable, I can now reference any field in that record just by using the variable.
It's been awhile since I got back to this, as it was low priority for fixing. However, this code is somehow first putting in the timezone correctly for only a microsecond until the script completes, and then replaces it with the State in the timezone field. Not sure what is causing that?
let a := substr(Phone, index(Phone, "(") + 1, index(Phone, ")") - 1); Region := first(select 'Area Codes' where 'Area Code' = a).Region; let b := substr(Phone, 1, 3); 'Time Zone' := first(select 'Area Codes' where 'Area Code' = b).Timezone; let c := Zip; let d := first(select 'Zip Codes' where 'Zip Code' = c); City := d.City; State := d.State
- 3 mths agoLast active