Process Builder dealing or Comparison between two instances of an Object

Question

Scenario Name: Automating Certification(examination) system using salesforce

sObjects: "Certification" object is the Parent of the "Exam" object. A certification can have multiple Exam types (eg.Exam A, Exam B, etc.) But a single exam type can have maximum 2 entries(2 x Exam A, 2 x Exam B, etc.) An Exam can have Exam_type and Exam_state(Failed,Passed).

Scenario: A person gets two attempts at an Exam, if they were to fail the first attempt, the second attempt is manually set in salesforce as a new Exam entry. No more than 2 exams of the same exam type should exist.

Automation: I want to check if the same Exam type entry exists, and if it is "failed" (within that Certification). If it is, create a new exam and set it to "ACTIVE". And if there are more than 2 "FAILED" exams, not to create a third one.

Failure: I did this automation using a checkbox which is set to true once a new exam is created, then will check this checkbox before creating a new exam. This works fine. But when i put this in to production, the older entries which were done manually do not have the checkbox "checked" at all. Which ends up creating 3rd attempts for certain exams.

Question: How can i do a certain comparison and fix this? Can i compare or set the maximum no. of exams per type to 2? Can this be achieved using the process automation without an APEX code?

Kind Note: Please do not down vote, I will remove my question if any of y'all find it useless or find my question structure not good enough, please comment if so.

Answer 1

Process builder cannot prevent creation of records. One approach is to create a rollup summary field for each exam type in the Certification object, then on Exam object create a validation rule for each type that references the summary field and prevents save if 2.

If Exam only has a lookup relationship to Certification and is not in master-detail, then you can't use the standard rollup summary field and you'll have to use the free Declarative Lookup Rollup Summaries tool instead.

If you add more exam types in the future, you'll have to add a rollup field and validation rule for each type.

The best solution is to write a before insert Apex trigger. The trigger can count how many exams exist of the incoming type and throw an error if there are already 2, so there's no need to create rollup fields or validation rules.