Created:
| Last Modified:
Hello,
I started working with Jump on 10/12/2020, so I am very new to all of this. I have solved most of the issues with this community board but I am completely stuck with this one.
I am attempting to create a tracker for technician training, hour logs, etc. I am using a for loop to gather the information and turn it into data tables to be graphed. I then, in the same for loop, create a button and append it to the main window for each technician. When the button is pressed, the technician's window comes up as a journal with 2 outline boxes.
Now, here's the issue:
When the button is pressed it opens a journal with the 2 outline boxes and the graph inside the correct outline box, however, the graph is only representative of the last name in the list. All of the graphs show the same data. I have tried countless things to try and fix this issue, but I am not experienced enough to see what I am missing.
Here is my script: (Be advised, the first section of the script are SQL scripts that I use to pull live data from another database. You will not be able to run this script)
// New Technician Tracker Script
//=== CLEAN UP WORKSPACE ===
Names Default To Here( 1 );
Clear Log();
//Close All(Windows, No Save);
Close All( Data Tables, No Save );
Close All( Journals, No Save );
Clear Symbols();
Write( "Cleaning up workspace\!N\!N" );
//Generating Main Page to append to
New Window( "Team Tracker",
Outline Box( "Team Tracker",
H List Box(
V List Box(
sh1 = Border Box( Left( 100 ), Right( 100 ), Top( 75 ), Bottom( 75 ), sides( 15 ),
V List Box(
Button Box( "Shift 1",
New Window( "Shift 1 Journal", <<journal, Button Box( "Close Journal", Close All( Journals, No Save ) ) )
)
)
)
)
)
)
);
//YTD OPS Hours
dtops = Open Database(
"DSN=SQL Database;Trusted_Connection=Yes;APP=JMP;WSID=KGORSAGE-MOBL1;",
"select distinct a.siteid as 'Site ID', a.assetnum as 'Asset Number', a.wonum as 'Work Order Number',
a.description as 'Work Order Description', a.status as 'WO Status',
a.worktype as 'Work Type', a.actfinish as 'Finish Date', a.actlabhrs as 'Actual Labor Hours',
b.craft as 'Lead Craft', b.laborcode as 'IDSID', b.regularhrs as 'Assigned Hours',
c.craft as 'Assignment Craft'
from MAXINTEL.hpm.hpm_workorder as a
left join (select refwo, siteid, laborcode, craft, regularhrs from MAXINTEL.hpm.hpm_labtrans)
as b on a.wonum = b.refwo and a.siteid = b.siteid
left join MAXINTEL.hpm.hpm_assignment as c on a.wonum = c.wonum
where a.status in ('COMP', 'close')
and b.craft in ('Waste')
and a.reportdate >= '01-01-2020'
and a.istask = 0
and a.worktype = ('OPS')
and a.glaccount = ('54150')
and b.laborcode in ('AHUTCHEN','EBROWN','EGILLIS','CTSPEARM','GJGOWENS','NFATHERE', 'NMCENTE', 'RJDELAH','SPHILLI1','SSKELDI','TBUCZKOW', 'TRISTONE','TSFRAKES', 'TYLERELL')
Order by a.actfinish desc, b.laborcode"
);
dtops << set name( "Year To Date OPS Hours" );
//YTD PM Hours
dtpm = Open Database(
"DSN=SQL Database;Trusted_Connection=Yes;APP=JMP;WSID=KGORSAGE-MOBL1;",
"select distinct a.siteid as 'Site ID', a.assetnum as 'Asset Number', a.wonum as 'Work Order Number',
a.description as 'Work Order Description', a.status as 'WO Status',
a.worktype as 'Work Type', a.actfinish as 'Finish Date', a.actlabhrs as 'Actual Labor Hours',
b.craft as 'Lead Craft', b.laborcode as 'IDSID', b.regularhrs as 'Assigned Hours',
c.craft as 'Assignment Craft'
from MAXINTEL.hpm.hpm_workorder as a
left join (select refwo, siteid, laborcode, craft, regularhrs from MAXINTEL.hpm.hpm_labtrans)
as b on a.wonum = b.refwo and a.siteid = b.siteid
left join MAXINTEL.hpm.hpm_assignment as c on a.wonum = c.wonum
where a.status in ('COMP', 'close')
and b.craft in ('Waste')
and a.reportdate >= '01-01-2020'
and a.istask = 0
and a.worktype = ('PM')
and a.glaccount = ('54150')
and b.laborcode in ('AHUTCHEN','EBROWN','EGILLIS','CTSPEARM','GJGOWENS','NFATHERE', 'NMCENTE', 'RJDELAH','SPHILLI1','SSKELDI','TBUCZKOW', 'TRISTONE','TSFRAKES', 'TYLERELL')
Order by a.actfinish desc, b.laborcode"
);
dtpm << set name( "Year To Date PM Hours" );
//YTD CM Hours
dtcm = Open Database(
"DSN=SQL Database;Trusted_Connection=Yes;APP=JMP;WSID=KGORSAGE-MOBL1;",
"select distinct a.siteid as 'Site ID', a.assetnum as 'Asset Number', a.wonum as 'Work Order Number',
a.description as 'Work Order Description', a.status as 'WO Status',
a.worktype as 'Work Type', a.actfinish as 'Finish Date', a.actlabhrs as 'Actual Labor Hours',
b.craft as 'Lead Craft', b.laborcode as 'IDSID', b.regularhrs as 'Assigned Hours',
c.craft as 'Assignment Craft'
from MAXINTEL.hpm.hpm_workorder as a
left join (select refwo, siteid, laborcode, craft, regularhrs from MAXINTEL.hpm.hpm_labtrans)
as b on a.wonum = b.refwo and a.siteid = b.siteid
left join MAXINTEL.hpm.hpm_assignment as c on a.wonum = c.wonum
where a.status in ('COMP', 'close')
and b.craft in ('Waste')
and a.reportdate >= '01-01-2020'
and a.istask = 0
and a.worktype = ('CM')
and a.glaccount = ('54150')
and b.laborcode in ('AHUTCHEN','EBROWN','EGILLIS','CTSPEARM','GJGOWENS','NFATHERE', 'NMCENTE', 'RJDELAH','SPHILLI1','SSKELDI','TBUCZKOW', 'TRISTONE','TSFRAKES', 'TYLERELL')
Order by a.actfinish desc, b.laborcode"
);
dtcm << set name( "Year To Date CM Hours" );
//YTD PR Hours
dtpr = Open Database(
"DSN=SQL Database;Trusted_Connection=Yes;APP=JMP;WSID=KGORSAGE-MOBL1;",
"select distinct a.siteid as 'Site ID', a.assetnum as 'Asset Number', a.wonum as 'Work Order Number',
a.description as 'Work Order Description', a.status as 'WO Status',
a.worktype as 'Work Type', a.actfinish as 'Finish Date', a.actlabhrs as 'Actual Labor Hours',
b.craft as 'Lead Craft', b.laborcode as 'IDSID', b.regularhrs as 'Assigned Hours',
c.craft as 'Assignment Craft'
from MAXINTEL.hpm.hpm_workorder as a
left join (select refwo, siteid, laborcode, craft, regularhrs from MAXINTEL.hpm.hpm_labtrans)
as b on a.wonum = b.refwo and a.siteid = b.siteid
left join MAXINTEL.hpm.hpm_assignment as c on a.wonum = c.wonum
where a.status in ('COMP', 'close')
and b.craft in ('Waste')
and a.reportdate >= '01-01-2020'
and a.istask = 0
and a.worktype = ('PR')
and a.glaccount = ('54150')
and b.laborcode in ('AHUTCHEN','EBROWN','EGILLIS','CTSPEARM','GJGOWENS','NFATHERE', 'NMCENTE', 'RJDELAH','SPHILLI1','SSKELDI','TBUCZKOW', 'TRISTONE','TSFRAKES', 'TYLERELL')
Order by a.actfinish desc, b.laborcode"
);
dtpr << set name( "Year To Date PR Hours" );
//YTD Trainee Hours
dttrainee = Open Database(
"DSN=SQL Database;Trusted_Connection=Yes;APP=JMP;WSID=KGORSAGE-MOBL1;",
"select distinct a.siteid as 'Site ID', a.assetnum as 'Asset Number', a.wonum as 'Work Order Number',
a.description as 'Work Order Description', a.status as 'WO Status',
a.worktype as 'Work Type', a.actfinish as 'Finish Date', a.actlabhrs as 'Actual Labor Hours',
b.craft as 'Lead Craft', b.laborcode as 'IDSID', b.regularhrs as 'Assigned Hours',
c.craft as 'Assignment Craft'
from MAXINTEL.hpm.hpm_workorder as a
left join (select refwo, siteid, laborcode, craft, regularhrs from MAXINTEL.hpm.hpm_labtrans)
as b on a.wonum = b.refwo and a.siteid = b.siteid
left join MAXINTEL.hpm.hpm_assignment as c on a.wonum = c.wonum
where a.status in ('COMP', 'close')
and b.craft in ('Waste')
and a.reportdate >= '01-01-2020'
and a.istask = 0
and c.craft in ('TRAINEE')
--and a.worktype = ('OPS')
and a.glaccount = ('54150')
and b.laborcode in ('AHUTCHEN','EBROWN','EGILLIS','CTSPEARM','GJGOWENS','NFATHERE', 'NMCENTE', 'RJDELAH','SPHILLI1','SSKELDI','TBUCZKOW', 'TRISTONE','TSFRAKES', 'TYLERELL')
Order by a.actfinish desc, b.laborcode"
);
dttrainee << set name( "Year to Date Trainee Hours" );
//YTD Trainer Hours
dttrainer = Open Database(
"DSN=SQL Database;Trusted_Connection=Yes;APP=JMP;WSID=KGORSAGE-MOBL1;",
"select distinct a.siteid as 'Site ID', a.assetnum as 'Asset Number', a.wonum as 'Work Order Number',
a.description as 'Work Order Description', a.status as 'WO Status',
a.worktype as 'Work Type', a.actfinish as 'Finish Date', a.actlabhrs as 'Actual Labor Hours',
b.craft as 'Lead Craft', b.laborcode as 'IDSID', b.regularhrs as 'Assigned Hours',
c.craft as 'Assignment Craft'
from MAXINTEL.hpm.hpm_workorder as a
left join (select refwo, siteid, laborcode, craft, regularhrs from MAXINTEL.hpm.hpm_labtrans)
as b on a.wonum = b.refwo and a.siteid = b.siteid
left join MAXINTEL.hpm.hpm_assignment as c on a.wonum = c.wonum
where a.status in ('COMP', 'close')
and a.reportdate >= '01-01-2020'
and a.istask = 0
and c.craft in ('TRAINER')
--and a.worktype = ('OPS')
and a.glaccount = ('54150')
and b.laborcode in ('AHUTCHEN','EBROWN','EGILLIS','CTSPEARM','GJGOWENS','NFATHERE', 'NMCENTE', 'RJDELAH','SPHILLI1','SSKELDI','TBUCZKOW', 'TRISTONE','TSFRAKES', 'TYLERELL')
Order by a.actfinish desc, b.laborcode"
);
dttrainer << set name( "Year to Date Trainer Hours" );
//Compiling all Hours into single table
dtcombined = Data Table( "Year to Date OPS Hours" ) << Concatenate(
Data Table( "Year to Date CM Hours" ),
Data Table( "Year to Date PM Hours" ),
Data Table( "Year to Date PR Hours" ),
Data Table( "Year to Date Trainer Hours" ),
Data Table( "Year to Date Trainee Hours" )
);
dtcombined << set name( "Total Hours for all WO's" );
dtcombined << New Column( "Technician Full Name",
Character,
"Continuous",
Format( "Best", 12 ),
Formula(
If(
:IDSID == "AHUTCHEN", "Angela Hutchens",
:IDSID == "CTSPEARM", "Colby Spearman",
:IDSID == "EBROWN", "Eric Brown",
:IDSID == "EGILLIS", "Eric Gillispie",
:IDSID == "GJGOWENS", "Gary Gowens",
:IDSID == "NFATHERE", "Andy Fatheree",
:IDSID == "NMCENTE", "Nico Mcentee",
:IDSID == "RJDELAH", "Ricardo De La Hoya",
:IDSID == "SPHILLI1", "Steven Phillips",
:IDSID == "SSKELDI", "Scott Skelding",
:IDSID == "TBUCZKOW", "Tyler 'Buck' Buczkowski",
:IDSID == "TRISTONE", "Triston Evans",
:IDSID == "TYLERELL", "Tyler Ellis",
:IDSID == "TSFRAKES", "Tyler Frakes "
)
),
Set Display Width( 88 )
);
//Creating Tech list
tech_list = (dtcombined << Tabulate( Show Control Panel( 0 ), Add Table( Row Table( Grouping Columns( :Technician Full Name ) ) ) ));
list = tech_list << Make Into Data Table;
list << set name( "Technicians" );
list << delete column( :N );
ntechnicians = N Rows( list );
techlist = list:Technician Full Name << get values;
//Creating Data Tables for each technician individually
For( itechnician = 1, itechnician <= ntechnicians, itechnician += 1,
technician_name = techlist[itechnician];
dtcombined << Select Where( :Technician Full Name == technician_name );
dtcombined << Subset( (Selected Rows( 1 )), selected columns( 0 ), output table name( technician_name || "_Full_Data" ) );
//Extracting hour types by Work Type
Data Table( technician_name || "_Full_Data" ) << New Column( "PM Hours",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( If( :Work Type == "PM", :Actual Labor Hours, 0 ) )
);
Data Table( technician_name || "_Full_Data" ) << New Column( "Trainer Hours",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( If( :Assignment Craft == "TRAINER", :Actual Labor Hours, 0 ) ),
Set Display Width( 88 )
);
Data Table( technician_name || "_Full_Data" ) << New Column( "Trainee Hours",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( If( :Assignment Craft == "TRAINEE", :Actual Labor Hours, 0 ) )
);
Data Table( technician_name || "_Full_Data" ) << New Column( "PR Hours",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( If( :Work Type == "PR", :Actual Labor Hours, 0 ) )
);
Data Table( technician_name || "_Full_Data" ) << New Column( "CM Hours",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( If( :Work Type == "CM", :Actual Labor Hours, 0 ) ),
);
Data Table( technician_name || "_Full_Data" ) << New Column( "OPS Hours",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( If( :Work Type == "OPS", :Actual Labor Hours, 0 ) ),
Set Selected
);
//Tabulating to prepare data for graphing
Tech_Tabulate_Temp = Data Table( technician_name || "_Full_Data" ) << Tabulate(
Show Control Panel( 0 ),
Add Table(
Column Table( Analysis Columns( :Trainer Hours ) ),
Column Table( Analysis Columns( :Trainee Hours ) ),
Column Table( Analysis Columns( :PR Hours ) ),
Column Table( Analysis Columns( :CM Hours ) ),
Column Table( Analysis Columns( :PM Hours ) ),
Column Table( Analysis Columns( :OPS Hours ) )
)
);
Tech_Tabulate = Tech_Tabulate_Temp << Make Into Data Table;
Tech_Tabulate << set name( technician_name || "Tabulated" );
//Changing labels for the sake of clarity
Column( Tech_Tabulate, 1 ) << Set Name( "Trainer Hours" );
Column( Tech_Tabulate, 2 ) << Set Name( "Trainee Hours" );
Column( Tech_Tabulate, 3 ) << Set Name( "PR Hours" );
Column( Tech_Tabulate, 4 ) << Set Name( "CM Hours" );
Column( Tech_Tabulate, 5 ) << Set Name( "PM Hours" );
Column( Tech_Tabulate, 6 ) << Set Name( "OPS Hours" );
//Transposing for optimal graphing
Prepared_Tech_Data = Tech_Tabulate << Transpose(
columns(
:Name( "Trainer Hours" ),
:Name( "Trainee Hours" ),
:Name( "PR Hours" ),
:Name( "CM Hours" ),
:Name( "PM Hours" ),
:Name( "OPS Hours" )
)
);
ptd = Prepared_Tech_Data << set name( technician_name || "_Tabulated Transposed" );
Column( 2 ) << Set Name( "Hours" );
//Creating a button for each technician for the main page
tb = Button Box( technician_name,
nw = New Window( "Technician Tracking",
<<journal,
Button Box( "Close Journal", Close All( Journals, No Save ) ),
ob = Outline Box( "YTD Hours Breakdown", gbb = Graph Builder(
Variables( X( :Hours ), Y( :Label ) ),
show control panel( 0 ),
show legend( 0 ),
show title( 0 ),
Elements( Bar( X, Y, Summary Statistic( "Sum" ), Label( "Label by Value" ) ) )
); ),
Outline Box( "Total Hours" )
)
);
//Appending the created button to the main page, closing/ hiding tables
sh1 << append( tb );
Close( Data Table( technician_name || "_Full_Data" ), No save );
Close( Data Table( technician_name || "Tabulated" ), No save );
Data Table( technician_name || "_Tabulated Transposed" ) << show window( 0 );
);
//Closing tables selectively
Close( Data Table( "Technicians" ), No save );
Close( Data Table( "Use for Graphs" ), No save );
Close( Data Table( "Use for Tech Tabulating" ), No save );
Close( Data Table( "Total Hours for all WO's" ), No save );
Close( Data Table( "Year to Date OPS Hours" ), nosave );
Close( Data Table( "Year to Date PM Hours" ), nosave );
Close( Data Table( "Year to Date CM Hours" ), nosave );
Close( Data Table( "Year to Date PR Hours" ), nosave );
Close( Data Table( "Year to Date Trainee Hours" ), nosave );
Close( Data Table( "Year to Date Trainer Hours" ), nosave );Someone, please help me!
Thank you,
Kyle
The main reason I chose a journal was to display all of the graphs in one clean presentation. The goal with this project is to have multiple graphs of training progress, hours worked, certification progress, etc, and from what I was reading Journals seemed like the best choice. If there is an easier way to go, by all means, I'll do it another way!
Can I add a filter function to the button box so that the graph is shown when you press the specific techs button? And I also changed the layout a little bit so they are now in tab boxes for easier sorting of information. My thought was that I could append each graph to the tab page box it belongs to
.
