0

ALERT when value drops

Our database is being used to measure orphans in Africa.  Each child is weighed every week or month, and we would need the database to immediately alert the user if the weight of a child drops, eg, last month, they weighed 20 kilos but this month, they weigh 18 or 19, etc.  Is there a way to provide immediate feedback to a user who enters a value if it's less than the previous value for a contact's previous record?

Thanks

Brian

19 replies

null
    • Roberto
    • 5 yrs ago
    • Reported - view

    I hope to help.
    1. Create a calculated field
    2. In the formula, insert an IF condition
    Maybe I can get the example base I've created so you can see it better.

    • Roberto
    • 5 yrs ago
    • Reported - view
    • visionavenger
    • 5 yrs ago
    • Reported - view

    Thanks for this.  I'll give it a go and post back.  Cheers :)

    • visionavenger
    • 5 yrs ago
    • Reported - view

    Sorry for the delay in coming back to you.  My mother took ill and passed away last month.  I am only returned to this project now.

    Thank you for your help so far.  I created a Calculated Field and have gone into the Formula Field, but I'm not sure exactly how the formula should work.  Perhaps it's easier to work with the Text version.

    I was thinking it would be something like this, but I'm not sure how to tell the database to compare THIS record's Weight field against the Previous Weight field. Would it be something like this?
    IF Weight < Weight then ALERT else OK

    Thank you for your continued help.

    Brian

    • Mconneen
    • 5 yrs ago
    • Reported - view

    Sorry for your loss.   

    How about something like this?   First.. create a Perons table.. then create a Health Check table.. and add a composite relationship from Health Check to Person. 

    relationship

    When entering the results of an exam, there are two formulas.. One is getting the Previous Result of this exam type (Weight, BP, Heart Rate, etc..)   The other is providing a status, based on exam type.. In your case.. For a Weight exam, it is checking if the Previous Weight is greater than the current weight .. display a warning .. else display OK. 

    Exam

    Once the exam is recorded..   You see.. 

    Person

    Here is the code for the Previous Result formula.. 

    code1

    let t := this;
    let prev := last((select 'Health Check')[Person.Id = number(t.Person.Id) and 'Exam Date' < t.'Exam Date' and 'Exam Type' = t.'Exam Type'] order by 'Exam Date');
    prev.Result

    Here is the code for the Status ...

    code 2

    let t := this;
    if text(t.'Exam Type') = "Weight" then
        if 'Previous Result' > t.Result then
           styled("Warning", "Red")
      else
            styled("OK", "Green")
      end
    end

    I will let you add the logic for the other Exam Types that you may like to record. 

    • visionavenger
    • 5 yrs ago
    • Reported - view

    Hi Mconneen

    Thank you for your detailed help. I already have a table called 'Contact' and a table called 'Health', so I modified your formula to account for the names, as such:

    let t := this;
    let prev := last((select 'Health')Contact.Id = number(t.Contact.Id) and 'Date-Assessed' < t.'Date-Assessed'] order by 'Date-Assessed');
    prev.Result

    But I get an error, as follows:

    181201_Ninox

    I don't suppose you would have a look at this, if we paid you to get it working, would you?  Do you do any consultancy?

    Thanks in advance

    Brian

    • visionavenger
    • 5 yrs ago
    • Reported - view

    PS: We need quite a second alert based on the World Health Organization's height/weight rations, eg, if a child is 60cm tall, then it should weigh 6Kg.  If it's under that, then it another alert needs to appear.  This is quite a complex chart that measures the height-to-weight ratio from birth to 5 years, mapping what a child should weigh based on its height (refer http://www.who.int/childgrowth/standards/chts_wflh_girls_z/en/).  Would it be extremely difficult for Ninox to do some sort of Lookup?  I know Microsoft Access was able to do lookup tables, but I'm not sure whether Ninox could do something similar?

    Cheers

    Brian

    • Tacho
    • 5 yrs ago
    • Reported - view

    the opening square bracket in front of contact.id is missing 

    • visionavenger
    • 5 yrs ago
    • Reported - view

    Thanks.  That fixed it.  But I don't see a reference to weight in the Formula?  How is it checked the previous weight against the current weight that's been input?  Is it possible to get a pop-up alert, or is color-coding a field the only way to display an alert?

    • Mconneen
    • 5 yrs ago
    • Reported - view

    Brian.. I will email you a link to the sample database.  Yes.. an alert is  possible.. Yes.. a look up is possible.  Let me review the link you provided and I will see what I can do for you.   Thanks for your dedication to the children's health! 

    • visionavenger
    • 5 yrs ago
    • Reported - view

    That's fantastic.  Thank you SO much.  My email address is visionavenger@outlook.com.

    Look forward to hearing from you.

    Thank you again. :)

    • visionavenger
    • 5 yrs ago
    • Reported - view

    Hi Mconneen

    Just wondering whether you had a chance to look at the LOOKUP Table issue?  If you're very busy, would you have a guide I could reivew to see whether I could try to have a go at it?

    Thanks so much for any further help you can provide

    Brian

    • Mconneen
    • 5 yrs ago
    • Reported - view

    @Brian

    I briefly reviewed the tables (https://www.who.int/childgrowth/standards/WFL_girls_0_2_zscores.pdf?ua=1) . It seems you would want to do a range lookup.. That is more than possible. I will not have time to look at this until after the Christmas Holiday .. perhaps I can get to it the first of the year.

    • visionavenger
    • 5 yrs ago
    • Reported - view

    @Mconneen

    You're very kind.  Thanks so much for you offer to help!  If you're too busy, I'm happy to give it a go, and you could then fix any blunders I make (LOL).  Either way, I greatly appreciate all the help you've provided so far and for all that you're continuing to provide.

    I hope you have a great Christmas break, and I look forward to any update you have in the new year.

    Kind regards

    Brian

    • Mconneen
    • 5 yrs ago
    • Reported - view

    @Brian.. I have a soft spot for folks in the public / medical sector.  My better half is an R.N.  The daughter has a Masters in Publich Health (MPH) and works for a non-profit.. The Pops was a fireman/civil servent.. ;)  Will try to get to it. 

    • Mconneen
    • 5 yrs ago
    • Reported - view

    @Brian, 

    I sent you an email...   Here is a bit more detailed question.. 

    Please advise as to how you intend to use this table.   Following is a snapshot from the girls table. 

    whoTable

    Here is what it conveys.. Please review and advise... 

    A value that falls within the (1) range.. would be considred GREEN .. 

    A value that falls within the (2) range .. would be considred YELLOW .. 

    A value that falls within the (3) range.. or under the lowest or over the greatest .. would be considred RED. 

    Thoughts? 

    • Mconneen
    • 5 yrs ago
    • Reported - view

    @Brain, 

    Also.. what would the "warnning" notice be?  Would a simple rating as to the "column" be OK?  

    whoTable2

    • Mconneen
    • 5 yrs ago
    • Reported - view

    @Brian..  To make it a bit more confusing...  I am reading through 

    https://www.who.int/childgrowth/standards/readme_wflh_exp.pdf?ua=1

    It says "Weight-for-length and weight-for-height charts are separate...."  and also references a break point range.. So.. I am a bit confused as to which table to use..  Further.. there are separate charts for infants.. 

    My inital thought would be a "between" type query .. so using the above 45.0 row... There would be multiple rows.. something like (but not exactly).. 

    45.0,0.000,1.736,SD4-

    45.0,1.736,1.902,SD3-

    The query would find where Length is equal to measured length.. and measured weight is greater than or equal to min weight and less than max weight ...   and return the indicator..   So, if the child was 45.0 and weighed 1.801 kg.. the query would return SD3- as the weight is "between" the ranges min / max. 

    Please look for my prior e-mail and perhaps we can take this offline and schedule a Skype call to surface the user story requirements.. 

    • Mconneen
    • 5 yrs ago
    • Reported - view

    @Brian.. I sent you an email with some additional questions.   I have the this table built in Ninox.. 
    https://www.who.int/childgrowth/standards/sft_lhfa_girls_z_2_5.pdf?ua=1

    HeightAge

    Per the link.. Do you want to see the "alert" color coded?    Are any of your users color blind?   Which negates color coding.. :) 

    Per the email.. I could also build you reference tables for the other provided WHO indicators:

    indicators

    Using these tables.. I believe this negates the initial requirement of an alert when the weight drops / gains from prior weight..  Provided that the value is within the acceptable range.. (SD0 +/- SD) ..   no alert.  

    Please review your email and reply..   

    Merry Christmas and Happy Ninoxing.. :) 

Content aside

  • 5 yrs agoLast active
  • 19Replies
  • 6242Views