4 Replies Latest reply on Jul 17, 2013 10:15 AM by stewart_1

    Sub totals from Value List



      Sub totals from Value List


           We have a script in a record that is used to log production hours. Each record can have multiple people listed. The script is made up of several Value Lists. The two that concern me are the Name and Hrs lists.

           We have a total Hrs field at the bottom of the Hrs list. What we would like to do is break down the total Hrs into sub totals for each person in the Name list. The Names list can have any number of different people or just one person.

           Is there a way to create a sub total of Hrs for each individual in the Names value list?

           I've inlcluded screen shots of the Script in Brows and Layout mode.


        • 1. Re: Sub totals from Value List

               You need to make some major changes to the structure of your database. The repeating fields shown in the left hand side should be replaced with individual records in a related table. That is crucial to setting up a system where you can produce the needed summary report with your sub totals.

               What is the structure of the data shown on the right hand side? Is each row of data shown also entered into a different repetition of a repeating field?

          • 2. Re: Sub totals from Value List

                 I suspected that our current setup would not do what we want; I just needed to confirm. Both sides of the of the script are set up the same. They have always served us well until this recent request to generate sub totals of the hours.

                 Our original creator of the this database is no longer with us so we're having to catch up on how to make these things work. I expect we'll probably keep the database as it is.

                 Thanks for the feedback.

            • 3. Re: Sub totals from Value List

                   As you evaluate the possibility of restructuring your database, keep in mind that FileMaker has an option that can be used with the Import Records menu option/script step that makes moving data from a set of repeating fields into a set of separate records in a related table a very simple and straight forward operation. Thus moving your data will be pretty easy, but you'd then need to redesign layouts, scripts etc to use the new data model.

                   But also keep in mind that using the current set up that went obsolete with the release of FileMaker 3, severely restricts the options of what you can do with this data.

              • 4. Re: Sub totals from Value List

                     Many thanks fof the advice. I'll run this be the powers that be to see what they think. This database has been around since the early 90's. I'm not surprosed that we may be passed due to start from scratch.