How to auto-enter text based on multiple criteria

[filemistaker]

Hello there,

I'm faily new to Filemaker scripting and could use a bit of instruction.

I'm working with a database of several thousand records which I need to code in 2 ways based on the zip codes contained in the field ZIP.

If the ZIP field contains any of the zip codes in a particular city the CITY field should be filled with the correct city name.

The next step is similar, if CITY contains any city from a particular region, the REGION field should be filled with the appropriate number.

I figure if I can figure out how to do step 1, then step 2 could be accomplished in the same way--zip codes traded for city names, etc. and respective field names changed.

This seems pretty basic, but I'm lost. I've tried a few different methods, the latest one using a Replace script with the calculation:

Replace["CITY" "if(ZIP= "12345" or "56478" or "98076" or "12378","Cityville","")"]

Which is apparently 100% the wrong way to go about this, as it fills every CITY field with "Cityville" regardless of zip code.

Could anyone more knowledgeable than me offer some advice?

Am I even close to the right track on how to do this?

[BobWeaver]

The syntax of the If() function is incorrect. If you want to link things together with an OR operation, you have to repeat the entire comparison operation like so:

if(ZIP= "12345" or ZIP= "56478" or ZIP= "98076" or ZIP= "12378","Cityville","")

However, trying to do what you want by hardcoding all the zip codes into a function is the wrong way to go about it. What will happen when you or someone else needs to add a new zip code?

A better option is to set up a new table (call it "CityZip") with two fields: City and Zip. Populate this table with all the different City and Zip combinations. Then, create a relationship between the zip field in your main table to the zip field in the CityZip table. Now, your auto enter calculation simply becomes:

CityZip::City

To auto enter the region, you can add a "Region" field to the CityZip table, and fill in the region that is associated with the City. Then, create another relationship from the City field in the main table to the City field in the CityZip table. This relationship name will default to "CityZip2." Then your auto enter calculation will be:

CityZip2::Region

[filemistaker]

Thanks very much for responding, but for some reason this is still not working. I've followed the syntax you suggested exactly, but no go. Now, rather than filling ALL the city fields with the city name, the script fills none.

I thought maybe the final ,[color:red]"") was to blame, but even switching to CASE and omitting that part had no effect.

As for the field relationship idea, would this work with imported records?

The records I need to assign cities to are imported into FileMaker as a large batch (several thousand). The list of Cities will only come from a limited geographic area and will never change.

[BobWeaver]

I would check the data to make sure you don't accidentally have any leading or trailing spaces, carriage returns, etc. in your zip code data. If the data is not identical, then it won't be considered equal, although many Filemaker text functions will ignore whether it's upper or lower case.

The related table should work fine with imported data in either table. You can import your initial set of Region, City, Zip reference data into the related table, and then you can import your main data into your main data table. It should all work together.