ISBN-10 to ISBN-13

The International Standard Book Number (ISBN) is used as a unique identifier for books. It is not only used to distinguish different titles, but different version of the same book (for more information see the ISBN User's Guide). Starting January 1st, 2007, the number was increased from 10 to 13 digits.

The ISBN-13 can be calculated from the ISBN-10. First, the numbers 978 are put in front of the ISBN-10. The last number of both ISBN-10 & ISBN-13 is a check digit (used to validate the previous numbers) . So the check digit is dropped from the ISBN-10 and a new one is calculated for the ISBN-13.

ISBN-10: 0-060541-71-7
ISBN-13: 978-0-060541-71-2

There are several sites on the web that will do this conversion:

http://www.isbn-international.org/converter/converter.html
http://custompcsoftware.com/isbn13.html

In TIMSS there is one field for ISBN and it will hold 20 characters, so the new size is not an issue. The issue is if you want to maintain both numbers in your database.

I've chosen to just convert all my ISBN number to 13 since we don't deal with them that much and you can find the ISBN-10 based on the ISBN13. If you want to maintain both you can add an extender field, or you could create a view that calls a function that converts one to the other. It's really up to you.

I have created an SQL function to convert 10 to 13 and it's shown below:

CREATE Function dbo.ISBN10TO13(@ISBN10 as varchar(20))
Returns varchar(20) as

Begin
declare @ISBNO varchar(20)
Declare @NewISBN varchar(12)
Declare @ISBN13 varchar(20)
Declare @ISBN13F varchar(20)
Declare @i integer
Declare @n integer
Declare @v integer

--remove any hyphens or spaces
Set @ISBNO=replace(replace(@ISBN10,'-',''),' ','')

If len(@ISBNO)=10 --correct length
Begin
Set @NewISBN = '978' + SubString(@ISBNO, 1, 9)
Set @n = 0
Set @i = 1

While @i<=12 Begin Set @v = SubString(@NewISBN, @i, 1) If @i%2 = 0 Begin Set @n = @n + 3 * @v End Else Begin Set @n = @n + @v End Set @i = @i + 1 End Set @n = @n%10 If @n<>0
Begin
Set @n = 10 - @n
End

Set @ISBN13= @NewISBN+str(@n,1)
-- add hyphens
Set @ISBN13F= substring(@ISBN13,1,3)+'-'+substring(@ISBN13,4,1)+'-' +substring(@ISBN13,5,6)+'-'+substring(@ISBN13,11,2)+'-'+substring(@ISBN13,13,1)
End

Else -- wrong length, return same value
Begin
Set @ISBN13F=@ISBN10
End

Return @ISBN13F
End

Applies to: TIMSS5, TIMSS6, Personify

TIMSS 6: Ad-Hoc Query Security

Another good idea I saw at TAUG was adding security to your Ad-Hoc Query categories. I don't necessarily want to keep people from running a particular query, but I want to limit their choices so they can easily find what they are looking for.

Similarly to the Call Topic Security you can setup security on the Ad-Hoc Query Report Categories. In Types and Codes for QUERY_CATEGORY, un-check the Public Code flag and save. This will activate the Special Security button where you can pick the groups that have access to this Query Category.

Thanks to Pam R. who mentioned this in her TAUG presentation.

Applies to TIMSS6

Crystal Reports: Sorting Parameter

I have a couple Crystal reports that I've added a parameter to that allows the report to be sorted different ways. For example, our meeting function sheets can either be sorted by room or by date. To do this in the same report, I created a parameter called SortBy that is either DATE or ROOM (see Online Report Parameter Pick List to see how to pick parameters for your online report).

Then I added a formula field to the report that creates a value to sort on based on the parameter value:

If {?SortBy}='DATE' then ToText({Command.start_date})
else
If {?SortBy}='ROOM' then ToText({Command.facility_room_id})


and I use this value to sort my report.

I've done something similar with my committee roster. One group wants their committee sorted one way, and the rest a different way. So I created a parameter and a formula that generates the value to sort by:

if {?Format}='NORMAL' then
if ({COM_COMMITTEE_MEMBER.VOTING_STATUS_CODE}= 'NON_VOTING' and {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'STAFF') then 99 else
if {COM_COMMITTEE_MEMBER.VOTING_STATUS_CODE}= 'NON_VOTING' then 11 else
if {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'CHAIR' then 1 else
if {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'VCHAIR' then 2 else
if {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'SEC' then 3 else
if {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'TREAS' then 4 else
if {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'EXOFF' then 5 else
if {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'MEMBER' then 10
else 9
else if {?Format}='CFLT' then
if {COM_COMMITTEE_MEMBER.POSITION_CODE}= 'CHAIR' then 1 else 9

Thanks to Edward B. for the hint at TAUG.

Applies to: TIMSS6 & Personify

Spamming with Outlook

If you organization is like mine, you love to spam your members.

All kidding aside, one of the features with Outlook 2003 was that pesky dialog box that pops up every time you send a message using a mail merge or a Cognos Script. There is a way you can turn this off!

This Microsoft white paper has a section on Customizing Outlook Security Settings. You'll need to make a change to the exchange server, and update the registry on the local machine. It's well worth it when you are emailing a couple thousand renewal notices and don't have to click OK every 5 seconds to send each message.

Applies to TIMSS5, TIMSS6, Personify