Sorting a report by address

[Ron Lacharite]

I need to sort a report by address. The problem is that if I just use the address (street) field, the numbers of the streets appear first (such as all streets that start with 100).

What I need to do is split the field at the first space, flip the field and sort by street name. Then a secondary sort by ADDRESS gives me the address sort.

I could do this with other report writers, but I can't figure out how to write the variable to do this with Alpha5 v.4

Hope someone knows the formula

[Tom Cone Jr]

RE: Sorting a report by address

Ron,

No magic here, just a bit of twiddling with some of Alpha Five's string functions.

From the interactive editor:

Address = "1234 Main St."

?isdigit(substr(Address,1,1))
= .T.

?at(" ",Address,1)
= 5.000000

?right(Address, len(Address) - at(" ",Address,1))
= "Main St."

?left(Address, at(" ",Address,1) - 1)
= "1234"


SortAddr = if(isdigit(substr(Address,1,1)), right(Address, len(Address) - at(" ",Address,1)) + left(Address, at(" ",Address,1) - 1), Address)
?SortAddr
= "Main St.1234"

Address = "P.O. Box 111"
?SortAddr
= "P.O. Box 111"

An alternative that might be more useful in the long run would be to split the field into its components storing the number in a separate field from the street name. I believe the Update Operation Genie can help you do this if you want.

Let me know if you have trouble deciphering the expression.

-- tom

[Ron Lacharite]

RE: Sorting a report by address

I'll try it right away.
For general information, I do have a second field where non street addresses are put (P.O., secondary headers etc). But it is not only much easier to keep the street address in one field, but I often have to input hundred of companies -- and the source always has it as one field.

I might note that I have always used a SORT field. This is a six character field where I put the sort for the company. That way I can deal with all the problems (names with 'the', too many that start with New England etc). It is not hard to input (I default to the COMPANY field), but has proven to be invaluable.

Thanks for your help.

[Ron Lacharite]

RE: Sorting a report by address

Tom,

Sorry. I'm more of a doofus at this than I thought.
Where do I enter the information for the new variable. The edit structure doesn't seem to allow for a definition.
Then do I simply indicate this as an index? And do I have to set the index before going into the report, or can the report be always indexed the same way?

[Tom Cone Jr]

RE: Sorting a report by address


I suggest you spend some time in ch. 10 of the User's Guide, especially in the section on Ordering and Selecting records.

In the meantime,

Open the report in design mode;
Click Report menu;
Choose Detail Properties;
Choose Records tab;
Enter the expression in the 'Order Expression' field. This will control the sequence of the report's details region.

You will need to modify my expression so that instead of referring to a memory variable called 'Address' it refers instead to the address field in your table.

I'm sorry. Thought this was obvious since your question indicated considerable experience with databases and string manipulation. I was trying to illustrate how to use Alpha Five's string functions to manipulate an address which contained both a street number and a street name, since that's what it sounded like you needed. I was not trying to write an expression that would work 'as is' in your report. If you still need help after studying the references I've mentioned let us know, and upload (attach) a working model of your database. We can show you how to fix it, assuming you also tell us which report you're working on...

-- tom

[Ron Lacharite]

RE: Sorting a report by address

I realize that my manual is for the original Alpha5 (cir. 1994). So I am using the electronic version to see if I get better results.

In the meantime I found the place to enter the expression. When I evaluate it tells me I am missing a right parenthesis. When I try putting one in the logical places, it tells me it is the wrong kind of data.

Here is the expression. The field names are fine.

Sortaddr
if(isdigit(substr(Address,1,1)), right(Address, len(Address) - at(" ",Address,1)Address

[Tom Cone Jr]

RE: Sorting a report by address

Ron,

Looks as though you didn't scroll over far enough to the right to see the end of my expression. Your's looks incomplete.

-- tom

[Tom Cone Jr]

RE: Sorting a report by address


Your Order Expression should begin with

if(


and end with


address)


the 'SortAddr =' must of course be omitted.

-- tom

[Ron Lacharite]

RE: Sorting a report by address

When I try to preview the report I am still getting:
"Argument is incorrect data type"

I have been reading chapter 10. It says I can keep the detail data together without a page break. Simply choose that option in the Detail Properties dialogue box. I can find no such option.

[Tom Cone Jr]

RE: Sorting a report by address

Ron, it should not be so complicated.

How about showing us the complete expression exactly as you're typing into the Order Expression box on Detail Properties of the report layout. ?

-- tom

[Tom Cone Jr]

RE: Sorting a report by address


Ron, another idea, would be for you to zip a copy of your database together, and attach it to a reply here. Tell us which report you're working on, and we'll see if we can suggest the fix.

-- tom

[Tom Cone Jr]

RE: Sorting a report by address


Ron,

Looks as though the user's guide is pointing you to the wrong dialog box.

Try:

Report
Region Properties
Detail
Region

You'll find the checkbox there to 'Keep region together'.

-- tom

[Ron Lacharite]

RE: Sorting a report by address

Select Report. Design.
Report Tab. Detail Properties.
Records. Order Expression.

if(isdigit(substr(address,1,1)), right(Address, len(Address) - at(" ",Address,1)Address)

If I do evaluation or try to run preview I get the notice that the argument is incorrect data type.

[Tom Cone Jr]

RE: Sorting a report by address

Ron,

your expression is not complete and contains typos.

see the attachment to this message, which contains the full expression I posted originally.

-- tom

[Ron Lacharite]

RE: Sorting a report by address

if(isdigit(substr(Address,1,1)), right(Address), len(Address) - at(" ", Address,1)) + left(Address, at(" ",Address,1) - 1), Address)

For some reason I can't get anything from Alpha forum to wrap. It looks like I have it all now.
But now I get the message that there is a bad character. Do you see anything I typed wrong?