Using Power Fx for BSN or bank account validation

In the world of data management and validation, the Burger Service Number (BSN) is a critical piece of information for businesses and organizations operating in the Netherlands.

Dutch social security numbers and non-IBAN bank account number are validated using the ‘elfproef’ or eleven-test. This is a calculation using the individual digits that make up the BSN or bank account number.

The Importance of BSN Validation

Before we dive into the technical aspects, let’s understand why BSN validation is crucial:

1. Data Accuracy: Valid BSN numbers ensure that your data is accurate and reliable, reducing errors and minimizing the risk of processing incorrect information.
2. Compliance: In the Netherlands, handling personal data, including BSN numbers, is governed by strict regulations. Ensuring the validity of BSNs helps your organization comply with data protection laws.
3. User Experience: Validating BSNs during data entry can enhance the user experience by preventing users from inputting incorrect information, reducing frustration, and minimizing data correction efforts. This validation can be done locally without having to rely on an external service.

Keep in mind that we are only validating the number and format, not if the number is actually in valid use.

Let’s take a look at this calculation

Each digit in the bank account number is assigned a weight, starting from 9 for the first digit and decreasing by one for each subsequent digit.

• The number is divided into its individual digits.
• Each digit is multiplied by its corresponding weight.
• The results of these multiplications are summed up.
• The sum is then divided by 11.
• If the remainder of this division is 0, the number is considered valid.

 For example: (all bank account numbers and BSN are fictitious)

Bank account number 73.61.60.221:

9×7 + 8×3 + 7×6 + 6×1 + 5×6 + 4×0 + 3×2 + 2×2 + 1×1 = 176

176/11 =  16.0  valid

• If the bank account number is fixed to a length of ten digits by adding zeros to the front, the eleven test also works in reverse, from left to right.

Bank account number 073.61.60.221:

1×0 + 2×7 + 3×3 + 4×6 + 5×1 + 6×6 + 7×0 + 8×2 + 9×2 + 10×1 = 132

132/11 = 12.0 = valid

• In case of BSN numbers the last digit is multiplied by -1 instead of 1:

BSN: 442641023

9x4 + 8x4 + 7x2 + 6x6 + 5x4 + 4x1 + 3x0 + 2x2 + -1x3 = 143

143/11  13.0 = valid

Using Power FX for BSN and bank account Validation

 In this example a Text Input is used to capture the users input and the result of Valid or Invalid is written to a variable that can be used in other controls like a label.

This formula performs a series of checks to determine the validity of the entered BSN or bank account number. It checks if the length is 9 or 10 digits and whether the calculated checksum is zero. If both conditions are met, it returns “Valid”; otherwise, it returns “Invalid.”

Conclusion

Power Fx provides an easy way of incorporating validations through calculations like the BSN validation we explored in this post.