Complex Formula maybe?

Question

I understand what I want to do, and some of what I need to do - I just can't seem to implement it.

We have many tables.
We have 2 tables in particular that already have a relationship called Water Systems and Samples.
Water Samples are input and assigned to water systems.
We get a notification whenever a sample fails - which is great.
However, when a sample fails, we're required to take another sample some weeks/months later.
We would like to be notified when a sample comes back passing again once a sample has already failed.

So,&nbsp;
take sample&gt;failed&gt;take another sample&gt;passed after recognized failure&gt;send notification.

Ideas?

------------------------------Savi Newman------------------------------

markshnier__you · Answer

I think that this is more of a relationship question than a formula question.&nbsp; You have a relationship where one water system has many water samples.
Do you want to fire a notification where the second most recent water sample was a fail but the most recent sample notification was a pass.&nbsp; I will make the assumption that they sample at most once per day.&nbsp;&nbsp;
On the Relationship, make a Summary Maximum of the date of the sample where the pass/fail is equal to fail.&nbsp; Call it [Most Recent Failure Date].&nbsp; Lookup that field down to the detailed samples children table.&nbsp;
Make another summary field of the minimum of the sample date where the pass fail is equal to pass and also subject to the condition that the sample date is greater than [Most Recent Failure Date].&nbsp; Call this [Date of 1st Pass after a Fail]&nbsp;
Then simply set up a notification to Fire when the pass/fail is equal to pass but the [Sample date] equals the date in the field [Date of 1st Pass after a Fail]

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

donlarson · Answer

Savi,

Here is an architecture to support getting Notifications when a Retest passes.

There are two key pieces:

The Sample Type is is set to Retest and is a Look Up Field in the Sample Status Change Table
You build the Notification in the Sample Status Change Table.

Your Notification is straightforward.
When a Sample Status Change is Added

Related Sample Type = Retest
Sample Status = Pass

Then send the Notification.
This way you can have a hundred samples tested that are Random, Periodic or whatever you call Samples that are not Retests that Pass and no Notification will be sent.------------------------------Don Larson------------------------------

Forum Discussion

Complex Formula maybe?