Trouble with breaking by SUBSTRING

[Rio38]

I am having problems with a report in that it is not hitting the IF statements based on the BREAK BY statements in teh FOR EACH block.

Examples below.

I am creating a report and my main FOR EACH block looks like the following:

[SIZE=2]FOR EACH wo_mstr USE-INDEX wo_nbr NO-LOCK WHERE[/SIZE]
[SIZE=2](wo_site >= site AND wo_site <= site1) AND[/SIZE]
[SIZE=2](wo_nbr >= order AND wo_nbr <= order1)[/SIZE]
[SIZE=2]    BREAK BY wo_site[/SIZE]
[SIZE=2]             BY SUBSTRING(wo_nbr,1,2)[/SIZE]
[SIZE=2]             BY wo_nbr:[/SIZE]

I then have the following code later in the program to display the totals for all of the Work Orders with the same first two characters and various other totals based on the BREAK BY statements:

[SIZE=2]IF sum_det = "S" THEN[/SIZE]
[SIZE=2]DO:[/SIZE]
[SIZE=2]mthd_var = tot_var - (mtl_scrap + labor_scrap + [/SIZE]
[SIZE=2]eff_var + sub_var + czeh_plug).[/SIZE]
[SIZE=2]display[/SIZE]
[SIZE=2]wo_nbr column-label "Order"[/SIZE]
[SIZE=2]wo_status column-label "St"[/SIZE]
[SIZE=2]can-find(first tr_hist where[/SIZE]
[SIZE=2]tr_nbr = wo_nbr and [/SIZE]
[SIZE=2]tr_type = "wo-close" and[/SIZE]
[SIZE=2]tr_effdate >= cdate and[/SIZE]
[SIZE=2]tr_effdate <= cdate1) @ mfc_logical[/SIZE]
[SIZE=2]column-label "Acctg!Clse"[/SIZE]
[SIZE=2]substring(wo__chr05,1,8) @ wo__chr05[/SIZE]
[SIZE=2]column-label "Mfg!Close"[/SIZE]
[SIZE=2]wo_part column-label "Item Number"[/SIZE]
[SIZE=2]wo_qty_comp column-label "Qty Completed"[/SIZE]
[SIZE=2]mtl_scrap column-label "Net!Material!Yield"[/SIZE]
[SIZE=2]format "->,>>>,>>9.99"[/SIZE]
[SIZE=2]labor_scrap column-label "Labor Burden!Yield"[/SIZE]
[SIZE=2]format "->,>>>,>>9.99"[/SIZE]
[SIZE=2]eff_var column-label "Efficiency"[/SIZE]
[SIZE=2]format "->,>>>,>>9.99"[/SIZE]
[SIZE=2]sub_var column-label "Substitute!Part"[/SIZE]
[SIZE=2]format "->,>>>,>>9.99"[/SIZE]
[SIZE=2]czeh_plug column-label "Czech!Plug"[/SIZE]
[SIZE=2]format "->,>>>,>>9.99"[/SIZE]
[SIZE=2]mthd_var column-label "Method!Variance"[/SIZE]
[SIZE=2]format "->,>>>,>>9.99"[/SIZE]
[SIZE=2]tot_var column-label "Total!Variance"[/SIZE]
[SIZE=2]format "->,>>>,>>9.99"[/SIZE]
[SIZE=2]with width 200.[/SIZE]
[SIZE=2]ACCUMULATE w_mthd_oth (TOTAL by wo_site by substring(wo_nbr,1,2)).[/SIZE]
[SIZE=2]ACCUMULATE tot_var (TOTAL by wo_site by[/SIZE]
[SIZE=2]substring(wo_nbr,1,2)).[/SIZE]
[SIZE=2]ACCUMULATE mtl_scrap (TOTAL by wo_site by[/SIZE]
[SIZE=2]substring(wo_nbr,1,2)).[/SIZE]
[SIZE=2]ACCUMULATE labor_scrap (TOTAL by wo_site by[/SIZE]
[SIZE=2]substring(wo_nbr,1,2)).[/SIZE]
[SIZE=2]ACCUMULATE eff_var (TOTAL by wo_site by[/SIZE]
[SIZE=2]substring(wo_nbr,1,2)).[/SIZE]
[SIZE=2]ACCUMULATE sub_var (TOTAL by wo_site by[/SIZE]
[SIZE=2]substring(wo_nbr,1,2)).[/SIZE]
[SIZE=2]ACCUMULATE czeh_plug (TOTAL by wo_site by[/SIZE]
[SIZE=2]substring(wo_nbr,1,2)).[/SIZE]
[SIZE=2]ACCUMULATE mthd_var (TOTAL BY wo_site by[/SIZE]
[SIZE=2]substring(wo_nbr,1,2)).[/SIZE]
 
[SIZE=2]if last-of(substring(wo_nbr,1,2)) then do:[/SIZE]
[SIZE=2]UNDERLINE mtl_scrap labor_scrap tot_var[/SIZE]
[SIZE=2]mthd_var czeh_plug eff_var sub_var[/SIZE]
[SIZE=2]WITH WIDTH 200.[/SIZE]
 
[SIZE=2]display[/SIZE]
[SIZE=2]"Total: " + substring(wo_nbr,1,2) @ wo_part[/SIZE]
[SIZE=2]ACCUM TOTAL BY substring(wo_nbr,1,2) [/SIZE]
[SIZE=2]mtl_scrap @ mtl_scrap[/SIZE]
[SIZE=2]ACCUM TOTAL by substring(wo_nbr,1,2) [/SIZE]
[SIZE=2]mthd_var @ mthd_var [/SIZE]
[SIZE=2]ACCUM TOTAL by substring(wo_nbr,1,2) [/SIZE]
[SIZE=2]tot_var @ tot_var[/SIZE]
[SIZE=2]ACCUM TOTAL by substring(wo_nbr,1,2) [/SIZE]
[SIZE=2]labor_Scrap @ labor_scrap[/SIZE]
[SIZE=2]ACCUM TOTAL by substring(wo_nbr,1,2) [/SIZE]
[SIZE=2]eff_var @ eff_var[/SIZE]
[SIZE=2]ACCUM TOTAL by substring(wo_nbr,1,2) [/SIZE]
[SIZE=2]sub_var @ sub_var[/SIZE]
[SIZE=2]ACCUM TOTAL by substring(wo_nbr,1,2) [/SIZE]
[SIZE=2]czeh_plug @ czeh_plug[/SIZE]
[SIZE=2]WITH WIDTH 200.[/SIZE]
[SIZE=2]down 1 with width 200.[/SIZE]
[SIZE=2]end.[/SIZE]
 
[SIZE=2]if last-of(wo_site) then do:[/SIZE]
[SIZE=2]UNDERLINE mtl_scrap labor_scrap tot_var[/SIZE]
[SIZE=2]mthd_var czeh_plug eff_var sub_var[/SIZE]
[SIZE=2]WITH WIDTH 200.[/SIZE]
 
[SIZE=2]DISPLAY[/SIZE]
[SIZE=2]"Total: " + wo_site @ wo_part[/SIZE]
[SIZE=2]ACCUM TOTAL BY wo_site mtl_scrap @ mtl_scrap[/SIZE]
[SIZE=2]ACCUM TOTAL BY wo_site mthd_var @ mthd_var[/SIZE]
[SIZE=2]ACCUM TOTAL BY wo_site tot_var @ tot_var[/SIZE]
[SIZE=2]ACCUM TOTAL BY wo_site labor_Scrap @ labor_scrap[/SIZE]
[SIZE=2]ACCUM TOTAL BY wo_site eff_var @ eff_var[/SIZE]
[SIZE=2]ACCUM TOTAL BY wo_site sub_var @ sub_var[/SIZE]
[SIZE=2]ACCUM TOTAL BY wo_site czeh_plug @ czeh_plug[/SIZE]
[SIZE=2]WITH WIDTH 200.[/SIZE]
 
[SIZE=2]end.[/SIZE]
 
[SIZE=2]IF LAST(wo_nbr) THEN DO:[/SIZE]
[SIZE=2]UNDERLINE mtl_scrap labor_scrap tot_var[/SIZE]
[SIZE=2]mthd_var czeh_plug eff_var sub_var[/SIZE]
[SIZE=2]WITH WIDTH 200.[/SIZE]
 
[SIZE=2]DISPLAY[/SIZE]
[SIZE=2]"Total: " + wo_site @ wo_part[/SIZE]
[SIZE=2]ACCUM TOTAL mtl_scrap @ mtl_scrap[/SIZE]
[SIZE=2]ACCUM TOTAL mthd_var @ mthd_var[/SIZE]
[SIZE=2]ACCUM TOTAL tot_var @ tot_var[/SIZE]
[SIZE=2]ACCUM TOTAL labor_Scrap @ labor_scrap[/SIZE]
[SIZE=2]ACCUM TOTAL eff_var @ eff_var[/SIZE]
[SIZE=2]ACCUM TOTAL sub_var @ sub_var[/SIZE]
[SIZE=2]ACCUM TOTAL czeh_plug @ czeh_plug[/SIZE]
[SIZE=2]WITH WIDTH 200.[/SIZE]
 
[SIZE=2]END.[/SIZE]
[SIZE=2]END. /* IF sum_det = "S" */ [/SIZE]

 

[ramyapriyacsit]

I think the Break By is working fine with substring function
check the flow of the program
it is becoz something else

Regards

 

[Rio38]

I found that it was actually the NEXT statements I had in the routine. They just happened to fall on the last-of condition for that field. I replaced the next statements with some logic to either diplay or not display the record and then handled it later.

Thanks for your replies.

 

[ramyapriyacsit]

Fine !!