calculation too large?

[KLA]

I've got a field (City) that should have a Value List of 200 cities (so far only have half that imputted - don't know if the value list will hold 200). Anyway, i have another field (ADI) that is a calculation to display a number that is dependent on the City field. (If City = New York, then ADI = 1, etc.)

Case(

City = "New York", "1",

City = "Los Angeles","2",

City="Chicago","3",

City = "Philadelphia", "4",

City = "San Francisco", "5",

"") (etc,)

This works, however, i don't know if the calculation box will accept a calculation that large (I have to type in a statement for each city). Also, the value is going to change frequently for the ADI so, it would be nice if people could easily modify this list (like if it could be done in value lists instead of a calculation) so that anyone in the office can change the numbers and not just me. Any suggestions? Thanks:)

[andygaunt]

Hi,

You could create a seperate file that holds all your cities and their adi's.

Each city is a new record in this.

Then from your main file create a value list to use the values from the field city in the second database [all values].

then create a relationship from your main file to the cities file with city::city.

now on your adi in the main file set to a calculation field. Select your cities relationship and choose the adi field.

Now you can change the number for a city and the adi will change in the main database. Also you can add any number of cities to the new database.

1 calculation I know, but there you go, at least it is small

HTH

[Fitch]

Man, you have to be fast around here! Andy is quick on the draw. I was going to say exactly that, except why not define ADI as a number field rather than a calc? Set it to lookup ADI from the Cities file based on a City::City relationship.

The only reason to do it as a calc rather than a lookup is if you anticipate changing the ADI later and want it to be updated in your old records. Otherwise a simple lookup should be ok.

[RussBaker]

Yeah - go the separate file and relationship way... Apart from being generally better, it will avoid you having to give users a value list editing privilage which might get them in trouble elsewhere in the main file.

BUT. No matter how you get it, a value list or portal which displays 200 lines is going to be interesting for users who are "scrolling challenged" or for those lucky enough to have a blindingly fast CPU which will scroll past the city they want too fast to stop on it. I also suspect that the 200 is likely to grow?

You might want to consider some dynamic value lists as part of the relationship with the cities/ADI file. One way might be to record the State and then have a menu for choosing the state, and then the City menu would only show cities in that state. Or have a series of A-Z tags with a relationship using the letter and the first letter of the city name to show only cities starting with that letter.

Russ Baker