function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Donny.ax1663Donny.ax1663 

If Then Formula Troubles.

Hello,

 

I am fairly new to the SF game and this is my first post. I can't seem to grasp if/then formulas.  I am reaching out to all of you in hopes I can get squared away.

 

I am trying to estabilish a formula to look at a custom payment object field called "sku shipped"  then depending on what is entered it will populate another field with a number.

 

This is what I am trying to do, and I hope this makes sense.

 

If  Sku_Shipped_1__c  = "1:GREENC" then  Weight_1__c  = "2"

 

"Sku_Shipped_1_c" is a field that is populated from orders

"Weight_1_c" is the custom field I want populated with the number.

"2" is the weight

 

Basically, what I am trying to acheive is this, when a customer orders product the "sku shipped" field is auto populated from the website or a phone rep, but I want that to then reflect the number "2" in my weight column.

 

Any help is appreicated. 

 

 

Best Answer chosen by Admin (Salesforce Developers) 
Shannon HaleShannon Hale

Sure thing.

 

I had thought the weight was the number, but if it's the count, and the actual weight is 2oz * count, we'll need to make a small change and multiply by two:

 

IF (
  ISNUMBER( LEFT( Sku_Shipped_1__c, 2 ) ),  /* Line 1 */
  VALUE( LEFT( Sku_Shipped_1__c, 2 ) ) * 2, /* Line 2 */
  VALUE( LEFT( Sku_Shipped_1__c, 1 ) ) * 2  /* Line 3 */
)

 

Line 1 is the condition you're checking in the IF() function:

LEFT( Sku_Shipped_1__c, 2) gets the first two characters of Sku_Shipped_1__c.

ISNUMBER( LEFT( Sku_Shipped_1__c, 2) ) checks whether those two characters are numeric. If they are, then the string starts with a 2 digit number. If not, the string starts with a 1 digit number.

 

Line 2 is the value to return if the condition is true (string starts with a 2 digit number):

LEFT( Sku_Shipped_1__c, 2), again, is getting the first two characters of Sku_Shipped_1__c.

VALUE( LEFT( Sku_Shipped_1__c, 2) ) converts those two characters into a number, because LEFT() returns a string.

Multiply the number by 2 to get the weight.

 

Line 3 is the value to return if the condition is false (string starts with a 1 digit number): 

LEFT( Sku_Shipped_1__c, 1) gets the first character of Sku_Shipped_1__c.

VALUE( LEFT( Sku_Shipped_1__c, 1) ) converts the characters into a number.

Multiply the number by 2 to get the weight.

 

More documentation on these functions are here...

IF(): http://login.salesforce.com/help/doc/en/customize_functions_i_z.htm#IF

LEFT(): http://login.salesforce.com/help/doc/en/customize_functions_i_z.htm#LEFT

VALUE(): http://login.salesforce.com/help/doc/en/customize_functions_i_z.htm#VALUE

 

Because this formula automatically determines the number of the count, you don't need to add any checks for each individual number -- it will handle 1, 7, 13, or 20, and even go up to 99 if you need to do that at some point.

 

Let me know if anything is still unclear.

All Answers

Shannon HaleShannon Hale

Hi Donny, welcome to Salesforce formulas!

 

I think the function you're looking for is the IF() function, and it works like this:

 

IF(
  Sku_Shipped_1__c = "1:GREENC", /* Condition to match */
  2, /* Formula value if there's a match */
  0 /* Formula value if there's no match */
)

 

You could put something else where I have the zero -- even another IF() function, so they can be nested.

 

If you have a number of possible values for Sku_Shipped_1__c, each with a different weight, you can use a CASE() function, which is more efficient than nested IF() functions:

 

CASE(Sku_Shipped_1__c, /* Field containing value to check */
  "1:GREENC", 2, /* First value to match, then formula value */
  "VALUE2", 3, /* Second value to match, then formula value */
  "VALUE3", 1, /* Repeat for each possible match */
  0 /* The default value to use if no other matches occur */
)

 

Here is the documentation on IF(): 

http://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions_i_z.htm&language=en_US#IF

 

And here is the documentation on CASE():

http://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions_a_h.htm&language=en_US#CASE

 

I hope that helps, and good luck!

Donny.ax1663Donny.ax1663

Thank you sir! I will give this a shot!

 

I appreciate the links and detalied reply!  Much appreciated!

 

- Donny 

Donny.ax1663Donny.ax1663

Shale,

 

It worked perfectly for me, however I have too many sku's. I did not know there was a character limit (wish I knew that before I typed it all out).  I tried it with a workflow update and again, too many characters.  

 

If you happen to know the next question I would be grateful.  I have about 400 different sku's, however each item weighs the same.  1 bottle, is 1 oz, 2 bottles weighs 2 oz etc.  I tried to play with "contains" but I keep getting "error: Syntax Error"  

 

This is what I did.

 

CASE (CONTAINS (Sku_Shipped_1_c, /* Field containing value to check */

“1”, 2, /* 1 value to match, then formula value */

“2”, 3, /* 2 value to match, then formula value */

“3”, 4, /* 3 value to match, then formula value */

“4”, 5, /* 4 value to match, then formula value */

“5”, 6, /* 5 value to match, then formula value */

“6”, 7, /* 6 value to match, then formula value */

“7”, 8, /* 7 value to match, then formula value */

“8”, 9, /* 8 value to match, then formula value */

“9”, 10, /* 9 value to match, then formula value */

“10”, 11, /* 10 value to match, then formula value */

“11”, 12, /* 11 value to match, then formula value */

“12”, 13, /* 12 value to match, then formula value */

“13”, 14, /* 13 value to match, then formula value */

“14”, 15, /* 14 value to match, then formula value */

“15”, 16, /* 15 value to match, then formula value */

“16”, 17, /* 16 value to match, then formula value */

“17”, 18, /* 17 value to match, then formula value */

“18”, 19, /* 18 value to match, then formula value */

“19”, 20, /* 19 value to match, then formula value */

“20”, 21, /* Repeat for each possible match */

8 /* The default value to use if no other matches occur */

)

 

If you have any suggestions as how to read just the number in part of the sku.  For example, 1:15DPRG and 1:GREENC weight the same, instead of using both lines wich causes too many characters, Could I just use the "1" or any other number in place of the full sku, this way it looks for numbers and saves me a lot of character counts.

Thank you in advance. 

 

-Donny 

Donny.ax1663Donny.ax1663

Disregard, I just read that Case does not play well with contains.  I will give it a go with If()

 

- Donny 

Shannon HaleShannon Hale

Donny, are you hitting the Maximum Number of Characters (3900) limit, or the Maximum Formula Size (In Bytes) When Compiled (5000) limit?

 

If the error says, "Formula is too long (3,902 characters). Maximum length is 3,900 characters.” then if you still have comments in there, you might be able to get around it by removing the comments (the /* */ parts) if you haven't tried that already.

 

If the error says, “Compiled formula is too big to execute (13,974 characters). Maximum size is 5,000 characters.” then you're hitting the formula compile size limit, and you'll need another approach. The workflow rule approach is for the latter error, but it generally only helps if you refer to some other field a number of times in your formula (for example you were repeating Sku_Shipped_1_c many times). When you have a CASE() with 400 conditions it's pretty hard to reduce that without considering a different approach.

 

My concern with CONTAINS() is that "1" would also match "12:GREENC" or "31:GREENC".

 

If your numbers are never more than 2 digits, and are always at the beginning of the picklist value. could try something like this:

 

IF (
  ISNUMBER( LEFT( TEXT( Picklist_Test__c ), 2 ) ),
  VALUE( LEFT( TEXT( Picklist_Test__c ), 2 ) ),
  VALUE( LEFT( TEXT( Picklist_Test__c ), 1 ) )
)

 

Basically it checks whether the first 2 characters are a number, and if so, it returns the number. If the first 2 characters are not a number, it returns the value of the first character. The compile size is only 934 characters.

Donny.ax1663Donny.ax1663

Thanks Shale,

 

Yes, the numbers are always at the beginning, however it is not a pick list.  It is a text field that is populated from Authorize.net based on what was orderd online or a text field that is populated based on the opportunity created by my reps. The Weight column must be a number howerver.  

 

I assume you wanted me to replace "Picklist_Test__c" with "Sku_Shipped_1__c", but I receive an error

 

"Error: Incorrect number of parameters for function 'NUMBER()'. Expected 0 received 1.

 

Also, you were right, if I used the IF/Contains 18 will populate 9 as it reads it as an 8 instead of an 18

 

Thanks again, and sorry for my newbieness. 

 

- Donny 

Shannon HaleShannon Hale

Oops - sorry, little case of copy&paste there.

 

If your field's a text field you should be able to do this -- no need to wrap Sku_Shipped_1__c with TEXT().

 

IF (
  ISNUMBER( LEFT( Sku_Shipped_1__c, 2 ) ),
  VALUE( LEFT( Sku_Shipped_1__c, 2 ) ),
  VALUE( LEFT( Sku_Shipped_1__c, 1 ) )
)

 Don't feel bad about asking questions -- everyone has to start somewhere!

Donny SDonny S

Shale,

 

Could you help me make sense of what I am reading here so I can edit to my needs?  Currently we have about 400 different products.  All of them are 2oz each.  So a customer may order !:GREENC, 2:GREENC or 8:GREENC ETC.  If it is 1, I need the weight to reflect 2. If it is 2, I need the weight to reflect 4, if it is 8 I need the weight to reflect 16.  So on and so forth.  20 is the limit a customer can order.  Just trying to understand what is written on the code you gave me so I can edit it for numbers 1-20.  


Thank you again!

Shannon HaleShannon Hale

Sure thing.

 

I had thought the weight was the number, but if it's the count, and the actual weight is 2oz * count, we'll need to make a small change and multiply by two:

 

IF (
  ISNUMBER( LEFT( Sku_Shipped_1__c, 2 ) ),  /* Line 1 */
  VALUE( LEFT( Sku_Shipped_1__c, 2 ) ) * 2, /* Line 2 */
  VALUE( LEFT( Sku_Shipped_1__c, 1 ) ) * 2  /* Line 3 */
)

 

Line 1 is the condition you're checking in the IF() function:

LEFT( Sku_Shipped_1__c, 2) gets the first two characters of Sku_Shipped_1__c.

ISNUMBER( LEFT( Sku_Shipped_1__c, 2) ) checks whether those two characters are numeric. If they are, then the string starts with a 2 digit number. If not, the string starts with a 1 digit number.

 

Line 2 is the value to return if the condition is true (string starts with a 2 digit number):

LEFT( Sku_Shipped_1__c, 2), again, is getting the first two characters of Sku_Shipped_1__c.

VALUE( LEFT( Sku_Shipped_1__c, 2) ) converts those two characters into a number, because LEFT() returns a string.

Multiply the number by 2 to get the weight.

 

Line 3 is the value to return if the condition is false (string starts with a 1 digit number): 

LEFT( Sku_Shipped_1__c, 1) gets the first character of Sku_Shipped_1__c.

VALUE( LEFT( Sku_Shipped_1__c, 1) ) converts the characters into a number.

Multiply the number by 2 to get the weight.

 

More documentation on these functions are here...

IF(): http://login.salesforce.com/help/doc/en/customize_functions_i_z.htm#IF

LEFT(): http://login.salesforce.com/help/doc/en/customize_functions_i_z.htm#LEFT

VALUE(): http://login.salesforce.com/help/doc/en/customize_functions_i_z.htm#VALUE

 

Because this formula automatically determines the number of the count, you don't need to add any checks for each individual number -- it will handle 1, 7, 13, or 20, and even go up to 99 if you need to do that at some point.

 

Let me know if anything is still unclear.

This was selected as the best answer
Donny SDonny S

Brilliant!  You have been a tremendous help!    Thank you kindly !

Shannon HaleShannon Hale

Glad to hear it! Please "Accept as Solution" the answer that helped you the most so that other people can see that the question has been answered and can benefit.

Donny.ax1663Donny.ax1663

Done and done.  I really appreciate it Shannon! 

Donny.ax1663Donny.ax1663

Shannon,

 

Last question, and I swear I am done.  It turns out that the software we are importing too requires the weight to be in pounds and ounces.  So basically, I need to  multiply by 0.01. I tried adding on to the formula below, however SF reads the 0.01 as 0 and just returns the weight back as 0.  I don't seem to find an answer online (one that I understand anywah).

 

Basucally, if the number on the left is 1, the weight returned back needs to reflect ".2".  If the number to the left is 4  the weight needs to reflect ".8".  So I want to multiply the left number by 2, them multiply by 0.001.

 

IF (
ISNUMBER( LEFT( Sku_Shipped_1__c, 2 ) ),
VALUE( LEFT( Sku_Shipped_1__c, 2 ) ) * 2 * 0.01,
VALUE( LEFT( Sku_Shipped_1__c, 1 ) ) * 2 * 0.01
)

 

 

Thank you in advance!

Shannon HaleShannon Hale

I think your formula is correct, but your formula field is probably set to 0 decimal places.

 

When you edit the formula, just above the formula editor there are Formula Options for return type and decimal places -- try changing that to 2.

Donny.ax1663Donny.ax1663

Ah, that is exactly what it was.  Thanks again.  I am thankful I had the formula correct at least!

 

- Donny