6 Replies Latest reply on Nov 2, 2016 6:28 PM by philmodjunk

    Sum part of repeating field

    philmcgeehan

      Any one know if there is a way to return a sum of part of a Repeating field?

      I have a numeric field with 30 repetitions; I'd like to be able to display the first 15 in the normal way, but then I want to have a calculated field next to those that shows the sum of the remaining repeating fields (repetitions 16 - 30).

        • 1. Re: Sum part of repeating field
          philmodjunk

          Sounds like you shouldn't be using a repeating field for this, but

           

          Field[16] + Field[17] + field [18] + and so forth.... + field [30]

           

          Will compute the desired result as long as you use your field name in place of "field" and spell out all the repetitions instead of "and so forth..."

          • 2. Re: Sum part of repeating field
            philmcgeehan

            I thought it'd be a bit tidier using a repeating field; I've not really used them before and thought this would be a good case to try it out.

            Guess I'll go back to the trusted separate fields.

            Thanks for your help.

            • 3. Re: Sum part of repeating field
              David Moyer

              Hi,

              I hope that doesn't mean 30 separate fields.  That would be almost sinful.  If so, I recommend that you create a related table to store that data.  Repeating fields should really only be used for display purposes.

              • 4. Re: Sum part of repeating field
                BruceRobertson

                +1 for David's suggestion.

                I strongly encourage you to go that way.

                Separate fields is certainly the worst way.

                Though I discourage the idea, the sum of repeated fields would be relatively easy to set up.

                Field[16] + Field[17] + field [18] etc

                 

                But it would be quite helpful if you can provide a more meaningful description. The idea of the reps 16-30 representing some different attribute as compared to 1-15 seems odd. What is this really all about?

                • 5. Re: Sum part of repeating field
                  philmcgeehan

                  I appreciate its not the best way to go about it, having multiple separate fields, which is why I thought the repeating field would be a good idea in the first place.

                   

                  It's for gathering the required quantities of different materials on each floor of a building.

                  I have an Estimate table that has a related table called Quantities. Displayed in a portal on the Estimate layout, the users can select a product in the first column of the Quantities portal, then, they want to be able to enter the quantity required on each floor (that's what I was planning on using the repeating field for, the different floors, Ground - 29th).

                   

                  The reason for trying to sum the last half of the floors was to truncate a printed report; where the quantities for Ground to 15th floor would be displayed, and then a sum of the remaining floors, so I could fit them all on a page.

                   

                  Is there a better way of trying to accomplish what I'm trying to do? I'm sure there probably is, but I got a bit stuck on how best to add a product, then add different quantities for different floors for that product, in the Estimate layout.

                  • 6. Re: Sum part of repeating field
                    philmodjunk

                    As others suggested, use a related table with one related record in place of each repetition. If each record has a product ID and a quantity, you will have a number of options available for summing groups of records where the product ID is the same.

                     

                    Summary reports

                    ExecuteSQL

                    Self Join Relationships

                     

                    are all possible options to get the needed sub totals.