Forum Discussion

SaviNewman1's avatar
SaviNewman1
Qrew Trainee
9 months ago

Complex Formula maybe?

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, 
take sample>failed>take another sample>passed after recognized failure>send notification.
Ideas?


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

2 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    9 months ago

    I think that this is more of a relationship question than a formula question.  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.  I will make the assumption that they sample at most once per day.  

    On the Relationship, make a Summary Maximum of the date of the sample where the pass/fail is equal to fail.  Call it [Most Recent Failure Date].  Lookup that field down to the detailed samples children table. 

    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].  Call this [Date of 1st Pass after a Fail] 

    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's avatar
    DonLarson
    Qrew Commander
    9 months ago

    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
    ------------------------------