newbie. isbn converison w/ filemaker 4 as DB.

[Mitsunari]

i have a filemaker data base FM4. i have a very large library that i have manually typed in the isbn of the book into a field called “ISBN”. i need to convert the isbn to the “ean” number (bar code). example: the isbn of book A is: 438510347X the “ean” number for this is: 9784385103471

i can create a field called “EAN” in the inventory file of the db near the isbn field, but i am a bit stumped as to how to create the script. can anyone help?

i did a google search and found this calculation for the isbn to ean.

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.

my friend did a perl conversion script, but we had no success. since i am using filemaker on an apple OS9 enviornment, i was wondering if applescript wouldn’t be a better choice if imbedded in filemaker to convert current list of many isbn already manually entered. the perl script is as follows:

sub isbn_to_ean
{
my $isbn = shift;
my $ean = ‘978’ . substr($isbn, 0, 9);

my $sum = 0;
foreach my $index ( 0, 2, 4, 6, 8, 10 ) {
$sum += substr($ean, $index, 1);
$sum += 3 * substr($ean, $index + 1, 1);
}
$ean .= ( 10 * ( int( $sum / 10 ) + 1 ) - $sum ) % 10;

return $ean;

[jonn8]

This is an old script I had for ISBN to EAN conversion:

–Open this script in a new Script Editor window.

set EAN to my [color=green]ISBN_to_EAN/color
–>“9784385103471”

on [color=green]ISBN_to_EAN/color
set {EAN, EAN_odds, EAN_evens, EAN_check_digit} to {“978”, 0, 0, 0}
repeat with i from 1 to ((count of ISBN) - 1)
if (character i of ISBN) is in {“0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”} then set EAN to EAN & (character i of ISBN)
end repeat

 [b][color=blue]repeat[/color][/b] [b][color=blue]with[/color][/b] [color=green]i[/color] [b][color=blue]from[/color][/b] 1 [b][color=blue]to[/color][/b] ([color=blue]count[/color] [b][color=blue]of[/color][/b] [color=green]EAN[/color]) [b][color=blue]by[/color][/b] 2
      [b][color=blue]set[/color][/b] [color=green]EAN_odds[/color] [b][color=blue]to[/color][/b] [color=green]EAN_odds[/color] + ([color=blue]character[/color] [color=green]i[/color] [b][color=blue]of[/color][/b] [color=green]EAN[/color] [b][color=blue]as[/color][/b] [color=blue]integer[/color])
      [b][color=blue]set[/color][/b] [color=green]EAN_evens[/color] [b][color=blue]to[/color][/b] [color=green]EAN_evens[/color] + ([color=blue]character[/color] ([color=green]i[/color] + 1) [b][color=blue]of[/color][/b] [color=green]EAN[/color] [b][color=blue]as[/color][/b] [color=blue]integer[/color])
 [b][color=blue]end[/color][/b] [b][color=blue]repeat[/color][/b]
 [b][color=blue]set[/color][/b] [color=green]EAN_evens[/color] [b][color=blue]to[/color][/b] ([color=green]EAN_evens[/color] * 3)
 
 [b][color=blue]repeat[/color][/b] 10 [b][color=blue]times[/color][/b]
      [b][color=blue]if[/color][/b] ((([color=green]EAN_evens[/color] + [color=green]EAN_odds[/color] + [color=green]EAN_check_digit[/color]) [b][color=blue]mod[/color][/b] 10) = 0) [b][color=blue]then[/color][/b] [b][color=blue]exit[/color][/b] [b][color=blue]repeat[/color][/b]
      [b][color=blue]set[/color][/b] [color=green]EAN_check_digit[/color] [b][color=blue]to[/color][/b] [color=green]EAN_check_digit[/color] + 1
 [b][color=blue]end[/color][/b] [b][color=blue]repeat[/color][/b]
 
 [b][color=blue]return[/color][/b] [color=green]EAN[/color] & [color=green]EAN_check_digit[/color]

end ISBN_to_EAN

You could write another routine that loops trough your database, grabs the ISBN for each record, calculates the EAN, and populates the EAN field:

–Open this script in a new Script Editor window.

tell application “FileMaker Pro”
tell database 1
repeat with i from 1 to (count records)
tell record i
set ISBN to cellValue of cell “ISBN”
set cellValue of cell “EAN” to my [color=green]ISBN_to_EAN/color
end tell
end repeat
end tell
end tell

Jon

---

[This script was automatically tagged for color coded syntax by Convert Script to Markup Code]

[jonn8]

The ISBN to EAN routine could be condensed down to:

–Open this script in a new Script Editor window.

set EAN to my [color=green]ISBN_to_EAN/color
–>“9784385103471”

on [color=green]ISBN_to_EAN/color
set {EAN, EAN_total} to {{9, 7, 8}, 0}
repeat with i from 1 to ((count ISBN) - 1)
if (ISBN’s character i) is in {“0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”} then set end of EAN to (ISBN’s character i as integer)
end repeat
repeat with i from 1 to (count EAN) by 2
set EAN_total to EAN_total + (EAN’s item i) + ((EAN’s item (i + 1)) * 3)
end repeat
if (EAN_total mod 10) = 0 then return “” & EAN & 0
return “” & EAN & (10 - (EAN_total mod 10))
end ISBN_to_EAN

Jon

---

[This script was automatically tagged for color coded syntax by Convert Script to Markup Code]

[Mitsunari]

how do i embed the script to filemaker.

what file should i attach it to, the EAN field?

also, this part at the begining the script set EAN to my ISBN_to_EAN(“438510347X”)
–>“9784385103471”
do i remove it to also convert the other isbn’s in my database.

i have created a test database called “isbn_to_ean_test”. i have created 2 fields. 1st is call ISBN.

the second is called EAN.

once i test it, i will them move it to the real DB called MitsuLibrayDB, in the “inventory” modual, layout “Main View”.

tick is how to imbed the script into filemaker and i’m doing something wong.

[Fenton]

how do i embed the script to filemaker.

Paste into a Perform AppleScript script step.
You can/should remove the lines: “tell FileMaker Pro” and its “end tell” (carefully). These are not needed inside FileMaker (but are in Script Editor).

what file should i attach it to, the EAN field?

It’s a script, in the file. It’s not “attached” to a field. The fields are specified in the AppleScript.

The fields named “EAN” and “ISBN” should be on the layout when the script is run (easiest); you can switch there in the FileMaker script.

set EAN to my ISBN_to_EAN(“438510347X”)
–>“9784385103471”
do i remove it to also convert the other isbn’s in my database.

Yes, remove it, or --comment it out. It’s just an example.