Patrick,
Actually, a calculated column is refreshed only when the row is inserted or updated. Your options to have this be current all the time include:
1) Creating a view that includes other columns in the table and also your calculated expression, and accessing that view in reports, or "looking up" the birthdate in that view.
2) Turning your calculation into a "stored procedure", in effect creating your own R:Base "function." Then use the SP in any select or report or other place you need the age calculation.
3) Updating any row before you use it in a report (or before you lookup information for it in a form).
=================== The SP process would go like this:
a) Create a file in RBEdit with these commands in it. Name the file AgeFromDate.pro
-------------------------------- -- AgeFromDate Stored Procedure File SET VAR prc_date DATE SET VAR prc_age INT IF prc_Date IS NULL THEN SET VAR prc_Age = NULL ELSE SET VAR prc_age = ((.#date - .prc_Date) / 365.25) ENDIF RETURN .prc_age ---------------------------------
b) In the designer, click on Stored procedures and choose "new." Use these details:
Procedure name: AgeFromDate
File Name: AgeFromDate.pro
"Add" an argument, name it prc_Date, datatype DATE, comment "birthdate, e.g."
Return value: INTEGER
Comment: Compute age in years from birthdate parameter
Go back to the R> prompt, and try this:
SELECT ChildBirthday (CALL AgeFromDate(ChildBirthday)) FROM yourTable WHERE ChildBirthday IS NOT NULL
My sample table details weren't children, but I got good results:
R>sel playerdob, (call agefromdate(playerdob)) from players where playerdob is not null
playerdob (call agef
---------- ----------
05/14/1962 42
06/17/1965 39
08/18/1968 36
07/29/1974 30
09/04/1966 38
07/02/1969 35
06/19/1958 46
06/10/1970 34
05/05/1949 55
05/19/1970 34
03/13/1971 34
Bill
Patrick Murphy wrote:
I have an calculated integer field in a table that determines a person's age. The calculation is: (IFNULL(ChildBirthday,0,((.#date- ChildBirthday) / 365.25))) I just discovered that the calculated age has not automatically updated for persons with birthdays in the last several months. So someone with a 4/1/2003 birthday is still showing an age of 1, rather than 2. Current system date is correct. I edited the column definitions and forced a table save, and that did result in the ages being refreshed, but obviously that is not a solution.
I assumed calculated columns were refreshed whenever the table was accessed, such as when editing, printing a report, or browsing , but perhaps not. Can anyone help me out here? How do I make sure that field contains the correct age?
Patrick
6.5++/7.1
<<inline: SPpic.jpg>>
