Function for Finnish bank reference number
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22751
Printed Date: 27 Apr 2024 at 3:38am
Topic: Function for Finnish bank reference number
Posted By: Constricto
Subject: Function for Finnish bank reference number
Date Posted: 27 Mar 2019 at 9:43pm
HI, are there anyone that know how to make a function that calculates the finnish/estonian bank reference number by the 7-3-1 method? here from the NETS web site "Example: The reference number are formed from the payment specifier, for example 1234567, by calculating a check digit, i.e. the last digit of the reference number, by using multipliers 7-3-1. The specifier’s digits are multiplied from right to left, and the products are added up. The sum is then subtracted from the next highest ten, and the remainder is the check digit added to the specifier.
Specifier: 1 2 3 4 5 6 7
Multiplier: 7 1 3 7 1 3 7
Product: 7 2 9 28 5 18 49 = 118
Check digit: 120 - 118 = 2
=> The reference number is 12345672"
|
Replies:
Posted By: lockwelle
Date Posted: 02 May 2019 at 10:36am
I would follow your example. The only 'hard' part is the check digit. I would take 10 - product mod 10 to get the check digit.
HTH
|
Posted By: jessewithag
Date Posted: 20 May 2019 at 8:49am
I had done something in crystal, here are my steps for that calculation for the 1234567 example, but work for characters up to 10 digits long using three formulas, "ToText", "Count", and "Calc 1":
1 (ToText). Make a formula to count the amount of characters in the field, convert into a string if needed.
"ToText" formula: ToText(SpecifierNumber)| This is the formula titled "ToText".
"Count" formula: len({@ToText}) | A field of 1234567 as characters returns "7" as the number of characters in that selection.
2. Use that "Count" formula to create a new conditional formula,"Calc 1", that calculates the sum based on the number of characters in the string, and multiples them according to their positions in the string by using a Mid() formula to go to a certian character in the string, grab it, then multiply it correctly. This combined with a Sting-to-number conversion produced the calculated answer.
ex for the number 1234567: (tonumber(Mid({@ToText},{Count},1)) * 7)
This produces the number "49" (7*7)
Below is the completed formula.
You would need to replace each "ToText" and "Count" formula fields with the name of your own, if named differently.
if {@Count}=10 then (
(tonumber(Mid({@ToText},{@Count},1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-1,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-2,1)) * 1) +
(tonumber(Mid({@ToText},{@Count}-3,1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-4,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-5,1)) * 1) +
(tonumber(Mid({@ToText},{@Count}-6,1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-7,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-8,1)) * 1) +
(tonumber(Mid({@ToText},{@Count}-9,1)) * 7) )
else if {@Count}=9 then (
(tonumber(Mid({@ToText},{@Count},1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-1,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-2,1)) * 1) +
(tonumber(Mid({@ToText},{@Count}-3,1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-4,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-5,1)) * 1) +
(tonumber(Mid({@ToText},{@Count}-6,1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-7,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-8,1)) * 1) )
else if {@Count}=8 then (
(tonumber(Mid({@ToText},{@Count},1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-1,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-2,1)) * 1) +
(tonumber(Mid({@ToText},{@Count}-3,1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-4,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-5,1)) * 1) +
(tonumber(Mid({@ToText},{@Count}-6,1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-7,1)) * 3) )
else if {@Count}=7 then (
(tonumber(Mid({@ToText},{@Count},1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-1,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-2,1)) * 1) +
(tonumber(Mid({@ToText},{@Count}-3,1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-4,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-5,1)) * 1) +
(tonumber(Mid({@ToText},{@Count}-6,1)) * 7) )
else if {@Count}=6 then (
(tonumber(Mid({@ToText},{@Count},1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-1,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-2,1)) * 1) +
(tonumber(Mid({@ToText},{@Count}-3,1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-4,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-5,1)) * 1) )
else if {@Count}=5 then (
(tonumber(Mid({@ToText},{@Count},1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-1,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-2,1)) * 1) +
(tonumber(Mid({@ToText},{@Count}-3,1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-4,1)) * 3) )
else if {@Count}=4 then (
(tonumber(Mid({@ToText},{@Count},1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-1,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-2,1)) * 1) +
(tonumber(Mid({@ToText},{@Count}-3,1)) * 7) )
else if {@Count}=3 then (
(tonumber(Mid({@ToText},{@Count},1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-1,1)) * 3) +
(tonumber(Mid({@ToText},{@Count}-2,1)) * 1) )
else if {@Count}=2 then (
(tonumber(Mid({@ToText},{@Count},1)) * 7) +
(tonumber(Mid({@ToText},{@Count}-1,1)) * 3) )
else if {@Count}=1 then (
(tonumber(Mid({@ToText},{@Count},1)) * 7) )
Again, the above formula should work for all fields that are numeric and up to 10 characters long. The field 1234567 produces the number 118 as the product.
|
|