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
    • Fred
    • 1 yr 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
      • 1 yr ago
      • Reported - view

       Man, this seems more troublesome then it needs to be.  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).  Checking the area codes table...and all these area codes are there that are getting skipped over for some reason.  May need your keen eye again!

    • Fred
    • 1 yr ago
    • Reported - view
     said:
    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:

    from:

    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

    to:

    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.

      • Dave_Irving
      • 1 yr ago
      • Reported - view

      I was bulk editing for both area codes and zip codes, to get everything populated that was already in the DB.  Let me get off my phone and back on my PC to see if there was a difference.  

      • Dave_Irving
      • 1 yr ago
      • Reported - view

       it just appeared to be a query overload for the server.  I filtered out my regions to null and then did a bulk edit with those and it worked flawlessly.  So weird!  All my past data is finally correct and everything entered from here on out should work flawlessly.  Thank you for all your help!

    • Dave_Irving
    • 1 yr ago
    • Reported - view

     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
      • Fred
      • 1 yr ago
      • Reported - view

      Can you add me to your workspace again?

      • Dave_Irving
      • 1 yr ago
      • Reported - view

      sure.  Message me your email again.

Login to reply

Content aside

  • 1 yr agoLast active
  • 32Replies
  • 307Views
  • 4 Following