Make automation wait to run, and include a specific set of multiple linked items

[MyersVx]

Hey folks,

This is a bit of a doozy so hopefully I can make it make sense. To start with, the enterprise I work for has disabled scripting, and 95% of 3rd party addons, so I am looking for a solution to the problem with built in AT functions that don't require either of those.

I have a base that takes in projects from a user. This is done with a form that is available to anyone with the link. The users submitting this form may or may not have air table access. 

When the form is submitted, the "project" (record) it creates needs to be "reviewed" by multiple departments (trades). Each trade's approver gets an email stating they have a project to review with a link to an interface with the pertinent info for their trade. (the trade approvers DO have AT edit access)

Each trade approver has 2 options, to approve the project, or to request more information from the user. If all trades approve an email is sent to the user saying that they are ready to advance to the next step of the project. If instead more information is required (from any of the trades), the approver will indicate that through a note (as a linked record, most likely - potentially using a form to use the trigger "when form is submitted" though I'm open to other options here)

What I need to have happen (via automation) is, once ALL of the trades have reviewed and selected an option, I need to send an email back to the user, that includes ALL of the trades' Information requests.(The person managing this does not want an email sent to the user any time a trade creates a "note" but 1 email with all of the notes after all of the appropriate trades have input their notes.)

Once the User makes their updates (from a second form that takes data and then an automation uses the updates to change the original project record) another email is sent to the trade approvers to again make a choice of approve or again request additional information. There may be multiple rounds of this and at the end of each round the automation needs to check if all approvers have reviewed and then send an email to the user with the "notes" if there are any, and approved to continue if no notes from current round exist.

I thought about just using an automation that makes a list of "note text" from all linked note records. But this won't work as I don't want the old notes going out in subsequent requests for info.

TL,DR: I need to wait for all approvers to do something, THEN send an email with the most recent linked records' note field.

Any help would be appreciated.

[kuovonne]

How are you keeping track of which departments/trades have reviewed the project? How are you keeping track of the approval “rounds”?

[MyersVx]

I am keeping track of the approvals, by creating an “approval” record in a separate table for each of “trades” selected by the user. When an approver “approves” a project, that “approval” record is updated by an automation indicating that (started by the approver clicking a button on the interface).

as far as tracking the rounds of requesting updates, I don’t have a tracker for that yet. Open to suggestions.

[kuovonne]

Is there one approval record for all trades? Or does each trade get its own approval record?

If each trade gets its own approval record, have a single-select {status} field that the approvers set when they review the project--pending, approved, or more info needed. (Make sure they don't set the single-select until after typing in the notes. Alternatively, the {status} field be a formula field.) Then use a rollup field in the project table to roll up the {status} field of the approvals. Trigger the email automation using this rollup field. 

The rollup formula might look something like this:

IF(
  AND(
    NOT(FIND("Pending", ARRAYJOIN(values)),
    FIND("More Info Needed", ARRAYJOIN(values))
  ),
  "Send email asking for more info"
)

[MyersVx]

Yes, each trade gets their own "Approval Record" 

I haven't actually used a rollup field before, so I want to make sure that I'm understanding this correctly.

Are you saying, I need to make a "notes" field in the approvals table, then use the rollup field to make an "array" of all of the notes (using the if statement to only include the "note" field if the status of that record is "more information needed") If so, how do I know when all the trades have responded? Or does the "and" part of that if only create the array of notes if they are ALL ready? then i just trigger the automation when that rollup field is no longer blank?

OR do you mean;

the IF(AND(.... will simply put out "send email asking for more info" if all the approval records are either NOT pending OR "more info needed"  -> then the automation will see to send an email and do so. If that is what you mean, how then are the notes captured?