newbie. i have a large library. over the years, i manually entered all isbns in to a field called, "isbn". i need to convert them to the original "EAN" bar code numbers. example: the isbn for my japanese dictionary is 438510347X. the bar code that is in EAN 9784385103471 under a bunch of lines. i want to make a field that is called "EAN" and have a script that would automatically convert the numbers in the isbn field to EAN number.

can anyone help?

i also have a publisher date formatted as follows: MM/DD/YY (01/01/2004)

i want to make a field to convert it to: YYYYMMDD (20040101)

can i do this and how?

for the conversion from isbn to ean, i did a google search and found an explanation of the conversion:

ISBNtoEAN

View printer-friendly version.

To Convert an ISBN to an EAN:

ISBN: XXXXXXXXX

EAN/JAN: 978 XXXXXXXXX C

Where:

978 = flag for number for books (international rule)

X = actual ISBN number (10 digits)

C = check digit

Append 978 to the front of the ISBN to form a new 10 digit number: YYYYYYYYYY

Calculate the check digit

Find the sum S of the digits when multiplied by the following numbers:

1313131313

YYYYYYYYYY

Take the next higher multiple of 10 and subtract the sum S. For example if S is

37, the next highest multiple of ten is 40. Then check digit would be 40 - 37 =>

3.

Script to convert ISBN to EAN would be something like this:

Set Field [EAN, "978" & Left (ISBN, 9) ]

Set Field [Check, 0 ]

Set Field [Pos, 2]

Loop

Set Field [Check, Check + Mid(Ean, Pos, 1) ]

Set Field [Pos, Pos + 2]

Exit Loop If Pos > 12

End Loop

Set field [Check, Check * 3]

Set Field [Pos, 1]

Loop

Set Field [Check, Check + Mid(Ean, Pos, 1) ]

Set Field [Pos, Pos + 2]

Exit Loop If Pos > 11

End Loop

Set Field [Check, 10 - Mod(Check, 10)]

If Check = 10

Check = 0

End If

Set Field [EAN, EAN & Check]

The calculation to convert your date is

Year(Date) *1000 + Month(Date) * 100 + Day (Date)

i made a test db called "isbn_to_ean_test" to test the script. i created a field called "ISBN" and also a field "EAN". in 'Define fields for "isbn_to_ean_test"' , i went to entry options for the EAN field. i checked "calcualted values" and a box appeared. i cut and pasted the above script into the box.

when i hit "okay", filemaker tells me there is no EAN field. what am i doing wrong?

You need the following fields ISBN & EAN both are text fields. You also need Check & Pos both are global number fields. You write the script with the script editor. You run the script for each record. A script is not a calculation. A calculation may be written for this but it would be very complex.

both ISBN and EAN fields are now defined as "text" fields. what do you mean by "You also need Check & Pos both are global number fields"?

which field do i attach the script to? isbn or ean?

Create two global number fields, one called Check and one called Pos. You can attach the script to any button you desire. I would think a Convert button would be most appropriate.

I would convert your date using Year(Date) & Right( "0" & Month(Date), 2 ) & Right( "0" & Day(Date), 2 ), to maintain the leading zeroes.

I am sending a sample file that shows how this is done. I did the file in FP5 formate. If you are using 7 you will have to convert it.

I got the isbn script working. thanks to everyone.

now i have a date conversion problem. i have several hundred records w/ date entered as "MM/DD/YYYY". the field name is "pub_date_full". i want to convert that dates to a different format: YYYYMMDD, w/out the "/" (slashes).

i tried using:

Year(Date) & Right( "0" & Month(Date), 2 ) & Right( "0" & Day(Date)

but i must be doing something wrong. getting various error codes. did try to make an apple script and attached it to a button, but nothing happened.

anyone know where i messed up?

The result of the calculation must be text, not date. YYYYMMDD is not a recognized date format in FileMaker.

If you use the calculation I gave in my first response you will have a number field that does just what you want.

i'm really a loser at this. i tried the intructions above w/ little/no success.

when it converts, the conversion is just 1 zero.

since the old db has a lot of useless data, i have created another database. i plan to import the publish date from the old field.

i will import that to a numbers field called FMdate. (filemaker date)

i created a second field called AZdate.

i would like so that when i import the filemaker dates that it will convert it to the date i need. example:

filemaker date is: 3/12/1998 (or 03/12/1998, different people over the years have helped in entering the date, so it is not perfect.)

after i import this to the FMdate field, can i have it so it automatically converts the FMdata (data) to AZdate which has to be YYYYMMDD?

Use Year(Datefield) & Right( "0" & Month(Datefield), 2 ) & Right( "0" & Day(Datefield), 2 ), as described above, and set the calculation result to text, not date. Your profile doesn't state what version you're on. If it's 7, then use semicolons instead of commas.