I am running FM7 on a Mac with 10.3.5.

I am looking to have field that will do calculations. I need 15
iterations.

"Price" will contain the starting price. There will be 15 price fields.

"Number" will contain the number of items being priced.

"Increment will contain the increment for the increase in each of the 14
subsequent" price fields.

"Total" will contain the 15 calculated values using the formula:

Total(1)=Price(1) * Number

Total(2)=Price(2) * Number

Total(3)=Price(3) * Number

Etc.

The "Price" field will be calculated as follows:

Price(2) = Price(1) + Increment
Price(3) = Price(2) + Increment

etc.

Obviously the Prices will have to be calculated before the Totals are
calculated.

Thanks in advance.

Rich

In article <1glglqr.13a369xli8nb6N%rich.sagall (AT) pobox (DOT) com>,
rich.sagall (AT) pobox (DOT) com (Rich Sagall) wrote:

Do NOT use Repeating fields. They're ancient left-overs that are only
half-implemented and so cause difficulty in doing many things.

In your case you'd be best to have 15 separate fields - change the
database's structure now, hopefully before you've got a ton of data
entered into it.

With 15 separate fields you can have the first field (Price1) that is
typed into by the user and then the other 14 that are calculation
fields to work out their values automatically (as long as you don't
need to ever manually change them individually). The 15 Total fields
can then also be calculation fields.



The only way you're going to get such a system to work using repeating
fields is to set up a button that runs a script to calculate and enter
all the other Price repetitions based on the first one. Something along
the lines of:

Set Field ["Price"-2, GetRepetition(Number, 1) + Increment]
Set Field ["Price"-3, GetRepetition(Number, 2) + Increment]
Set Field ["Price"-4, GetRepetition(Number, 3) + Increment]
etc.

With this in place you could have the repeating Total field calculate
itself by making it a Calculation field with 15 repetitions:

Total = Price * Extend(Number)
(Number result)

The Extend function makes FileMaker pretend that the Number field is a
repeating field with the same value in all repetitions, and then it can
multiply the "two" repeating fields together and insert the appropriate
result in each repetition of the Total field.




Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Hi Rich,

Don't get confused by dogma. Repeating fields can be useful.

They're not useful in a relational data model because they break first
normal form (more than one value per field) which among other things
leads to complications later when querying. In layman's terms, it
makes it hard to "Find" and isolate a given repetition and report on it.

Substituting a bunch of fields for repetitions is NEVER the right path.
It also breaks from the model, and again leads to querying and
reporting issues. You substitute one set of problems for another.

In your case, I suspect that a repeating field a appropriate. Why?
Because you're not using it to store data, only to calculate and
present a range of values based on single value fields. It's a simple
reporting device. So go for it. Here's how to do it.

Given:
PriceStart: number field. The price for "repetition 1"
Qty: number field. The number of items to be priced.
Increment: number field. The absolute value price increase (not a
relative percentage of PriceStart). In reading your post, I believe
this is what you're looking for.

Note, PriceStart, Qty, and Increment are non-repeating number fields.

Build a calculation field "Total" with 15 repetitions. Here is the formula:
Total (returns number, 15 reps) =
Let(
[
RepNum = Get(CalculationRepetitionNumber)-1;
Price = Extend(PriceStart) + (RepNum * Extend(Increment))
];
Price * Extend(Qty)
)

If you need to change Increment to act as a percentage of PriceStart,
just change:
Price = Extend(PriceStart) + (RepNum * Extend(Increment))
to
Price = Extend(PriceStart) * ((1+RepNum) * Extend(Increment))
or
Price = Extend(PriceStart) * ((1+Extend(Increment))^RepNum )

depending on whether you want the percent increment to compound or
remain absolute against the start price.

Hope this helps.

On 2004-10-10 21:23:21 -0400, rich.sagall (AT) pobox (DOT) com (Rich Sagall) said:

In article <2004101110545535705%nospam@nospamnet>, flamel
<nospam (AT) nospam (DOT) net> wrote:

Unfortunately, the problem with Repeating fields is that they've never
been fully implemented (I'm not sure about FileMaker 7 here though).
They were originally designed in the days of 'Flat File' databases as a
temporary solution to real related data and should have been removed
with FileMaker 3.

There's one great example for this. There is a 'Get Repetition'
function which allows you to choose the individual repetition by using
the value in another field, but there's no equivalent 'Set Repetition'
function - instead you have to use Set Field and hard code in the
repetition number or use some other complicated workaround.

Another example that VERY often crops up: if you use repeating fields
for lines on an invoice, then you can't (easily) total up the invoice
prices.

Along with all the other hassles for Finds, Summarising, etc. when
using repeating fields, it's simply best to avoid them ... in most
cases. There are a few (EXTREMELY few) times when they can be useful.

Some people use them for storing the various states of changeable
buttons, for example, but again this is simply a problem for anyone
trying to later change the solution (including themselves). It makes
little sense to anyone to read:

Set Field [MyButton, gButton State -2]

but is much easier to understand as:

Set Field [MyButton, gButton Ghosted]



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

On 2004-10-11 16:07:16 -0400, Helpful Harry
<helpful_harry (AT) nom (DOT) de.plume.com> said:

Sorry Harry, that's speculation and/or opinion. Repeating fields "are
what they are" and nothing more. They're not an intended substitute
for a relational model. They're not arrays or script variables. It's
not fair to try to lable a hammer as a poor screwdriver.

As it happens, Mr. Sagall started this thread with a perfect example of
a nail. No other feature of FMP can outshine the repeating calculation
field for solving his problem (as I understood the problem) so simply
and elegantly. Even using FP7's custom functions with recursion to
build a result array would have been significantly more difficult (I
think).

The only substantive change in version 7, relative to repeating fields,
is that new calculation syntax allows you to address a repetition as
"fieldName[n]" rather than "GetRepetition(fieldName;n)". It's much
easier on the eyes and a time saver, but doesn't functionally redefine
repeating fields.

More speculation and opinion, though it occurs to me that perhaps
you've got a relationship with the product's creators to draw from here
- in which case please tell us more. Not that I'm arguing that
repeating fields are a substitute for a relational model of course.
Nor are they programmable arrays.

If repeating fields were removed, with what would FMI have replaced
them? You can't turn them into related tables - that's a different
problem domain. Perhaps with a new array feature for manipulating and
returning lists of values. But then you'd present that array back in
what fashion - delimited text in a standard field? That might work,
but can it be as easy as a simple calc to manage?

Behind this seems to be a feature request for addressable variables.
I'm all for it too - I've logged my request and hope you (and others)
have done so too.

http://www.filemaker.com/company/pro...e_request.html

FWIW, being able to dynamically address and update fields by serialized
index STILL would have no relevance on whether repeating fields are
relational substitutes. They won't be. The relational model depends
on unordered data referenced by unique (un-ordered) keys. But an
ordered array referenced by index (pointer) would be very interesting
for scripts or calcs. It will probably depend on FMI keeping it simple
for most while making it useful for you and me (now I'm speculating).

The classic relational model example. Totally agree with you. I think
that's been beat dead here.

ALL CAPS. Yikes, more dogma. Why are you trying so hard to scare guys
like Rich away from them in the "extremely few" cases where repeating
fields are absolutely appropriate and even perhaps the best answer? Is
that helpful? ;^)

Part of the confusion lies in FileMaker using fields for not only data
modeling and storage, but also as programmable objects - for
application logic and interface development. As data storage elements,
repeating fields are bad news. For fields as programmable objects,
using repetitions is not inherently bad. Since so much of FileMaker
development is spent on application logic and interface, I'm not sure I
agree with the "EXTREME" part of "extremely few".

I think this is a matter of taste. Some developers prefer fewer fields
(with repeats) over a long list of fields with explicit names. I've
used both, so I'm not going to say one method is better than another.
Matt Petrowsky showed at DevCon a few years ago a solution using two
repeating fields: one for all images and icons, one for the metadata
labels. It was a technique that was arguably simpler to understand
(manage in browse mode only, not browse for icons and define fields for
metadata). Also at the time, the system didn't require kicking off
other users from the solution to update the metadata.

In summary, I think we're in rather close agreement that repeating
fields are totally inappropriate as a relational substitute. Same goes
for carriage return delimited lists in text fields. Since many users
back themselves into a corner using them inappropriately as such, you
are to be thanked for perseverence - it's EXTREMELY helpful to keep
beating the drum. :^)

But I don't agree there's a reason to wholesale toss repeating fields
into the bit bucket or to steer guys like Rich away from simple answers
to simple questions.

Thanks!

flamel <nospam (AT) nospam (DOT) net> wrote:

Isn't there a FileMaker Clinic for repeatophobiacs?? ;-)

--
Hans Rijnbout
Utrecht, Netherlands

In article <1glkhnd.1g7rtnnsvna0mN%jrijnb (AT) xs4all (DOT) nl>, jrijnb (AT) xs4all (DOT) nl
(Hans Rijnbout) wrote:

Nope, but there is one for Repeatoholics where you learn simple ways to
never use them again. ;o)

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

On 2004-10-12 20:17:34 -0400, Helpful Harry
<helpful_harry (AT) nom (DOT) de.plume.com> said:

Hehe. Harry starts the meetings saying, "Repeat after me..."

Anyone who has seen my posts here knows that I'm no fan of repeating
fields. But I recently came across a situation where the repeat
actually came in handy.

I had a client report script that let's the client select multiple
criteria from a simple "dialog" layout window. The criteria allows the
user to specify in a very simple way whether they want an 'and' search
or an 'or' search across criteria. But the date range that they can
specify needs to search across three separate date fields. To keep the
rest of the search programming flexible and (relatively) simple, I
wanted a way they could search on all three dates within one field. My
first thought was to just concatenate all three dates into one text
field. This works fine if searching for a specific date where the text
happens to match, but not for a range of dates where the search must be
done in date format.

What I ended up doing was creating a calculated repeating field (with
date result) that stored one of the reference date fields within each of
the first three repetitions. Now my script can search for the date
range and the record comes up if any of the repetitions fall within that
range.


Helpful Harry wrote:
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Pro Solutions Los Angeles, California

FileMaker 7 Certified Developer
Associate Member, FileMaker Solutions Alliance

Hi Harry,

first off, let me say that I don't like repeating fields either and that
I've replaced many of them in the system I inherited with related tables.
The problems you mentioned (summaries, finding, absence of SetRepetition,
etc.) and the often complicated work-arounds I had to devise led me to this
decision. However, your example quoted below:

is one of the few remaining instances of repeating fields in my solution.
My invoice file consists of a number of repeating fields (qty, price,
discount etc) and add up fine into a total invoice price. The main reason I
left this intact was because it was working perfectly as it was and the
time I needed to rebuild (in combination with the related orders file etc.)
seemed more effort than it was worth. Could you expand on your statement
that totalling up the invoice prices is difficult ? A simple Sum
Calculation on the repeating field holding the line total(qty * itemprice)
does the trick, consistently as far as I can tell.

Or do you mean that reporting the total invoice price in a summary would be
problematic ?

Please don't tell me that I have to manually check all my invoices of the
past few years to see if there are erroneous add-ups ;-(

Thanks and regards,

Peter