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
-
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. -
Thanks for this. I'll give it a go and post back. Cheers :)
-
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 OKThank you for your continued help.
Brian
-
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.
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.
Once the exam is recorded.. You see..
Here is the code for the Previous Result formula..
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.ResultHere is the code for the Status ...
let t := this;
if text(t.'Exam Type') = "Weight" then
if 'Previous Result' > t.Result then
styled("Warning", "Red")
else
styled("OK", "Green")
end
endI will let you add the logic for the other Exam Types that you may like to record.
-
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.ResultBut I get an error, as follows:
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
-
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
-
the opening square bracket in front of contact.id is missing
-
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?
-
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!
-
That's fantastic. Thank you SO much. My email address is visionavenger@outlook.com.
Look forward to hearing from you.
Thank you again. :)
-
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
-
@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.
-
@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
-
@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.
-
@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.
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?
-
@Brain,
Also.. what would the "warnning" notice be? Would a simple rating as to the "column" be OK?
-
@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..
-
@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
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:
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
- 6247Views