Show TOC Start of Content Area

Background documentation Validation Example: ISBN Numbers and Check Digits  Locate the document in its SAP Library structure

This section describes a comprehensive validation example for validating ISBN numbers, which is extensible to other validations that involve check digits.

By way of background, books are published with a 10-digit universal reference number known as an ISBN (International Standard Book Number). When creating new objects in a repository and entering long strings of digits manually, it is easy to transpose digits or make other data entry errors. Since the ISBN is used to order books, the number must be correct to avoid errors in the ordering process.

Recall that MDM validations allow you to perform one or more tests on data using Excel-like formulas to determine whether the data is valid or correct. While the tests can include mathematical, logical, and other functions, the results are always Boolean (that is, either TRUE or FALSE), signifying either a success or failure in passing the test.

Creating a validation for a business process involves breaking the process down into a series of questions or steps, each of which can be answered either TRUE or FALSE. Examples of simple tests include: (1) is a field value NULL; and (2) for a given SKU, is the value of the Price field greater than the value of the Cost field?

The validation algorithm for ISBNs is straightforward but slightly more complex, as it involves parsing the 10-digit number and performing some multiplication and division. The procedure is set forth in the ISBN Users’ Manual (http://www.isbn.org/standards/home/isbn/international/html/usm4.htm) as excerpted here:

The check digit is the last digit of an ISBN. It is calculated on a modulus 11 with weights 10-2, using X in lieu of 10 where ten would occur as a check digit.

This means that each of the first nine digits of the ISBN – excluding the check digit itself – is multiplied by a number ranging from 10 to 2 and that the resulting sum of the products, plus the check digit, must be divisible by 11 without a remainder. So for ISBN 0-8436-1072-7:

 

Grp

Publisher Prefix

Title Identifier

Chk

ISBN

0

8

4

3

6

1

0

7

2

7

Weight

10

9

8

7

6

5

4

3

2

 

Products

0

72

32

21

36

5

0

21

4

7

Total: 198

As 198 can be evenly divided by 11, 0-8436-1072-7 is a valid ISBN.

7 is the valid check digit.

Using MDM’s validation capability, we can create an automated business rule that tests ISBNs. In order to do this, we must break the process down into its discrete steps that can be answered TRUE or FALSE, as follows:

...

       1.      We need to test whether the ISBN field is NULL. MDM validations return SUCCESS when logical and mathematical operations are performed upon NULL values. Clearly, a missing ISBN has not passed the validation test.

       2.      We need to make sure the ISBN contains 10 digits.

       3.      The ISBN must be parsed, each digit multiplied by its factor, all of the results added, and the sum divided by 11 to see whether or not there is a remainder.

When all these conditions or tests have been met, and the answer to each is TRUE, then the ISBN will have been validated.

The figure below shows one way of writing the validation expression to verify the ISBN field of a book repository; the lines of the validation expression are analyzed in the table below.

This graphic is explained in the accompanying text

Note

Remember, when writing validations, syntax matters, spacing does not. Make sure the functions you use have the correct arguments and that parentheses are used properly, particularly in complex nested functions where it is easy to lose track of how many open and close parentheses you have. Since spaces do not matter in composing expressions, you can lay out more complex expressions in a way that makes it easier to view whether parentheses have been properly matched, as shown above.

Line

Description

1

This line asks the first two questions:

      IS_NOT_NULL(ISBN) verifies that the ISBN is not NULL.

      LEN(ISBN)=10 verifies that the number has exactly 10 digits.

Of course, you can use different functions that would return the same result, just not as efficiently. For example, (IF(IS_NULL(ISBN),FALSE,LEN(ISBN)>9 AND LEN(ISBN)<11) does the same thing using an IF/THEN/ELSE function; if ISBN is NULL return a FALSE else if ISBN string length is greater than 9 and less than 11 (i.e. exactly 10 characters long), return a TRUE.

2

Begins the nesting for the modulus expression.

3

String Left function (LEFT) parses the left-most digit and multiplies by the appropriate weight.

4-11

String Mid function (MID) parses the middle digits, multiplies by the respective weights, and adds to the previous products.

12

This is the test for the last or check digit and is in the form of an IF/THEN/ELSE function with the string Mid function embedded as arguments that read as follows: if the last digit is an ‘X’ convert it to the value of 10 as the multiplier, otherwise use the last digit’s value as the multiplier and add to the previous products.

13

Closes the nesting and calculates modulus 11 (whether or not there is a remainder after dividing by the prime number, 11).

This validation on the book repository successfully tests a variety of correct and incorrect ISBNs, as shown in the following figure.

This graphic is explained in the accompanying text

An alternate method would be to create separate validations and execute them as a group. This has two benefits: (1) the expressions are simpler and easier to diagnose if something does not work; and (2) the different causes of failure can be easily identified, which may be important if they are to be assigned to different workflows for resolution.

The original single expression is broken down into three separate expressions:

      Is the ISBN field NULL?

This graphic is explained in the accompanying text

      Does the ISBN field contain exactly 10 characters?

This graphic is explained in the accompanying text

      Is the number divisible by 11 with no remainder?

This graphic is explained in the accompanying text

The result of grouping and executing these three validations on the book repository is shown in the following figure.

This graphic is explained in the accompanying text

Note

When valid 10-character ISBNs were altered by transposing adjoining characters the Mod 11 test caught the errors (items 2-4 from the top in the Records pane).

Functions and operators performed upon NULL fields pass the test; this is the standard behavior around NULL values.

The Mod 11 test passes ISBNs that are less than 10 digits long. This is expected as there are NULLs in parsed locations for which MDM returns a value of TRUE.

Validations offer a very powerful and flexible capability to test main table field and lookup data. Customers can create automated checks for data integrity that are configured to meet their unique requirements. And since the scripting is easy (no programming required), changes or new validations can be written as circumstances change, without the burden of involving significant IT resources. Validations can also be combined with workflows to further automate quality assurance on data. This provides customers with significant time savings and further assures the integrity of their valuable data assets.

The same validation methodology, with appropriate modifications, can be used to verify UPC, EAN, and other check digit calculations.

End of Content Area