More fun with repating fields

[The Mad Jammer]

Oddly enough, after a recent long discussion about repeating fields, I have been given the task of maintaining a FMP 5.5 database that someone else wrote a few years ago and it contains a repeating field with 35 elements.The people who use this database were told to entered was a specific date in the repeating field. There are 42000 rcords on the db and about 4500 records were affected by this change Now, of course, they have been asked to change the original to another date, so they have to update all 4500 records again. Its called job security.

The problem is this. The date was not entered into the same element on each record, it was entered in various elements of the repeating field. In other words this date could show up in any of the elements of the repeating field on these records.

It's no problem finding all the rcords that have this date in the repeating field. The issue is how to overwrite the exact text of the date in these 4500 records with a new date. Therefore I have to range over all of the elements of the repeating field in these records, identify the elements that have this date in them and substituting a new date.

I'm lost on this one. The Replace command is not going to do the job because it simply writes over whatever is in the current element in each record with the new data. I'm looking at the Substitute function to help but it's not available as a script step. Nor have I found a script step that will let me access each element of a repeating field in a loop.

Yes, a relationship schema is certainly the way to go but that re-write will have to come later.

Right now I'd just like some ideas on how to get this job handled. If all else fails, the input specialists will just have to do it manually. I'm just trying to save them some work look like a hero.

Thanks

The Mad Jammer

[-Queue-]

Create a layout containing only the repeating field, displaying all repetitions. Tab through them to make sure the order is sequential. Then create a script.

Go to Record/Request/Page [First]

Freeze Window

Loop

Exit Record/Request

Loop

Go to Next Field

If [status(CurrentFieldContents) = Date( oldmonth, oldday, oldyear )]

Set Field [ , Date( newmonth, newday, newyear )]

End If

Exit Loop If [status(CurrentRepetitionNumber) = 35]

End Loop

Go to Record/Request/Page [Exit after last, Next]

End Loop

The trick is to not specify a target field in the Set Field step, only the calculation.

[Ugo DI LUCA]

Another solution would be to temporarily set the repeat field to be a lookup.

gNewValue

gOldValue

gSerial

gIteration = a repeat as a array of 1|2|3|4|5|....|35|

cNewKey = Case(MyRepeat = Extend(gOldValue);gNewValue;gOldValue) - repeated calc of 35 reps

A relation from gSerial to Serial, and a change to the definition of MyRepeat to be a lookup from cNewKey.

Script = Replace Content of gSerial with the value of Serial

Done.

Revert the options you've changed and delete all fields unless they might happent to be other updates of this kind.

Attached a test file

repeatLookup.zip

[-Queue-]

Beware that with Ugo's technique, it may take a while for the file to update when cNewKey is created and deleted.

[The Mad Jammer]

Thank you gentlemen. There are only a couple of slight problems. I'm using FM Pro 5.5, not version 7. As a result, Status(CurrentFieldContents) doesn't exist and I've found nothing in this version to use as a substitute.

Secondly, the data I'm looking for may be embedded in some other text in the repetition, so I need a substring function to locate the desired data, in this case a date.

Jammer

[Ugo DI LUCA]

Hi,

Check back as Status(CurrentFieldContents) does exists for sure in Filemaker 5.5.

With Queue's solution, you'd use.

If[PatternCount(Status(CurrentFieldContents);gOldDate)]

SetField[ ;Substitute(Status(CurrentFieldContents);gOldDate;gNewDate)]

gOldDate and gNewDate should be text values I think.

With the other suggestion

cNewKey = Substitute(MyRepeat;Extend(gOldValue);Extend(gNewValue)

Actually, it would have worked even with the first scenario.

And gSerial surely isn't a global. Make it a number field named Serial_match

[The Mad Jammer]

I checked backed and the version is actually 5.0v3 and CurrentFieldContents is not there. Sorry about that. The FMP server version is 5.5.

Jammer

[-Queue-]

Go with Ugo's technique then. I don't remember there being any way to grab the current field contents in 5.0, though it has been 4 years since I used it.

[Ugo DI LUCA]

Or use another calc which locate the rep to be changed.

gIteration = the array 1|2|3|4...|35| (global rep)

cRepNumber (calc-35 reps) = Choose(PatternCount(MyRepeat);gOldValue);0;gIteration)

cMyNum = Max(cRpeNumber)

and use Queue's with cMyNum and compares it with Status(CurrentRepetitionNumber)

[The Mad Jammer]

Thanks guys. No wonder people hardly ever used repeating fields.

Jammer