Issue with calendar manually created field

Scriptcase 9 General
#1

All,
Wondering if anyone can assist in what I am doing wrong here.

I have a calendar application within a HR Project.
I have a hire date in the database.
I want the HR Benefits calendar to show when a employees benefits are to be reviewed.

Here is what I did,

  1. Created a new field in the application called benefit_reviewed.
  2. defined the filed as a date.
  3. Put the following code in the lookup
    SELECT DATE_ADD(hire_date,INTERVAL 90 DAY)
    FROM employees
    WHERE emp_no = ‘{emp_no}’
    This should calculate the hire date + 90 days
    And display the benefit_review field on the calendar for the employee

ANY Ideas why I am getting nothing on any dates?

Kev

#2

You have a calendar app showing dates and you want the records shown in this calender on the review date right? The calender is shown on the database field which you have mapped to the calender as appointment date. To be able to use this you need to fill the generated appointment table with the review dates. You need to decide when you generate this, i.e. when updating employee data or by a process or button. You need an sql statement like:

delete * from myappointmenttable
insert into myappointmenttable fields where (select … from employee where …)

#3

So, If I have a hire date and I want to put a review date which is always 90 days after the hire date, I “have” to add it as a column to the table?
I thought I could creat a new field an call it review date and then assign that to the calendar show date programmatically.
ie {review_date} = DATE_ADD(hire_date,INTERVAL 90 DAY)