RSMUS.comThere is a KB article, How to use Report Writer to create a calculated field to mask the bank account number on the Employee Checks report and on the Direct Deposit Statement of Earnings report in Microsoft Dynamics GP, on this subject but the problem is not all bank account numbers have the same length of characters unlike social security numbers where you know there will be exactly nine characters. There are bank account numbers with only six characters while there are some that are as long as seventeen. In the KB article referenced above, it uses the substring function. This is good when you are masking the characters from the beginning or from the left, but what if you want to show the last four (4) characters of the bank account number? I discovered 2 solutions that will resolve this problem in Dynamics GP.
First Solution:
You will need two calculated fields. You will use a combination of the RW_Trim and RW_Right functions. The first calculated field will be to trim the bank account number in Dynamics GP. The function RW_Trim has three parameters. First is the string you want to trim, second is the direction (enter 1-from beginning/left or leading, 2-from end/right or trailing, 3-both), third is the character that you want trimmed, this will return the trimmed string. Now, in the second calculated field, you will use the first calculated field as a parameter in the function, RW_Right. The function RW_Right has two parameters. First is the string, second is an integer of how many characters you want to show from the right of the string being passed, this will return a string. I found out (after much testing and trial and error), that using the RW_Right function alone is not enough because when this function is used with the bank account number which is in an array, the report field contains trailing spaces as it contains the longest bank account number in the array, thus returning spaces. Your two calculated fields should look like these:
Calculated Field1 Name: bn1 (string result type)
FUNCTION_SCRIPT(RW_Trim ddDeposits.DDArray12 Account Number[1] 2 “ “)
Calculated Field 2 Name: Bank Account Number 1 (string result type)
“**********” # FUNCTION_SCRIPT(RW_Right bn1 4)
Second Solution:
You will also need three calculated fields. The first calculated field is using the RW_Trim function for the bank account number like the first solution above. The second calculated field, you will use the RW_Length function to get the actual length of the bank account number. Remember, this contains trailing spaces, so you want to trim the report field first then get the actual length. This second calculated field will be of an integer result type. You will then pass this result to your third calculated field. Whether your bank account number has 6 or 7 or 17 characters long, you want to display only the last 4 characters, therefore if you do the math, your first character that you want to show will always be the length of the string minus 3. As an illustration, if the bank account number has 6 (characters) minus 3 = 3 – the first character you want to show will start at the third character. If 7 (characters) – 3 = 4 (show starting at character no. 4). If 17 (characters) – 3 = 14 (show starting at character no. 14). This is the number/integer that you want your first calculated field to return that you will pass as a second parameter for your RW_SUBSTRING function that you will use in your second calculated field. Your two calculated fields should then look like these:
Calculated Field1 Name: bn1 (string result type)
FUNCTION_SCRIPT(RW_Trim ddDeposits.DDArray12 Account Number[1] 2 “ “)
Calculated Field 2 Name: bn1len (integer result type)
“**********” # FUNCTION_SCRIPT(RW_Length bn1) – 3
Calculated Field 3 Name: Bank Account Number 1 (string result type)
“**********” # FUNCTION_SCRIPT(RW_Substring bn1len 4)
If you are looking to learn more about Microsoft Dynamics GP, contact our professionals at erp@rsmus.com. You can also receive other valuable tips through our Dynamics Community Newsletter.
By: Layla Doctor – Illinois Microsoft Dynamics GP partner
