Function Referencing Previous Value

[lord_lordy]

I'm new to Filemaker Pro and am moving my Training Diary over from a spreadsheet. A calculation I want to repeat is calculated as follows:

I have a table with a field called load. This table has an entry for every day.

I have a field called Chronic Training Load which is calculated by taking the previous value of Chronic Training Load and applying a factor to it and then adding in that days training load.

What I can't work out is in the calculation how to refer to the previous days value.

Any help appreciated.

Edited September 1, 2010 by Guest

[comment]

Is the previous day always "yesterday"? Or are there gaps between training days?

[lord_lordy]

The previous day is always yesterday. There will be no gaps in the days... in fact it's something I may have to look out how to ensure no gaps.

[comment]

You could base the date on an auto-entered serial number field. For example, if your first day is Jan 1, 2010, the date can be a calculation field =

SerialID + 733772

or just start the serial numbers from 733773. You need to be careful not to delete records, though.

To get the previous day's value you need a self-join relationship such as:

Days::cPrevID = Days 2::SerialID

where cPrevID would be a calculation =

SerialID - 1

However, this raises a problem of cascading calculation - it will slow down as the number of records increases. How do you arrive at a day's training load? Is it a sum of the various exercises performed during the day (hopefully in a child table)?

[lord_lordy]

Thanks - I will give this a try. Not done a self join relationship so that'll be fun !

The daily load is based on a child table. Since once a day is gone the calculations won't change I had wondered about storing the calculated field - I'm making the assumption this will mean it won't recalculate it each time.

[comment]

Yes, you would have to lookup the previous value - but that could be problematic if you pre-generate the daily records.

[Raybaudi]

To get the previous day's value you need a self-join relationship...

Why ?

Isn't it : GetAsDate ( SerialID - 1 ) ?

Edit: never mind, you said: previous day's value ( not previous day )

Edited September 1, 2010 by Guest

[Raybaudi]

BTW: Your idea to carry_over with a serial, using a trigger.

carry_over.zip

[lord_lordy]

It seems I can do that join using a field "Yesterday" which is a simple calculation of Date - 1

Am I missing something ? It appears I don't need to create a serial number for the date.

It has lead to another problem which is that for the very first day there is no yesterday ! So for that first day the calculation is slightly different - ie the term that refers to yesterday is just zero. I think this is what's causing my current error.

On a similar note I want to also have rolling weekly averages. I could do this by doing 6 self joins to the previous 6 days. This feels a little convoluted. Is there an easier way ? My feeling is that, since once a day has passed it should never have any information changed (unless an error was spotted which is very rare), a script would be better that I could run periodically for a subset of the days to populate the value for this field.

Being new to this software I don't really know how to set up such a script. Can any suggest resources I could look to to learn about scripting ? I have "The Missing Manual"for Filemaker Pro 11. Is there something / somewhere else I could look ?

[Vaughan]

This is not a scripting problem it's one of data design.

Shouldn't these daily load values be in a separate table? You already mention a "child" table. There could be a process whereby at the end of each "day" the load value is calculated and a record made for the next day.

This process need not happen exactly at midnight or sunrise or whatever' when each person opens the database a check is done to ensure that the load record for the current day is created.

Keep in mind that a computer clock could be inaccurately set, so work off the host time rather than the client time.

[lord_lordy]

I think there is a better way and believe that some sort of trigger to run the script to populate the values when data is entered is the right solution.

It can't be done in the child tables as they don't have an entry everyday. They include workouts that are done. The daily record includes a total load for the day which could be zero when there were no workouts and thus no entries in the child tables.

The actual calculation is:

Todays CTL = (Todays Load)*(1-Exp(-1/42)) + (Yesterdays CTL)*Exp(-1/42)

Day to day this calculation could be done based on some sort of trigger (having worked with databases as a software developer I kinda know this is what I need but having never been a database developer I'm struggling to work out the syntax).

I have imported 7 years of training data into my database. I would like to be able to back populate the values which i think I may need a script for.

Once I've sussed this I've got the even more fun calculation that is "Eddington Numbers" ;o)

[comment]

I can do that join using a field "Yesterday" which is a simple calculation of Date - 1

Yes, that would be the same as using SerialID - 1. The idea behind using a serial number is to make sure records are automatically assigned consecutive dates as they are created.

I would use a script triggered by OnRecordCommit to re-enter the previous value into the lookup field. This way you can create the daily records in advance, and any time you enter today's data, yesterday's value will be refreshed.

[comment]

It can't be done in the child tables as they don't have an entry everyday.

Hold on: if you don't have an entry every day, there is no point in creating a record for every possible day. So you could just create a parent record when you have some data to enter, and lookup the previous value at the same time. This will work as long as you enter data in chronological order (and even if you don't, there is always re-lookup).

[lord_lordy]

Sorry if I've confused this.

There is data every day. It's just there won't be a workout every day. The value for "CTL" will be there everyday as this is effectively load from previous days decayed based on time plus a factor based on todays load (which may be zero).

The process here would be a user would input daily data. Now they won't necessarily do this everyday but they will input an entry per day. For instance if they're away they may record data manually in a notepad and then when returning home input each days data at a later date. There is the issue that they may not enter data in order which for now I don't need to address as only I will use it initially but going forward I will need to either enforce chronological entry or have some method for ensuring this field is updated.

Thanks for all you help. I think the trigger on committ will get me what I want for the moment and will work as long as I entry in date order.

I will then have to figure out how to back populate this calc for all the historic days I've uploaded.

[comment]

Well, if you have REAL data to enter every day, then you do need a record for every day. But if it's just to "carry over" the CTL value, it's not necessary - you can calculate the compounded factor by looking at the number of days that have elapsed since the last entry.