Apex Code Development You need to sign in to do that
Don't have an account?
jeroen formula to big, how to extract a digit???
Hi everyone,
Having difficulties with this formula. In a custom formula field. I need to check if the (dutch)bank account is valid. Basically I have to multiply each number in the account and than divide it by 11. If it is a round number it's good otherwise not! Bank_account__c is a text field, because it is also possible to have other chars in the number (different system).
IF (MOD( ( VALUE(LEFT(TRIM(Bank_Account__c) , 1))* 9)
+ ( VALUE(RIGHT(LEFT(TRIM(Bank_Account__c) , 2),1)) * 8)
+ ( VALUE(RIGHT(LEFT(TRIM(Bank_Account__c) , 3),1)) * 7)
+ ( VALUE(RIGHT(LEFT(TRIM(Bank_Account__c) , 4),1)) * 6)
+ ( VALUE(RIGHT(LEFT(TRIM(Bank_Account__c) , 5),1)) * 5)
+ ( VALUE(RIGHT(LEFT(TRIM(Bank_Account__c) , 6),1)) * 4)
+ ( VALUE(RIGHT(LEFT(TRIM(Bank_Account__c) , 7),1)) * 3)
+ ( VALUE(RIGHT(LEFT(TRIM(Bank_Account__c) , 8),1)) * 2)
+ ( VALUE(RIGHT(LEFT(TRIM(Bank_Account__c) , 9),1)) * 1)
, 11) > 0, "WRONG" , "OK")
Problem is --> Error: Compiled formula is too big to execute.
I have tried a lot. But can't really get it smaller. If someone has a trick to extract all the individual numbers, than I am in buisiness :smileyhappy:
Hope to hear more.
Thanks
Jeroen
Hi Jeroen
The only thing I could think of is creating 9 'checksum' fields and run a workflow rule based on when the bank account field is updated.
the workflow rule would update each of the checksum fields and then you have a formula that looks at these 9 fields and determines whether the bank account is valid.
Because you're using workflow rules, the values are stored as numbers so the character usage of the formula should be pretty low.
Can't think of any other way of doing this.
cheers
Nick
All Answers
Without knowing anything about Dutch Banks, or their Account Numbering system, would it be feasable for you to break the Account Number into sections (like a US Bank Routing Number), and evaluate each section using seperate formulas for each section of the Account Number?
My guess is that if 1 section fails, then the Account Number would be invalid as a whole?
Have you tried splitting your formula into smaller pieces, writing the result of each piece to custom formula fields. and then using your final
IF (MOD( result fields
, 11) > 0, "WRONG" , "OK")
*** this is just a bastardized version of the abbreviated code you would use to evaluate your "Results" fields.
formula to evaluate the result fields?
Tried that, the systems adds all the formula fields, and I come up with even more characters used!
Thanks for thinking with me. :smileywink:
Jeroen
Hi Jeroen
The only thing I could think of is creating 9 'checksum' fields and run a workflow rule based on when the bank account field is updated.
the workflow rule would update each of the checksum fields and then you have a formula that looks at these 9 fields and determines whether the bank account is valid.
Because you're using workflow rules, the values are stored as numbers so the character usage of the formula should be pretty low.
Can't think of any other way of doing this.
cheers
Nick
Wowwww. That's an eye-opener! :smileysurprised:
The "strain" on the Salesforce servers are equal or greater this way, one might think! So why make this different!
Anyway, thanks for your help.
Jeroen
Hi.
If you think you got a problem, see what i have to solve:
IF((Mod((VALUE((mid(CNPJ__c,1,1)))*5)+(VALUE((mid(CNPJ__c,2,1)))*4)+(VALUE((mid(CNPJ__c,3,1)))*3)+(VALUE((mid(CNPJ__c,4,1)))*2)+(VALUE((mid(CNPJ__c,5,1)))*9)+(VALUE((mid(CNPJ__c,6,1)))*8)+(VALUE((mid(CNPJ__c,7,1)))*7)+(VALUE((mid(CNPJ__c,8,1)))*6)+(VALUE((mid(CNPJ__c,9,1)))*5)+(VALUE((mid(CNPJ__c,10,1)))*4)+(VALUE((mid(CNPJ__c,11,1)))*3)+(VALUE((mid(CNPJ__c,12,1)))*2),11))<2,0,(Mod((VALUE((mid(CNPJ__c,1,1)))*5)+(VALUE((mid(CNPJ__c,2,1)))*4)+(VALUE((mid(CNPJ__c,3,1)))*3)+(VALUE((mid(CNPJ__c,4,1)))*2)+(VALUE((mid(CNPJ__c,5,1)))*9)+(VALUE((mid(CNPJ__c,6,1)))*8)+(VALUE((mid(CNPJ__c,7,1)))*7)+(VALUE((mid(CNPJ__c,8,1)))*6)+(VALUE((mid(CNPJ__c,9,1)))*5)+(VALUE((mid(CNPJ__c,10,1)))*4)+(VALUE((mid(CNPJ__c,11,1)))*3)+(VALUE((mid(CNPJ__c,12,1)))*2),11)))
I believe not even the workflow can solve this and it´s only the first digit check. There is another one with the same lenght and i have to concatenate both into one single field.
That´s the real mission impossible.
It will be solved only if the platform could multiply a text string by a number without the VALUE function.
Hi,
Were you able to get the CNPJ validation done? Any guidance you can provide will be greatly appreciated.
Thanks
I created a TEXT Field and dealed with each number as an isolated digit with MID, VALUE and TEXT functions. Then i used some workkflows to validate the checksum and update 2 fields called Check1 and Check2.
Use extremely short field names like N1 N2 or something like that so the formula will not show the massage "too big".
If you have other idea, please let me know. It´s an incredible maze to solve. CNPJ, CPF, RG, PIS, BarCodes, and so many others...
Here´s an exemple. After that I Created 3 workflows to update the field DV (Dígito Verificador).
Field N = TEXT (10 caracters)
Formula:
11-(MOD(((VALUE(MID(N__c,10,1))*2)+
(VALUE(MID(N__c,9,1))*3)+
(VALUE(MID(N__c,8,1))*4)+
(VALUE(MID(N__c,7,1))*5)+
(VALUE(MID(N__c,6,1))*6)+
(VALUE(MID(N__c,5,1))*7)+
(VALUE(MID(N__c,4,1))*8)+
(VALUE(MID(N__c,3,1))*9)+
(VALUE(MID(N__c,2,1))*2)+
(VALUE(MID(N__c,1,1))*3)),11))