For this example, lets say process_id = 5 is a simple form that is filled out by the end user and then goes through 2 approval steps to be approved or rejected. If rejected, I believe the process ends and a new form must be submitted. I am trying to write a query against the LFForms database to determine all forms that have been submitted, completed, and approved. So far I can query the cf_bp_main_instances table to retrieve all those processes. I have determined that status = 2 is completed and not in progress. This however does not tell me whether the form was approved or rejected. I have found cf_submissions has the action that was taken, I can pull the max submission_id per bp_process_instance and report on the last action taken. Or there is a table cf_bp_worker_instances that has a status, but it does not seem to correlate very directly with approved/rejected.
Can someone point me in a direction to better tell when the process has completed and is approved vs rejected? TIA