We track all support inquiries in Cases for all accounts using Email-to-Case. I have a field on Account object labeled 'Case Status' that I need to populate with either 'Open Cases' or 'No Open Cases' depending on whether that account has cases that are marked 'New'.
I tried using Process Builder but could not figure out how to write a formula for this and/or if this process would be evoked from a single trigger. I am thinking a formula in 'Case Status' would return one of the two phrases depending on whether cases exist for that account or not but need help referencing the case status by account in the formula builder.
Any insight would be helpful! Thanks.
14 answers
Hi Aaron,
You're right to identify the problem here. A formula field can't possibly check child records, which rules it out - but even in process builder, if a case closes, how do you know whether that was the last open case or if there are more?
You're going to need to bring out the heavy guns!
Option 1: You can create a Flow that you call automatically whenever a case is created, or when its status changes (you call the flow using process builder). The flow will query any open cases on the account, and if the result is 1 or more, will set the value of your picklist.
Option 2: You can create a similar Trigger on the Case object. The trigger will run whenever a case changes, is created, or is deleted (which is the advantage of triggers), and perform a similar query to see if any open cases exist for the account.
