Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Function for Finnish bank reference number Post Reply Post New Topic
Author Message
Constricto
Newbie
Newbie
Avatar

Joined: 25 Mar 2019
Location: Norway
Online Status: Offline
Posts: 1
Quote Constricto Replybullet Topic: Function for Finnish bank reference number
    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"
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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
IP IP Logged
jessewithag
Newbie
Newbie


Joined: 25 Jun 2018
Location: United States
Online Status: Offline
Posts: 6
Quote jessewithag Replybullet 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.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.033 seconds.