Update custom object upon opportunity stage change

Question

I recently managed, with your help, to list available real estate properties (a custom object) based on its linked Opportunity StageName. Now I noticed that this method will eventually return false positives as some properties will be linked to both 'Closed Lost' and 'Reserved' Opportunities at the same time.

I think a better approach would be to have an 'is available' field on the custom object that gets updated every time a linked opportunity changes its StageName. Let's say the field starts as 'Yes' by default and when a liked opportunity is created with "Reserved" the Property.is_available is changed to "No". Stages "Reserved","Under Contract","Closed Won" would trigger a "No" on Property.is_available while Stages "Evaluating", "Verbal Acceptance" and "Closed Lost" would trigger a "Yes" on Property.is_available.

Would this be the right approach? Is it possible?

It is similar to this question but the business model is quite different.

---

EDIT: I tried to accomplish this using workflows but if I start the workflow using Property as the object, it won't list any Opportunity field in the 'Run this rule if the' dropdown options:

If instead I start the workflow from the Opportunity object, I can manage to have the triggering criteria set...

...but then I can't reach fields on the Property__c custom object:

Answer 1

You're not going to be able to thoroughly cover this need with workflow rules alone. You're basically trying to build a programmatic rollup summary.

I would suggest you give Declarative Lookup Rollup Summaries a go. What you'd do with DLRS is define a Rollup Summary where the Parent Object is Property and the child object is Opportunity. You'd set your Relationship Criteria to StageName IN ('Reserved','Under Contract', 'Closed Won'). Rollup type would be Count, and you'd pick a Number field on the Property to roll up to. Set the rollup to run in Realtime, System mode, and deploy the child trigger; then run a calculate job to populate current records.

The result would be a Number field on the Property counting the number of related Opportunities that meet criteria. You could then create a Text Formula field along the lines of

IF(Count_of_Opps__c > 0, "No", "Yes")

for your "Is Available" field.

---

If you cannot or prefer not to use DLRS, you would need to use an Apex trigger on the Opportunity object. You'll find lots of examples on SFSE. The reason you need to use a trigger is that only triggers can actuate on a record deletion. You cannot do that with Workflow Rules or with Process Builder & Flow.