Finding the smallest value and recorded the date for that record

[Ziphius]

I am trying to calculate a growth rate. I have a table of individuals (parent table) that were measured on multiple days (multiple measurement for each individual = child table). Because the growth rate is so slow, sometimes a later measurement is smaller than a previous one, just because of the accuracy of the measuring tool may not be sensitive enough if two measurements are taken close in time. I want the parent table to have a field showing the largest measurement (max(child:measurement)) and the smallest measurement (min(child:measurement)). Subtracting one from the other gives me the change in growth. I can get this far.

I also want the parent table to show the date of the min measurement and the date of the max measurement so I can subtract the two and divide the time elapsed into the change in growth to get a rate of growth. Can I get the dates using a calculation function or would I need to write a small script? Thanks in advance.

[Fitch]

It would be pretty simple to script it, e.g.

sort by measurement

go to first record, get date

go to last record, get date

do the math

There could be a way to do it by calculation but I can't think of it offhand.

[Ziphius]

It would be pretty simple to script it, e.g.

sort by measurement

go to first record, get date

go to last record, get date

do the math

Thanks for the quick reply. I should mention that some child records exists where a measurement was not taken so after sorting it would have to get the date from the first record with a measurement entry, same for the last. It would be nice to populate the two new parent fields "smallest measurement date" and largest "measurement date" so I can work with those variables for other calculations. Any chance on providing the basic script to do this? I am somewhat "script challenged" in this department. Thanks.

[comment]

The problem here is that both smallest measurement and largest measurement can exist in more than one entry. You need to specify a rule which date to show in such cases.

[Ziphius]

I'm not sure I understand so let me try to explain my problem a little better.

Let's say animal A is seen ten times, so have 10 child records, each with a date for the sighting. On sightings 2, 3, 6, and 10, I actually capture and measure the animal, so I have a body length entry for only those sightings. There is really no difference in body length between sighting 2 and 3 but because of instrument error, sighting 3 is actually the smallest measurement and sighting 10 is the largest. So if I do a Min(child:measurement), it will choose sighting 3 as the min.

So I create 4 new fields in the parent table of individuals, 1) smallest measurement, 2) date of smallest measurement, 3) largest measurement, 4) date of largest measurement. If I can fill these fields using a script or calculation, then I can determine the growth rate.

For fields 1) and 3) I simply use a calculation with the Min and Max functions for that field as described above, no problem. That leaves fields 2) and 4), for which I want to pull the min and max measurement dates, the date that is related to fields 1) and 3) for that record. Seems simple enough but I can't figure out how to do it. Is there a function that can lookup the "min" measurement and then pull in the value of the date field associated with that measurement?

Edited July 19, 2010 by Guest

[comment]

Let's say an animal is measured several times, with the following results:

#1   150    Jan 15

#2   151    Jan 20

#3   150    Jan 24

...

As you can see, the minimum result 150 is related to both record #1 and #3, with different dates each.

[Ziphius]

I see now, good point. None of my measurements are exactly the same but I guess it could happen in the future. Maybe if there was a way to (as Fitch suggested earlier)

1. go to the related records that contain the measurements (child table)

2. sort by measurements

3. choose the first measurement value (ignoring blank fields)

4. add this value to the parent table field called "min measurement"

5. add the value from the related field "date" to the parent field called "min measurement date"

6. choose the last measurement value (ignoring blank fields)

7. add this value to the parent table field called "max measurement"

8. add the related field date to the parent field called "max measurement date"

I guess I'm not sure how to "goto" a field of a related record and "get" the value of a another field within that record?

[comment]

Have a look at the attached. You haven't answered my question, so I picked the first matching record (in creation order) in every case.

GrowthRate.zip

[Ziphius]

That's great, just what I was looking for. I have one little problem that I can't seem to figure out. Each "Survey" has many "Individuals" sighted (parent-child relationship). Each unique Individual (Catalog) has been sighted multiple times (Catalog-Individuals = parent-child). So I have two parent tables relating to the "Individual" table. The "Catalog" table calculates the min and max measurement of all sightings of that individual. But to get the dates for the min and max measurements, I have to get it from the parent "Survey" table. The "Catalog" table is linked to the "Survey" table through the "Individuals" table and I'm not quite sure how pull the date from there. Hope this makes sense. I've attached a snapshot of the relationships.

Edited July 20, 2010 by Guest

[comment]

I'd add a calculation field to the Individuals table =

Sighting::Date

[Ziphius]

Ok, that was easy, thanks for that but unfortunately I've run into another small problem. Because the "date" field in the "Individuals" table references the related field in the "Surveys" table through a calculation like you suggested, it cannot be indexed and therefore I can't create a relationship based on it. Is there a workaround this?

When I try to index the new date field I get the following:

"The calculation “DateCopy” cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage"

and

By definition, an unstored calculation is not indexed so I can't create a relationship based on it.

[comment]

You don't need a relationship based on it (unless I am missing something).

[Ziphius]

I might be missing something. My Individuals table has a date field (Individuals:datecopy), a calculation field that is equal to the Survey date field (Survey:date) in a parent table. I want to display "datecopy" in another parent table (Catalog) based on a relationship Catalog:minmeasurement = Individuals:measurement. When I try to display it this way I get the "index missing" error.

[comment]

Does this work for you?

GrowthRateJoin.zip

[Ziphius]

This is exactly what I have with the only difference, which I think is part of the problem, is that the value that the relationship is based on (min and max measurements) is actually a calculated (unstored) value in the Individuals table. The value is actually the mean of a set of measurements stored in child table of the individuals table. This must be where the index error is coming from.

[comment]

Yes, that WOULD be a problem. However, I am not sure I follow your description and your picture does not show a child table of Individuals, as far as I can see.

Could you modify my last demo to reflect the real situation?

[Ziphius]

Sure, here it is...

GrowthRateJoin.fp7.zip

[comment]

Ok, that's what I thought. It's too bad you didn't mention this from the start, because it requires an altogether different approach.

Note that I have used a custom function here, but since it's not recursive it could be replaced by a calculation using the same formula.

GrowthRate3.zip

[Ziphius]

ok, trying to replicate what you did in my database. Since I don't have the "CorrespondingValue" function, I am trying to add it as a custom function but it is telling me that "index values" is a parameter that cannot be found so it won't allow me to create this as a custom function. This is beyond my level of expertise so I am at a loss at the moment. The function works fine in your database.

Never mind, I imported the function from your database so let me see if I can get mine working now

Edited July 20, 2010 by Guest

[Ziphius]

Ok, great, seems to be working. I can't say I quite understand what you did but I'm happy it is working. Thank you so much for your help with this!

Edited July 20, 2010 by Guest