325396_zh-CN

liliconcom

在 for 循环和通过按钮框分配日志方面需要帮助

你好，

 

我于 2020 年 10 月 12 日开始使用 Jump，所以我对这一切都很陌生。 我已经解决了这个社区委员会的大部分问题，但我完全被这个问题所困扰。

 

我正在尝试为技术人员培训、小时日志等创建跟踪器。 我正在使用 for 循环来收集信息并将其转换为要绘制的数据表。 然后，我在同一个 for 循环中创建一个按钮并将其附加到每个技术人员的主窗口中。 按下按钮时，技术人员的窗口将显示为带有 2 个轮廓框的日志。

 

 

现在，问题来了：

 

当按下按钮时，它会打开一个带有 2 个轮廓框和正确轮廓框内的图表的日志，但是，该图表仅代表列表中的姓氏。 所有图表都显示相同的数据。 我已经尝试了无数的方法来尝试解决这个问题，但是我没有足够的经验来了解我缺少什么。

这是我的脚本：（请注意，脚本的第一部分是我用来从另一个数据库中提取实时数据的 SQL 脚本。 您将无法运行此脚本）

 

// 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>

有人，请帮助我！

 

谢谢，

 

凯尔

数据表图形脚本回复：在 for 循环和通过按钮框分配日志方面需要帮助

我已经修改了我的代码，并且它似乎正确地进行了替换。我假设如果按下 Jim 按钮，Graph Builder 应该在数据表 Jim_Tabulated Transposed 上运行，如果按下 Steve 按钮，它将针对 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>

回复：在 for 循环和通过按钮框分配日志方面需要帮助

关于我如何解决这个问题的任何其他想法？ 你是对的，它只从最后一张桌子上拉出来。 在您的脚本中，它还调用了最后一个技术表。 有没有办法让它检查按钮的名称和它正在拉起的表的名称？

 

谢谢您的帮助

回复：在 for 循环和通过按钮框分配日志方面需要帮助

我选择期刊的主要原因是在一个简洁的演示文稿中显示所有图表。 这个项目的目标是有多个关于培训进度、工作时间、认证进度等的图表，从我正在阅读的内容来看，期刊似乎是最好的选择。 如果有更简单的方法，无论如何，我会用另一种方法来做！

 

我可以在按钮框中添加过滤器功能，以便在您按下特定技术按钮时显示图表吗？ 而且我还稍微更改了布局，因此它们现在位于选项卡框中，以便于对信息进行排序。 我的想法是我可以将每个图表附加到它所属的标签页框

 

.

回复：在 for 循环和通过按钮框分配日志方面需要帮助

如果来自所有技术的所有数据都在一个表中，那么您可以制作图形构建器图形并添加本地数据过滤器以更改每个技术级别的图形内容。

 

像这样使用期刊有点痛苦。 您选择这种显示方法有什么特定的原因吗？

 

 

回复：在 for 循环和通过按钮框分配日志方面需要帮助

我不确定那会是什么样子。

 

正如我所说，我上周一开始学习这个，并试图在这里学习。

!Re: 在 for 循环和通过按钮框分配日志方面需要帮助

感谢你的回复！

 

修订版似乎没有解决问题，它说数据表无法位于访问或评估中，并且图表的参数无效。 我不确定你需要什么数据来帮助解决这个问题。 我可以复制所有数据表并将它们粘贴到这里吗？ 以下是至少列出的转置表：

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 )
)

回复：在 for 循环和通过按钮框分配日志方面需要帮助

我认为问题在于您的 Graph Builder 平台始终针对为最后一个技术人员创建的最后一个表运行。

据我所知，每个技术人员都有一个单独的数据表，您需要 Graph Builder 指向该特定数据表。

如果你改用下面的代码，相信你会得到你想要的。作为说明，我无法对此进行测试，因为没有发布真实数据

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

回复：在 for 循环和通过按钮框分配日志方面需要帮助

用本地数据过滤器制作图表会起作用吗？

这样您就可以获得图形和数据之间的所有动态链接。