AppGini Community Forums

Posted: **2021-09-16 19:46**

I have task follow-up table in ,

there are fields as follows

1)task_name (lookup field)
2)required_days (lookup field-autofill)
3)start date
4)end date
5)task_days_count (calculated field, datediff (start_date,end_date)
6)status

i want to update status as

if field number 5 (task_days_count) <= field number 2 (required_days) then number 6 auto update "task done in time"
if field number 5 (task_days_count) > field number 2 (required_days) then status = "delay"

please help to complete it ,

thanks and best regards,

Posted: **2021-09-17 04:56**

Hi there
The issue here is that you can not use the required_days (lookup field-autofill) value to compare as it is storing the id instead of actual value in the database.
So, you have to use JOIN in the SQL statement to get the actual value (in this case, the actual value of required_days in the source lookup table [example : a table called task]).
You can set the status field as calculated field with the following SQL statement.

Code: Select all

SELECT CASE 
     WHEN `task_follow_up`.`task_days_count` <= `task`.`required_days` THEN 'TASK DONE IN TIME' 
     WHEN `task_follow_up`.`task_days_count` > `task`.`required_days` THEN 'DELAY' 
     ELSE '---'
END
FROM
    `task_follow_up` 
LEFT JOIN `task` ON `task`.`id`=`task_follow_up`.`task_name`
WHERE
    `task_follow_up`.`id` = %ID%

Hope this will help.

Stay safe.

Posted: **2021-09-17 10:15**

thank you so much sir,
it works .

Posted: **2021-09-17 10:48**

Glad to hear that. Thanks for the feedback.

AppGini Community Forums

tasks- delay or completed in time .

tasks- delay or completed in time .

Re: tasks- delay or completed in time .

Re: tasks- delay or completed in time .

Re: tasks- delay or completed in time .