1 2 Previous Next 19 Replies Latest reply on Sep 11, 2015 6:27 PM by Mike_Mitchell

    Repeating field within calculation field in Extend function

    steveald

      I use the Extend function to build a grid of numbers based on a repeating field that acts as the header row for the grid - much like a table in Excel. Each row is a calculation field containing an Extend function.

       

      For example, the repeating field may be 10, 20, 30, 40, ... and each row of the grid below it contains a calculation field that follows the format: RepeatingField * Extend ( CalcFieldA ) / Extend ( Row1CalcFieldB ); RepeatingField * Extend ( CalcFieldA ) / Extend ( Row2CalcFieldB )

       

      This time, I need to build an Extend function where one of the calculation fields contains the repeating field in the calculation. In the above example, CalcFieldA would be: ( RepeatingField * CalcFieldB - CalcFieldC ) * CalcFieldD )

       

      I can't think of any way to get the RepeatingField outside of the Extend portion of the equation. Is there another method I can use to create the rows of the grid that achieves the same effect as the Extend function on a repeating field?

       

      I thought about flipping it sideways and making the repeating field the vertical axis of the grid instead of the horizontal axis, but there is no single formula that will populate all the elements of the repeating field (the numbers incrementally increase, but not evenly).

        • 1. Re: Repeating field within calculation field in Extend function
          Mike_Mitchell

          I'm confused by what you mean by "same effect as the Extend function on a repeating field". Are you trying to fetch a particular repetition of the repeating field? Or are you trying to copy all repetitions of the repeating field into a single value?

          • 2. Re: Repeating field within calculation field in Extend function
            Extensitech

            I'm puzzled by your use of extend.

            Purpose

            Allows a value in non-repeating Field to be used with every repetition in a repeating field.

            Format

            Extend(non-repeatingField)

            Given that, I can't be sure what you mean by

            ...build an Extend function...

            Could you maybe indicate which of the fields you're describing are repeating, and which aren't?  A little background on what you're trying to achieve may make it easier to visualize your issue, too.

             

            Chris Cain

            Extensitech

            • 3. Re: Repeating field within calculation field in Extend function
              steveald

              Sorry, I tried to be as detailed as possible. The issue I see is this: normally, the repeating field sits alone in the calculation while all the other calculation fields are inside an Extend function, as in: RepField * Extend (CalcField1) / Extend (CalcField2). This time, the formula I have to work with requires the repeating field to be within one of the calculation field formulas. Does that make more sense?

              • 4. Re: Repeating field within calculation field in Extend function
                Mike_Mitchell

                No. 

                 

                You already said that. Please explain what you are actually trying to do. We don't need to know what the syntax of your calculation is; we need to know what outcome you're trying to achieve.

                • 5. Re: Repeating field within calculation field in Extend function
                  steveald

                  Okay, here is an example of where I use the process described above. This is how it looks in Browse mode:

                  Browse.jpg

                  And here it is in Layout mode:

                  Layout.jpg

                  The top row is a calculation field with over 10 repetitions and defined as: KCL Life Benefits * 1000 where KCL Life Benefits is a repeating field (over 10 reps) - part of which looks like this:

                  Repeating.jpg

                  So, you can see how the top of the grid is set up.

                  Each row in the grid is also a calculation field with over 10 repetitions and defined as: KCL Life Benefits * Extend ( Rate Factor ) * Extend ( Monthly Rate 25 ) where each successive row is calculated using Monthly Rate 29, Monthly Rate 34, etc.

                  (Oddly yes, in this example, the first three rows do produce the same results.)

                   

                  My problem is this. Above, the repeating field is separate from the other parts of the formula and can be easily used in a calculation using the Extend function. Now, the formula I have to use has the repeating field embedded within it. The formula is: (( Premium * Rate Factor - 52 ) * 1000 ) / Monthly Rate 25 where Premium is the repeating field. (It would be nice if Monthly Rate 25 was the repeating field, but it's not.) I can't think of any way to make a calculation field that says: Premium +-*/ Extend ( the rest of the formula ). So, I was hoping there may be another FileMaker function that would allow me to achieve the same results.

                  • 6. Re: Repeating field within calculation field in Extend function
                    Mike_Mitchell

                    I really don't understand your formula, but if you're wanting to make Monthly Rate 25 a repeating field, you can do that with a calculation equal to Extend ( Monthly Rate 25 ) with an appropriate number of repetitions.

                     

                    Another way to solve the conundrum would be to get rid of the entire repeating field business and use an actual table instead. This would be an appropriate place to use a join table:

                     

                         Age Bracket -- < Premium >-- Benefit

                     

                    where each record in the table represents the intersection between the age bracket and the benefit. It's then a simple matter to calculate the appropriate value based on the parent record (which you would probably want to do as either an auto-enter calculation or using a script to avoid large numbers of unstored calculations). In other words, the data model should probably be changed to a more normalized setup.

                     

                    The grid can then be reproduced from the join table using a number of different techniques, including the Virtual List, a grid based on global fields and relationships, etc.

                    • 7. Re: Repeating field within calculation field in Extend function
                      Extensitech

                      (( Premium * Extend ( Rate Factor ) - 52 ) * 1000 ) / Extend ( Monthly Rate 25 )

                       

                      ?

                       

                      The non-repeating fields are the only things that need extend.

                       

                      Chris Cain

                      Extensitech

                      • 8. Re: Repeating field within calculation field in Extend function
                        erolst

                        steveald wrote:

                        The formula is: (( Premium * Rate Factor - 52 ) * 1000 ) / Monthly Rate 25 where Premium is the repeating field. (It would be nice if Monthly Rate 25 was the repeating field, but it's not.

                        You cannot extend an expression, but you can (and must) extend the components of the expression that are non-repeating fields; so that would be

                         

                        ( Premium * Extend ( Rate Factor ) - 52 ) * 1000 /

                        Extend ( Monthly Rate 25 )

                        • 9. Re: Repeating field within calculation field in Extend function
                          steveald

                          A join table may be the alternative I was looking for. I will have to read up on it as I have never used one before.

                           

                          The formula is for calculating how much Life insurance benefit you can purchase for specific amounts of payroll deduction based on your age. Premium is the payroll deduction amount and is a repeating field that actually changes based on how often you get paid. Weekly premiums might be $3, $4, $5, ... while monthly premiums might be $5, $10, $15, .... Rate Factor is a number determined by how often you get paid. And Monthly Rate 25 is one of several values determined by your age and the kind of work you do (older people in more dangerous jobs get higher rates). The result is the benefit - i.e. a 30 year old in an easy job can pay $40 per month to get a $100,000 benefit. It's the reverse of the grid above that shows how much it will cost you weekly to get certain amount of benefit.

                          • 10. Re: Repeating field within calculation field in Extend function
                            Mike_Mitchell

                            I think my brain stem just exploded …  

                             

                            Anyway, yes, I think, given all the complications involved, normalizing your data might do you right.

                            • 11. Re: Repeating field within calculation field in Extend function
                              erolst

                              Well, a join table would the proper place to store your data – while repeating fields can be a valuable tool to display derived data, i.e. summarize them.

                               

                              So it still pays to know how to use them, and that Extend() works with non-repeating fields …

                              • 12. Re: Repeating field within calculation field in Extend function
                                steveald

                                Thank you, Chris (and erolst)!

                                 

                                Apparently, I was hung up on the format of my original calculations and didn't realize Extend could be applied within the calculation like that. I got close, but I had applied the Extend to everything except for Premium.

                                • 13. Re: Repeating field within calculation field in Extend function
                                  Extensitech

                                  Couldn't agree more, btw, although I'm still keen to solve the OP issue.

                                   

                                  Repeating fields were nice in FM2.0, when FM wasn't relational. As an artifact, they end up being useful for faux arrays and layout tricks, but this does have the feel of circumventing correct data modeling. Fields that end up with numbers in them are also a red flag that you're missing a chance to simplify things by normalizing.

                                   

                                  All that being said, sometimes "down and dirty" gets the job done.

                                   

                                  Chris Cain

                                  Extensitech

                                  • 14. Re: Repeating field within calculation field in Extend function
                                    steveald

                                    I'll definitely research join tables and normalizing data. I just wonder if it would have worked in this case given the variety of repeating- and non-repeating fields and other changing factors involved.

                                     

                                    Thanks again, everyone.

                                    1 2 Previous Next