AnsweredAssumed Answered

Calculation Field works with one record but does not work after sort?

Question asked by heykug on Sep 14, 2018
Latest reply on Sep 17, 2018 by heykug

I have tables:

Labor

Equipment

Rentals

Materials

Subs

that contains costs by jNum by date and a report pulling summary totals by job for each of those fields called sumL, sumE, sumR, sumM, and sumS (each of those are summaries of cost by type). I have a calculation field for "total billing cost" that is SUM( sumL; sumE; sumR; sumM; sumS).

I have created a report module that allows us to search for specific date ranges and job numbers that sorts Location then Job, so all job numbers and correlating sum's plus total billing appear beneath the Location when run. I.e.,

(not sure if you guys can see that...so it is similar to below!)

          jnum           sumL          sumE          sumR          sumM          sumS                              total billing cost

SITE FACILITY

          100              100          100               100               100          100                                        500

 

The report is working nicely, except for the total billing field. If we run it for ONE job, it sums correctly. If no date or job parameters are set, then it runs all data for all jobs from start to current (which is on purpose), subsummary data by loc then job, then sorts by loc and job, but the calculation for "total summary" is all out of whack. I can't even identify where the totals are coming from, or a pattern.

 

(again, similar to below if you cannot see the screenshot!)

     jnum           sumL          sumE          sumR          sumM          sumS                              total billing cost

SITE FACILITY

          100              100          100               100               100          100                                     14,542,562

          101               200          200               100               200          200                                    14,859,455

          102               200          300               100               200          200                                    14,400,451

          102               600          200               100               200          200                                    14,100,038

SITE FACILITY 1

          200

          201

          202

and so on.

 

Why does it work with one record, but not with multiples if the data is there? Someone please tell me what I am missing!! Please and thank you!

Outcomes