0

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.

32 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 10 mths ago
    • Reported - view

    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:

      • Dave_Irving
      • 10 mths ago
      • Reported - view

       I looked at your sample DB and I don't see anything different besides you not having a space after (xxx) .  However, I'm not getting any values in return.  Also, my phone field may also have a 00 international before the (xxx) in some instances.  So I only need it to match in between the (xxx) and return a valid area code.  Thanks for your help.  

    • Dave_Irving
    • 10 mths ago
    • Reported - view

    It doesn't appear to be a relationship problem either, because your data model has no relationships...hmmm.

    • Ninox partner
    • RoSoft_Steven.1
    • 10 mths ago
    • Reported - view

    You could change the formula like this:

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

    And:

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

    Which does look only for the numbers between the (xxx)

    • Dave_Irving
    • 10 mths ago
    • Reported - view

    RoSoft, so that didn't appear to be the problem.  It's not updating the new Region and Time Zone fields with phone numbers that are already in the DB.  It's only doing it on newly created phone numbers.  

    • Dave_Irving
    • 10 mths ago
    • Reported - view

    would I need something like this that checks the table "for x in select Table do".  If so, what could be the revised formula?

    • Ninox partner
    • RoSoft_Steven.1
    • 10 mths ago
    • Reported - view

    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
    
      • Dave_Irving
      • 10 mths ago
      • Reported - view

       My Region and Time Zone fields are formula fields.  However, my Phone field is a Phone field.  On my Area Code table, the three fields are all text fields just like yours.

    • Ninox partner
    • RoSoft_Steven.1
    • 10 mths ago
    • Reported - view

    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.

      • Dave_Irving
      • 10 mths ago
      • Reported - view

       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.

    • John_Halls
    • 10 mths ago
    • Reported - view

    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
      • 10 mths ago
      • Reported - view

      do you have a workaround solution?

      • Dave_Irving
      • 9 mths ago
      • Reported - view

      checking back in.

    • Fred
    • 9 mths ago
    • Reported - view

    can you post a sample of your DB?

    • Dave_Irving
    • 9 mths ago
    • Reported - view

    Sadly I can not, due to just being on the starter plan.  I'm on that until my company agrees to pay for a premium subscription.

      • Fred
      • 9 mths ago
      • Reported - view

      On a side note, if you are using MacOS or iOS, you can download the app and then you can download copies of your DB.

      I think that is a very weird limitation of the starter level that you can't download your DB through the browser.

    • Fred
    • 9 mths ago
    • Reported - view
     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
      • 9 mths ago
      • Reported - view

       Wow man, you have a keen eye.  I would never have caught that.  I had to use a formula to reformat my phone numbers that were already in ninox.  Currently I use a macro in Excel that corrects and auto-populates a lot of my data before I import it into Ninox, so I don't have to worry about it going forward.  Now, I have to figure out a formula to remove all the beginning spaces for me 😄.

      This was the code I used previously to re-format my phone numbers in Ninox:

      let sym := "+";
      let pad := "x";
      for i in select Table do
      let digits := replacex(i.Phone, "\D", "g", "");
      let ln := length(digits);
      let pre := ln - 10;
      let split := "(\d{" + pre + "})(\d{3})(\d{3})(\d)";
      if pre < 1 then sym := "" end;
      let format := sym + "$1 ($2) $3-$4";
      if ln < 10 then
      i.(Phone := rpad(i.Phone, 10, pad))
      else
      i.(Phone := replacex(digits, split, "", format))
      end
      end
      
      • Dave_Irving
      • 9 mths ago
      • Reported - view

       Hey, I need a workaround.  I am doing the same thing for Zip Codes, that I am doing for Area Codes.  However, it is bogging down the cloud server and becoming non-responsive.  I'm guessing because it is constantly filling those fields every time I go into another view.  Anyway, it can do the formula once, and permanently fill in another field to save on resources?

    • Fred
    • 9 mths ago
    • Reported - view
     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
      • 9 mths ago
      • Reported - view

       Alright, I am getting places now!  You are a rockstar.  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?

      let a := Zip;
      City := first(select 'Zip Codes' where 'Zip Code' = a).City;
      let b := Zip;
      State := first(select 'Zip Codes' where 'Zip Code' = a).State
      
    • Fred
    • 9 mths ago
    • Reported - view
     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 :=.

      • Dave_Irving
      • 9 mths ago
      • Reported - view

      I'll give that shot!

      • Dave_Irving
      • 9 mths ago
      • Reported - view

       Not going to like how that plays out, lol.  Thought is, if I'm doing everything after := , it does not know what "a" is defined as.  

      • Dave_Irving
      • 9 mths ago
      • Reported - view

       Think its going to work now... we shall see!  

Content aside

  • 7 mths agoLast active
  • 32Replies
  • 234Views
  • 4 Following