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?
I'm puzzled by your use of extend.
Allows a value in non-repeating Field to be used with every repetition in a repeating field.
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.
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?
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.
Okay, here is an example of where I use the process described above. This is how it looks in Browse mode:
And here it is in Layout mode:
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:
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.
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.
(( Premium * Extend ( Rate Factor ) - 52 ) * 1000 ) / Extend ( Monthly Rate 25 )
The non-repeating fields are the only things that need extend.
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 )
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.
I think my brain stem just exploded …
Anyway, yes, I think, given all the complications involved, normalizing your data might do you right.
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 …
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.
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.
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.