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.