2

So I have this long formula using CASE function. I am trying to look for a way to lessen the characters used. Below is my sample formula:

CASE (IPYG_Input_Preferred_Program__c,
"STEM" , "Senior High School",
"ABM" , "Senior High School",
"HUMSS" , "Senior High School",
"GAS" , "Senior High School",
"ARTSDESIGN" , "Senior High School",
"ENGINEERING MAJOR IN POWER ELECTRONICS BY RESEARCH" , "Graduate School",
"MASTER OF SCIENCE IN ELECTRICAL ENGINEERING BY RESEARCH" , "Graduate School",
"MASTER OF SCIENCE IN ENGINEERING MANAGEMENT" , "Graduate School",
"MASTER OF SCIENCE IN INDUSTRIAL ENGINEERING" , "Graduate School",
"MASTER OF SCIENCE IN MECHANICAL ENGINEERING" , "Graduate School",
"MASTER OF SCIENCE IN MATERIALS SCIENCE AND ENGINEERING" , "Graduate School",
"DOCTOR OF PHILOSOPHY IN CHEMICAL ENGINEERING BY RESEARCH" , "Graduate School",
"DOCTOR OF PHILOSOPHY IN CHEMISTRY" , "Graduate School",
"DOCTOR OF PHILOSOPHY IN COMPUTER ENGINEERING BY REA SERACH" , "Graduate School",
"DOCTOR OF PHILOSOPHY IN COMPUTER SCIENCE" , "Graduate School",
"DOCTOR OF PHILOSOPHY IN ELECTRONICS ENGINEERING" , "Graduate School",
"DOCTOR OF PHILOSOPHY IN ENVIRONMENTAL ENGINEERING" , "Graduate School",
"DOCTOR OF PHILOSOPHY IN INDUSTRIAL ENGINEERING BY REA SERACH" , "Graduate School",
"MASTER OF SCIENCE IN CIVIL ENGINEERING" , "Graduate School",
"MASTER OF SCIENCE IN CIVIL ENGINEERING BY RESEARCH" , "Graduate School",
"MASTER OF SCIENCE IN CHEMICAL ENGINEERING BY RESEARCH" , "Graduate School",
"MASTER OF SCIENCE IN CONSTRUCTION ENGINEERING AND MANAGEMENT" , "Graduate School",
"MASTER OF SCIENCE IN COMPUTER ENGINEERING" , "Graduate School",
"MASTER OF SCIENCE IN COMPUTER ENGINEERING BY RESEARCH" , "Graduate School",
"MASTER OF SCIENCE IN COMPUTER SCIENCE" , "Graduate School",
"MASTER OF SCIENCE IN ELECTRONICS ENGINEERING" , "Graduate School",
"MASTER OF SCIENCE IN ELECTRONICS ENGINEERING MAJOR IN MICROELECTRONICS" , "Graduate School",
"MASTER OF SCIENCE IN ELECTRONICS ENGINEERING BY RESEARCH" , "Graduate School",
"MASTER OF SCIENCE IN ELECTRICAL ENGINEERING" , "Graduate School",
"MASTER OF SCIENCE IN ELECTRICAL ENGINEERING MAJOR IN POWER ELECTRONICS" , "Graduate School",
"POST-GRADUATE DIPLOMA IN POWER ELECTRONICS" , "Graduate School",
"MASTER OF SCIENCE IN ENVIRONMENTAL ENGINEERING - DOCTOR OF PHILOSOPHY IN ENVIRONMENTAL ENGINEERING" , "Graduate School",
"MASTER IN BUSINESS ANALYTICS MASTER OF SCIENCE IN ENVIRONMENTAL ENGINEERING" , "Graduate School",
"MASTER OF ENGINEERING - CIVI ENGINEERING" , "Graduate School",
"MASTER OF ENGINEERING - COMPUTER ENGINEERING" , "Graduate School",
"MASTER OF ENGINEERING - ELECTRONICS ENGINEERING" , "Graduate School",
"Master of Business Administration" , "Graduate School",
"College"
)

Is there a way not to make the "Graduate School" repetitive? Any suggestions will be appreciated. Thank you!

2 Answers 2

6

You can rely on some common patterns in your example and use the CONTAINS function within nested IFs with the last else use case being the CASE to handle odd-balls

IF (CONTAINS(IPYG_Input_Preferred_Program__c,'MASTER OF'),'Graduate School',
IF (CONTAINS(IPYG_Input_Preferred_Program__c,'DOCTOR OF'),'Graduate School',
IF (CONTAINS(IPYG_Input_Preferred_Program__c,'Post-Graduate'),'Graduate School',

CASE (IPYG_Input_Preferred_Program__c,
"STEM" , "Senior High School",
"ABM" , "Senior High School",
"HUMSS" , "Senior High School",
"GAS" , "Senior High School",
"ARTSDESIGN" , "Senior High School",
"ENGINEERING MAJOR IN POWER ELECTRONICS BY RESEARCH" , "Graduate School",
"College")
)))
2
  • Thank you so much for this. Appreciate your insights
    – Eddd
    Commented Jun 23 at 4:51
  • 1
    My warning on this is that it is troublesome to maintain as new programs are added. An admin has to help by adding the picklist value and possibly updating the formula.
    – Phil W
    Commented Jun 23 at 6:42
5

If I were to address this requirement, I would introduce an object to represent the various programs (e.g. Program__c) and have that object hold both the program name and the type of institution. I'd then make IPYG_Input_Preferred_Program__c a lookup field that references this new object, rather than being a picklist or text field. The formula field would then simply be something like:

IPYG_Input_Preferred_Program__r.Type_of_Institute__c

The alternative is to still introduce the new object, only this time making sure it has a field (generally Name - though this may not be long enough) that matches the IPYG_Input_Preferred_Program__c value (if the field is a picklist, matching the value's API name) and another that identifies the type of institution. I'd then have a formula field that uses VLOOKUP, perhaps like:

BLANKVALUE(IF(ISBLANK(IPYG_Input_Preferred_Program__c),
              '',
              VLOOKUP($ObjectType.Program__c.Fields.Type_of_Institute__c,
                      $ObjectType.Program__c.Fields.Name,
                      IPYG_Input_Preferred_Program__c)),
           'College')

This should find the Program__c record with a name that matches the IPYG_Input_Preferred_Program__c, if there is one, returning the type of institution. If there is no relevant program, "College" is explicitly returned.

In both cases you need to set up the various Program__c records to hold the data. One example would be a program record called "MASTER OF SCIENCE IN ELECTRICAL ENGINEERING" that has the Type_of_Institute__c set to "Graduate School".

5
  • Hi! Thank you for this insight. I am already in the path that I created a custom object where all courses are listed. The dilemma that I am facing now is I don't know if it is possible to auto populate a value in a Look up field coming from a Text field. From object 1, I have this text field and I want to know if it is possible that if I enter a valid value which is an identical value coming from the custom object, will it auto populate into the Lookup field that I created to another object.
    – Eddd
    Commented Jun 23 at 4:53
  • You would need to write a record triggered flow or some apex to handle this. It would need to do a query for a record of the lookup object where the name matches the text, for example, then store the ID in the lookup field. I recommend you try for yourself and if you get stuck raise a new question showing what you tried.
    – Phil W
    Commented Jun 23 at 6:40
  • Can you recommend a tutorial for this kind of flow? I am not really good when it comes to flow so as of now I am not progressing.
    – Eddd
    Commented Jun 25 at 2:40
  • I tried creating a flow however I received this error after I activated it: Review the errors on this page. We can't save this record because the “Test flow for mapua program” process failed. Give your Salesforce admin these details. This error occurred when the flow tried to update records: MALFORMED_ID: Mapua Program: id value of incorrect type: BS Data Science. You can look up ExceptionCode values in the SOAP API Developer Guide. Error ID: 1959083070-624684 (1456471799)k up ExceptionCode values in the SOAP API Developer Guide. Error ID: 1959083070-624684 (1456471799)
    – Eddd
    Commented Jun 25 at 3:49
  • Raise a new question to show what you tried and where you are stuck. Including the error message like above is very important, as is including the detail of the object model and any variables used in the flow.
    – Phil W
    Commented Jun 25 at 6:02

You must log in to answer this question.

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