Help with calculations

[Kitty]

Please help. I have attached a sample file. Please see below the 2 requirements. This is for helping children and ensuring their height does not decrease as each visit to the doctor. If their is a decrease in height, it will be flagged.

Requirement 1:

The program is to flag any record in which the value of height decreases for the same patient as date progresses.

(A patient should never have a height less than what has been recorded in the database).

Requirement 2:

If the user enters a height value less than what has been recorded for the same patient, a pop up message will alert the user to

please check data and will not allow the user to proceed until the height value is greater or equal than what was already been recorded

for the same patient in the prior doctor visits.

thank you.

height_testing.zip

[Peter (duksis3)]

I can solve this via Max and Min height - add 2 summary fields to get values.

Flagging via conditional format.

Pop-up message you can get via data validation in field - data must be higher than Max if height is increasing.

After a while I try to write more details.

[Kitty]

Please note. The minimum and maximum height values varies by patient. Therefore, i can not implement a range constraint to the variable. I just need to flag the record in which the height value decreases for the same patient.

[comment]

Try it this way?

height_testing.fp7.zip

[Peter (duksis3)]

Of course - you need to calculate Max and Min for each patient. Use self-joined relationship.

[comment]

Why don't you post a demo file of what you mean - I don't get it myself.

[Peter (duksis3)]

Hi,

OK. Be patient:)

I hope I can manage to do it tomorrow.

[Peter (duksis3)]

Here I make a sample.

There are self-joined relationship, field script triggers and conditional formatting.

If I have understand your question correctly. I hope.

height_testing_offer.fp7.zip

[comment]

I see a Max summary field, but I don't see where it's being used...

Anyway, it doesn't work when you edit existing values.

[Peter (duksis3)]

Yes - Max isn't necessary at moment. Delete it if you want.

Not working?

What filemaker you are using? Sample is for FMP10 - script triggers is used.

[comment]

Here is your file, untouched, except editing some values. Note that record #3 is NOT flagged, but record #4 IS - both incorrectly. Done in version 10.v3.

height_testing_offer.fp7.zip

[Kitty]

Thanks. But i noticed that it works great as you continue entering data but it does not flag the values based on date values when height decreases. In other words, if the user enters a date value less than all the date values that have already been entered, the program does not flag it. It only flags based on the height value of the previous record entered.

-FYI: We enter records as it is given to us. If a record has a height less than dates already recorded, then it gets flagged. Currently it only flags based on the previous record value. We hope it can flag based on the height decreasing for any date values already entered. Help! Thanks so much.

[Kitty]

I am using FM10 on Windows.

[Kitty]

For Example

400 12/23/09 69

400 1/2/2010 67 Flag Yes

400 1/5/2010 68 Flag Yes because ht less than the ht 69 that was entered on 12/23/09

[comment]

I thought you only wanted to flag the records where the height actually decreased. Anyway, it's a simple adjustment. Note that you can use conditional formatting as "flag".

height_testing.fp7.zip

[Kitty]

No luck. I added a few records. for example I added:

400 1/23/2010 ht=81 and it did not get flagged.

I had previously added patient 400 with ht=82. The record with ht=81 should have been flagged since ht decreased from the maximum ht that has already been recorded. I attached the file for your review.

thanks so much!

height_testing_added_records.zip

[LaRetta]

The last 400 record you entered (which breaks) has date of 1/23/2010 but the prior 400 record has 1/28/2010. Recall your graph has date > comparison. Change that last record to 1/31 and see it work as designed. :wink2:

[Kitty]

HHHMMMM. the thing is that we enter records here in any order. We accumulated the documents and then enter them.

therefore, if a patient with ht 82 for Jan 29 is in the db and someone then enters ht 81 for Jan 23, the program should still flag the record with ht 81 on Jan 23 because it is less than the maximum value of ht recorded for that particular patient.

The ht value needs to be flagged when its value decreases. This will alert us weather the child may have a deformation or if it is data entry error. Thanks again. Any ideas?

[LaRetta]

So that date is not the date the person was measured? You said in your file, "The program is to flag any record in which the value of height decreases for the same patient as date progresses." If you aren't tracking the date the measurement was taken (or even using an auto-enter creation date) then how will system know? And the example entries you listed have no decrease in date.

If you don't restrict to only dates greater than the prior, all of your entries will flag as yes except for the maximum one (try removing the date from the relationship). So all older entries will flag yes as soon as the measurement increases.

And even your example of...

400 12/23/09 69

400 1/2/2010 67 Flag Yes

400 1/5/2010 68 Flag Yes because ht less than the ht 69 that was entered on 12/23/09... specifies that.

I'm afraid your request isn't making sense to me (I'm sure my fault). If date doesn't matter, why not just flag max height only? You will only appreciate the concept of 'change in height' if it involves change over time. :laugh2:

Edited January 15, 2010 by Guest
Added sentence

[comment]

First, I need to ask if you understand how it works. Because if you do, you should see that it would be trivial to change it to check previously entered records by creation order, instead of by date.

However, your plan seems to have a flaw: suppose you enter a child's height in February as 85. Next you enter its height in January as 86. This is higher than all previously entered data, so no flag and no questions asked - but the end result is that the child's height in February decreased compared to the measurement made a month ago.