Triggering Emails Based on Field Status

Just wanted to mention one more thing in case others are reading this thread. Many developers who are new to Quickbase assume that the only way to get a Report Link field (which can be used on a form to display records from another table), is by making a relationship. 

it is true that when you make a relationship it automatically gives you a Report Link field. But just like you could go to McDonald's and order the fries without the full meal deal, you can go to Quickbase and just make yourself a Report Link field without building a whole relationship.  Just make the field of type Report Link and configure the left panel to select a field and then configure the right panel as to the target table and field to match that field on the left.

This case the report link field will actually show "grandchildren" so you would probably go ahead and make a brand new report link field on the all flights table to show the embedded report of grand children of request logs.

Hi Mark,

I was able to create the automated email with the Report Link in it!

I'd like to try to embed the table into the email. (Remember I deleted the embedded report before so just created a Report Link field). I read other posts where you suggested creating a "mini form," but I can't get it to work.  I basically copied the original form and have the Report Link in there also.  Then I selected that form in the "Data Form" drop-down of the notification email settings, but when I run the notification email, it only shows the link, but not table.  I did a preview of the form, and do see the table in it, so I'm assuming there's a setting that I'm missing within the notification email set up.  Any ideas?

Thank you!

------------------------------
VW
------------------------------

Here is a pic of the settings I have for the notification email and a preview of the mini form.  Thank you!

------------------------------
VW
------------------------------

When editing the Notification setup, you need to search in the drop down list of fields and markers to select the marker to 

"include a copy of the form"

It will be near the bottom of the list. 

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------

Hi Mark,

I had checked that drop-down before and couldn't find anything that referenced the form.  I checked again and don't see anything.  See snipits below:

Field & Markers Drop-Down:

This was the only place in the notification setup that I could select the form, but I already have that selected and it didn't do anything.

Here are some pics of the Report Link field setup I have just in case:

I noticed when I look at the settings for the multi-record email, it has a different list in the marker drop down.  The only thing that seemed relevant was to add a summary with "%SummaryView%" inserted into the email, which ended up looking like the below picture.  It didn't list the actual information within the Report Link.

------------------------------
V W
------------------------------

Use:
"A copy of the changed record"

Great.  Thank you!

Hi Mark,

I have my "All Flights" table.

Then "Active Flights" table which is a QB Connection to pull the flights that are "active" from the "All Flights" table.  I basically created a formula field that changes the status to "flown" if the date is yesterday and before.

Then I have the "Request Log" table, that has a look up to the "Active Flights" table because I only want them to request a flight that is still "active." The reason for this is because I didn't want the drop down to grow to be a super long list of history that you can't book anymore.

Since the "Active Flights" table is being updated everyday, removing flights that are "Flown," the "Request Log" table is starting to make certain fields for older requests blank.  

Is there a way to preserve the lookup table information once the form entry is saved?  I had thought there was a setting somewhere to check off not to change data, but can't remember where that is or if that function is relevant to this situation.  Thank you!

You can set up snapshot fields. one for each lookup field  

https://helpv2.quickbase.com/hc/en-us/articles/4570403519508-Setting-Up-Snapshots-of-Lookup-Fields-

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------

Thanks!

Is it possible to do a lookup, based on a lookup?

Let's say I have two different tables that both have flight number in it.  If I do a form lookup of the flight number from table #1, is it possible to do a secondary lookup based on the first flight number that was selected, but pull information from table #2?  By doing this, it would reduce having to do two drop-down lookups with the same flight number.

I think I would be better able to answer your latest question if you used your real table names are not table 1 and table 2.

Can you tell me what you're trying to accomplish, what is the business purpose of your question.

Hi Mark!

I'm thinking about the snapshot view a bit more...  To recap...

- The first table is "All Flights" where we enter all new flights.  There is a formula field in there which determines if the flight is "active" or "flown" based on the date; if it's in the past, it changes the status to "flown"

- The second table is a QB Connected table called "Active Flights" which pulls only active flights from "All Flights."  I created this because in the third table, I have a drown down of the flights, but I only wanted to pull the active flights because I didn't want the drop down to become a huge list of flights that are in the past

- The third table is the "Request Log" where the passenger requests are entered to track the reservation.  This is where I have the lookup of the "Active Flights" dropdown.  

In my last post, you helped me with the snapshot field because what was happening is once that flight isn't "active" anymore, it was deleting information from the reservation in the request log related to flight information.  I think the snapshot field is fine for most of those fields, but I would like to be able to update the flight status to show in a reservation that the flight status is "flown" instead of keeping the snapshot that would still say "active" even if it was in the past.  

Because of this, I was wondering if I would need another relationship to "All Flights" just for the flight status so it could be real time if it's active or flown, but I didn't want to have to do two dropdowns for the same flight, in order to pull from two different tables.  This is why I was wondering if it's possible to have one dropdown lookup, but different fields pull from different tables. Maybe a a GetFieldValue or GetRecords formula would work, matching up the Record ID from the "All Flights" table. 

Thank you!

We should take a step back and look at the structure of your application. I don't understand why you need that metal table.

It seems to me that you should have a master table of All Flights which you have.

The Request Logs should be a child of that table.

There is no need for the table of active flights and I think that is tremendously confusing and complicating your app.  

If you want the drop-down list on request log to only show a drop-down list of active flights, then simply make a report in your all flights table of flights with your active and then set the form properties for the drop-down list to use the report of active flights.  

That way you don't need any Snapshot fields at all and you will be able to look back at any time to see what each All Fight's Passengers were.

How do I setup a lookup to a report?

Create a report of just Active flights.  call it "Drop down list of Active Flights".

Then once you create your relationship where one active flight has many request logs there will be a field on the form for either Related Active Flight or the Proxy field like flight number on the form. On the form properties edit that drop down field to use that report that you just created.  

I see.  Thank you!

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.