First off: I'm a Microsoft SQL and Access developer so my approach to solving this problem is very different.
I have the following problem: I need to record a persons weight over time. However I also need to display the weight loss from the previous weighing. How would you do this?
I have put some rules in place but AwarIM's rules seem to be tripping over each other. The idea would be to record on every weighing the date taken AND the previous weigh date. That way one can look up the previous weight to compare it and determine what was lost. I'm having trouble creating a rule that FIND's the previous record for a Client where the Datetaken is less than the one being entered. I've tried various combinations but it seems not to like do a query on itself.
Here is a sample of one of the many ways I tried this so you can get the idea maybe:
If Weight.dt_DateTaken IS DEFINED Then Weight.dt_LastWeighed=MAX Weight.dt_DateTaken WHERE (Weight.ref_Contact.ID=Weight.ref_Contact.ID AND Weight.dt_DateTaken<Weight.dt_DateTaken)
Criteria should be last weight records date for this client where the date is smaller than the current record being entered. Remember you cant use CURRENT_DATE as these records can be edited later and need to update!
Random thought, try (Weight.ID-1), should be the previous entry. But be careful that the database may discard an ID if the record is deleted. So you may have add something to ensure that Weight.ID IS DEFEFINED. But you could try this just to see if it gets you out of your hole.
The database isn't for one client but many so Weight.ID-1 may not reflect the last Weight reading for the same client and the readings are not entered after each other but rather as the client comes in periodically
Attributes:
Weight.Dt - the date of the weight
Weight.Wt - the weight on above date
Weight.LastDt - Date of last weight
Weight.LastWt - Last weight
GainOrLoss - Change since last weight
Rules on Weight BO:
Calc of Last Weight Date:
Weight.LastDt=MAX Weight.Dt WHERE (Weight.Dt<ThisWeight.Dt)
Last Weight:
FIND Weight WHERE Weight.Dt=ThisWeight.LastDt
ThisWeight.LastWt=ThatWeight.Wt
Gain or Loss:
Weight.GainOrLoss=Weight.Wt-Weight.LastWt
Rather than having to look up the last weight, these rules will show the last weight on the current weight record with the gain/loss info. You'll also need a rule to eliminate the possibility of a duplicate weight date. If dates are added out of sequence, you will also need to run a process which Finds all Weights and does an UPDATE action.
Ok, so I've seen this in a number of other posts and never really understood what it meant. By adding "This" in front of the attribute you are differentiating between the 2 values even though they have the same name. The value to be filtered against the value that is currently being entered. That's clever.
What you've done is what I did but without the "This" prefix the query goes into a spin and returns nothing.
