+ Start a Discussion
vandana rajuvandana raju 

conditional report

Hi
 I have some historical records in account object where the Phone field in some records is less than 10 digits and in some records it is 10 digits.
 I need to show in a report only those records whose Phone is exactly 10 digits and not less.

Pls let me know how to achieve this.

Thanks
Vandana
Best Answer chosen by vandana raju
Andrew EchevarriaAndrew Echevarria
One way is to create a number formula field on the object called PhoneLength__c, then for the formula write len(Phone) and it will always show the length. Then in your report you just do PhoneLength__c == 10

All Answers

Andrew EchevarriaAndrew Echevarria
One way is to create a number formula field on the object called PhoneLength__c, then for the formula write len(Phone) and it will always show the length. Then in your report you just do PhoneLength__c == 10
This was selected as the best answer
Andrew EchevarriaAndrew Echevarria
Also, you're able to filter Phone by "Less than", so you could potentially do a filter for less than 1000000000 and greater than 999999 so that they will have to be10  digits.
Roshni RahulRoshni Rahul
Hi vandana,

First create a formula field for account object which checks the length of the Phone number. Then create report for account object and add that formula check. In the filter, check whether the length of the digit is equal to 10. Then generate report.

Hope it works. If you are not getting, Please let me know.

Regards
Roshni
vandana rajuvandana raju
Hi andrew and Roshni
Creating a formula field worked. 
There is one more thing I want to know.
I have phone field in the format (512) 757-6000.
How to display in report this format and also length should be 10 digits.
Using formula field in above case doesnt display the above format

Thanks
Vandana
 
Andrew EchevarriaAndrew Echevarria
In the same formula field, substitute spaces, '(', ')', and '-' for '', using the Substitute function. For example SUBSTITUTE(Field__c, '(', '')
Andrew EchevarriaAndrew Echevarria
This formula should work, but try it compiling it. len(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SSN__c, '-', ''), '(', ''), '(', ''), '-', '')
vandana rajuvandana raju
Hi andrew
Thanks, that worked.

 
Roshni RahulRoshni Rahul
Hi Vandana,

LEN(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(Phone, '.', '') , ' ', '') , '-', '') , ')', '') , '(', '')).
This shoul be your formula field because your phone number willbe displayed as" (822) 922-3223" . Inorder to avoid the special characters you have to use SUBSTITUTE.
Happy coding.
I tried and it works perfectly for me.Try it.
If it works, mark my answer as Best Answer.

Thanks
Roshni
Roshni RahulRoshni Rahul
In the report filter, support the name of the formula field is Length_of_PhoneNumber, check whether it is equal to 10.