Overwrite date even with Constants.Today in expression

ERP 10
1

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

Thanks

2

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

image.png945×121 3.5 KB

3

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.

4

image.png920×600 27.8 KB

Try like this.

5

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

Thank you!

Caleb

6

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.