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.

Improve this question

1 Answer 1

Reset to default
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
2
  • 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
    Commented Apr 10, 2018 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
    Commented Apr 10, 2018 at 22:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .