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,
tasks- delay or completed in time .
-
- Veteran Member
- Posts: 32
- Joined: 2021-06-12 21:01
Re: tasks- delay or completed in time .
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.
Hope this will help.
Stay safe.
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%
Stay safe.
Zala.
Appgini 24.10.1579, MacOS 14.3.1 Windows 11 on Parallels.
Appgini 24.10.1579, MacOS 14.3.1 Windows 11 on Parallels.
-
- Veteran Member
- Posts: 32
- Joined: 2021-06-12 21:01
Re: tasks- delay or completed in time .
thank you so much sir,
it works .
it works .
Re: tasks- delay or completed in time .
Glad to hear that. Thanks for the feedback.
Zala.
Appgini 24.10.1579, MacOS 14.3.1 Windows 11 on Parallels.
Appgini 24.10.1579, MacOS 14.3.1 Windows 11 on Parallels.