| Author |
Message |
korey_premier
Newbie
Joined: 23 Jun 2014
Location: United States
Online Status: Offline
Posts: 8
|
  
Topic: Dynamic Date Range in Select Formula
Posted: 25 Sep 2014 at 8:12am |
|
Hello all!
I'm trying to select records in a report based on a condition. The report uses a DateTime formula field that calculates the beginning and end of each shift.
I would like for the select statement to use these formula fields to determine which records to select based on the shift.
The select expert will not allow me to use '{LABOR_TICKET.CLOCK_IN} in DateTime ({@1st Shift Start}) to DateTime ({@1st Shift End})'. I recieve an error that says "Date required"
if {EMPLOYEE.SHIFT_ID} = "1ST" then
{LABOR_TICKET.CLOCK_IN} in Date ({@1st Shift Start}) to Date ({@1st Shift End})
else if {EMPLOYEE.SHIFT_ID} = "2ND" then
{LABOR_TICKET.CLOCK_IN} in Date ({@2nd Shift Start}) to Date ({@2nd Shift End})
else if {EMPLOYEE.SHIFT_ID} = "3RD" then
{LABOR_TICKET.CLOCK_IN} in Date ({@3rd Shift Start}) to Date ({@3rd Shift End})
Am I going about this the wrong way?
Thanks,
Korey
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 25 Sep 2014 at 11:18am |
I am not exactly tracking what you are trying to do here.
It looks like you are trying to use the formula as a condition in joining two tables. Although you can use conditional joins that is not the way to do it.
|
| IP Logged |
|
korey_premier
Newbie
Joined: 23 Jun 2014
Location: United States
Online Status: Offline
Posts: 8
|
Posted: 25 Sep 2014 at 11:23am |
|
This is a formula that I was trying to add to the select expert. It works but is only selecting records based on the date range and not the date time that I need.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 25 Sep 2014 at 11:41am |
how are you joining EMPLOYEE to LABOR_TICKET?
What are all of the shift start and end formula's being referenced?
|
| IP Logged |
|
korey_premier
Newbie
Joined: 23 Jun 2014
Location: United States
Online Status: Offline
Posts: 8
|
Posted: 26 Sep 2014 at 2:24am |
|
I'm only joining the the two tables using an EMPLOYEE_ID field.
The formulas that calculate the start and end times for each shift are...
1st Shift Start - DateAdd('h', 4, {?Work Day})
1st Shift End - DateAdd('h', 16, {?Work Day})
2nd Shift Start - DateAdd('h', 14, {?Work Day})
2nd Shift End - DateAdd('h', 28, {?Work Day})
3rd Shift Start - DateAdd('h', 24, {?Work Day})
3rd Shift End - DateAdd('h', 32, {?Work Day})
The user of the report enters the {?Work Day} parameter as date (I've also tried this as a datetime field).
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 26 Sep 2014 at 3:59am |
This should do basically what you your initial formula was after but I am not sure it is doing what you want. YOu cannot alter actuall data in the data set with the If-Then. You can create a fomrual field based on data or select specific rows based on criteria but not change the original data. This is why I think you are trying to make the formula replace a join condition...
({EMPLOYEE.SHIFT_ID} = "1ST" and datediff('h',{?Work Day},{LABOR_TICKET.CLOCK_IN}) in 4 to 16)
or
({EMPLOYEE.SHIFT_ID} = "2ND" and datediff('h',{?Work Day},{LABOR_TICKET.CLOCK_IN}) in 14 to 28)
or
({EMPLOYEE.SHIFT_ID} = "3RD" and datediff('h',{?Work Day},{LABOR_TICKET.CLOCK_IN}) in 24 to 32) Edited by DBlank - 26 Sep 2014 at 4:00am
|
| IP Logged |
|
korey_premier
Newbie
Joined: 23 Jun 2014
Location: United States
Online Status: Offline
Posts: 8
|
Posted: 26 Sep 2014 at 6:11am |
|
I can't thank you enough for your help! Worked like I had hoped.
I was unaware that you could not select data using an if-then and appreciate your willingness help me learn.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 26 Sep 2014 at 6:25am |
glad you got it to work.
some people use if-then in the select but to my (limited) knowledge that is not a good way to approach it. The select should just return a boolean result (true/false) of an evaluation of the record/row in the overall data set. Your syntax should maximize that expecation and the use of the if-then is unnecessary. It still is doing a boolean evaluation of the if-then syntax, it is not actually setting any data.
Rather using syntax that combines the use of operators like AND and OR with () to properly enclose each combination of requirements (if necessary) makes it cleaner and easier to understand.
|
| IP Logged |
|
|
|
