Apex Code Development You need to sign in to do that
Don't have an account?
msglsmo Formula Field to decode a number?
Hi All,
Our customer support agents provide an activation code to our customers when they are activating a piece of equipment. The activation code is based off of the Asset's serial number, which is in Salesforce. We use the below chart to decode the serial number:
0->A
1->F
2->T
3->L
4->G
5->P
6->Z
7->N
8->Q
9->S
For example, a serial number might be 12345D. We drop the "D" and use 12345. The Activation Code in this case would be FTLGP.
Is there a way to create a Formula Field, Filed Update workflow, etc, to create a filed that decodes this serial number for us automatically?
Thanks!
Mike,
Wow! My fault! So LEFT() will only work for the first character. I got overzealous!
You need to use MID() instead. The syntax is: MID(text, start_num, num_chars)
So, here's your formula with the modifications:
As you can see MID() is going to work much better. You can, of course, leave the first one as LEFT(), but I think that confuses things!
Let me know if you run into any fruther problems!
Amber
All Answers
I have a couple of questions.
1.) Do you always drop the last character? Just the letter(s)?
2.) Does the serial number ever change?
I can help you write the exact formula with this info, but you can take a stab at it yourself by starting with the Case() function in the formual builder.
Let us know!
Thanks,
Amber
Thank you VERY much for your offer and quick response!
To answer your questions:
1. The serial number asset field will reflect the letter, eg: 15986Z. In decoding, we ignore the last digit, which is always a letter.
2. We have two different serial number formats. One piece of equipment uses a serial number that starts with two letters and ends with five numbers and follows the XX##### format - for example, AB01234. We do not need to activate this older format, so there is not any decoding needed for this serial number format. The serial number format we need to decifer strictly follows the #####XX format - for example, 12345D.
Thank you again, very much. It is very appreciated.
Mike
Mike,
This formula is going to be a beast! It is doable, at least at first glance, but ugly.
I'm going to give you a sample with much fewer options and will rely on you to expand it for your purposes.
Here's my mapping:
1=A
2=B
3=C
This formula gets you the first character of your activation code mapped.
CASE(LEFT(Serial_Number__c, 1),
"1", "A",
"2", "B",
"3", "C",
"ZZZ")
If you have a 3 digit activation code the formula looks like this:
(
(CASE(LEFT(Serial_Number__c, 1),
"1", "A",
"2", "B",
"3", "C",
"ZZZ")) &
(CASE(LEFT(Serial_Number__c, 2),
"1", "A",
"2", "B",
"3", "C",
"ZZZ")) &
(CASE(LEFT(Serial_Number__c, 3),
"1", "A",
"2", "B",
"3", "C",
"ZZZ"))
)
Notice how the number at the end of the longest line changes? That's to map each character in turn. The "&" does the concatenation.
Again, this is simplified and you'd probably need more stuff options and mappings, but this will get you started!
Let us know if you get stuck and we'll do what we can to help!
Good luck!
Amber
HI Amber,
Thank you for the starting point! I looked at what you did, but my extension to where you started isn't working. I may misunderstood the CASE function. I changed the decifering key a little where 0=9, 2=7, 4=5, etc, on down to 9=0. Here is what formula I used:
The result is a properly decifered 1st number, but then a whole lot of "See Chart" following.
Any ideas?
Thank you again!
Mike
Mike,
Wow! My fault! So LEFT() will only work for the first character. I got overzealous!
You need to use MID() instead. The syntax is: MID(text, start_num, num_chars)
So, here's your formula with the modifications:
As you can see MID() is going to work much better. You can, of course, leave the first one as LEFT(), but I think that confuses things!
Let me know if you run into any fruther problems!
Amber
THANK YOU!!!!!
This works perfectly for our equipment that requires an activation code!
One other quick question for you. We have 4 products that have serial numbers. Only one requires an activation pin:
Model 1 - Format XX#####
Model 4 - Format #####X (This is the one requiring an activation code)
Model S - XXXX#####X
Model M - ######
Is there a way to leave this field blank for the three models that do not need an activation code?
Thanks!
Mike
Nice one Amber! :-)
Mike,
So glad that worked! Here's how I'd tackle making the formula blank for the other models:
IF(
(Model__c = "Model4"),
(That big long formula we just figured out),
""
)
Basically you're saying, if the Model is Model4, then do the formula, otherwise, leave the field blank.
That should do it!
Let me know if that barfs on you.
Good luck!
Amber
That makes perfect sense. Thank you again!
Unfortunately, I tried it and I am over the character limit for the field. The new formula is 5081 characters and the limit is 5000. Dang!
I certainly appreciate your help. You have been very kind to share your knowledge!
Mike
I could be wrong on this, but taking out all the line breaks may save you some characters. Let me know if it does!
Thanks!
Amber
I just attempted a different approach and it works. Instead of doing this process as a Formula Field, I instead created a workflow field update and went that route. Everything works as designed now.
Once again, I really appreciate your assistance!