Sorting table according custom values

[hiho]

Hi,

I have a problem with sorting a table.

I have to sort an adress list according to the norwegian Postal code. that is a code that goes from 0000 to 9999.

The problem is that the norwegian post wants it to be sorted, but not logically!

The codes go from 0000-1499, then to 1700-1799 and then back to 1500-1599 etc.

I know about the custom value list, but that takes "only" 3.000 entries. I obviously need 10.000 entries.

Is there a way i can solve this?

Thanks in advance,

Peter from Norway.

[comment]

The "etc." part is not clear: is the range 1700-1799 the only exception, or is there a repeating pattern?

[Fitch]

You could add an additional "group" field that would auto-enter or calculate like so:

Case( 

      postalCode<1500; 1; 

      postalCode<1600; 3; 

      postalCode<1700; 4; 

      postalCode<1800; 2; 

      etc... ) 

Then sort by group and postal code.

Edited February 12, 2009 by Guest
I'm assuming there are other exceptions.

[hiho]

First of all, thanks for replying so quickly!

Yes, there are more exceptions. I don't know them by heart (I am at home now, and the list lies at work).

Does that make a difference? I mean... how many exceptions there are?

Peter

[hiho]

Hello, and thanks for reacting!

That sounds like a good idea.

Can you get me started on how to make such a list? I mean... how do i make such a case list?

And does it also work with not only smaller then 1500 (as in your example), but for example between 1400 and 1499?

Peter

[comment]

I would suggest you create a table of PostalCodeGroups with these fields:

StartingCode (Text)

SortOrder (Number)

Then just enter a record for each group e.g.

"0000", 1 ;

"1700", 2 ;

"1500", 3 ;

"1800", 4 ;

etc.

Define a relationship to this table as:

YourTable::Postal = PostalCodeGroups::StartingCode

In your table, define a SortOrder field as Number, lookup from PostalCodeGroups::SortOrder, lookup next lower.

[hiho]

Thanks for the tip!

Kind of a job to do, but that should work.

Didn't think of solving it like that :

Thanks again!

Peter