3 Replies Latest reply on Mar 10, 2013 7:02 AM by JimMac

# Reorganize a Parsed Street Addres into a Sequentially Organized List

### Title

Reorganize a Parsed Street Addres into a Sequentially Organized List

### Post

My db includes an imported Address field comprised of 3 parts: St #, St Name and St Unit, i.e., 9 Washington Street #A7 (note there is no space between # and A7).

Regarding the parsing of 9 Washington Street #A7:

in a field called St # with Calculation result is Number, the calculation, LeftWords ( Address ; 1 ), extracts 9 as the St #;

in a field called St Name with Calculation result is Text, the calculation, If ( Position ( Address ; "#" ; 1 ;1 ) ; MiddleWords ( Address ; 2 ; WordCount ( Address ) - 2 ) ; RightWords ( Address ; WordCount ( Address ) - 1 )), extracts Washington Street as the St Name; and

in a field called St Unit with Calculation result is Text, the calculation, Let ( [ L = Length ( Address ) ; cpos = Position ( Address ; "#" ; L ; -1 ) ];  If ( cpos ; Trim ( Right ( Address ; L - cpos ) ) ) ) extracts A7 as the St Unit. (If there is no St Unit, the extraction leaves St Unit blank as it should be.)

These calculations parse perfectly and do everything the parse is designed to do.

The problem is in trying to reorganize the 3 parts back into a sequentially organized list where the addresses order as follows:

9 Washington Street

9 Washington Street 1

9 Washington Street 2

9 Washington Street 2B

9 Washington Street 12

9 Washington Street 12B

so on and so forth.

Instead the addresses order as follows:

9 Washington Street

9 Washington Street 1

9 Washington Street 12

9 Washington Street 12B

9 Washington Street 2

9 Washington Street 2B

Another example of what I wish to occur:

2 Adams Road 3B

2 Adams Road 6

2 Adams Road 7X

2 Adams Road 8

2 Adams Road 10

2 Adams Road 10X

2 Adams Road 27B

2 Adams Road A

2 Adams Road A3

2 Adams Road A7

2 Adams Road D

2 Adams Road D4

2 Adams Road DD

Instead the addresses order as follows:

2 Adams Road 10

2 Adams Road 10X

2 Adams Road 27B

2 Adams Road 3B

2 Adams Road 6

2 Adams Road 7X

2 Adams Road 8

2 Adams Road A

2 Adams Road A3

2 Adams Road A7

2 Adams Road D

2 Adams Road D4

2 Adams Road DD

To sequentially order the addresses the way I wanted, I created a Sort Script (named: Sort by St Name, St #, St Unit) in a list layout in a sort ascending order of St Name first, St # second, and St Unit third.

The St # sorts sequentially: 1, 2, 3, 4, 5, 6, 6.5, 7, 8, 9, 10, 11, etc. The St Name sorts alphabetically A to Z. The St Unit sorts out of order as noted in the italicized, underlined lists above.

What modification must be made to the third calculation field to permit St Unit to sequentially order itself just as the St # and St Name already do? (Again: St # is Number, St Name is Text, and St Unit is Text.)

• ###### 1. Re: Reorganize a Parsed Street Addres into a Sequentially Organized List

A Sort Unit number field that would pad zeros on the front of 1 digit units (or two zeros, depending on longest unit number)?

http://www.filemaker.com/12help/html/find_sort.5.25.html

Sorting records troubleshooting
Records don't sort in the correct order
 • If numbers don’t sort correctly (11 is before 2, for example), they are entered in a field formatted to contain text data. Change the field type to Number. For more information, see About choosing a field type.
 • You might have text in a field formatted to contain numbers. Number fields ignore text when sorting.

• ###### 2. Re: Reorganize a Parsed Street Addres into a Sequentially Organized List

Parse your St Unit field into a Number field [number part] and a new Text field [letter part].  Then the sort works like you want.

If you want to show as your format show...

The make two new fields the Number part and Letter part... Sort by the new fields but don't display them.  Display the original St Unit Field.

Jim...

• ###### 3. Re: Reorganize a Parsed Street Addres into a Sequentially Organized List

You seem very informed on parsing, but I forgot to mention to parse examples like D, D4 and DD by stipping the numeric part first.   There would be and empty field for each, since no number precedes the first Text letter "D"  thus D4 is the text.

JIm...