Calculations between variable dates
I am the weight recorder for a weight loss group. Currently I record weights into a rather complex spreadsheet. It seems to me a database makes better sense for the kind of information I want to track, but I'm a FileMaker newbie.
Here is what I'd like my weight recording form to do. First, it would automatically fill in today's date (editable in case I'm having to enter or modify information on a day other than when the weigh in happens). I should be able to choose the member's name from a drop down list, or add a new or visiting member on the fly. The form will immediately calculate the amount lost or gained since the last weigh in.
We weigh in weekly, but for any member a weigh in may be one of these things:
1) New member - no previous weight on record
2) Regular member - last weigh in was 1 week ago - may qualify as best loser of the week.
3) Absent or Excused - doesn't weigh in due to illness/work/away - when they come back their last weigh in will be more than a week previous, can't qualify as a best loser
4) First weigh in of year - for our official records, this is the beginning weight, but if they weighed in the previous year, they may want to know how much they've lost or gained since they last weighed.
How do I tell FileMaker to identify the correct record to compare to the current record for calculated weight change? What would my calculation look like?
Thanks in advance