Search Form For Calculated Field Parameters

[kpcollier]

I'm trying to use a search form solely for setting a condition in a calculated field in a tabular report.

I have a Job_Table that houses information about our Jobs (Job_Number is the ID for this table). I have a TimeEntry_Table that houses Job_ID, Date, And Hours for our employee timesheet. The datapage uses Job_Table as the source.

I have a calculated field that queries to the TimeEntry_Table and sums up the Hours for each Job. This works. However, now I am trying to add a date range to this. I've been trying to use two virtual fields in the search form to set parameters. If the user leaves these blank, it'll just sum up all of the hours matching the Job_ID. But, if they enter in dates in the two virtual fields, I am trying to use these in my Calculated Field. 

CASE WHEN [@DateFrom] IS NOT BLANK AND [@DateTo] IS NOT BLANK

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number AND Date BETWEEN [@DateFrom] AND [@DateTo]

ELSE

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number

END

To start off the CASE statement, I'm not sure if I should use the parameter name or cbViritualParam. It seemed neither worked. I am not sure if you can check to see if parameters or virtual fields are empty from the search form. Next, you can see the BETWEEN condition. This is where I am trying to enter in the dates from the virtual fields to filter the number of records that are summed up. 

Nothing is showing up with my workflow.

Any help is appreciated. 

[kpcollier]

Adding parenthesis to the SELECT statements made this work.

CASE WHEN ([@calcfield:5] != ' ') AND ([@calcfield:6] != ' ')
THEN
(SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = [@field:Job_Number] AND Date BETWEEN '[@DateFrom]' AND '[@DateTo]')
ELSE
(SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = [@field:Job_Number])
END

 

[kpcollier]

I was missing the single quotes on the parameters. 

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number AND Date BETWEEN '[@DateFrom]' AND '[@DateTo]'

Now, both SELECT statements work. If there are no parameters, it sums up all hours related to that job. If there are date parameters, it will only sum up the hours between those dates.

The problem is now getting the CASE statement to work. I can't seem to get the formula to run when nothing is entered into the date fields. I can't figure out how to check against blank parameters. I have tried adding the parameters to separate calc fields, and then running the CASE statement if those calc fields are blank or not, and I still can't get it to run. 

CASE WHEN ([@calcfield:5] is not blank) AND ([@calcfield:6] is not blank)

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number AND Date BETWEEN '[@DateFrom]' AND '[@DateTo]'

ELSE 

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number

END

 

[cheonsa]

On 6/7/2021 at 10:19 AM, kpcollier said:

I was missing the single quotes on the parameters. 

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number AND Date BETWEEN '[@DateFrom]' AND '[@DateTo]'

Now, both SELECT statements work. If there are no parameters, it sums up all hours related to that job. If there are date parameters, it will only sum up the hours between those dates.

The problem is now getting the CASE statement to work. I can't seem to get the formula to run when nothing is entered into the date fields. I can't figure out how to check against blank parameters. I have tried adding the parameters to separate calc fields, and then running the CASE statement if those calc fields are blank or not, and I still can't get it to run. 

CASE WHEN ([@calcfield:5] is not blank) AND ([@calcfield:6] is not blank)

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number AND Date BETWEEN '[@DateFrom]' AND '[@DateTo]'

ELSE 

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number

END

 

Hello @kpcollier, 

Have you tried to use this one:

CASE WHEN ([@calcfield:5] != '' ) AND ([@calcfield:6] != '')

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number AND Date BETWEEN '[@DateFrom]' AND '[@DateTo]'

ELSE 

SELECT Sum(Hours) FROM TimeEntry_Table WHERE Job_ID = Job_Number

END

Sometimes, "is not blank" is not working in calculated fields.