Repeated calculations

[bpeirson]

A few weeks ago I recieved code similar to that listed below from lbass on this forum. It worked great as written. Then I copied and modified the code to what is shown.

//To be placed in the details section
whileprintingrecords;
numbervar WeightinPounds;
numbervar timeinminutes;
timeinminutes := timeinminutes + {Routing.CycleTime} + {Routing.SetupTime}/{QuoteDet.Qty1};
WeightinPounds := WeightinPounds + {Materials.PartWt}*{Materials.Qty}

As soon as I added the second formula, "timeinminutes" the "WeightinPounds" formula iterates for every instance of "timeinminutes" found in the "Routing" table.

Without "timeinminutes" the "WeightinPounds" works fine. I need both values on the report. Is there a way to calculate both without miscalculations?

There will be cases where multiple records exist in either the "Materials" table or the "Routing" table or both.

Thanks in advance.

 

[lbass]

You probably need to create a separate formula for timeinminutes that calculates only once per some group, but there is not enough information here to really be more specific. Sample data that shows how the data is repeating would help.

-LB

 

[bpeirson]

Based on your suggestion I separated the "timeinminutes" formula from the "weightinppounds" formula. First I put it in the detail section with the weight, then in its own detail section. Same reults either way. Then I tried "timeinminutes" in its own group. Then I swapped the groups. When I swapped the groups the time calculation was repeated and the weight calculation appears once. I have included some of the results below.

Each printout shows the suppressed sections so we can see what the formulas are doing. The first printout shows the correct weight calculation.

The time tally calulation is shown in minutes, the total is converted to hours. When both formulas are used none of the calculated values are correct.

Thanks again, Brad.

No "timeinminutes" formula

[tt]
Description Qty Unit Weight Total Weight Unit Hours Total Hours

SUPPRESSED GROUP HEADER
Weight & minute reset formulas 0.00 0.00

SUPPRESSED DETAILS
Weight tally 0.00
Weight tally 2.10
Weight tally 4.01
Weight tally 4.01
Weight tally 4.01
Weight tally 4.01

UNSUPRESSED GROUP FOOTER
PSH11C052 11 4.01 44.16 0.00 0.00

SUPPRESSED GROUP
Weight & minute reset formulas 0.00 0.00

SUPPRESSED DETAILS
Weight tally 15.83
Weight tally 16.36

UNSUPRESSED GROUP FOOTER
PSL11C2 1 16.36 16.36 0.00 0.00
[/tt]

"timeinminutes" formula in its own detail section

[tt]
Description Qty Unit Weight Total Weight Unit Hours Total Hours

SUPPRESSED GROUP HEADER
Weight & minute reset formulas 0.00 0.00

SUPPRESSED DETAILS
Weight tally 0.00
time tally 3.55
Weight tally 0.00
time tally 5.16
Weight tally 2.10
time tally 8.70
Weight tally 4.01
time tally 12.25
Weight tally 4.01
time tally 15.80
Weight tally 4.01
time tally 19.34
Weight tally 4.01
time tally 22.89
Weight tally 4.01
time tally 25.34
Weight tally 4.01
time tally 26.95
Weight tally 4.01
time tally 29.41
Weight tally 6.11
time tally 30.20
Weight tally 6.11
time tally 32.66
Weight tally 6.11
time tally 35.11
Weight tally 8.03
time tally 37.57
Weight tally 10.13
time tally 40.02
Weight tally 10.13
time tally 41.64
Weight tally 10.13
time tally 43.25
Weight tally 12.04
time tally 44.86
Weight tally 14.14
time tally 46.48
Weight tally 14.14
time tally 47.27
Weight tally 14.14
time tally 48.18
Weight tally 16.06
time tally 48.98
Weight tally 16.06
time tally 49.77
Weight tally 16.06
time tally 50.57
Weight tally 18.16
time tally 51.48
Weight tally 20.07
time tally 52.39
Weight tally 20.07
time tally 53.30
Weight tally 20.07
time tally 54.20
Weight tally 20.07
time tally 55.11
Weight tally 20.07
time tally 55.91

UNSUPRESSED GROUP FOOTER
PSH11C052 11 20.07 220.79 0.93 10.25

SUPPRESSED GROUP HEADER
Weight & minute reset formulas 0.00 0.00

SUPPRESSED DETAILS
Weight tally 15.83
time tally 10.00
Weight tally 16.36
time tally 18.00
Weight tally 16.89
time tally 24.56
Weight tally 32.72
time tally 31.13
Weight tally 33.25
time tally 34.13
Weight tally 49.08
time tally 37.13
Weight tally 49.61
time tally 47.13
Weight tally 50.15
time tally 53.38
Weight tally 65.98
time tally 59.63
Weight tally 66.51
time tally 67.13
Weight tally 82.34
time tally 75.13
Weight tally 98.17
time tally 82.63

UNSUPRESSED GROUP FOOTER
PSL11C2 1 98.17 98.17 1.38 1.38
[/tt]

"timeinminutes" formula in its own Group Section

[tt]
Description Qty Unit Weight Total Weight Unit Hours Total Hours

SUPPRESSED GROUP HEADERS
Weight reset formula 0.00
minute reset formula 0.00

SUPPRESSED DETAILS
Weight tally 0.00
Weight tally 0.00
Weight tally 2.10
Weight tally 4.01
Weight tally 4.01
Weight tally 4.01
Weight tally 4.01
Weight tally 4.01
Weight tally 4.01
Weight tally 4.01
Weight tally 6.11
Weight tally 6.11
Weight tally 6.11
Weight tally 8.03
Weight tally 10.13
Weight tally 10.13
Weight tally 10.13
Weight tally 12.04
Weight tally 14.14
Weight tally 14.14
Weight tally 14.14
Weight tally 16.06
Weight tally 16.06
Weight tally 16.06
Weight tally 18.16
Weight tally 20.07
Weight tally 20.07
Weight tally 20.07
Weight tally 20.07
Weight tally 20.07

SUPRESSED GROUP FOOTER
time tally 0.80

UNSUPRESSED GROUP FOOTER
PSH11C052 11 20.07 220.79 0.01 0.15

SUPPRESSED GROUP HEADERS
Weight reset formula 0.00
minute reset formula 0.00

SUPPRESSED DETAILS
Weight tally 15.83
Weight tally 16.36
Weight tally 16.89
Weight tally 32.72
Weight tally 33.25
Weight tally 49.08
Weight tally 49.61
Weight tally 50.15
Weight tally 65.98
Weight tally 66.51
Weight tally 82.34
Weight tally 98.17

SUPRESSED GROUP FOOTER
time tally 7.50

UNSUPRESSED GROUP FOOTER
PSL11C2 1 98.17 98.17 0.13 0.13
[/tt]

 

[lbass]

I don't really understand why you are using variables in the first place and I can't find your original thread. If adding the route table is causing the problem, you could add the route table and calculations related to it in a subreport.

-LB

 

[bpeirson]

I am trying to use the formulas for 3 reasons:
[ol]
[li]The running total fields won't let me do a running total of one field multilpied by another field.[/li]
[li]As I was building the report column by column it seemed OK to use a formula for time because it worked so well for weight.[/li]
[li]Our preferred format is to have all the information on one line per part number, a subreport will not allow this.[/li]
[/ol]

The original thread is here thread766-1476557. I'm not sure if anything in that thread will help now. The solution then required a correction to problems with my table links. I have revised my links in this case but I may try again.

It looks like I will probably use a sub-report as you suggest.

Brad

 

[lbass]

1-Running totals can use a formula that multiplies as the field to summarize--as long as it doesn't already contain a summary or a whileprintingrecords feature like the previous() function, for example.

2-Makes sense.

3-Using a subreport does not rule out having info on one line. A subreport can be formatted so that it displays only one field, for example. Or it can be not displayed, but pass values to a later section.

-LB