9 Replies Latest reply on Sep 23, 2013 11:16 AM by productionQC

    Calculating Totals based on dropdown menu selection???

    productionQC

      Title

      Calculating Totals based on dropdown menu selection???

      Post

           Hello -

           I have a table called RepairOrders in which the user selects a JobType and a Location, performs the work, then the cost of that job is entered in a field called JobCost.  I have one layout used for data entry, then I have another layout, still based on RepairOrders, in which we review the data using charts etc.  It is setup to sort records based on the user selecting a "Crews" name from a dropdown and only those records are shown/charted.  What we would like to be able to do is show total costs based on location and total costs based on Jobtype. 

           Ex:  Backyard $5000.00
                  Patio        $2500.00

           We are able to show the total of all cost for each crew, but how do we break it down to location and again break it down to job type.  I have used FilterValues ( Work Required ;"Patch" ) to flag records and then used a summary count in another field to provide how many of that type of repair that crew has logged against them.  After the user selects the crew and the records are sorted, I am not quite sure how to program a field to report the totals of each location and each type of repair.

           Help?

            

        • 1. Re: Calculating Totals based on dropdown menu selection???
          philmodjunk

               What is the basic design of your RepairOrders records. Is each of these values entered into a different record in that table or do you use a repeating field or group of individual records to record different costs in the same record?

               When you "chart" your data, I assume that you are using a FileMaker chart object to chart the data? If so, what kind of chart? If not, exactly what do you mean by "chart"?

          • 2. Re: Calculating Totals based on dropdown menu selection???
            productionQC

                 Hi Phil,

                  

                 Each repair order is entered as a record.  On the Repair Order layout, the user selects new record, then selects the following from drop-down fields:  Crew, Job Type, Job Location and then enters the job cost.  We then have a layout based on Repair Orders that the user selects the crew, say "Bobs Team".  With a simple script, we sort the records based on that crew.  Within Filemakers charts we have created a simple bar graph that displays the total number of jobs under each category of job type and another graph showing by location.   This all works fine, but I am confused as to how to proceed.  When the user selects a crew, I need to total the JobCost field based on each of the locations.  For example if the user selects "Bobs Crew" then I need to see a total  of say "Back Yard" and have a total of the JobCost field representing that location.  I am expecting to create a field called BackYardCost and have a calculation that totals the JobCost field only where a record has the field JobLocation set to "Back Yard" from the drop down.  So on for each of the set locations in the dropdown and again for each JobType.

                  

                 Hope this makes sense.

                  

                  

            • 3. Re: Calculating Totals based on dropdown menu selection???
              philmodjunk

                   It confirms most of what I thought, but when you chart your data, are you limiting the chart to just one crew?

                   If so, a summary field can compute the subtotal for each specified value in the location field and each bar can show the subtotal for that location. It's a matter of selecting the correct values for you Y series and then specifying the "summarized data" data source option. Then you also need to sort your records by the location field.

                   If you need to chart multiple crews in one chart with location based subtotals for each crew, then things become more complex due to the fact that FileMaker Charts only support sorting/grouping records on a single field. You end up having to create a calcualtion field that combines data from the two fields into a single value that correctly groups data for the chart.

              • 4. Re: Calculating Totals based on dropdown menu selection???
                productionQC

                     Hi Phil-

                      

                     I have upload screenshots of the review layout.  The first image shows the crew and year set to all.  The second image shows the crew set to one particular crew.  As you can see there is a line graph in the upper right.  I need to change the y to reflected the total $$ of each jobtype.  Thusly when the category is all, it should show $$ of all crews for basement, garage, driveway, etc.  and when it is set to a crew, it should show me the totals of only that crew.  The script for the dropdown crew and year selection is as follows...

                Set Error Capture [ On ]

                Enter Find Mode [ ]

                If [ REPAIRORDER::Finisher = "ALL" ]

                Show All Records

                Set Field [ REPAIRORDER::YEARExtract ; REPAIRORDER::ReportGraphYear ]

                Perform Find [ ]

                Else

                Set Field [ REPAIRORDER::FinisherExstract ; REPAIRORDER::Finisher ]

                If [ REPAIRORDER::ReportGraphYear = "ALL" ]

                Show All Records

                Else

                Set Field [ REPAIRORDER::YEARExtract ; REPAIRORDER::ReportGraphYear ]

                End If

                Perform Find [ ]

                End If

                • 6. Re: Calculating Totals based on dropdown menu selection???
                  philmodjunk

                       And this does not change my previous suggestions.

                       Your script finds records, but does not sort them. The records have to be sorted by the Location field so that they are grouped on the different values in that field. Then you can get a chart where each y-value represents a different sub total. You have to specify a summary field  that totals your cost field as the y value.

                  • 7. Re: Calculating Totals based on dropdown menu selection???
                    productionQC

                         Hi Phil,

                         So where do I perform the sort, in the summary field, how?

                    • 8. Re: Calculating Totals based on dropdown menu selection???
                      philmodjunk

                           Include the Sort Records step in the script that you posted. Specify the location field.

                           And I see in your examples more than one chart with data grouped on more than one category. What I am describing gets this working for grouping by only one specified field. To show multiple charts like your screen shot has is going to be a problem due to the limitations in how FileMaker charts work. You'll be able to chart you data by location or by repair type, not both on the same layout. You may have to copy a chart's image into a container field, then sort the data again before you can show both charts at the same time.

                      • 9. Re: Calculating Totals based on dropdown menu selection???
                        productionQC

                             Hi Phil-

                             It looks like I will have to have multiple layouts to achieve this...

                             As always, thank you for your help!