I need to sort an address table alphabetically.

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered
I need to sort an address table alphabetically. Trying to remove the numerals in the front of the address and put the result in a formula text field, but I can’t seem to make it work. I’ve tried NotLeft with “ “ for a space but it doesn’t work. Can only presume it doesn’t work because it’s the Address Field? I’ve got to believe I’m missing an easy solution, any help would be appreciated.
Photo of bgstatdex

bgstatdex

  • 270 Points 250 badge 2x thumb

Posted 5 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
What fields within the address are you trying to sort on?
(Edited)
Photo of bgstatdex

bgstatdex

  • 270 Points 250 badge 2x thumb
[Street 1]. I’m using the field type “Address”. Although I created another text formula field “Street” thinking that field type was my problem, but that didn’t work either. The formula field was simply [Street 1].
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
You can try this as a start.  If the address field was named [Address], then this will strip off the first part of the text up to the first space.  I don't know if you have a few hundred addresses or thousands, but you may need to have a Street name override field to handle cases where this formula does not work.
 
Note that if you look at the field properties for the Address field it will give you the name of the Subfields.  for example [Address: City]
Photo of bgstatdex

bgstatdex

  • 270 Points 250 badge 2x thumb

Ok I have probably not given enough information.  I am using the Field Type “Address” which is a lookup field that then automatically, when an address is found, fills in the subfields “Street 1”, “Street 2”, etc.  The “Address” field is in a table called Properties.  My users will be working mainly in a table called Commissions which is a child table to Properties.  When a user wants to add a commission, it has a dropdown for Properties to look and see if that property is already in the database.  If the property is not then they choose the “Add a Property” choice.  The Properties report I am using to filter the dropdown is open properties and will be mainly 100 – 200 properties.  My problem is the dropdown needs to be alphabetized by Street Name and not by the first Street Numbers, because scrolling through 100 – 200 items is not very workable.  So I tried to add a formula text field to take the characters off up to the first space, but I can’t seem to do it.  The thought is then I can add that formula text field to the report and sort by that field.  So even though the user sees the numbers before the street name, it is still only by the street name.  Tried NotLeft([Address],” “) and NotLeft([Street 1},” “) and cannot get it to work.  Hopefully this is not too much information.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,546 Points 50k badge 2x thumb
Sorry about that. 

I did test the following formula but it did not seem to get posted with my response as i intended.

NotLeft([Address: Street 1], " ")
Photo of bgstatdex

bgstatdex

  • 270 Points 250 badge 2x thumb
I knew it was something simple I was missing, the "Address:".  Thanks it works perfectly!