Discussions

 View Only
  • 1.  I need to sort an address table alphabetically.

    Posted 06-30-2018 05:11
    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.


  • 2.  RE: I need to sort an address table alphabetically.

    Posted 06-30-2018 12:17
    What fields within the address are you trying to sort on?


  • 3.  RE: I need to sort an address table alphabetically.

    Posted 06-30-2018 13:44
    [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].


  • 4.  RE: I need to sort an address table alphabetically.

    Posted 06-30-2018 17:14
    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]


  • 5.  RE: I need to sort an address table alphabetically.

    Posted 06-30-2018 17:42

    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.



  • 6.  RE: I need to sort an address table alphabetically.

    Posted 06-30-2018 18:04
    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], " ")


  • 7.  RE: I need to sort an address table alphabetically.

    Posted 06-30-2018 19:58
    I knew it was something simple I was missing, the "Address:".  Thanks it works perfectly!