I'm working in Filemaker Pro Advanced 8. There are 25,000 records in my database. I'm looking to create a script that sorts through all records and gives me only the records with 4 letters (or less) in a specific field and for the life of me, the script eludes me. The answer is simple...but I do not see it. :-( Any help would be appreciated.

Hi,

Create a unstored calculation that is

Length ( Your field )

That will give you the number of characters in that field.

You can then search this field for ≤4

Script would look something like

Show all records

Enter Find Mode

Set field[Your Table::Your new calc field;"≤4"]

Perform Find

Michael

Edited September 14, 200718 yr by Guest
Added Script

For the purpose of searching, a STORED calculation field might be more appropriate?

Also, Show All Records before a find really doesn't do anything (except cost you your existing found set in case the search fails). In this case, the script could be a one-liner of Perform Find with stored criteria.

I better elaborate a bit.....I'm looking to correct zipcodes. Some records imported BUT left off the first zero, sometimes two leading zero's. I'm looking to create a script that will find them and add the proper amount of zero's. I can have the field be either text or number, whatever is necessary.

00423 = ignore

0423 = add a zero to the beginning

423 = add two zero's to the beginning

20330 = ignore

0330 = add a zero to the beginning

and so on....

more clear?

Thank you....

Are they all based on 5-digit zip codes, or might there be some 9-digit zip codes in there? Either way, it should be pretty simple to fix them all in one fell swoop.

Maybe it is the example you used, but the

20330 = ignore

0330 = add a zero to the beginning

and so on....

Would appear that your 0330 might be either

00330 or 20330

Lee

I can have the field be either text or number, whatever is necessary.

A number field is not really friendly to leading zeros. In any case, you could just find all records and do a Replace Field Contents with a calculation of:

Right ( "00000" & Zipcode ; 5 )

This will add leading zeros where required while leaving 5-digit codes as they are. A backup is strongly advised before you try this.

What is a "backup"?....ok, kidding.

I'm trying your procedure now....

A backup is that thing you don't want to be kidding about...

You know, it just occurred to me that you could find the problematic records quite easily by searching for ##### with the Omit box checked.

Kidding is my preference, as not only do I understand "backups", "I" do not fear them.

This forum is great.....thank you all....the solution worked!