1 2 Previous Next 15 Replies Latest reply on Nov 17, 2016 6:19 AM by erolst

    How to create a report to show totals for multiple fields within a record

    Tom_Droz

      Hi All

       

      FM14 ADV

       

      I have a record that has a technician with total hours billed.  I can generate a report easily that will show how many hours each technician worked on a found set of records.

       

      However there is a 2nd option within the record.  If there were multiple technicians that had hours on that record, the file is setup so that there can be up to 3 technicians with hours for each.

       

      My question is how do I do a report showing the total for each technician?

       

      I think I need to run an if statement, so that if there is detail breakout, I need to show that data, if not, show the selected technican.  But if there is multiple technicians how do I show totals for each in a found set?

       

      One thought was to do looping and create a virtual list, but then I am not sure how many empty records I would need in my VL table.  Probably in the thousands potentially.

       

      Any other thoughts on how to make this work?

       

       

       

      Tom

        • 1. Re: How to create a report to show totals for multiple fields within a record
          planteg

          Hi Tom,

           

          I wonder if your table definition is not wrong. As far as I understand, there are three fields for a record in order that for one record, on two or three amounts would be set, for one to three technicians. I thing you would better have a related table that would capture the time spent by each technician (on a project, or ?). That would be easier to create a report. What are these records about: are they time entries for the technicians ?

           

          Without knowing more details, it's a hard to provide a solution. And yes maybe a virtual table would be helpful.

           

          Hope that help

          • 2. Re: How to create a report to show totals for multiple fields within a record
            Tom_Droz

            planteg

            I agree, if I was starting from scratch that is what I would do, but that isn't an option.

             

            The old file has 2 fields, total hours and tech (name)

             

            For many reasons I am stuck with the above, so for the multiple tech option (which is used some of the time)

            I added 3 text fields, tech1, tech2, tech3.

            and 3 number fields hours1, hours2, hours3

             

            If I were to place these in a portal table, I would then have some records with a portal entry and most without.

             

            Maybe that isn't the best way to add the new fields

            • 3. Re: How to create a report to show totals for multiple fields within a record
              planteg

              There are still some things I don't understand.

               

              As far as I understand, the report should show the total amount worked for a (some) technician(s) in a found set. How is the found set gathered: for a specific period, month ?


              The way your table is constructed, a specific technician name may appear on any of the 3 columns, so that's hard to compute the total for a specific technician.

               

              I and others would like to help you, but I fell there are few pieces information missing.

               

              I am sorry.

              • 4. Re: How to create a report to show totals for multiple fields within a record
                Tom_Droz

                planteg

                Not sure how the found set is generated matters, but lets say it is for a time period.

                 

                The result I need to show for that period is

                 

                Tec1 34 total hours

                tec2 27 total hours

                Tec1's name could be in any of the 4 fields that hold a tech's name and could be or not be in any given field

                 

                thanks

                • 5. Re: How to create a report to show totals for multiple fields within a record
                  BruceRobertson

                  If the database was properly designed, the reporting would be simple, and minimal or zero volunteer hours would be required to build the report.

                   

                  Since the database is NOT properly designed, the choice is either for you to spend time modifying the design and updating the data. Or for volunteers to spend the time communicating, building, testing, to accommodate what should not need to be done at all. Some will be OK with participating. Some will not.

                  • 6. Re: How to create a report to show totals for multiple fields within a record
                    philmodjunk

                    From what you have described just in this thread. The design change needed to move this data into a related table with one record for each technicians hours, but linked to the original record in a relationship would not be all that difficult to do.

                     

                    Redesigning would then seem the more profitable use of your time.

                     

                    You might consider, as a temporary measure, setting up a script to export this data into a report table where the data is properly broken up into separate records for each technician. Once that works for the report, it's a pretty short trip to make that report table the actual table to record technician hours and a copy of your export script can be used to export all existing data into such a table.

                    • 7. Re: How to create a report to show totals for multiple fields within a record
                      Tom_Droz

                      Phil

                      I take your point that a redesign seems like the best idea.

                      • 8. Re: How to create a report to show totals for multiple fields within a record
                        erolst

                        Tom_Droz wrote:

                        [...] point that a redesign seems like the best idea.

                        ... has been everybody's point from the get-go.

                        • 9. Re: How to create a report to show totals for multiple fields within a record
                          keywords

                          As I understand it, all three pairs of fields belong to a single job record, but not all will be used. Have you tried creating a report layout with tech1, tech2, tech3 fields stacked one above the other on the body of the report, and each side by side with its corresponding hours field. If all fields were then set to sliding up, only those containing data would show on the report.

                          • 10. Re: How to create a report to show totals for multiple fields within a record
                            Tom_Droz

                            Ok, I have redesign the data so there is now a tech table which has the tech name and the number of hours worked.

                             

                            This has created a new issue....

                             

                            The job has the total hours calculated for the job,  and 99% of the time 1 tech did all the hours.

                             

                            How do I assign all the hours, by default, to tech1 and still allow for the 1% of time, for the hours to be manually allocated?

                             

                            thanks!

                            • 11. Re: How to create a report to show totals for multiple fields within a record
                              erolst

                              Tom_Droz wrote:

                              The job has the total hours calculated for the job, and 99% of the time 1 tech did all the hours.

                               

                              How do I assign all the hours, by default, to tech1 and still allow for the 1% of time, for the hours to be manually allocated?

                              If there's 1 tech, you create 1 related record. If there are 2 techs, you create 2 related records etc. Since by definition there is always at least one technician, you could automate the creation of the first related record.

                               

                              Don't create a setup where the first technician and their hours is entered in the Job record proper, and only 2 etc. go into related records; that will thoroughly defy the purpose. Best now to get rid of the tech and techHours field in the Job table altogether (after you've transferred existing data into the related table), so there can not be any confusion.

                               

                              However many technicians are involved, the total is simply the sum of the hours in all related records, whatever their count may be.

                              • 12. Re: How to create a report to show totals for multiple fields within a record
                                Tom_Droz

                                Thank you Erlost

                                 

                                I have it set so when the 1st tech is selected, it is assigned as the 1st related record.  If they want to assign more techs they click on a popup that allows more techs entries via a portal.

                                 

                                So 2 tables "Shop" "Tech"  with a TO Shop_TECH

                                 

                                Lets say one tech works on the whole job and the total hours in "Shop" for various activities is 15.

                                 

                                What is the best way to place "15" in to the hours for Tech1 in table "Tech"?

                                 

                                I could script it so that every time hours changes that it would update the total for tech1 to match the total hours, if only one tech exist.  This just dosent seem very smooth.

                                • 13. Re: How to create a report to show totals for multiple fields within a record
                                  erolst

                                  Tom_Droz wrote:

                                  What is the best way to place "15" in to the hours for Tech1 in table "Tech"?

                                   

                                  I could script it so that every time hours changes that it would update the total for tech1 to match the total hours, if only one tech exist. This just dosent seem very smooth.

                                  I would separate "various" hours from the tech's hours; if you don't want to do that, and one technicians hours are always the same as "various", I'd enter it into the related tech record and have the Job field updated via script.

                                   

                                  But you could also conveniently use a (par force) unstored calculation field that can take into account the number of related tech records to get the correct number; then on conclusion of the job, you could transfer that number into a stored number field to improve performance for reports etc.

                                   

                                  It all depends on your own business rules; maybe someone more knowledgeable will come up with a better suggestion.

                                  • 14. Re: How to create a report to show totals for multiple fields within a record
                                    Tom_Droz

                                    Erolst

                                    Thanks for your feedback and time on this.

                                     

                                    As the total hours is in the shop table, and a 2nd tech is rarely used, I decided to script it so that when the shop hours change, if there is only one related record in the tech table, I set the tech hours to the total hours in shop.

                                     

                                    If they decide to enter a 2nd related record then the script exits instead of setting the tech hours

                                    1 2 Previous Next