Formula field to be changed when a record gets 'reopened'

Question

I have a custom object named Requirement (a Job) that have several related child objects (Applications, Submissions, Interviews, Placements etc).

On the Requirement object, I have a formula field called 'Sales Pipeline' that displays a number based on the record counts of the child objects (rollup summaries). With this, we can see "where the job is at": Is the job at the Applications phase/Submissions etc

When a job has been filled, the Sales Pipeline field will show 100% of the forecasted revenue.

The issue I am having is that a single job can have multiple Placement records associated with it. After the first placement is made, the User can go back into the same job and reopen it so they can recruit for that job again (ie. adding more Applications).

In this case, the Sales Pipeline field would only show the fact that 1 Placement/Start (100% of Forecasted Revenue) was made and not where the job currently is, which is Applications since the job is now receiving a second round of applications.

Any ideas on how this code could be improved to handle the above case?

Appreciate the help, Carmen

CASE(
    RecordType.DeveloperName,

    "Perm",
    IF( Number_of_Starts__c >= 1, 1 * Forecasted_Revenue_Perm__c, 
        IF(Number_of_Placements__c >= 1, 0.9 * Forecasted_Revenue_Perm__c,
            IF(Number_of_Interviews__c >= 1, 0.7 * Forecasted_Revenue_Perm__c,
                IF(Number_of_Subs__c >= 1, 0.6 * Forecasted_Revenue_Perm__c,
                    IF(Number_of_Applications__c >= 1, 0.5 * Forecasted_Revenue_Perm__c, 0.2 * Forecasted_Revenue_Perm__c
                        )
                    )
                )
            )
        ),

    "Temp",
    IF( Number_of_Starts__c >= 1, 1 * Forecasted_Revenue_Temp_Payroll__c ,
        IF(Number_of_Placements__c >= 1, 0.9 * Forecasted_Revenue_Temp_Payroll__c,
            IF(Number_of_Interviews__c >= 1, 0.7 * Forecasted_Revenue_Temp_Payroll__c,
                IF(Number_of_Subs__c >= 1, 0.6 * Forecasted_Revenue_Temp_Payroll__c,
                    IF(Number_of_Applications__c >= 1, 0.5 * Forecasted_Revenue_Temp_Payroll__c, 0.2 * Forecasted_Revenue_Temp_Payroll__c
                        )
                    )
                )
            )
        ),

    NULL
    )

Answer 1

My recommendation is to change your process. Don't re-open the job, make a new one. Think of it less as a job and more as a job opening and when you need to refill the position you can post a new job opening. You could have a generic job object to act as a parent to all your job opening records that each act as a parent to the applications that are submitted to each new job opening: Job > Job Opening > application. A Job is active when it has an active job opening. This would be a roll-up field most likely. The status field would be on the job opening level.

Alternatively, don't track the status at the job level, track it at the application level. When an application is accepted set the status to accepted and set all other active applications to rejected.

In general re-opening anything causes exactly the problem you are facing right now.