Calculating Rates based on values from previous record

[Ziphius]

I have a table of manta ray surveys (Surveys table) with each survey receiving a timestamp. Each survey has multiple manta ray sightings (Sightings table, related by surveyID, one to many). The mantas are recognizable so there is an individuals table (Individuals) that also have many sightings of that individuals (Sightings table, related by individualID, one to many). On some sightings, a size measurement of the manta ray is taken, so the size field in the sightings table sometimes has a number, or is sometimes empty.

I'm trying to write script that will populate a "Growth Rate" field in the sightings table. Essentially I want it to:

1. Find records in the sightings table with Sightings:Size > 0 (to include only sightings where sizes were obtained)

2. Sort the found set by individualID and by SurveyID (so the records are ordered by individual and then by survey date)

3. For each individual (IndvidualID) in the found set, calculate the the change in size (Sightings:Size - Sightings:Size from the previous recorded size) and divide this value by the time elapsed between the two sizes (SurveyDate - SurveyDate from the previous recorded size) to populate the Growth Rate field giving a a growth rate of XX mm per day.

Perhaps its better to script it by including all records but ignoring those whose Size field is empty. If anyone could help me develop this script, that would be greatly appreciated.

[Fitch]

There are various ways you might approach this, but if you have a sorted found set you might try using:

GetNthRecord( Sightings:Size ; Get ( RecordNumber ) - 1 )

By the way, very nice job explaining your problem. I really appreciate when people take the time to think it through and write clearly.

[comment]

You can use the GetNthRecord() function to get data from the previous record in the found set.

Another option is to define a self-join relationship between consecutive sightings of the same individual.

[Ziphius]

Thanks "comment", but I am not sure how to do a self-join relationship between consecutive sightings of the same individual.

As for a script, using the GetNthRecord, this is my butchered script so far:

Go to Layout [individualSightings]

Perform Find [size > 0]

Sort Records [individuals:ID, Survey:Date]

Loop

If(IsEmpty(IndvidualSightings::GrowthRate);((IndvidualSightings::Size) - (GetNthRecord(IndvidualSightings::Size;Get(RecordNumber) - 1))) / ((Surveys::Date)-(GetNthRecord(Surveys::Date;Get(RecordNumber) - 1)));"")

Go to Record [Next]

End Loop

So essentially, find all individual sightings where individuals were sized, sort these by individual and date, then if the GrowthRate field is empty, subtract the previous record size from the current, subtract the previous record date from the current, then divide the two to get a rate and populate the GrowthRate field. Go to the next record and do the same.

At least two problems here, first is when it begins the loop on the first record of the next individual, it will subtract the size and date from the previous record which will be the last record of a different individual. So, essentially I want it to skip this step for the first record of each individual. Second, I don't have a way to break the loop once it goes through all the records.

Any suggestions?

[comment]

how to do a self-join relationship between consecutive sightings of the same individual.

Here's a simplified example:

GrowthRate.zip

[Ziphius]

Perfect, thank you for that. Well at least yours work perfect, I'm having a little trouble. My sizes are calculation means of a bunch of sizes, unstored. My Date field is also a calculation: Case (Sizes::IndividualID ; Surveys::Date ), extracting the date from a parent table Surveys. also unstored. I noticed that your Size and Date fields were indexed. Since mine aren't indexed I get "<Index Missing>" in the Prev:Date and Prev:Size fields. When I try to apply "All Indexing" as a storage option, I get the following message:

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

The Size field calculates an average from several repeated measurements of the same sighting (i.e. summary field) hence the error. I feel like I am almost there. Any thoughts?

[comment]

Well, I said it was simplified...

However, I now see you may have oversimplified the matter too. So far we have:

Surveys -< Sightings >- Individuals

Then you said that the Size field in the Sightings table is sometimes empty.

Now you say the Size field is an average of measurements of the same sighting - that's not quite clear.

Do I understand correctly that an individual may have many sightings during a single survey - some with a Size value, some without? If that's true, then neither GetNthRecord() nor the self-join can work here - because you need to compare two averages.

[Ziphius]

I guess I did oversimplify.

During a survey (a one tank scuba dive at the study area) I record the individual manta rays that are present, so each survey will have only one sighting of that individual if they are present:

Surveys -< IndividualSightings

Each individual, if seen on more than one survey, will have multiple IndividualSightings

Individuals -< IndividualSightings

just how you have shown it above. But an individual does not get multiple sightings during a single survey.

Do I understand correctly that an individual may have many sightings during a single survey - some with a Size value, some without? If that's true, then neither GetNthRecord() nor the self-join can work here - because you need to compare two averages.

No, this is incorrect. When I am able to measure the size of the manta ray (using paired-lasers), I take a minimum of 4 quick measurements in succession, just in case the first is erroneous for whatever reason, then I average them to get a single size for that individual sighting. There is only one final size for that individual during any survey. Most times, I'm unable to get a size because the manta simply passes quickly through the study area. In this case their is no size measurement for that sighting.

If the size field with the formula is a problem, can I simply create another Size field that pulls in the calculated value from the size field with the function?

[comment]

I take a minimum of 4 quick measurements in succession, just in case the first is erroneous for whatever reason, then I average them

So where exactly does all that happen? There is only one sighting record: where do you put the 4 or more measurements?

[Ziphius]

I guess I have been describing the database more simply than it really is so I've attached the relationships here. Hopefully this helps and doesn't make things worse.

Each survey I document which individuals are present. I photograph their ventral and dorsal sides, which have unique patterns that identify them. Each sighting I select the best image, and the best image for all sightings is assigned the best catalog image. The catalog is a table of all individuals. On each survey, an animal could be sized, biopsied, and or tagged, and if they are a new record is created. To size an animal, I take several photographs (Size frames), each of which I can get a size from, and average them to give a size for that sighting (Sizes table).

You will also notice that the date is not within the sighting table but in the survey table. I replicated the Date in the sighting table (DateCopy) to be able to perform the relationship with itself.

Sorry, tried to describe it more simply thinking I could adjust a simple solution to fit my database.

[Ziphius]

I should also clarify since we are using Date, Date is not unique in the Survey table since some days had more than one dive or survey. There should be:

A Date -< B Surveys

Currently dates are embedded in the surveys table so I need to fix this and create a parent Date table.

[comment]

I'm afraid the screenshoot is rather cryptic. I think you are saying there is another table of Sizes, which is a child of Sightings? But still, some sightings have no child records in there?

[comment]

Date is not unique in the Survey table since some days had more than one dive or survey. There should be:

A Date -< B Surveys

Currently dates are embedded in the surveys table so I need to fix this and create a parent Date table.

I don't think such table is necessary or useful.

[Ziphius]

I'm afraid the screenshoot is rather cryptic. I think you are saying there is another table of Sizes, which is a child of Sightings? But still, some sightings have no child records in there?

Correct.

Individuals -< IndividualSightings -< Sizes -< SizeFrames

also

Surveys -< IndividualSightings

SizeFrames contains each of the 4 photos I take to get a size. I size each photo and use the average as the size populated in the table Sizes. For some IndividualSightings, Sizes are obtained but not always. But if an individual has multiple sightings in which it was sized, then I want to measure the growth rate (change in size across that time period).

I don't think such table is necessary or useful.

Ok, then I will leave it as is.

[comment]

Oh my. I guess this just cannot be simple, can it?

SizeFrames contains each of the 4 photos I take to get a size. I size each photo and use the average as the size populated in the table Sizes.

How come one Sighting can have many Sizes?

[Ziphius]

Because the technique used for sizing involves two parallel lasers fixed at a known distance, mounted on the camera. You photograph the animal with the laser dots on the image to give it a scale for measurement. However, if the animals is angled in any way, you can get a poor measurement. So rather than just taking a single measurement and risking it is bad, I take a minimum of 4 and average them. I store each image in the database in case I need to go back to the image and look at it again, but for the actual size from that sighting, I just want the average of the 4 measurements. So there is only one size per sighting, its just the size is averaged over 4 measurements (images) for better accuracy.

[comment]

I am afraid you have to dumb this down a bit more for me, because I can't reconcile it with:

IndividualSightings -< Sizes -< SizeFrames

[Ziphius]

Ok, I see the problem here. The Sizes table is unnecessary since Size should simply be a field within the IndividualSightings table that either get populated if the animal is sized, or not. The SizeFrames is then nested within the IndividualSightings table. I think I create a separate size table because I was trying to create an Individuals table for only sized animals, and not knowing what I was doing, did it the wrong way. So, the way I have it now, IndividualSightings is actually a one to one with Sizes. Sorry about that.

[Ziphius]

I would like to keep the size table separate from the IndividualSightings table (but linked as a one to one by the IndividualSightingID) just so it is easier to work with the sizes that way.

[comment]

I would like to keep the size table separate from the IndividualSightings table (but linked as a one to one by the IndividualSightingID) just so it is easier to work with the sizes that way.

Well, it's rather redundant, but it makes no practical difference for our purpose. In any case, the previous sighting cannot be identified by a relationship, but this shouldn't be too difficult to adapt:

Growth2.zip

[Ziphius]

This post is somewhat old but I had a follow up question. The report in the file above shows the change in growth between two sightings in which the animal was sized. Is there a way to do a report with one record for each IndividualSightingID and subtract the smallest size ever measured from the largest size ever measured and divide by the number of days elapsed between those two dates? Lets call that field "EstimatedGrowthRate".

cEstimateGrowthRate = (max(size)-min(size)) / (date of max(size))-date of min(size))

How do I get the bold part?

[comment]

Do these animals ever shrink?

[Ziphius]

Well, not physically, but since there is some error in the measurements (about 1%) an animal that has stopped growing or that is measured a few days later may appear to lose a couple of mm so the growth rate between those two periods may be negative. To correct for this I would filter by a minimum time span between measurements (lets say 90 days since they grow very slowly) and a minimum growth change (lets say 2 cm to overcome any changes in measurement error). I realize this formula just got a whole lot more complicated but thanks for asking. I've attached a mock database which shows a little better what I'm dealing with. You will also see that some of the growth changes calculate correctly and others do not, not sure why.

Another issue is adding a new individual to the catalog. Usually a sighting is entered and once confirmed that it is a new individual, a new parent record (catalog) should be created. I'm not sure the best way to do that - type in a new name and have it create a new catalog and add the foreign key to the sighting?

Growth3testfmp12.zip

[comment]

Couldn't you ignore the measurement error and calculate the growth rate as ( MaxSize - MinSize ) / ( MaxDate - MinDate )?

I am afraid I cannot yet open v.12 files.

[Lee Smith]

According to their Profile, neither can they.

@Ziphius

Please update your profile to reflect your current FileMaker information. Click My Profile for a quick link to do this.

TIA

Lee

[Ziphius]

My apologies, profile updated.

I could ignore the measurement error and do it your way as a crude method but for a scientific paper or dissertation I couldn't get away with that since the MaxSize and MinSize don't necessarily correspond with the MaxDate and MinDate. But if I can get that far I would be happy.

So I created the Max and Min summary fields for size and date and added them to the subsummary part when sorted by individual and those fields seem to be working fine. Now to create a summary function to do calculate ( MaxSize - MinSize ) / ( MaxDate - MinDate ) as shown above, how do I do this since these are 4 summary fields. I presume it has something to do with the GetSummary function but I'm not sure how to make that work since I can't seem to just plug that in as the summaryfield parameter of the function.

GetSummary((sMaxSize - sMinSize)/(sMaxDate-sMinDate);_fkSightingID)

gives me the following error: "Only summary fields are allowed as the first argument in a GetSummary function." I just posted this same issue for a different calculation so I will probably get in trouble here for posting similar questions in different forums.

Unfortunately I'm unable to save the database file in a version earlier than FM12.

[Ziphius]

Thanks to "comment" response to my other post, the same solution works here so I've posted it:

(GetSummary(sMaxSize;_fkCatalogID)-GetSummary(sMinSize;_fkCatalogID))/(GetSummary(sMaxDate;_fkCatalogID)-GetSummary(sMinDate;_fkCatalogID))

If anyone can tell me why the cGrowth calculation doesn't work properly, I'd be interested to know why. As far as getting the summary calculations, this seems to work fine. Thank you!

Growth4testfmp12.zip

[LaRetta]

png's of calcs, layout and graph attached

Your relationships are sound and properly specified. Your leading part is Sightings::CatalogID and the layout is based upon Sightings. All fields are correctly specified on the layout and it properly sorts by CatalogID then Surveys::Date ascending. The calculations appear to be correct.

Sightings >--- Surveys is sorted on Sightings side ascending PK_SightingsID

Sightings >--- Catalogs is sorted on Signings side ascending PK_SightingsID

and on the Catalogs side on CatalogID ascending

I have not had time to go further with the file but I think it covers everything which might be the problem. What specifically is not working please?

Ziphius.zip

[Ziphius]

My bad, it wasn't sorted properly so the summary field was incorrect. All good now, thank you so much!