function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
phiberoptikphiberoptik 

Has anyone created a formula for deciphering the Carrier based on a tracking number's format?

I currently have a Text field that accepts a tracking number for a shipment. My thought was that I could create a formula field that would look at the format of the tracking number and display the carrier's name. I am stuck in that I cannot use REGEX() for a formula field and I am not sure how to compare the format of the value in the text field to get the Carrier.


Has anyone found this solved elsewhere? I have not yet considered a workflow rule as I wanted to post this first.

Best Answer chosen by Admin (Salesforce Developers) 
phiberoptikphiberoptik

Nevermind, I solved it myself and for your reference, my solution is below. If you see any reason why this might be flawed please let me know.

 

Tracking Number is a Text field.

Carrier is a Text field.

 

Created a workflow rule on the object these fields reside on.

Run Criteria: created and everytime its edited.

Evaluation Criteria:

 

 

OR(
   NOT(ISBLANK(Tracking_Number__c)),
   ISCHANGED(Tracking_Number__c)
)

 

Workflow Action: Field Update

Field to Update: Carrier

Use formula to specifiy new value:

 

IF( BEGINS(Tracking_Number__c , "IZ"), "UPS",
IF( BEGINS(Tracking_Number__c , "EA"), "USPS Express Mail",
IF( BEGINS(Tracking_Number__c , "EC"), "USPS Express Mail International",
IF( BEGINS(Tracking_Number__c , "CP"), "USPS Priority Mail International",
IF( BEGINS(Tracking_Number__c , "RA"), "USPS Registered Mail",
IF( BEGINS(Tracking_Number__c , "82 "), "USPS Global Express Guaranteed",
IF( BEGINS(Tracking_Number__c , "EA"), "USPS Express Mail",
IF( BEGINS(Tracking_Number__c , "03"), "USPS Delivery Confirmation",
IF( BEGINS(Tracking_Number__c , "23"), "USPS Express Signature Confirmation",
IF( BEGINS(Tracking_Number__c , "70"), "USPS Certified Mail",
IF( BEGINS(Tracking_Number__c , "71"), "USPS Certified Mail",
IF( BEGINS(Tracking_Number__c , "94"), "USPS Certified Mail",
IF( REGEX(Tracking_Number__c , "[0-9]{4} [0-9]{4} [0-9]{2}"), "DHL",
IF( REGEX(Tracking_Number__c , "[0-9]{4} [0-9]{4} [0-9]{4} [0-9]{3}"), "Fed Ex Ground",
IF( REGEX(Tracking_Number__c , "[0-9]{4} [0-9]{4} [0-9]{4}"), "Fed Ex Express",
IF( REGEX(Tracking_Number__c , "[A-Z]{2} [0-9]{3} [0-9]{3} [0-9]{3} CA"), "Canada Post",
null))))))))))))))))