In the new Production 2.0 Schema improveit 360 has created non-customizable formulas to ensure that the Start and End dates as well as Completion dates on the Project Activities are driving the Project Status, Project Progress and Completion %.
In this Article we will discuss the Project Activity fields and formulas and how these will impact the Project Status, Project Progress and Completion % on the Project object.
Project Status Contributing Fields
First, there are new checkbox fields on the Project Activity to indicate when it is Canceled, Completed, or put On Hold. Users can pull these fields into a List View to interact with them, or create an automation to automatically check the checkbox based on another Object/Field being changed on the Sale or Project records. For example, if the Sale is Canceled, we may want the system to automatically Cancel all Project Activities.
These checkboxes along with the Start and End dates are used to determine the Status of each Project Activity.
Project Activity: Status
IF ( i360core__Completed__c, 'Completed',
IF( i360core__On_Hold__c , 'On Hold',
IF( i360core__Canceled__c, 'Canceled',
IF( i360core__Completion_Percent__c > 0,
IF( TODAY() > DATEVALUE(i360core__End__c) , 'In Progress - Late' , 'In Progress' ),
IF ( TEXT(i360core__Project__r.i360core__Status__c) = 'Canceled', 'Canceled',
IF( TODAY() > DATEVALUE(i360core__Start__c) , 'Not Started - Late' , 'Not Started' ))))))
This formula states that if the Completed checkbox is True give a Status of "Completed", if the On Hold checkbox is True give a Status of "On Hold", and if the Canceled checkbox is True, give a Status of "Canceled." If the Project's Status field is Canceled, give a Status of "Canceled" and if the Completion Percent is greater than zero and the End Date of the Project Activity is greater than Today, give a Status of "In Progress" else if the End Date is less than Today give a Status of "In Progress - Late" and finally if the Start Date is greater than Today give a Status of "Not Started" but if the Start Date is less than Today give a Status of "Not Started - Late".
This means there can only be one of the following Statuses for any Project Activity:
- Completed
- On Hold
- Canceled
- In Progress - Late
- In Progress
- Not Started - Late
- Not Started
Project Activity: Status Enum
This field is used to capture the Minimum and Maximum completion statuses on Project Activities as a numeric value. These MIN and MAX values will be used to determine the overall Project Status.
IF( i360core__Completed__c, 0,
IF( OR (i360core__On_Hold__c, i360core__Canceled__c), -1,
IF( i360core__Completion_Percent__c > 0, 1, 2)))
The formula above is used to set a numeric value to each Project Activity Status which should inform the overall Project Status.
Completed: 0
On Hold: -1
Canceled: -1
In Progress - Late: 1
In Progress: 1
Not Started - Late: 2
Not Started: 2
Here are some example Project Activity records and their Status Enum value:
Next let's look at how these values will inform the overall Project Activity Min and Max and the overall Project Status.
Project: Project Activity Status Min and Max
Roll-up Summary fields which capture the Minimum and Maximum values of all Project Activities. For example, if the Project has an Activity which is "On Hold" and another Activity which is "Not Started" then the value in the Min field would be -1 and the value in the Max field would be 2. However, if none of my Project Activities are On Hold or Canceled but some are Completed, then the Min value would be 0. See the above definition for Status Enum field on the Project Activity.
Project: Status
The following formula field is used to determine Project Status based on the Status Enum of the individual Project Activities on each Project.
IF( NOT( ISBLANK(i360core__Sale__r.i360core__Canceled_On__c)), 'Canceled',
IF( i360core__Project_Activity_Status_Max__c <= 0, 'Completed',
IF( ISBLANK(i360core__Project_Activity_Status_Min__c) || (i360core__Project_Activity_Status_Min__c == i360core__Project_Activity_Status_Max__c && i360core__Project_Activity_Status_Min__c > 1),
'Not Started', 'In Progress')))
The formula states that if the Canceled On date field on the Sale is populated, give a Status of "Canceled" and if the Project Activity Status Min is Blank OR is Equal to the Project Activity Status Max AND the PA Status Min is greater than 1, give a Status of "Not Started." If the Project Activity Status Max is less than or equal to zero, give a Status of "Completed", else give a value of "In Progress"
Here are some examples of the Project Activity Min and Max fields on the Project and how they are related to Project Status:
Note: As of 5/29/25 the Project will show as Completed if the Project Activity Status Max is Blank (meaning there are no Project Activities assigned to the Project). There is an enhancement coming which will make the Status "Not Started" when there are no Project Activities related to the Project.
Here is a look at some individual Project Activities Status and Status Enum:
Project Completion % and Progress Contributing Fields
There are several fields which contribute to the Project Completion % and overall Project Progress. One of the most important factors are your company's Business Hours. Your Project Activity Duration will only count Days and Hours that occur during your company's set Business Hours. So, if your system settings indicate your company does not work on Sundays but one of the Project Activities occurs on a Sunday, that Project Activity's Duration will not be calculated, and will not factor into the Completion % or Project Progress.
Project Activity: Duration Working Days
The number of working days between the start and end date. 1 is added to include the first working day the Project Activity starts. This is dependent on the Business Hours and Holidays setup in the Company Information section of Setup. End Date/Time - Start Date/Time (+1)
Project Activity: Duration Working Hours
The number of Hours between the start and end date. This is dependent on the Business Hours and Holidays setup in the Company Information section of Setup. End Date/Time - Start Date/Time.
Project Activity: Completed Contribution
(i360core__Completion_Percent__c / 100.0) * i360core__Duration_Working_Days__c
Project: Duration Days
Roll-up Summary of Duration Working Days from the Project Activities
Project Activity: Contribution to Project (%)
i360core__Duration_Working_Days__c / i360core__Project__r.i360core__Duration_Days__c
Let's look at an example Project and its Project Activities for real-world calculations of Completion % and Project Progress:
In this example our Completion % is 66.67% because the Installation activity (which has not yet been completed) makes up nearly 35% of the effort on this Project.
The individual Project Activities show that only the Re-Measure and Installation steps on this Project will contribute to the Duration (Hours) field as the other Activities Start and End on the same Date/Time. Note that the Installation Activity spans overnight however, the system is only counting the Hours which occur during the set Business Hours.
Note that the Duration (Days) field counts all Project Activities but excludes repeat days. In this example the Re-Measure and Order Roofing Materials take place on the same date, so the system is only counting that date one time in the Duration (Days) calculation.
Let's take a look at how each of these Project Activities are weighted into the Project Completion %:
I believe my Installation Activity should be weighted higher than just 33% of this Project's Completion. The reason is because my Activities which are merely internal Tasks have a Start Date and Time and End Date and Time, so they are contributing to the overall Working Days and Working Hours of the Project.
In order to clear out the Duration fields (Working Days and Working Hours) so that the Activities do not count into my Project's Completion %, I will need to remove the Start and End dates and times from these Activities.
Note that once I save this change, the Duration fields will update to Zeros.
Now that I have removed Start and End date/times from all the steps which are internal Tasks and not Appointments, let's look at how this has impacted our Completion % on the Project.
Now our Completion % is showing only 33.33% complete, which is a more accurate representation of how far along this Project really is. This means my Installation activity makes up 66.67% of the Project Completion.
Let's look at how these Project Activities each contribute to the overall Project Completion %:
Note the Contribution % to Project is now 0% for all the Activities where we removed the Start and End dates.
Note there is a disadvantage to removing Start and End dates from steps which occur either prior to or after a step which does have Start and End dates. We are no longer able to accurately capture the Calculated Start Date and Calculated End Date as there are steps which are no longer being considered in these Date fields. This Project's first step "Approve Contract" was Completed on 5/12/25 however, the Project shows a Calculated Start Date of 5/16/25, which is not accurate.
Comments
0 comments
Article is closed for comments.