Populating FM6 repeating field

[elipsett]

Is there any way to populate a repeating field from some other data source in a FM6 database? If you have any suggestions other than manual input or copying from another repeating field, I'd love to hear about them.

Thanks for your time.

[mr_vodka]

I am unclear on the exact question here. You can use a Set Field ["yourfield" - 2', "YourData"] to set a rep in a repeating field. When you use setfield, there is an option to put in which rep you want.

[Søren Dyhr]

Bearing in mind that this is a relational database and not a wordprocessor should it be stessed that copying as such, not is fully embraced, rather the opposite ...relational teory seeks to store data in only one single place, to avoid syncronization issues.

These ideals isn't just a fad ...there are actually some substance behind the ideals, in form of mathematical proffs etc. The good question is if we really should lend you a hand to accomplish what you're after on your premises, or we should unhessitated try to prevent you getting into deep.

Perhaps will this, of La Fontaines fables put it a bit in perspective:

The Gardener and the Bear

Bidpai

In the eastern part of Persia there lived at one time a gardener whose one joy in life was his flowers and fruit trees. He had neither wife, nor children, nor friends; nothing except his garden. At length, however, the good man wearied of having no one to talk to. He decided to go out into the world and find a friend. Scarcely was he outside the garden before he came face to face with a bear, who, like the gardener, was looking for a companion. Immediately a great friendship sprang up between these two.

The gardener invited the bear to come into his garden, and fed him on quinces and melons. In return for this kindness, when the gardener lay down to take his afternoon nap, the bear stood by and drove off the flies.

One afternoon it happened that an unusually large fly alighted on the gardener's nose. The bear drove it off, but it only flew to the gardener's chin. Again the bear drove it away, but in a few moments it was back once more on the gardener's nose. The bear now was filled with rage. With no thought beyond that of punishing the fly, he seized a huge stone, and hurled it with such force at the gardener's nose that he killed not only the fly, but the sleeping gardener.

It is better to have a wise enemy than a foolish friend.

Snipped from here! http://www.pitt.edu/~dash/type1586.html#bidpai

--sd

[elipsett]

Apologies for not being clear.

It is trivial to place one datum into the first slot in a repeating field. What I want to do is copy in the whole dataset, for all the repeating fields. You can get the count indicating the number of data in the repeating field, but there is no corresponding command (for example) "get(field_x,datum1)" or "set(field_x, datum_1").

I have a large number of individual records being used via portals in a number of databases, but I also need to siphon off the data in a form that prevents it from being updated by changes to the original records. For various reasons, dumping it into a repeating field would be nicest.

(Writing the whole thing in FM8.5 or VB would be even nicer, but unfortunately that is not an option.)

[mr_vodka]

I am afraid I still do not understand your issue. As I previously mentioned, in a Set Field step, there is an option to choose the repetition that you want to store the data into.

For example,

Set Field ["yourfield" - 2', "datum1"] will set repetition 2 of yourfield with the value of the datum1 field.

If you want to retrieve a value from a repeating field, you can use

Set Field ["anotherfield", "GetRepetition ( yourfield , 2 )"]

This will take the data from rep 2 of yourfield and put it into the field "anotherfield"

[elipsett]

Ah!

Now that may be the answer!

It is possible to explicitly specify which field to input something into with SetField, yes, but I didn't know how to do it programmatically... let me play with your code and see what happens.

Thanks.

[elipsett]

The code you posted

Set Field ["yourfield" - 2', "datum1"]

is exactly what I would like to do, especially if it is possible to replace the explicit "2" with a variable so I can use it in a loop.

Unfortunately, the SetField command in the script editor requires a single explicit integer, and (apparently) cannot be changed programmatically.

Am I missing something? Is there a way to change the "2" specification in a script?

[Søren Dyhr]

You do not have native variables under fm6, so your resque might be applescript, lets say you have 3 field in a record where the middle one is a repeater - Issueing a record 1 will give you this:

{"dfsdfdsf", {"", "fsdff", "", "dffsdf", ""}, "tyuyu"}

ordinary fields are stored inside a single bracket, while repeaters are inside double brackets.

Here does it mean that cell 1 contains: "dfsdfdsf"

Cell 2 (the repeater) {"", "fsdff", "", "dffsdf", ""}

Cell 3 contains: "tyuyu"

So in order to address each part of a repeater do it like:

set aVar to cell 2

set item 1 of aVar to "fffff"

set cell 2 to aVar

--sd

[elipsett]

You do not have native variables under fm6

I'm not entirely sure what you mean by this... data can be parked to global variables easily enough.

[Søren Dyhr]

No of course not you need to see it before you know it. But you can't store the indirection in a global field. Either you loop the cursor to the right location via go to next field in cascades until it arrives to right spot - or alternatively use the AS'in i provided you with in the previous message.

Now if you had you task structured propperly could you have issued a single Go To Portal Row, perhaps you should start an make you self a tiny normalized template to see how much easier it is with portals??

--sd

[Ender]

Unfortunately, the SetField command in the script editor requires a single explicit integer, and (apparently) cannot be changed programmatically.

Yes, this is a limitation of versions prior to FM7. A loop is one method to deal with it. Or, if you don't have a lot of repetitions, you could use multiple Set Field[] steps (one for each rep) and use If[] branching to choose the right one:

If [ gRep = 1 ]

Set Field ["yourfield" - 1, "datum1"]

Else

If [ gRep = 2 ]

Set Field ["yourfield" - 2, "datum1"]

Else

If [ gRep = 3 ]

Set Field ["yourfield" - 3, "datum1"]

...

Or a better solution in the long run is to change the repeating fields to related records (kept in another file). Or upgrade to FM8.5, and add new tables for the related records.

[comment]

Only in version 8 and higher you can specify the repetition number to be set by calculation. In version 6, the repetition number must be hard-coded in the Set Field[] step - same as the field name (which must be hard-coded in all versions).

It's hard to see any advantage in using a repeating field for this. If related data must be consolidated into a single record, you could put it in a text field, separated by carriage returns (or another separator, if the data itself contains carriage returns).

But it's also hard to imagine circumstances where such procedure would be required. If the purpose is to take a snapshot of the data, export seems a much simpler and more suitable alternative.

[elipsett]

A loop is one method to deal with it. Or, if you don't have a lot of repetitions, you could use multiple Set Field[] steps (one for each rep) and use If[] branching to choose the right one

I had hoped to avoid hardcoding explicit field reps, because it makes downstream modification a royal pain. How could you loop it, which (if I understand correctly) would require explicitly specified field reps?

Edited June 16, 2007 by Guest