| Author |
Message |
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
   Topic: Select by status from multiple tables
Posted: 07 Aug 2014 at 3:15am |
|
I'm probably making this way too complicated, but I have tried multiple potential solutions and am not getting anywhere.
The desired outcome is to select work orders that have a status of 2 and have subtask statuses of 9.
The problem is that there are three potential main tasks for the work order: STN1, STN2, and STN3. Each main task comes with a set of default subtasks, which typically (but not always) have a null default value, but can also have a value of 1, 2, or 9. The subtasks can have different statuses as work progresses. I only want to see records where the work order status =2, the main task is STN1, STN2, or STN3 and the work order subtasks statuses ALL have a value 9 (meaning all 3 to 5 subtask status are 9). I'm not interested in records with just one subtask status as 9.
The tables join: WKORDER.WO_ID = WKWOTSK.WT_WO_ID
I've tried a variety of solutions so far that seem really promising, but are not returning the desired result. I'm either getting work order records with all subtasks despite their status, or all work order records with at least one subtask status at 9. I tried changing my join several times but couldn't get the anticipated result.
My most recent solution that seemed promising was to create a formula for each of the potential subtasks:
if ({WKWOTSK.WT_TASK_TY}= "X" and {WKWOTSK.WT_STAT_CD}=9) then 0 else 1
and then created another formula that should give a value of 0 if all the subtasks returned 0, as {@SubtaskCalculator}:
if ({@InspecCalc}=0 and {@MeterFFCalc}=0 and {@MeterPitCalc}=0 and {@WaterTapCalc}=0 and {@SewerTapCalc}=0) then 0 else 1
I then added {@SubtaskCalculator} = 0 to my select expert. I didn't get any results at all, and I know the result should have been two records.
Any tips would be appreciated?
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Aug 2014 at 3:31am |
your doing a group select criteria
group on the WKORDER.WO_ID
in the select expert switch it to group select (show formula)
in the formula workshop (formula editor) switch it to 'use default values for nulls'
then use a group condition here
Maximum({WKWOTSK.WT_TASK_TY},{WKORDER.WO_ID})= "X" Maximum({WKWOTSK.WT_STAT_CD},{WKORDER.WO_ID})=9 and Minimum({WKWOTSK.WT_STAT_CD},{WKORDER.WO_ID})=9
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Aug 2014 at 3:31am |
|
also keep in mind that if you are doing any further summarization you have to use running totals or shared variables to get those values and excluded groups still appear in the group tree.
Edited by DBlank - 07 Aug 2014 at 3:32am
|
|
IP Logged |
|
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
Posted: 07 Aug 2014 at 5:17am |
|
DBlank:
I'm trying to understand what you are telling me to do.
Currently, I have my records grouped by WO_NUMBER (WKORDER table) because each work order has between 3 and 5 subtasks (WKWOTSK table) attached to it and I need to see the status of each subtask.
Are you telling me to keep my current record selection as
WO_STAT_CD = 2 and WO_ACTN_CD in [STN1, STN2, STN3]
and to additionally group on WKORDER.WO_ID? and then add the group select formula?
Also, should I replace the "X" in your formula with my subtasks?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Aug 2014 at 5:56am |
Sorry,
mixed up some of your logic when I was cutting and pasting.
You are trying to do do a group selection because your criteria is based on the the results of multiple rows across a group, correct?
You cannot apply selection criteria at the row level to accomplish that.
I can't picture your data set exactly so I am not sure the exact syntax but the concept is that you need to create a group result using group summary functions that allow you to include or excldeu an entire group based on those results.
If you post your sample data set I or somone might be able to give you the exact syntax.
|
|
IP Logged |
|
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
Posted: 07 Aug 2014 at 8:26am |
|
Okay, thank you. The group select information helped me. I don't use it that much so I forgot about that option.
My data looks like this:
Tables Fields used in the report
WKORDER WO_NUMBER, WO_ACTN_CD (main task), WO_STAT_CD (status)
WKWOTSK WT_TASK_CD (subtask), WT_STAT_CD (status)
Tables are joined as WKORDER.WO_ID = WKWOTSK.WT_WO_ID
Data is grouped by WKORDER.WO_NUMBER
DESIGN:
GH1 WKORDER.WO_NUMBER WO_ACTN_TY WO_STAT_TY
D WT_TASK_CD WT_STAT_CD
GF1
What I need are all work orders where
{WO_STAT_CD} = 2 and
{WO_ACTN_CD}) in [STN1, STN2, STN3) and
{WT_TASK_CD} must be one of ['WD 170', 'WM 53', 'WM 80', 'SD 02', 'SD 190']
This is where I am running into trouble because each work order can have any combination of the listed subtasks, but might not have them all. I need to return all work orders that have those subtasks, regardless of which ones they have, where the status of all the subtasks is 9.
What the might look like if I could figure it out would be:
Record 1 WKORDER.WO_NUMBER WO_ACTN_TY WO_STAT_TY
14-000014 STN1 2
WT_TASK_CD WT_STAT_CD
WD 170 9
WM 80 9
SD 190 9
Record 2 WKORDER.WO_NUMBER WO_ACTN_TY WO_STAT_TY
14-000015 STN3 2
WT_TASK_CD WT_STAT_CD
WD 170 9
WM 53 9
SD 02 9
|
|
IP Logged |
|
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
Posted: 15 Aug 2014 at 7:09am |
|
I finally was able to get this done. I stepped away from the report for a few days and with a fresh pair of eyes was able to modify your recommendation DBlank and make it work for my report in just a few minutes. Sometimes you just need to walk away.
The group select feature is what did the trick. I just substituted out WO_Number for WO_ID.
|
|
IP Logged |
|
|
|
