Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
RPReddyRPReddy 

VAt number format validation

 

hi,

i have written validation rule for vat number format checking.it was checking for only one country.for rest of the countries it  havenot work.for example in that rule i have written for 3 countries..it is working fine for germany ,for  rest of the countries(finland,denmark) rule is not working.can u tell me where is the problem and what i have to do.

 

 

OR(OR(AND(AND(ISPICKVAL( Country__c ,'GERMANY') ,ISBLANK( VAT_No_c__c )),ISPICKVAL( Account_Type__c , 'Corporate')),NOT(REGEX( VAT_No_c__c ,"([A-Z]{2}\\d{9})?"))),
OR(AND(AND(ISPICKVAL( Country__c ,'FINLAND') ,ISBLANK( VAT_No_c__c)),ISPICKVAL(Account_Type__c ,'Corporate')), NOT(REGEX( VAT_No_c__c ,"([A-Z]{2}\\d{4})?")))
OR(AND(AND(ISPICKVAL( Country__c ,'DENMARK') ,ISBLANK( VAT_No_c__c)),ISPICKVAL(Account_Type__c ,'Corporate')),NOT(REGEX( VAT_No_c__c ,"([A-Z]{2}\\d{7})?"))))

 

Regards,

Praveen

phiberoptikphiberoptik

Double check your OR() functions. Also, why do you have double AND() functions for each portion of the rule?
SwarnasankhaSwarnasankha

Hi Praveen!

 

Please try using the following VR:

 

OR
(
    AND
    (
       TEXT(Country__c)="GERMANY",
       Text(Account_Type__c)="Corporate",
       OR
       (
           ISBLANK( VAT_No_c__c ),
           NOT(REGEX( VAT_No_c__c ,"([A-Z]{2}\\d{9})?"))
       )
    ),
    AND
    (
       TEXT(Country__c)="FINLAND",
       Text(Account_Type__c)="Corporate",
       OR
       (
           ISBLANK( VAT_No_c__c ),
           NOT(REGEX( VAT_No_c__c ,"([A-Z]{2}\\d{4})?"))
       )
    ),
    AND
    (

       TEXT(Country__c)="DENMARK",
       Text(Account_Type__c)="Corporate",
       OR
       (
           ISBLANK( VAT_No_c__c ),
           NOT(REGEX( VAT_No_c__c ,"([A-Z]{2}\\d{7})?"))
       )
    )
)

 

Based on the conditions in the VR, it will fire when you choose a specific Country, choose the Account Type as Corporate and if the VAT Number is either left Blank or is Not as per the format specified.

 

In my opinion you should have a seperate VR for managing an empty VAT Number and a seperate VR for enforcing the Country specific VAT Number format.

 

In case you do decide to use a seperate VR for managing an Empty VAT Number then you can use the following VR for the formatting:

 

OR
(
    AND
    (
       TEXT(Country__c)="GERMANY",
       Text(Account_Type__c)="Corporate",
       NOT(ISBLANK( VAT_No_c__c )),
       NOT(REGEX( VAT_No_c__c ,"([A-Z]{2}\\d{9})?"))
    ),
    AND
    (
       TEXT(Country__c)="FINLAND",
       Text(Account_Type__c)="Corporate",
       NOT(ISBLANK( VAT_No_c__c )),
       NOT(REGEX( VAT_No_c__c ,"([A-Z]{2}\\d{9})?"))

    ),
    AND
    (

       TEXT(Country__c)="DENMARK",
       Text(Account_Type__c)="Corporate",
       NOT(ISBLANK( VAT_No_c__c )),
       NOT(REGEX( VAT_No_c__c ,"([A-Z]{2}\\d{9})?"))
     )
)

 

Hope this helps fix your problem.

 
Thomas Panni 7Thomas Panni 7
I did that for a few countries and it works fine, but: how can I do that with the full 27 EU country coverage and not being limited by salesforce formula size? Thanks in advance
 
Matias CalvoMatias Calvo
Hello Thomas,

Probably you have already fixed your problem.

If still helps you, we have a free app in Appexchange that validates VAT codes for all the 28 European countries, including UK.
It validates not only VAT but other legal/tax/finance codes (IBAN, BIC...).

Check it here: https://goo.gl/HHZ4c3

Please try it and give us feedback. We are happy to improve it with your comments

I hope this helps you