0

I have a requirement to produce an image field (i.e one star out of three) based on whether a picklist value is "Supply" or "Supply and install" and determined by the Square meterage number field is <50,51-70,>71,

The Square Meterage metrics change the output image based on the initial Picklist Value:

Supply,<50, Image 1,
Supply, 51-70, Image 2,
Supply, >71, Image 3,

OR

Supply & Install, <60, Image 1,
Supply & Install, 61-80, Image 2,
Supply & Install, >81, Image 3,

So if the Picklist value was 'Supply' and the Square meterage was 62.5 my formula field would output Image 2.

Any help most appreciated.

The below works but it is 1/4 of what the end formula would look like. When at 3/4 it is at the character limit. Any ideas to reduce it?

    IF( Actual_SqM_Sale_Price__c <=790,  IMAGE('/servlet/servlet.FileDownload?file=0150k000000xxxx', "" ,22 , 138),
IF( Actual_SqM_Sale_Price__c >790 && Actual_SqM_Sale_Price__c <815 && ISPICKVAL(Service_Type__c, 'Supply Only'),  IMAGE('/servlet/servlet.FileDownload?file=0150k000000xxxx', "" ,22 , 138),
IF( Actual_SqM_Sale_Price__c >=815 && Actual_SqM_Sale_Price__c <840 && ISPICKVAL(Service_Type__c, 'Supply Only'),  IMAGE('/servlet/servlet.FileDownload?file=0150k000000xxxx', "" ,22 , 138),
IF( Actual_SqM_Sale_Price__c >=840 && Actual_SqM_Sale_Price__c <865 && ISPICKVAL(Service_Type__c, 'Supply Only'),  IMAGE('/servlet/servlet.FileDownload?file=0150k000000xxxx', "" ,22 , 138),
IF( Actual_SqM_Sale_Price__c >=865 && Actual_SqM_Sale_Price__c <890 && ISPICKVAL(Service_Type__c, 'Supply Only'),  IMAGE('/servlet/servlet.FileDownload?file=0150k000000xxxx', "" ,22 , 138),
  • 1
    Check my updated answer. – Saroj Bera May 8 '18 at 6:09
1

It is tricky to check pick list values. You should use ISPICKVAL(picklist_field, text_literal) function. It will make reason to include the border values too.

IF(Square_meterage_number__c <= 50 && ISPICKVAL(Picklist_Field__c, 'Supply'), IMAGE('/resource/leadscore/image_1.png', "",20,100),  IF(Square_meterage_number__c >= 51 && Square_meterage_number__c <= 70 && ISPICKVAL(Picklist_Field__c, 'Supply'), IMAGE('/resource/leadscore/image_2.png', "",20,100),  IF(Square_meterage_number__c >= 71 && ISPICKVAL(Picklist_Field__c, 'Supply'), IMAGE('/resource/leadscore/image_3.png', "",20,100),  IF(Square_meterage_number__c <= 60 && ISPICKVAL(Picklist_Field__c, 'Supply & Install'), IMAGE('/resource/leadscore/image_1.png', "",20,100),  IF(Square_meterage_number__c >= 61 && Square_meterage_number__c <= 80 && ISPICKVAL(Picklist_Field__c, 'Supply & Install'), IMAGE('/resource/leadscore/image_2.png', "",20,100),  IF(Square_meterage_number__c >= 81 && ISPICKVAL(Picklist_Field__c, 'Supply & Install'), IMAGE('/resource/leadscore/image_3.png', "",20,100),""))))))
| improve this answer | |
  • Yes, I needed to use ISPICKVAL(... IS there a way to reduce this in size. I've completed 3/4 of the formula - There are four different Picklist values and i've already reached my 5000 character limit. – Quango Bruce May 8 '18 at 5:27
3

You can try something like this.

IF(Square_meterage_number__c < 50 && Picklist_Field__c = 'Supply', IMAGE('/resource/sample/image_1.png', "",20,100),
IF(Square_meterage_number__c > 51 && Square_meterage_number__c < 70 && Picklist_Field__c = 'Supply', IMAGE('/resource/sample/image_2.png', "",20,100),
IF(Square_meterage_number__c > 71 && Picklist_Field__c = 'Supply', IMAGE('/resource/sample/image_3.png', "",20,100),
IF(Square_meterage_number__c < 60 && Picklist_Field__c = 'Supply & Install', IMAGE('/resource/sample/image_1.png', "",20,100),
IF(Square_meterage_number__c > 61 && Square_meterage_number__c < 80 && Picklist_Field__c = 'Supply & Install', IMAGE('/resource/sample/image_2.png', "",20,100),
IF(Square_meterage_number__c > 81 && Picklist_Field__c = 'Supply & Install', IMAGE('/resource/sample/image_3.png', "",20,100)'
""))))

To reduce the formula length you can upload all your images in a zip file in static resources. Keep the zip file name and the image file name as short as possible. Also, you can remove the size parameters and instead resize the image outside Salesforce before uploading.

Old format

IMAGE('/servlet/servlet.FileDownload?file=0150k000000xxxx', "" ,22 , 138)

New Format

IMAGE('/resource/zip/im_1.png', "")

If still, you want to reduce the length then create another intermediate formula field (hide in the layout) which will give you the image name of the image and in the actual formula field show the image using IMAGE function.

First formula field (It will give you the image name hidden in the layout)

CASE( Square_meterage_number__c ,  '1',  'im_1.png',
                       '2',  'im_2.png',
                       '3',  'im_3.png',
                       '4',  'im_4.png',

Second formula field (it will show the actual image)

IMAGE('/resource/sample/' + first_formula__c,"",20,100)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.