'Formula too long' Error

[jonbarr]

I'm using CR 8.0 on ODBC base, and want to include a simple formula to return a name for a Postal (Zip) Code. Here in New Zealand we have about 2100 place names using codes from 0001 to 9999. I have downloaded the codes into Excel, pasted into Word and converted table to text, resulting in a Word document of 39 pages (2,100 lines)showing one code on each line. Pasting this into Crystal, I received an error message saying the formula was too long. Halving the selection gave the same result - error message. Checking previous threads I found a reference (in 2001) to a maximum formula size of 54Kb, but my formula would appear to be nowhere near this limit. Does anyone know what the limit is, or how to overcome this problem?
Sample below. Any suggestions welcome.
if {SOP30200.ZIPCODE} = 0101 Then "Whangarei"
else if {SOP30200.ZIPCODE} = 0121 Then "Glenbervie"
else if {SOP30200.ZIPCODE} = 0121 Then "Hukerenui"
else if {SOP30200.ZIPCODE} = 0121 Then "Kauri"
else if {SOP30200.ZIPCODE} = 0121 Then "Kokopu"
else if {SOP30200.ZIPCODE} = 0121 Then "Mangapai"
else if {SOP30200.ZIPCODE} = 0121 Then "Oakleigh"

 

[AlohaMichael]

You seem to be going about it the wrong way.
I would think if you can create another table in your database and link the fields.
IF you can't create another table you could try a
select case statement

Select {SOP30200.ZIPCODE}

case 0121 : "Glenbervie"
case 0122 : "Hukerenui"

default : "Beverly Hills";

Although I think you will still get an error using the case statement.
You did realize that you were using the same ZipCode for each IF statement. Additionally I know VB limits the number of IF-Else commands & I would think Crystal would do the same.

 

[jonbarr]

Thanks AlohaMichael.
I cannot create another table in the database (Great Plains) and yes, I now realise the codes in my example were the same. I grabbed the first seven entries without noticing that six of them happened to be the same code, and this might cause confusion. (They're little towns in the same remote district) Sorry.

 

[Madawc]

I'd advise you go about it the other way. Start with a formula with a dozen or so commands. Assuming that's OK, find out how many you can use without the system complaining.

As a general rule, any system is likely to fail when you push it to its limits. 2100 place-names can easily be split between 21 formulae of 100 place-names each. Then one more to choose between the 21 on the basis of a range of code values.

Madawc Williams (East Anglia)

 

[GJParker]

Use an Excel Spreadsheet as a data source containing the Zip Code and Place Name, link this to the table in your main database.

Crystal is quite happy to use multiple data sources.

HTH

Gary Parker
MIS Data Analyst
Manchester, England