Triggering Emails Based on Field Status

Hi Mark!

I've gone back and cleaned the app up a bit... or rather rebuilt it now that I've had more time to think it through to clean it up and remove redundancies.

I have two tables, "Flights" and "Request Log."  Same concepts, the different flights get listed in "Flights." There is a report in "Flights" for "Current Active" flights.  "Current Active Flights" will be a lookup in "Request Log."

I have a relationship where the Record ID in "Flights" is looked up in the "Request Log" and a Report Link from "Flights" to "Request Log" on that Record ID.

I also have a mini form created in "Flights" that pulls the passenger records in "Request Log" using the Report Link field.

The notification email I have set up is set to trigger when the Flight Status is changed to "Final."  Right now, it pulls any passenger in the "Request Log" associated to the Record ID report link...  I'd like to take it a step further and pull all passengers from the "Request Log" with that Record ID, but also only those passengers that have "approved" in the "Passenger Status" field.

Essentially, we'd take a reservation and the passenger would have to go through a clearance process, like TSA.  If they are approved and the flight is "Final" then those names be entered in that notification email.  However, if they are "Denied" at TSA, then I don't want that record to show up in the automated email.  I want the email to only show approved passengers.

Would creating a report with only "Approved" passengers be best in the "request log" table or maybe a concatenated formula field that combines the status of the passenger and the record ID?

Thank you!

OK great, your setup sounds correct now.

When you have a report link field, the Field Properties setup of the field takes care of making sure that only passengers on that flight end up on that embedded report. 

The report name that shows on the form properties might currently be called "Embedded for Flights".  I personally never allow that name to remain because the system auto generates so many of those "embedded for " reports that you can't tell which are actually in use.  So I locate that report in the report list or a better navigation technique is to go into form properties and click the blue link on the panel beside the report link field  called "Change settings" and edit the report to called it "Used on Flights (ie name of Parent table) "   and then you can select your columns for the report, its sort and to finally answer your question, the filters. 

So just filter that embedded report to only include Approved Passengers.

Perfect.  Thank you!

Hi Mark,

Right now, I have the "Flight" table and "Request Log."  There's a relationship to bring the Record ID from the "Flight" table to the "Request Log" table and report link to tie the two together.  I have a notification email that triggers when the flight status on the "Flight" table is changed to "Final" and pulls the passenger list from the "Request Log."

On the "Request Log" table, I also have two other fields; Seat granted (checkbox) and email address.  I'd like to also send an email to the address in the email address field when the "seat granted" checkbox is checked AND the "flight status" is changed to "Final" on the "Flight" table.  

I'm tripping up how to trigger that email, but yet email the address in the Request Log table.  Because, if the trigger is saving the status to "Final" on the Flight table, I'm not sure how to send the email to the individual listed in the "Request Log" table.

As you may have already found out. You cannot trigger individual email notifications on the child table by a change in a look up field, which comes from a parent.  

But there is a pretty straightforward solution.

Create a date time field on the child record called ]date time to trigger email{. 

Then set up a pipeline which is triggered by the status change on the Parent Record to search for all the children and update each of those records in a for each loop I updating that field to the current date time. The easiest way to get the current time to populate that field would be to create a field on the Parent Record called current date time with the formula of

Now()

Feel free to post back if you have any problem setting up the pipeline.

OK so first I create two fields:

1. In the child table called "date time to trigger email"
2. In the parent table called "current date time" with formula of Now()

Then I create a Trigger Pipeline that:
A. Record Updated that is not triggered on any field, but triggered on "Flight Status" Advanced Query = "Final" (not sure if this is right, but I'd like to specify that it is triggered when that field has "final")

B. Then Update record.  For some reason it won't let me pick the child table, it only shows this:



If I get the logic, and above did work, I'd update the child table "date time to trigger email" with the info in the Parent table "current date time."  

Do I set up another pipeline that triggers an email when the Child table is updated in "date time to trigger?  Or what would trigger the child table records having emails sent out?

Thank you!

The first step, step A is the trigger step. It will be set to trigger when the flight status changes and then you need to use the blue button to Add Conditions.  You will need to list the flight status and the correct date time field as fields to be used in subsequent steps.  You do not need to use an Advanced Query, just the blue button to add conditions.  Make sure that you set the trigger to only trigger when the flight status changes, else other edits could trigger the emails.

Then the next step will be a search step. You will specify fields to be used in subsequent steps as the Related flight and also the  date time field to trigger the email.   Limit the search to where the related flight is ... and then drag down the record ID from step A  into the box.  In other words you just want the children of the specific trigger flight.

It will give you a For Each loop automatically after the Search.  Then drag across  an update Record step in the for each loop to update the children to set the date time trigger for email to the current date time dragged down from the step A.

Hi Mark,

Appreciate you walking  me through the steps more.  Pipelines are still pretty new to me.

I've been able to do the first part.  Here is my Step A:


For B, this is what I've done so far:  Is this correct so far?

When you say, "limit the search to where the related flight is ... and then drag down the record ID from step A  into the box," do I do that with a condition?  The limit box there only specifies a number.  

When you say, "It will give you a For Each loop automatically after the Search.  Then drag across  an update Record step in the for each loop to update the children to set the date time trigger for email to the current date time dragged down from the step A" I'm guessing that means in this portion below to add a sub-step to update the record related to that Rlated flight.


Thank you!

OK, so on the Search step use the blue button to ADD Condition for the field   related flight  

Then set that where it equals and then drag down the Record ID from step A into the box.

And in the for each loop step you want to drag across update record and update the field for date / time to trigger email.   Once you get that Fields box showing you would drag the current date time field from step eight into that box. 

Note that once you are ready to run the pipeline if you cycle the pipeline on and off it will pop up a box that will give you kind of a play-by-play and you can leave that up in one screen while you go to another tab and update the flight closeout to the value   Final  and then flip back to the Pipelines tab where you can watch the pipeline run.