I created a view containing the original table's fields, plus the date formula as an expression, turned on alias names to name the date formula field, and modified the reports and forms and programs that used the original table and age field, to look instead to the view and the calculated date field there instead.
When I set up the age field it worked perfectly "that day"! I didn't focus on or realize that over time the age calculation would not be updated unless that particular record was selected or viewed. Printing a report containing the age just doesn't trigger a recalc.
Thanks again for taking the time to lay out these solutions. I'm sure it will benefit others also. Another potential crisis averted!
Patrick
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
--------------------------------------------------------------------------------
--- RBASE-L ================================================ TO POST A MESSAGE TO ALL MEMBERS: Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE: Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body, place any text to search for.
================================================
