+ Start a Discussion
Jose María Nicolás ArfelisJose María Nicolás Arfelis 

How can I change the status of a student/applicant according to the highest status in an application stage?

Hi everybody,

I have the following scenario:

an applicant can have one or more applications, for this I am using a Person Account object and a custom object called Admisión. The object Admisión has a lookup field called Name of the Account, pointing to the Person Account or applicant, so the applicant has a related list called Admisiones.

The Admisión object has the field Stage with values Review pending, In review, Admission accepted, Admission denied. I wanted to have a Status field in the Person Account object with values "Initial contact", "Admission Inquiry", "Admission denied" and "Admission accepted", so their values change according to the stage value in the object Admisión.

The problem is: an applicant can not have a general status for all applications submitted by the applicant, as an application can have one status and the another application another status.

We would like further to build reports based on the status of the applicant, would be a good approach to add the applicant status field in the object Admisión? or wouldn't you consider even to use this field?.

I guess this can only be implemented with code.
Thinking about this: for example, if the applicant has three applications, one in stage "In review", another in stage "Denied" and the another one in stage "Accepted", the application with the "highest" level of stage should "win" and be the criterium in order to change the status of the applicant from "Admission inquiry" to "Admission accepted".

I think, this can not be done with Process Builder but programm code is needed. Does anybody know how to build a programm which makes this idea possible to implement?.
Best Answer chosen by Jose María Nicolás Arfelis
sathishkumar periyasamysathishkumar periyasamy
Jose, I like Brenda idea to create a Rank formula field. but you need to build trigger on the Admission object instead of account object. Please find sample code below

trigger applicantStatusChange on Admission__c(after Insert,after update) {
    if (Trigger.isAfter) {
        set<Id> setApplicantId = new set<Id>();
        for(Admission__c iterator : Trigger.New) {
            if(Trigger.oldMap != null) {
                if(iterator.Status__c != Trigger.oldMap.get(iterator.Id).Status__c) {
                    setApplicantId.add(iterator.AccountId__c);
                }
            }
            else {
                if(iterator.Status__c != null) {
                    setApplicantId.add(iterator.AccountId__c);
                }
            }
        }

        if(setApplicantId.size() > 0) {
            list<Admission__c> lstAdmission = [Select Id, Status__c, rank__c, AccountId__c from Admission__c where AccountId__c IN:setApplicantId order by rank__c Desc];
            map<Id, Admission__c> mapAdmission = new map<Id, Admission__c>();
            for(Admission__c iterator : lstAdmission) {
                if(!mapAdmission.containsKey(iterator.AccountId__c)) {
                    mapAdmission.put(iterator.AccountId__c, iterator);
                }
            }
            list<Account> lstUpdateAccount = new list<Account>();
            for(Id applicationID : mapAdmission.keySet()) {
                Admission__c objAdmission = mapAdmission.get(applicationID);
                Account objAccount = new Account(Id = applicationID);
                if(objAdmission.rank__c = 3) {
                    objAccount.AccountStage__c = 'Your expected Stage Value';
                }
                else if(objAdmission.rank__c = 2) {
                    objAccount.AccountStage__c = 'Your expected Stage Value';
                }
                else if(objAdmission.rank__c = 1) {
                    objAccount.AccountStage__c = 'Your expected Stage Value';
                }
                else if(objAdmission.rank__c = 0) {
                    objAccount.AccountStage__c = 'Your expected Stage Value';
                }
                lstUpdateAccount.add(objAccount);
            }

            if(lstUpdateAccount.size() > 0) {
                update lstUpdateAccount;
            }
        }
    }
}

Please let me know if you have any questions.

All Answers

Brenda S FinnBrenda S Finn
Yes you could do this in a trigger whenever an Applicant or Admission object is updated. You would need to assign a ranking to each Admission Status to make it easier to determine which one is the "highest" status and to avoid hard-coding picklist values in the code. I would suggest the following.

1. Create a formula field on Admission called Rank. Within it, assign a value from 1-n based on StageName. So lets say we define it as follows:
Admission denied : 0
Review pending : 1
In review : 2
Admission accepted : 3
(Im not sure what would be considered a higher stage - Denied or In Review?) Obviously, the ranking is part of the business use case/requirements.
2. Then write a before update Trigger on Applicant object. Query all the Admission records ordered by Rank field and assign the Rank of the first one found to the Applicant. It would look something like this:
trigger Applicant__c on Applicant__c(before update) {
if (Trigger.isBefore && Trigger.isUpdate) {

    for (Applicant__c applicant : [select Id, (select, Status__c from Admissions__r 
             order by  Rank__c DESC) from Applicant__c]) {
        // if this applicant has admissions, pick the first one
        if (applicant.Admissions__r.size() > 0) {
            Trigger.newMap,get(applicant.Id).Highest_Stage__c =      
                applicant.Admissions__r.get(0).Status__c;
        }
    }
}
Hope that helps. Let me know if you need more information/clarification.

 
Jose María Nicolás ArfelisJose María Nicolás Arfelis
Brenda, thank you so much for your answer. Good idea! :). But how could this trigger change the status of the applicant?.
For example let's say the applicant has two applications, one with stage "Admission denied" and one with "Admission accepted". In this case "Admission accepted" would be the highest level or would win, so the status of the applicant will have to change according to the highest level in his applications.

For example and as mentioned: if the stage in the object Admisión is "Admission accepted", the status of the applicant should go from "Admission inquiry" to "Admission accepted".
If the stage in the object Admisión is "Admission denied", the status of the applicant should go from "Admission inquiry" to "Admission denied".
When the stage is either "Review pending" or "In Review", the status of the applicant should be "Admission Inquiry".
Brenda S FinnBrenda S Finn
Good morning. So because it is a before trigger, there is no need o execute an explicit DML statement. So on lines 8-9 in the sample code   I gave you, I am setting the Status of the Applicant to the Status value from the Admission object with the highest value in  Rank field. Then after the trigger executes and all other operations are performed, SF will commit this change to the database for you.

Does that answer your question?
(and I just noticed a syntax error: the Trigger.newMap, should be Trigger.newMap.)

Finally - to answer your question about the statuses of the Admissions , you need to setup that formula field so that way your Admissions objects are returned with the highest Rank value first.
Jose María Nicolás ArfelisJose María Nicolás Arfelis
Thanks Brenda. I understand your answer this way: The statuses of the applicant (the Person Account object has a field Status in it) should be "Pending Review", "In Review", "Admission accepted" and "Admission denied", that means, Status would have the same values as the Stage field in the Admission object?. Aditionally, the applicant status would have to be contained in the object Admisión as well?.
Brenda S FinnBrenda S Finn
Jose -

That is correct. The status field on Applicant would be set to the same value(s) that are defined on the Admission object Status field. So yes you will need to create a Status field on Admission in addition to the Rank formula field.

Let me know if you need any more help!
Jose María Nicolás ArfelisJose María Nicolás Arfelis
Actually, it is required the statuses of the applicant shouldn't be the same as the stages in the object Admisión :(. 
As mentioned above:
 
For example and as mentioned: if the stage in the object Admisión is "Admission accepted", the status of the applicant should go from "Admission inquiry" to "Admission accepted".
If the stage in the object Admisión is "Admission denied", the status of the applicant should go from "Admission inquiry" to "Admission denied".
When the stage is either "Review pending" or "In Review", the status of the applicant should be "Admission Inquiry".

That's why I am looking for a way to adjust your code (although I am not a programmer) so this requirement can be also implemented.
Jose María Nicolás ArfelisJose María Nicolás Arfelis
Just to give it a try, I added the custom field "Estado del alumno" (Applicant status) to my custom object Admisión. I created a dependency between the Stage field and the "Estado del alumno". I added and adjusted the trigger:
 
trigger CambiaEstadoSolicitante on Account (before update) {
if (Trigger.isBefore && Trigger.isUpdate) {

    for (Account Solicitante : [select Id, (select Estado_del_alumno__c from Admisiones__r order by  Rank__c DESC) from Account]) {


        if (Solicitante.Admisiones__r.size() > 0) {

            Trigger.newMap.get(Solicitante.Id).Estado__c =     

                Solicitante.Admisiones__r.get(0).Estado_del_alumno__c;

        }

    }

}
}

but this is not working. 
Brenda S FinnBrenda S Finn
Jose

What is happening or not happening?  And did you get an answer to your previous question :

For example and as mentioned: if the stage in the object Admisión is "Admission accepted", the status of the applicant should go from "Admission inquiry" to "Admission accepted".
If the stage in the object Admisión is "Admission denied", the status of the applicant should go from "Admission inquiry" to "Admission denied".
When the stage is either "Review pending" or "In Review", the status of the applicant should be "Admission Inquiry".

I understand your question - the statuses are not a 1-1 mapping so creating a dependent picklist may work, however does that require that the user enter in two values for Admission Status? Another way to handle it is to have yet another formula field that maps the Admission Status value to the Applicant Status value. Then, in your Trigger query, it should be updated as follows (I replaced your Admission Status field with (new formula field Applicant_Status__c which I know you would need to name differently).
trigger CambiaEstadoSolicitante on Account (before update) {
if (Trigger.isBefore && Trigger.isUpdate) {

    for (Account Solicitante : [select Id, (select Estado_del_alumno__c, Applicant_Status__c from Admisiones__r order by  Rank__c DESC) from Account]) {


        if (Solicitante.Admisiones__r.size() > 0) {

            Trigger.newMap.get(Solicitante.Id).Estado__c =     

                Solicitante.Admisiones__r.get(0).Applicant_Status__c;

        }

    }

}
}

 
Jose María Nicolás ArfelisJose María Nicolás Arfelis
Sorry, even with the formula field "Estado del alumno" (Applicant status) based on the stage field in the Admission object, the trigger doesn't work, the Applicant status in the Person Account object remains always the same, the status doesn't change :(.
Maybe the idea was not understood (sorry, if I am wrong):

- If an applicant has for example 3 admissions. One with "In Review", another with "Admission denied" and another with "Admission accepted", so the stage with the highest level would win, in this case "Admission accepted". The Person Account status should then have the status "Admission accepted". So, it is about two objects: Admisión (Admission application) and the Person Account object. One depends on another. 

 
Brenda S FinnBrenda S Finn
Jose

Good evening. Well I noticed one error in the query which does not explain why the records are not being updated but you need to add a filter ot the query. Change line #4  from
for (Account Solicitante : [select Id, (select Estado_del_alumno__c, Applicant_Status__c from Admisiones__r order by  Rank__c DESC) from Account]) {
to
for (Account Solicitante : [select Id, (select Estado_del_alumno__c, Applicant_Status__c from Admisiones__r order by  Rank__c DESC) from Account where Id in:Trigger.new]) {
You were not filtering to only look at Accounts that were updated.
Actually we need this trigger to be defined on the Admission object, NOT the Applicant object. My bad. You are right - it will not fire except when the Applicant object is updated. Now your trigger will fire whenever the Admission Status value is changed to a non-null value.
So your trigger should look something like this:

 
trigger CambiaEstadoSolicitante on Admission (before update) {
if (Trigger.isBefore && Trigger.isUpdate) {
    // first lets get list of Accounts for our changed Admissions objects
    Set<Id> setAccountIds = new Set<Id>();
    for (Admission admission : Trigger.new) {
        // if Admission Status has changed
        if (admission.Estado_del_alumno__c != Trigger.oldMap.get(admission.Id) && 
            admission.Estado_del_alumno__c != null)) {
              setAcctIds.add(admission.Account__c);
        }
    }

    List<Account> lstAccts = new List<Account>();
    for (Account acct : [select Id, (select Estado_del_alumno__c, Applicant_Status__c from Admisiones__r order by  Rank__c DESC) from Account where Id in:new List<Id>(setAcctIds)]) {
        // if this Account has multiple Admissions, get the one with highest rank and set Account Status to its ApplicantStatus field value
        if (acct.Admisiones__r.size() > 0) { 
            Account newAcct = new Account(Status = acct.Admisiones__r.get(0).Applicant_Status__c, Id=acct.Id);
            lstAccts.add(newAcct);
        }
        if (!lstAccts.isEmpty()) {
            update lstAccts;
        }
    }
    }
}


 
Jose María Nicolás ArfelisJose María Nicolás Arfelis
Thanks Brenda. Without programming knowledge in APEX I am unable to find the cause why I can not save the trigger and understand what you have written in some parts of the code.
I will give it a try:
 
if (Trigger.isBefore && Trigger.isUpdate) {
03
	    // first lets get list of Accounts for our changed Admissions objects
04
	    Set<Id> setAccountIds = new Set<Id>();
05
	    for (Admission admission : Trigger.new) {
06
	        // if Admission Status has changed
07
	        if (admission.Estado_del_alumno__c != Trigger.oldMap.get(admission.Id) &&
08
	            admission.Estado_del_alumno__c != null)) {
09
	              setAcctIds.add(admission.Account__c);
10
	        }
11
	    }
12
First on line 4 you are declaring a variable to get the Person Account Id, right?.
On lines 6 and 7: it is not admission status, but admission stage. The stage will have to change and depending on it the Person Account status will have to change, so Admission stage and Person Account status are two different fields, otherwise I get confused ;).

On line 7 you probably mean "admission.Etapa__c" instead of "admission.Estado_del_alumno__c". And what you probably mean there is, if the Admission stage has a new value and this is not null.

On line 9: I didn't understand what you want to achieve on this line. Anyway I replaced Account__c by Nombre_de_la_cuenta__c, which would be the master-detail field to the Person Account within  the Admission object (API Admisi_n__c).

Unfortunately lines 7 - 11 are causing an error: Comparison arguments must be compatible types: String, Admisi_n__c.

Then:
 
List<Account> lstAccts = new List<Account>();
14
	    for (Account acct : [select Id, (select Estado_del_alumno__c, Applicant_Status__c from Admisiones__r order by  Rank__c DESC) from Account where Id in:new List<Id>(setAcctIds)]) {
15
	        // if this Account has multiple Admissions, get the one with highest rank and set Account Status to its ApplicantStatus field value
16
	        if (acct.Admisiones__r.size() > 0) {
17
	            Account newAcct = new Account(Status = acct.Admisiones__r.get(0).Applicant_Status__c, Id=acct.Id);
18
	            lstAccts.add(newAcct);
19
	        }
20
	        if (!lstAccts.isEmpty()) {
21
	            update lstAccts;
22
	        }
23
	    }
24
	    }
You are declaring a list, a group of records of the Person Account object probably.
On line 14: You have written
Lines 14-19: retrieve the records "select Estado_del_alumno__c, Applicant_Status__c", but the only Applicant status field in the object Admisi_n__c is the Estado_del_alumno__c and it is a formula field. So I have then written "select Estado_del_alumno__c...", I haven't used Applicant_Status__c.

Thanks for your efforts.







 
sathishkumar periyasamysathishkumar periyasamy
Jose, I like Brenda idea to create a Rank formula field. but you need to build trigger on the Admission object instead of account object. Please find sample code below

trigger applicantStatusChange on Admission__c(after Insert,after update) {
    if (Trigger.isAfter) {
        set<Id> setApplicantId = new set<Id>();
        for(Admission__c iterator : Trigger.New) {
            if(Trigger.oldMap != null) {
                if(iterator.Status__c != Trigger.oldMap.get(iterator.Id).Status__c) {
                    setApplicantId.add(iterator.AccountId__c);
                }
            }
            else {
                if(iterator.Status__c != null) {
                    setApplicantId.add(iterator.AccountId__c);
                }
            }
        }

        if(setApplicantId.size() > 0) {
            list<Admission__c> lstAdmission = [Select Id, Status__c, rank__c, AccountId__c from Admission__c where AccountId__c IN:setApplicantId order by rank__c Desc];
            map<Id, Admission__c> mapAdmission = new map<Id, Admission__c>();
            for(Admission__c iterator : lstAdmission) {
                if(!mapAdmission.containsKey(iterator.AccountId__c)) {
                    mapAdmission.put(iterator.AccountId__c, iterator);
                }
            }
            list<Account> lstUpdateAccount = new list<Account>();
            for(Id applicationID : mapAdmission.keySet()) {
                Admission__c objAdmission = mapAdmission.get(applicationID);
                Account objAccount = new Account(Id = applicationID);
                if(objAdmission.rank__c = 3) {
                    objAccount.AccountStage__c = 'Your expected Stage Value';
                }
                else if(objAdmission.rank__c = 2) {
                    objAccount.AccountStage__c = 'Your expected Stage Value';
                }
                else if(objAdmission.rank__c = 1) {
                    objAccount.AccountStage__c = 'Your expected Stage Value';
                }
                else if(objAdmission.rank__c = 0) {
                    objAccount.AccountStage__c = 'Your expected Stage Value';
                }
                lstUpdateAccount.add(objAccount);
            }

            if(lstUpdateAccount.size() > 0) {
                update lstUpdateAccount;
            }
        }
    }
}

Please let me know if you have any questions.
This was selected as the best answer
Jose María Nicolás ArfelisJose María Nicolás Arfelis
Thank you so much, Satthish. It works!.
Brenda, Thank you as well, you made efforts to help me to solve this requirement. I really appreciate.
I learned something new these days.
 
Jose María Nicolás ArfelisJose María Nicolás Arfelis
Guys, I was also wondering this:

How would the Application stage be changed? if the end user would change it or not...at the moment I think they should do it manually. My worry is, what about if they receive thousands and thousands applications, they wouldn't be able to change the stage for every application. Did you have any experience in a similar situation?. Is there any other way to make this automatically, so the end users don't need to change the Application stage manually?.

The another question is, other states of the applicant would be "Preinscrito" and "Matriculado" (Enrolled). There are two other custom objects "Preinscripción" and "Matriculación" for this with fields Stage in every object. Here I would also apply what you implemented, but in this case, one trigger for "Preinscripción" and one trigger for "Matriculación". Would there be conflicts between the trigger for Admission and these other two triggers? would this approach be optimal?.