If job status depends solely on which dates are filled then it should be calculated directly from that. There is no need for a script to act as a middleman.
Thanks comment... so how do I do that?
Quotation Date ; "Submitted" ;
Commission Date ; "Commission" ;
<so on and so forth...>
Note that the order of conditions matters - read the help on Case() function to find out more.
Job status would be defined as a text field and a Calculated value?
No, it would be a Calculation field, with the result of the calculation set to Text.
Thank u so much comment... will give it a shot and update here... cheerios!
Realised comment's solution does not work for my case. I do have several dates that are entered as the project progressed meaning...
1) Estimate date is set when quotation is written and sent > 'Submitted' ;
2) When the project is confirmed, the user would enter the date and the status will change to 'Commissioned'
3) Upon completion and delivery of the deliverables, the user would enter the delivery date and hence changing its status to 'Delivered'
so on and so forth...
Hence my database does have more than one dates for every record/project.
Is there a solution that will change my Job status field every time a record is updated via Date fields?
Wondering if anyone can help?
*developer who is stuck*
Thank you for your posts.
The calculation by "comment" is definitely correct. You only need to reference the correct fields.
Case ( Delivery Date ; "Delivered" ;
Confirmed Date ; "Commissioned" ;
Estimate Date ; "Submitted" ;
<default value> )
As "comment" also mentioned, the order is key. If you enter an Estimate Date, and you display "Submitted", then the Case statement stops evaluating the other criteria. Therefore, you want to start with the end product and work backwards.
If you are still running into difficulty, please let me know.
Thanks TSGal,.. for your kind inputs. Will give it a try again and let you know.
Thank you and have a great weekend ahead!
It works! Thanks again TSGal!