Howdy

I am converting some ISBN book numbers to the new 13 digit format. I will have a 12 digit string, and I will apply a simple calculation to each INDIVIDUAL digit, then add them up in a calc field.

I think this will be some use of the Left () or Right () functions, but I can't get it right.

Basically, I want to grab the 1st digit from the left, then multiply it times 3, and set that into a separate field. Then I move on the 2nd digit from the left, x 1, then the 3rd x 3, and so on.

Or perhaps I can put it all into one long function, which would excellent.

Help appreciated in nailing down the proper syntax here.

Try:

Let ( [ 

isbn12 =  "978" & Left ( Filter ( ISBN10 ; "0123456789" ) ; 9 ) ;

checksum = 

Middle ( isbn12 ; 1 ; 1 ) + 

Middle ( isbn12 ; 3 ; 1 ) +

Middle ( isbn12 ; 5 ; 1 ) +

Middle ( isbn12 ; 7 ; 1 ) +

Middle ( isbn12 ; 9 ; 1 ) +

Middle ( isbn12 ; 11 ; 1 ) +

3 * (

Middle ( isbn12 ; 2 ; 1 ) + 

Middle ( isbn12 ; 4 ; 1 ) +

Middle ( isbn12 ; 6 ; 1 ) +

Middle ( isbn12 ; 8 ; 1 ) +

Middle ( isbn12 ; 10 ; 1 ) +

Middle ( isbn12 ; 12 ; 1 ) 

) 

] ; 

"978-" & Replace ( ISBN10 ; Length ( ISBN10 ) ; 1 ; Mod ( -checksum ; 10 ) )

) 

I'd suggest browsing the web, and especially Brian Dunning's Custom Functions web site, and see if somebody else has already solved this problem. I bet they have.

Otherwise you can roll your own. Since you have FMP 8.5 you can use the Let() function. Read up on Let() it will be especially useful.

You use declared variables to hold bits of temporary information for later use in the calculation. So in this case you might pull the 12 bits out of the original ISDN number, then multiply them and reassemble the new ISDN number.

Let(

[

bit01 = Middle( ISDN ; 1 ; 1 ) ;

bit02 = Middle( ISDN ; 2 ; 1 ) ;

bit03 = Middle( ISDN ; 3 ; 1 ) ;

...

bit12 = Middle( ISDN ; 12 ; 1 )

] ;

bit01 * 3 &

bit02 * 1 &

bit03 * 3 &

...

)

Note that the above function is written for clarity rather than function optimisation. I'd also put code in there to clean up the old ISDN number and make sure it is valid with no leading or trailing white space, no illegal characters etc.

This seems to work flawlessly. I am spot-checking a whole batch of the results and they are all perfect so far.

This is a huge help and I've learned so great new tricks

Thanks much

One more question:

I have some items where ISBN is already correctly ISBN-13. I would like to add an IF clause so that if the string length in ISBN is already 13 characters (starting with 978), then simply plug in that ISBN to the new ISBN calc field

I've tried using the Lengeth function, but am again struggling with the proper syntax

Many thanks

Andy