325396_fr-FR

liliconcom

Besoin d'aide avec la boucle for et l'attribution de journaux via des boîtes à boutons

Bonjour,

 

J'ai commencé à travailler avec Jump le 10/12/2020, donc je suis très novice dans tout cela. J'ai résolu la plupart des problèmes avec ce tableau communautaire, mais je suis complètement coincé avec celui-ci.

 

J'essaie de créer un tracker pour la formation des techniciens, les journaux d'heures, etc. J'utilise une boucle for pour rassembler les informations et les transformer en tableaux de données à représenter graphiquement. Ensuite, dans la même boucle for, je crée un bouton et l'ajoute à la fenêtre principale pour chaque technicien. Lorsque le bouton est enfoncé, la fenêtre du technicien apparaît sous la forme d'un journal avec 2 zones de contour.

 

 

Maintenant, voici le problème :

 

Lorsque le bouton est enfoncé, il ouvre un journal avec les 2 cases de contour et le graphique à l'intérieur de la bonne zone de contour, cependant, le graphique n'est représentatif que du nom de famille dans la liste. Tous les graphiques montrent les mêmes données. J'ai essayé d'innombrables choses pour essayer de résoudre ce problème, mais je ne suis pas assez expérimenté pour voir ce qui me manque.

Voici mon script : (Attention, la première section du script sont des scripts SQL que j'utilise pour extraire des données en direct d'une autre base de données. Vous ne pourrez pas exécuter ce 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>= '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></JOURNAL></JOURNAL>

Quelqu'un, s'il vous plaît aidez-moi!

 

Merci,

 

Kyle

Tableau de donnéesGraphiqueScriptRe : Besoin d'aide avec la boucle for et l'attribution de journaux via des boîtes à boutons

J'ai retravaillé mon code, et il semble faire la substitution correctement.Je suppose que le générateur de graphes devrait s'exécuter sur la table de données Jim_Tabulated Transposed si le bouton Jim avait été enfoncé, et si le bouton Steve avait été enfoncé, il s'exécuterait sur la table de données Steve_Tabulated Transposed.

// 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>= '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
 Eval(
  Substitute(
    Expr(
     tb = Button Box( technician_name,
      nw = New Window( "Technician Tracking",
       <<JOURNAL></JOURNAL></JOURNAL>

Re : Besoin d'aide avec la boucle for et l'attribution de journaux via des boîtes à boutons

D'autres idées sur la façon dont je pourrais résoudre ce problème? Vous avez raison de ne tirer que de la dernière table. Dans votre script, il fait également appel à la table du dernier technicien. Existe-t-il un moyen de lui faire vérifier le nom du bouton par rapport au nom de la table qu'il tire?

 

Merci de votre aide

Re : Besoin d'aide avec la boucle for et l'attribution de journaux via des boîtes à boutons

La principale raison pour laquelle j'ai choisi un journal était d'afficher tous les graphiques dans une présentation claire. L'objectif de ce projet est d'avoir plusieurs graphiques de progression de la formation, des heures travaillées, de la progression de la certification, etc., et d'après ce que je lisais, les revues semblaient être le meilleur choix. S'il y a un moyen plus facile d'y aller, bien sûr, je le ferai d'une autre manière!

 

Puis-je ajouter une fonction de filtre à la boîte à boutons afin que le graphique s'affiche lorsque vous appuyez sur le bouton techs spécifique ? Et j'ai aussi un peu changé la mise en page pour qu'elles soient maintenant dans des onglets pour faciliter le tri des informations. Ma pensée était que je pouvais ajouter chaque graphique à la boîte de page d'onglet à laquelle il appartient

 

.

Re : Besoin d'aide avec la boucle for et l'attribution de journaux via des boîtes à boutons

si toutes les données de tous les techniciens se trouvaient dans une table, vous pourriez faire figurer le générateur de graphiques que vous avez et ajouter un filtre de données local pour modifier le contenu du graphique pour chaque niveau de technologie.

 

Les revues sont un peu pénibles à travailler comme ça. Y a-t-il une raison spécifique pour laquelle vous choisissez cette méthode d'affichage ?

 

 

Re : Besoin d'aide avec la boucle for et l'attribution de journaux via des boîtes à boutons

Je ne sais pas à quoi cela ressemblerait.

 

Comme je l'ai dit, j'ai commencé à apprendre ce lundi dernier et j'essaie d'apprendre en déplacement ici.

!Re : Besoin d'aide avec la boucle for et l'attribution des journaux via les boîtes à boutons

Merci pour votre réponse!

 

La révision n'a pas semblé résoudre le problème, elle indique que la table de données ne peut pas être localisée dans l'accès ou l'évaluation et que l'argument du graphique n'est pas valide. Je ne sais pas de quelles données vous avez besoin pour vous aider. Je peux copier tous les tableaux de données et les coller ici ? Voici au moins les tableaux transposés tabulés :

New Table( "Tyler Frakes_Tabulated Transposed",
 Add Rows( 6 ),
 New Script(
  "Source",
  Data Table( "Tyler FrakesTabulated" ) <<
  Transpose(
   columns(
    :Trainer Hours,
    :Trainee Hours,
    :PR Hours,
    :CM Hours,
    :PM Hours,
    :OPS Hours
   ),
   Output Table( "Transpose of Tyler FrakesTabulated" )
  )
 ),
 New Column( "Label",
  Character( 13 ),
  "Nominal",
  Set Values(
   {"Trainer Hours", "Trainee Hours", "PR Hours", "CM Hours", "PM Hours",
   "OPS Hours"}
  )
 ),
 New Column( "Hours",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values(
   [16.6666666666667, 67, 28.3666666666667, 277.383333333333,
   340.533333333333, 823.533333333333]
  )
 ),
 Set Label Columns( :Label )
)

New Table( "Tyler Ellis_Tabulated Transposed",
 Add Rows( 6 ),
 New Script(
  "Source",
  Data Table( "Tyler EllisTabulated" ) <<
  Transpose(
   columns(
    :Trainer Hours,
    :Trainee Hours,
    :PR Hours,
    :CM Hours,
    :PM Hours,
    :OPS Hours
   ),
   Output Table( "Transpose of Tyler EllisTabulated" )
  )
 ),
 New Column( "Label",
  Character( 13 ),
  "Nominal",
  Set Values(
   {"Trainer Hours", "Trainee Hours", "PR Hours", "CM Hours", "PM Hours",
   "OPS Hours"}
  )
 ),
 New Column( "Hours",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [90, 82, 326.45, 419.966666666667, 315.65, 650.816666666667] )
 ),
 Set Label Columns( :Label )
)

New Table( "Tyler 'Buck' Buczkowski_Tabulated Transposed",
 Add Rows( 6 ),
 New Script(
  "Source",
  Data Table( "Tyler 'Buck' BuczkowskiTabulated" ) <<
  Transpose(
   columns(
    :Trainer Hours,
    :Trainee Hours,
    :PR Hours,
    :CM Hours,
    :PM Hours,
    :OPS Hours
   ),
   Output Table( "Transpose of Tyler 'Buck' BuczkowskiTabulated" )
  )
 ),
 New Column( "Label",
  Character( 13 ),
  "Nominal",
  Set Values(
   {"Trainer Hours", "Trainee Hours", "PR Hours", "CM Hours", "PM Hours",
   "OPS Hours"}
  )
 ),
 New Column( "Hours",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [0, 67, 17.5, 233.05, 223.883333333333, 736.566666666667] )
 ),
 Set Label Columns( :Label )
)

New Table( "Steven Phillips_Tabulated Transposed",
 Add Rows( 6 ),
 New Script(
  "Source",
  Data Table( "Steven PhillipsTabulated" ) <<
  Transpose(
   columns(
    :Trainer Hours,
    :Trainee Hours,
    :PR Hours,
    :CM Hours,
    :PM Hours,
    :OPS Hours
   ),
   Output Table( "Transpose of Steven PhillipsTabulated" )
  )
 ),
 New Column( "Label",
  Character( 13 ),
  "Nominal",
  Set Values(
   {"Trainer Hours", "Trainee Hours", "PR Hours", "CM Hours", "PM Hours",
   "OPS Hours"}
  )
 ),
 New Column( "Hours",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values(
   [27.5, 117, 93.9166666666667, 187.266666666667, 312.2, 632.333333333333]
  )
 ),
 Set Label Columns( :Label )
)

New Table( "Triston Evans_Tabulated Transposed",
 Add Rows( 6 ),
 New Script(
  "Source",
  Data Table( "Triston EvansTabulated" ) <<
  Transpose(
   columns(
    :Trainer Hours,
    :Trainee Hours,
    :PR Hours,
    :CM Hours,
    :PM Hours,
    :OPS Hours
   ),
   Output Table( "Transpose of Triston EvansTabulated" )
  )
 ),
 New Column( "Label",
  Character( 13 ),
  "Nominal",
  Set Values(
   {"Trainer Hours", "Trainee Hours", "PR Hours", "CM Hours", "PM Hours",
   "OPS Hours"}
  )
 ),
 New Column( "Hours",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [6.5, 125, 658.533333333333, 259.233333333333, 281.7, 773.25] )
 ),
 Set Label Columns( :Label )
)

New Table( "Scott Skelding_Tabulated Transposed",
 Add Rows( 6 ),
 New Script(
  "Source",
  Data Table( "Scott SkeldingTabulated" ) <<
  Transpose(
   columns(
    :Trainer Hours,
    :Trainee Hours,
    :PR Hours,
    :CM Hours,
    :PM Hours,
    :OPS Hours
   ),
   Output Table( "Transpose of Scott SkeldingTabulated" )
  )
 ),
 New Column( "Label",
  Character( 13 ),
  "Nominal",
  Set Values(
   {"Trainer Hours", "Trainee Hours", "PR Hours", "CM Hours", "PM Hours",
   "OPS Hours"}
  )
 ),
 New Column( "Hours",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values(
   [82.5, 21.2666666666667, 67.4166666666667, 185.4, 113.25,
   608.933333333333]
  )
 ),
 Set Label Columns( :Label )
)

New Table( "Ricardo De La Hoya_Tabulated Transposed",
 Add Rows( 6 ),
 New Script(
  "Source",
  Data Table( "Ricardo De La HoyaTabulated" ) <<
  Transpose(
   columns(
    :Trainer Hours,
    :Trainee Hours,
    :PR Hours,
    :CM Hours,
    :PM Hours,
    :OPS Hours
   ),
   Output Table( "Transpose of Ricardo De La HoyaTabulated" )
  )
 ),
 New Column( "Label",
  Character( 13 ),
  "Nominal",
  Set Values(
   {"Trainer Hours", "Trainee Hours", "PR Hours", "CM Hours", "PM Hours",
   "OPS Hours"}
  )
 ),
 New Column( "Hours",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [25.5, 60.5, 7.25, 44.3333333333333, 96.5, 73] )
 ),
 Set Label Columns( :Label )
)

New Table( "Nico Mcentee_Tabulated Transposed",
 Add Rows( 6 ),
 New Script(
  "Source",
  Data Table( "Nico McenteeTabulated" ) <<
  Transpose(
   columns(
    :Trainer Hours,
    :Trainee Hours,
    :PR Hours,
    :CM Hours,
    :PM Hours,
    :OPS Hours
   ),
   Output Table( "Transpose of Nico McenteeTabulated" )
  )
 ),
 New Column( "Label",
  Character( 13 ),
  "Nominal",
  Set Values(
   {"Trainer Hours", "Trainee Hours", "PR Hours", "CM Hours", "PM Hours",
   "OPS Hours"}
  )
 ),
 New Column( "Hours",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [17, 76, 86.9666666666667, 211.45, 210, 634.016666666667] )
 ),
 Set Label Columns( :Label )
)

New Table( "Gary Gowens_Tabulated Transposed",
 Add Rows( 6 ),
 New Script(
  "Source",
  Data Table( "Gary GowensTabulated" ) <<
  Transpose(
   columns(
    :Trainer Hours,
    :Trainee Hours,
    :PR Hours,
    :CM Hours,
    :PM Hours,
    :OPS Hours
   ),
   Output Table( "Transpose of Gary GowensTabulated" )
  )
 ),
 New Column( "Label",
  Character( 13 ),
  "Nominal",
  Set Values(
   {"Trainer Hours", "Trainee Hours", "PR Hours", "CM Hours", "PM Hours",
   "OPS Hours"}
  )
 ),
 New Column( "Hours",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values(
   [16.5, 88.1666666666667, 58.5, 419.05, 236.083333333333,
   663.966666666667]
  )
 ),
 Set Label Columns( :Label )
)

New Table( "Eric Gillispie_Tabulated Transposed",
 Add Rows( 6 ),
 New Script(
  "Source",
  Data Table( "Eric GillispieTabulated" ) <<
  Transpose(
   columns(
    :Trainer Hours,
    :Trainee Hours,
    :PR Hours,
    :CM Hours,
    :PM Hours,
    :OPS Hours
   ),
   Output Table( "Transpose of Eric GillispieTabulated" )
  )
 ),
 New Column( "Label",
  Character( 13 ),
  "Nominal",
  Set Values(
   {"Trainer Hours", "Trainee Hours", "PR Hours", "CM Hours", "PM Hours",
   "OPS Hours"}
  )
 ),
 New Column( "Hours",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [10, 76.2, 229.25, 180.866666666667, 316, 635.266666666667] )
 ),
 Set Label Columns( :Label )
)

New Table( "Eric Brown_Tabulated Transposed",
 Add Rows( 6 ),
 New Script(
  "Source",
  Data Table( "Eric BrownTabulated" ) <<
  Transpose(
   columns(
    :Trainer Hours,
    :Trainee Hours,
    :PR Hours,
    :CM Hours,
    :PM Hours,
    :OPS Hours
   ),
   Output Table( "Transpose of Eric BrownTabulated" )
  )
 ),
 New Column( "Label",
  Character( 13 ),
  "Nominal",
  Set Values(
   {"Trainer Hours", "Trainee Hours", "PR Hours", "CM Hours", "PM Hours",
   "OPS Hours"}
  )
 ),
 New Column( "Hours",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values(
   [29.3333333333333, 57, 20.7166666666667, 88.8166666666667,
   148.966666666667, 723.516666666667]
  )
 ),
 Set Label Columns( :Label )
)

New Table( "Colby Spearman_Tabulated Transposed",
 Add Rows( 6 ),
 New Script(
  "Source",
  Data Table( "Colby SpearmanTabulated" ) <<
  Transpose(
   columns(
    :Trainer Hours,
    :Trainee Hours,
    :PR Hours,
    :CM Hours,
    :PM Hours,
    :OPS Hours
   ),
   Output Table( "Transpose of Colby SpearmanTabulated" )
  )
 ),
 New Column( "Label",
  Character( 13 ),
  "Nominal",
  Set Values(
   {"Trainer Hours", "Trainee Hours", "PR Hours", "CM Hours", "PM Hours",
   "OPS Hours"}
  )
 ),
 New Column( "Hours",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [0, 24.3333333333333, 2.5, 54.5, 0, 116.8] )
 ),
 Set Label Columns( :Label )
)

New Table( "Angela Hutchens_Tabulated Transposed",
 Add Rows( 6 ),
 New Script(
  "Source",
  Data Table( "Angela HutchensTabulated" ) <<
  Transpose(
   columns(
    :Trainer Hours,
    :Trainee Hours,
    :PR Hours,
    :CM Hours,
    :PM Hours,
    :OPS Hours
   ),
   Output Table( "Transpose of Angela HutchensTabulated" )
  )
 ),
 New Column( "Label",
  Character( 13 ),
  "Nominal",
  Set Values(
   {"Trainer Hours", "Trainee Hours", "PR Hours", "CM Hours", "PM Hours",
   "OPS Hours"}
  )
 ),
 New Column( "Hours",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [69.5, 0, 0, 3, 6, 11] )
 ),
 Set Label Columns( :Label )
)

New Table( "Andy Fatheree_Tabulated Transposed",
 Add Rows( 6 ),
 New Script(
  "Source",
  Data Table( "Andy FathereeTabulated" ) <<
  Transpose(
   columns(
    :Trainer Hours,
    :Trainee Hours,
    :PR Hours,
    :CM Hours,
    :PM Hours,
    :OPS Hours
   ),
   Output Table( "Transpose of Andy FathereeTabulated" )
  )
 ),
 New Column( "Label",
  Character( 13 ),
  "Nominal",
  Set Values(
   {"Trainer Hours", "Trainee Hours", "PR Hours", "CM Hours", "PM Hours",
   "OPS Hours"}
  )
 ),
 New Column( "Hours",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [31.5, 23.9, 278.75, 109.2, 114.85, 793.516666666667] )
 ),
 Set Label Columns( :Label )
)

Re : Besoin d'aide avec la boucle for et l'attribution de journaux via des boîtes à boutons

Je pense que le problème est que votre plate-forme Graph Builder s'exécute toujours sur la dernière table créée pour le dernier technicien.

D'après ce que je peux dire, il existe une table de données distincte pour chaque technicien et vous avez besoin du générateur de graphiques pour pointer vers cette table de données spécifique.

Si vous changez avec le code suivant, je pense que vous obtiendrez ce que vous voulez.À noter, je n'ai pas pu tester cela car aucune donnée réelle n'a été publiée

tb = Button Box( technician_name,
 nw = New Window( "Technician Tracking",
  <<JOURNAL></JOURNAL>

Re : Besoin d'aide avec la boucle for et l'attribution de journaux via des boîtes à boutons

cela fonctionnerait-il de créer un graphique avec un filtre de données local ?

de cette façon, vous obtenez tous les liens dynamiques entre le graphique et les données.