Discussions

Expand all | Collapse all

Extract part of address from address field

  • 1.  Extract part of address from address field

    Posted 11-15-2018 17:36
    Can I extract data from an address field so that I can format the address when printing it on a letter?


    Example


    Address field: 

    3300 North Brook Place, Boise, ID 83710 United States



    How I want to format address:

    <p>3300 North Brook Place</p><p>Boise, ID 83710 United States</p>





  • 2.  RE: Extract part of address from address field

    Posted 11-15-2018 17:43
    If you look at the address field properties, you will see that it is really made up of 6 fields, and each has its own field name and Field ID#, so "yes" is the answer to your question.

    Post back if you need help with the actual formula.


  • 3.  RE: Extract part of address from address field

    Posted 06-09-2020 17:55
    How do I extract the Zip Code subfield from the address field.  I want to use this value in another formula.  Basically, I want to make another field the value of the Zip Code subfield.

    ------------------------------
    Jason Beaver
    ------------------------------



  • 4.  RE: Extract part of address from address field

    Posted 06-09-2020 17:59
    Edited by Mark Shnier (YQC) 06-09-2020 18:00
    The field name for that subfield is listed on the field properties of the [Address field].  One of mine looks like this


    31 Address: Street 1
    32 Address: Street 2
    33 Address: City
    34 Address: State/Region
    35 Address: Postal Code
    36 Address: Country


    So my formula would just be
    [Address: Postal Code]


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Extract part of address from address field

    Posted 06-09-2020 18:00
    thank you

    ------------------------------
    Jason Beaver
    ------------------------------



  • 6.  RE: Extract part of address from address field

    Posted 08-27-2020 20:14
    Edited by Shuma Chaudhuri 08-27-2020 20:25
    Hello, thanks for this article.  I am a new user to Quickbase.  I would like to leverage what you have mentioned above about how the address is made up of individual subfields.

    I will be using the map report functionality to display mapping indicators for each of my client's data submission.  In the map view, I do not want the numerical portion of the address to be displayed when hovering over the map indicator.  How do I create a formula text field using the subfield?  Thank you.

    I found a formula for Excel but am having trouble applying it to quickbase.  I inserted the field I saw mentioned in part of the discussion about using Address: Street 1.

    LEFT([Address: Street 1],1),"",LEFT([Address: Street 1],FIND(" ",[Address: Street 1])-1)



    ------------------------------
    Shuma Chaudhuri
    ------------------------------



  • 7.  RE: Extract part of address from address field

    Posted 08-27-2020 21:17
    I can certainly help you with a formula, but I don't think there is a way with either the native Maps or the 3rd Party Add On from Juiced Technologies to not show the full address. 

    But let us know if you need help with an address based formula with an example of the format if the result. Maybe that would still be useful for you on a regular vanilla table report.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 8.  RE: Extract part of address from address field

    Posted 30 days ago
    Edited by Shuma Chaudhuri 30 days ago
    Thanks so much for such a quick response, Mark.  In my mind of working with my onboarding lead, we were thinking we can set up a formula-text field to store this "truncated" address of removing the house number, and then use this modified field to display on the map, where the actual address would still ensure the "marker" on the map is accurate.

    Will that work?  If not, then I will abandon the idea.  I need the map feature badly for my organization, but I am very worried about displaying the PII visibly for basically anyone on the internet who will be able to see the map.

    P.S. if it helps for me to explain my use case more, I am glad to do so.
    ------------------------------
    Shuma Chaudhuri
    ------------------------------



  • 9.  RE: Extract part of address from address field

    Posted 30 days ago
    I'm sorry but in my testing I was unable to suppress the exact address from showing on a Maps report type.  They allow you to show some fields in addition to the address , but I could not see a way to suppress the full address from showing.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 10.  RE: Extract part of address from address field

    Posted 29 days ago

    The way we were trying to approach this is to add a new field called Protected Address as a formula text field.  Then for the formula, we were trying to use the Part function.  For Kirk, the formula is working, where the house # is not included in this new field.  But I can't get the formula to work. 

    Example Address
    111 Main Street, Boston MA 12345

    Here is the formula that Kirk had:
    Part([Address: Street 1], 2, " ") & " " & Part([Address: Street 1], 3, " ") & " " & Part([Address: Street 1], 4, " ") & " " & Part([Address: Street 1], 5, " ")

    His result was:
    Main Street, Boston, MA 12345

    But for me, my result comes back:

    Main Street

    The hope was that once we got this formula working, then I could configure the map report to display this new "Protected 
    Address" field rather than the "Address" field.



    ------------------------------
    Shuma Chaudhuri
    ------------------------------



  • 11.  RE: Extract part of address from address field

    Posted 29 days ago
    Here is what I suggest.  The Maps report required an Address Field type.  Unfortunately, there is no such field type as a Formula Address field.

    You can use that formula to create street Address 1 without the numeric portion.  
    '
    Then make a new Address field called [Address for Mapping].

    Then using a form rule or an Automation or both, populate that [Address for Mapping] field and map that.  if you populate all fields including the postal code= there is a good chance that it will map correctly.

     Feel free to post back if you have have trouble with the form rule or Automaton.  There is also a way to get an Automation to run though all your existing addresses (assuming that you already have a lot of addresses records to populate the [Address for Mapping] field.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 12.  RE: Extract part of address from address field

    Posted 11-15-2018 18:36
    Thanks. 

    I found the address fields you described. But I have encountered a new problem. 

    The address I want to format (a customer contact address) is in the "Proposals" table.

    The address in the Proposals table is a lookup to the "Customer Contacts" table. 

    The address in the "Customer Contacts" table is a lookup to the "Site List" table. 


    What is the best way to do this? Should I format the address in the "Site List" table then carry the formatted text through to the "Proposals" table as a lookup? 


  • 13.  RE: Extract part of address from address field

    Posted 11-15-2018 18:38
    Yes, I suggest that you do this on the highest up source record, so that it is easily available as a lookup field anywhere else in the app, or else you will find yourself re-building that formula concatenation field over and over, on each child and grandchild table.


  • 14.  RE: Extract part of address from address field

    Posted 03-14-2020 11:01
    Hi

    Quick question, do you know if it's possible to make an address field have a default value? For Example Address field A will always have the address 1234 Street 1 City State Zip Code.

    ------------------------------
    Wallace (DHS) Yeboah
    ------------------------------



  • 15.  RE: Extract part of address from address field

    Posted 03-14-2020 12:19
    I suggest building a quick little automation.
    When a record is added and the address field is blank, say address line one is blank, then have the automation fill in all the address field components.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 16.  RE: Extract part of address from address field

    Posted 03-14-2020 13:47
    Perfect! Thanks alot Mark.

    ------------------------------
    Wallace (DHS) Yeboah
    ------------------------------