There may be another post to this, I have searched and haven't found another topic which applies. Here is the nitty gritty, I bought a list of names from polk in a CSV file, I know filemaker better than excel so I opted to make the list into a database. This database contains over 2000 records. This data includes VIN, or vehicle identification number used for cars, trucks, and vans. Using simple scripts I was able to filter the list into the make of the car, but now I want to sort model. The VIN field consists of a 17 digit number in one field. The 5th character of this 17 digit number defines the model, but seeing as I only have version 5 of filemaker, I am stuck as to how I "count" 5 character from the first character, then apply a case formula to define model(ie caravan, corvette, miata, etc). What is the best way to do this, and I don't care if I have to use a script or a calculation, but I have far too many records to do it manually.

Thanks in advance

This topic came up twice before (if I recall), but they are hard to locate because the Search for the Forum will not locate the word VIN because of length (less than 4 characters. Use pluses and full words like this [color:red]+Vehicle +Identification +number and you will have better luck.

See if these help:

Topic One

Topic Two

Do to the period of time envolved since the posts, the attachments may not download, let me know, and I can upload them again.

Lee

Edited November 3, 200718 yr by Guest

Ok I want to modify this VIN year calculation, the 5th character is used to denote the model of vehicle. I can do it a clunky way, which would be to modify that calculation, to single out the 5th character, and place it in a field.

Lets use this example: 1B3LC56K78N632083

The 5th character is a "C"

If I Modify the previous calculation as below-

Position("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Middle,VIN,5,1),1,1)

I get the calculation of 3, since the 5th character is a "C", and it is also the 3rd character in the string. How do I make the calculation return "C", instead of 3?

I will call the above calculation, VIN_filter_5, and make it an invisible container field.

I then I will make a case calculation, for dechipering the contents of the "VIN_filter_5" field. I know this is clunky, but it is a simple marketting database, and really doesn't matter.

As you can tell I am hardly the math whiz here, and need all the help I can get. I want to thank "LEE" for his help so far, and will thank everyone in advance.

:thankyou:

For the time being, I am using a case calculation to convert the number in to a letter, you know the old...

case(field=1,"A",field=2,"B",etc...,"")

I know this is clunky, but it works for now, if anyone else has a better idea send it my way.

Thanks,

Doug

How do I make the calculation return "C", instead of 3?

Middle( VIN , 5 , 1 )