This seems to be a bit odd:
GetAsDate(JOBS :: Date_service)<Get(CurrentDate) and GetAsDate(JOBS :: Time_service_start)<Get(CurrentTime);
If Jobs :: Date_service is a date field, there's no need for GetAsdate. Likewise Jobs :: Time_service_start should be a time field and GetAsDate is definitely not what you want for comparing to Get ( currenttime ).
Finally, make sure your calculation's storage option is set to "Unstored" so that it updates with the current date and time automatically.
The Status field should be Calculation field (unstored, with result type Text), preferably in the same table where the events are (JOBS?). I think the calculation could be =
Let ( [ start = Timestamp ( Date_service ; Time_service_start ) ; end = Timestamp ( Date_service ; Time_service_end ) ; now = Get ( CurrentTimestamp ) ] ; Case ( now < start ; "Pending" ; now > end ; "Complete" ; "Work in progress" )
but it's hard to be sure, since your calculation does not mention a Time_service_end field.
Thanks a lot to Comment and PhilModJunk
This is definately the way to go! There was just one bracket missiing and once added it worked like a charm.
Here's the script steps used, Kudos to Comment:
Let ( [start = Timestamp ( Date_service ; Time_service_start ) ;end = Timestamp ( Date_service ; Time_service_end ) ;now = Get ( CurrentTimeStamp )] ;Case (now < start ; "Pending" ;now > end ; "Complete" ;"Work in progress"))
There was just one bracket missiing
Sorry about that - I didn't select properly when copying.
BTW, this is a calculation - there are no script steps here.
it's a calculation, not a script. Sorry