I have a query for closing and completing jobs and I need to have the date fields (ClosedDate + JobCompletionDate) update as the check boxes are checked, but also have the ability to overwrite this.
The expression for JobHead.ClosedDate is “(ttResult.JobHead_JobClosed == true) ? Constants.Today : ttResult.JobHead_BlankDate_c” The BlankDate field is so that if a user unchecks JobClosed then it goes back to a blank date instead of keeping today’s date.
The expression for JobHead.JobCompletionDate is “(ttResult.JobHead_JobComplete || ttResult.JobHead_JobClosed == true) ? Constants.Today : ttResult.JobHead_BlankDate_c”.
I also have an expression on JobComplete that makes it true if JobClosed is true…
Is there a way to overwrite Constants.Today in the updatable dashboard? Is there something I can add to the expression? Is this even possible?
Here’s the query phrase:
select
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobHead].[ProdQty] as [JobHead_ProdQty],
[JobHead].[QtyCompleted] as [JobHead_QtyCompleted],
[JobHead].[JobClosed] as [JobHead_JobClosed],
[JobHead].[JobComplete] as [JobHead_JobComplete],
[JobHead].[CreateDate] as [JobHead_CreateDate],
[JobHead].[ClosedDate] as [JobHead_ClosedDate],
[JobHead].[JobCompletionDate] as [JobHead_JobCompletionDate],
[JobHead].[BlankDate_c] as [JobHead_BlankDate_c]
from Erp.JobHead as JobHead
You can use case structure like this. Review attached sample to start with. modify per your requirements.
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobHead].[ProdQty] as [JobHead_ProdQty],
[JobHead].[QtyCompleted] as [JobHead_QtyCompleted],
[JobHead].[JobClosed] as [JobHead_JobClosed],
[JobHead].[JobComplete] as [JobHead_JobComplete],
[JobHead].[CreateDate] as [JobHead_CreateDate],
[JobHead].[ClosedDate] as [JobHead_ClosedDate],
[JobHead].[JobCompletionDate] as [JobHead_JobCompletionDate],
**(case ** when JobHead.JobClosed =1 then cast(getdate() as date) else ‘01/01/1900’ – this is blank date end) as [JobHead_ClosedDateCalc]
from Erp.JobHead as JobHead
How would I do this within the Business Activity Query Designer?
EDIT: I see that you put [JobHead_ClosedDateCalc] so I assumed it was a calculated field? I tried playing around with the calculated field editor and adding the information you bolded but couldn’t find the correct syntax.
That doesn’t work.
A few things: “when jobhead.jobclosed =1” doesn’t work/tie to JobClosed so it just makes every one 1/1/1900. Also, this doesn’t tie into the actual job closing fields. I knew that going in, but I thought I’d mention it… The goal here is to have the ClosedDate field update with the JobClosed check box (and make it “today’s” date), but also have the ability to overwrite that date if needed. I’m not sure if this is possible
Is there a way to add a prompt to an expression? This would solve any issues I’m having…
So my expression on JobHead.ClosedDate is “(ttResult.JobHead_JobClosed == true) ? Constants.Today : ttResult.JobHead_ClosedDate” Maybe instead of the else part of the statement being the ClosedDate field, it could be a prompt for a new date. Is this possible?
edit: Now that I think about… This wouldn’t solve the main issue of having JobClosed as true and making the date today’s date while having the ability to overwrite the date if needed.
