isbn to ean conversion and date conversion.

[hafnerd]

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.

[RalphL]

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)

[hafnerd]

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?

[RalphL]

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.

[hafnerd]

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?

[-Queue-]

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.

[RalphL]

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.

[hafnerd]

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?

[-Queue-]

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

[RalphL]

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

[hafnerd]

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?

[-Queue-]

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.