0

I have a custom field that is a text field, Height__c (It was set as a text field from the start, not sure why)

Height__c needs to be compared to a couple of statements:

If height__c is >10" call it"Tall" If Height__c is > 0" but <=10", call it "Short" If Height__c is > 10" but <=20", call it "S-Short" If Height_-c is blank we call it "None"

it also has to be coming from the record_type: Gauze

So far my formula is:

Record_Type_Name__c  ='Gauze' &&

IF(VALUE(HEight__c)>10,'tall',
IF(AND(VALUE(HEight__c) > 0,TEXT(VALUE(HEight__c) <= 10,'Short',
    IF(AND(VALUE(HEight__c) > 10, TEXT(VALUE(HEight__c)  <=  20),
 's short','None')

            )
        )
)
))

It is returning the error: Error: Incorrect parameter type for function 'TEXT()'. Expected Number, Date, DateTime, Picklist, received Boolean

I know its difficult because the field type is text, but i want it to convert to a number in the formula to compare it to the statements above but bring back a text field as the outcome. Any ideas what I did wrong?

I dont want to change the field type because it is being used in other api call outs as a text field. the outcome should be a text formula that shows either "tall", "short", "s-short", or"none. Users are inputting 10" or 12" into the text field so I dont know how that can be inputted into the formula field with the " converting to a number field.

5

So your question implies this, you have 4 conditions

  1. if height > 10 then tall
  2. if height > 0 but <=10 short
  3. if height >10 but <=20 s short (THIS CONFLICTS WITH 1st IF STATEMENT)
  4. else NONE

I am assuming that your 1st IF condition means if height >20 then tall, else its logical failure.

There are few things bit not right in your formula,

IF(AND(VALUE(HEight__c) > 0,TEXT(VALUE(HEight__c) <= 10,'Short',

The above line translates to convert height into Number, then compare it if its less than or equal t0 10, if true assign it a String 'Short' and again convert it to text.

This mean you are trying to convert already present text to text again and hence you are getting compile time error.

Error: Incorrect parameter type for function 'TEXT()'. Expected Number, Date, 

On top of that, you are doing logical Operation AND here on string which is again not possible in formula field.

I was able to fix your formula by few tweaks, you can refer this.

    IF(RecordType.Name='Gauze',
    IF(VALUE(Height__c)>20,'tall',
        IF(AND(VALUE(Height__c) > 0,VALUE(Height__c) <= 10),'Short',
            IF(AND(VALUE(Height__c) > 10, VALUE(Height__c)  <=  20),'s short','None')            
        )
    ),
'None')
|improve this answer|||||
  • Looks like a great answer. I wonder if, just as an exercise, we could pull out the RecordType.Name part and only use it once. I can't find a way to do it off the top of my head, but it's an interesting thing to look into. – DavidSchach Apr 10 '18 at 22:20
  • 2
    I was able to use RecordType.Name once, edited answer to reflect same. I had to fix it, else I might have gotten a sleepless night. – Pranay Jaiswal Apr 10 '18 at 22:59

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.