Trigger to Update picklist value based child records picklist value

Question

I have two custom objects Sale__c and Project__c. Both Sale__c and Project__c have different status in a picklist. Sale__c status is broken into two picklist Status__c and Status Detail__c Project has one status proect_status__c.

I am looking to create a trigger that updates the Sales status and status detail to "Net" (status) "completed" (status detail) when all related projects in a sale have a status of "completed" I only want the status and detail to change if the status is already NOT "final" or "paid in full" OR status detail NOT "invoiced"

I have tried to do this with a simple workflow rule and field update, but have not had any luck.

Answer 1

You'd need to first figure out whether or not all projects have status 'completed'. To do that, you need 2 roll-up summary fields on Sale__c.

1. Number_of_projects__c which just counts the number of projects linked to the Sale
2. Number_of_completed_projects__c which counts the number of projects with status 'Completed'

Note that this will only work if the relationship is Master-Detail. If it isn't, you will have to either change it to Master-Detail or revert to an APEX trigger.

Then, based on the contents of the 2 roll-up fields, you can create a workflow rule on Sale that checks the current Sale status and substatus. If those are allright, then check if the 2 roll-up fields are equal (and you might want to check that they are >0 as well). If all of those prerequiresites are OK, then update the Sale status and substatus.