Set one Repeating Field from another

[dvalley]

Before I get deluged with "never use repeating fields", let me say that this is a legacy database from the FileMaker IV (not 4.0) days. I had to condense a series of lookup databases into this main one all having repeating fields. I need to copy the contents of a single 40 repeating field into four ten repeat fields. I used script step

Set Field("xxx,1", GetRepetition("yyy,1"))

Set Field("xxx,2", GetRepetition("yyy,2")), etc. for all repeats, but onlt the first fields populate themselves. I used script debug and watched the script work, but only the first fields were populated. I found that I can copy and paste said repetitions, but I really don't want to write that one out.

Is there an answer as to why that formula will not work beyond the first repetition? Is there on that will work?

[Vaughan]

FileMaker IV? You mean FileMaker II. After II came Pro.

[Lee Smith]

Hi Vaughan

I think they mean FileMaker 4

This is how FileMaker was released (I ommitted many step upgrades, such as version 1.1, 1.2. etc.)

FileMaker, version 1.0 (published by Forethought Inc.)

FileMaker Plus, version 2.1 (published by Nashoba Systems)

FileMaker 4, version 4 (published by Nashoba Systems)

FileMaker II, version 1.0 (published by Claris Corporation from this point on)

FileMaker Pro 2.0v1 (NOTE: FileMaker Network version is 2.0v1 for AppleTalk)

FileMaker Pro 3.0v1 (NOTE: AppleTalk Network, MacIPX Network, and TCP/IP Network versions 3.0v1)

FileMaker Pro 4.0v1

FileMaker Pro 5

FileMaker Pro 5.5

FileMaker Pro 6.

Lee

[Ugo DI LUCA]

Hi,

I may be totally wrong, but I think whichever version of FM you're using, you'd never catch more than the first repetition if your repeating field is referenced through a relationship.

[Lee Smith]

Hi Ugo,

Not my point. My point was that since there has never been a FileMaker IV, that maybe dvalley meant FileMaker 4 by Nashoba Systems.

BTW, I agree with you about the chances of repeating fields ever being any better or functionaly, in fact, I'm betting that, such as they are, they're as good as they have ever been or will ever get.

Lee

[Vaughan]

Either way, it's about time the system had a re-design.

I'm sure the clever programmers at FMI could make repeating fields even better, but why bother? I personally think they

Thanks for the history lesson Lee!

[dvalley]

I do mean FileMaker 4. The comment about related repeating fields not carrieing over via get repetition is interesting, however these fields exist in the same file and still do not work via script. I would love to redesign the whole database system, but there are about 175 different related files all in constant use for over ten years. Nobody will let us purge old data. Most have repeating fields which get imported into other files with repeating fields. There are a few core files (also with repeating fields) which are used by nearly every related file. To redesign the system one must do it all at once, and we would need about six months of working on nothing else...and who has that kind of time? (There are, afterall, only two of us.)

[-Queue-]

They what? I'm sitting on the edge of my seat here.

[-Queue-]

Is there any way you could attach the files or a scaled-down version of them, so we could see the problem?

[CobaltSky]

Hello dvalley,

The question you have raised has been discussed on a few occasions, and is a known issue with the way the present implementation of repeating fields in Filemaker works, however there are ways to work around the problem which will not necessitate resorting to cut and paste procedures.

In fact, you will find that there is an article in the TechInfo Knowledgebase archive on the FMI site which discusses the problem and sheds a little light, including a suggested work-aroound. The article is a bit dated now and the solution which is suggested in the article is a but clunky, but not as clunky as a string of copy and paste steps would be. For what it is worth, the article is at:

http://www.filemaker.com/ti/104269.html

However for most types of data (eg text and number data) there is a better solution than the one suggested in the article. It was mentioned on a thread elsewhere some time back by the inimitable dj.

For reasons which are somewhat obscure, addition of a null to the end of the calculation expression in which the source value (repetition) is specified causes the calulation to work. So although as you've indicated:

Set Field ["xxx"-2, "GetRepetition(yyy, 2)"]

will fail, you'll be surprised (as I was) to learn that with a null added to the calc expression, Ie for transferring values between repeating fields of data type text:

Set Field ["vvv"-2, "GetRepetition(www, 2) & "" "]

or for a pair of repeating number fields:

Set Field ["xxx"-2, "GetRepetition(yyy, 2) + 0 "]

- it will work. Try it and I think you'll be pleased with the result.

Ugo's suggestion that "you'd never catch more than the first repetition if your repeating field is referenced through a relationship." is, as he himself predicts, totally wrong. The above formulations will work just as effectively wherever the source data is located.

And as for Lee's suggestion about repeating fields, that "such as they are, they're as good as they have ever been or will ever get....", I'm afraid I couldn't agree with that either. In fact, quite the contrary.

Repeating fields are the closest Filemaker has thus far come to an array architecture. In early versions of FM, this was provided (and most frequently used) as a poor substitute for relationships, and since relational structures were offered in version 3, that application of repeating fields has been less desirable.

In their current implementation, because of problems like the one that is the subject of this thread, repeating fields really don't cater for arrays any better then they did as a stand-in for relationships - ie they remain clunky. However array architectures have value in their own right, and most advanced dbms systems offer support for arrays and variables in some form. Thus as FMP matures I would be *quite* surprised if repeating fields were not only retained, but enhanced so that they provide a more adequate support for the handling of data arrays.

In fact I would not be at all surprised if we see additional functionality or improved handling of repeating fields in an upcoming version of FMP, as it is one of several logical 'next steps' in the evolution of a truly industrial strength database development environment.

[Mike D.]

Ray,

You have impressed me again. Thanks for the info about putting a null on the end of repeating field calculations.

As for the use of repeating fields as data arrays, that would be useful in an upcoming project. I hadn't considered repeating fields to be used in that capacity.

Thanks, Mike

[-Queue-]

I vote that none of us answers any future questions until Ray's had a crack at it. We'll save lots of time and possible confusion that way.

[Ugo DI LUCA]

(attachment deleted. See one post down the new one)

I vote this too...

I just went into some tests off list (prior to these added arguments), and made this script for this purpose....

Based upon relationships, using Ernst Demo (SetRepeating Field) and Ray's formula to extract a value from a value list.

Seems to be working as expected. I used the GetRepetition to parse the data out into a global field.

In fact Ray, I now remember these discussions.

[Ugo DI LUCA]

Hi,

As I had some spare time this morning, I played a little more on the subject and this demo.

This solves 2 issues.

1. Parsing the 40 repetitions into 4 other repeating fields (10 rep each) according to the order the values were entered.

2. Custom Parsing to the 4 other repeating fields, by using an external Value List, created by a script.

Involves 7 relationships and 2 set of scripts. As far as I tested it, it seems to be working well.

HTH.

PlayingRepeats.zip

[Lee Smith]

Hi Ray,

I was wondering if this:

Thus as FMP matures I would be *quite* surprised if repeating fields were not only retained, but enhanced so that they provide a more adequate support for the handling of data arrays.

Happened in v7?

If so, if you have made a sample file using any of the new enhancements?

Lee

[comment]

Here is one:

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=137788&an=0&page=0#137788

and you will find much more if you search for [color:"blue"]"repeating" in the [color:"red"]Sample Files & Open Source Solutions and the [color:"red"]Articles - Tips, Tricks & Techniques areas.

[Lee Smith]

Thanks for the link.

What a neat file, I'll study it and see what makes it tick.

Unfortunately, I couldn't look at these samples back when this one was submitted (I didn't have my copy of v7 yet).

I have already started going thru the samples having to do with versions 7.

Thanks again,

Lee

[CobaltSky]

Hi Lee,

I see that while I was sleeping, down here in Melbourne, Comment has leapt to the fray and provided you with something to get your teeth into.

To answer your first question, yes partly. Certainly there were several important improvements to the functionality of repeating fields in v7, so perhaps my prediction came true in the sense that repeating fields were retained and enhanced in v7, rather than being dropped or side-lined.

It's also the case that the enhancements do take the functionality forward in directions which make repeating fields more useful as an array handler, so in terms of what I was proposing, they are a positive move. I'd have liked it if they'd gone even further (yeah I know... - some people are never happy...)!

Off the cuff, here are some of the changes that *did* occur in respect of repeating fields in v7:

1. Setting a repetition other than the first no longer requires the work-arounds I mentioned above.

2. Repeating field contents can now (optionally) be summarized by repetition.

3. It is now possible to display discontiguous ranges of repetitions - and thus (for instance) to position a repetition other than the first on its own on a layout.

4. Conventional array syntax is now supported, so you can reference a repetition value using FieldName[n] rather than the previous, more cumbersome method GetRepetition(FieldName, n).

5. The new Get(CalculationRepetitionNumber) function enables more direct control of repetition results than was previously possible.

6. The number of repetitions permitted per field has been increased from 1,000 to 32,767.

From memory, that's about it (I find that it's getting harder over time to recall exactly which things v6 doesn't do!). But each of these changes adds flexibility and makes repetitions more usable and more useful.

To answer your second question, to date I have not published any demos that deal specifically with the operation of repeating fields in v7. But I daresay you'll find a few that others have posted around some of the forums (in addition to the one Comment has already mentioned, that is).

[Lee Smith]

Hi Ray,

Thank you for your reply.

I just quickly read through it, and see that I need to read it more thoroughly in the Morning when I'm fresh.

It has been one of those days. I'm just now getting to my Email and the Forum.

Thanks again for your response.

Lee