Calc on Repeating field??

[droosan]

Everybody has been so helpful I'll try another.

I need to a calculation to determine which repetition of a repeating field holds a specified string.

For example if a repeating field animals holds the values:

squid, llama, wombat, gnut

I want the result of "wombat" to be 3 and the result of "dodo" to be 0 or -1 or whatever. Finding the first occurance is good enough.

Any ideas?

I could 'if" each rep,but i'd rather not do that if I can avoid it, since the repeating field may be long.

Thanks in advance.

[Steven H. Blackwell]

This probably will require a script, but let me play with this.

Old Advance Man

[Steven H. Blackwell]

OK, there are two ways I have ascertained to do this. Neither is seamless. But maybe this will get you started.

First, if you're running Mac OS, you can get this information with an AppleScript. Assume two fields in the database. One is called "repeatfield", and it holds the repeats. The other is called "examinefield" and it holds the variable you're checking. Assume that all values in the repeat are unique. A third field called "resultfield" is where the specific repetition number matching the "examinefield" is returned. Run the following from the Perform AppleScript ScriptMaker step:

tell application "FileMaker Pro"

set checkfield to get data cell "examineField" of current record

set targetfield to get data cell "repeatfield" of current record

end tell

set counter to 1

repeat with i from 1 to count of items of targetfield

if item i of targetfield is in checkfield then

exit repeat

else

set counter to counter + 1

end if

end repeat

display dialog counter

tell application "FileMaker Pro"

set data cell "resultfield" of current record to counter

end tell

OK, now there is another way to do this. It's generally easy to discover where an item is in an ASCII 13 (paragraph) delimited list. THe problem is that repeats are separated by ASCII 29's. Not so easy.

So, we are going to put the value in the repeating field into a global, substitute the ASCII 29's with ASCII 13's, and then return a result. We need to be sure that certain fields are not empty, because that will return errors.

First, define an unstored calculation field called "cleanfield":

cleanfield=

Substitute(g_field, "", "

[droosan]

Thanks for taking the time to answer. It seems that there should be an easier way to do this.

I tried your second method. It works. I need the result stored by record so I just changed g_field to a normal field and it worked fine. Thanks.

[Thom]

I know of 3 ways to do this without a script. One uses a portal to a related file instead of repeating field. Each record (row) in the portal is numbered. One field holds your search word and is one side of a relationship to the related records. Your found word (number) is the result of a match.

The first repeating solution requires 4 calc fields. The first three are giant Case() functions. The fourth is a sum of the first three. I'm assuming a repeating field with 200 repetitions. If you need less, you could get by with fewer calc fields.

The other repeating solution uses a separate repeating field filled with numbers. One or two more simple calc fields give you your number.

If any of these sound interesting, I can give you more details when I get back to where my files are.

[danjacoby]

Yet another example of why the repeating field is something to be avoided by any means necessary.

I highly recommend creating a related file and exporting the info from the repeating fields into it (you can have FMP create separate records for each repetition).

[droosan]

This reason I am stuck (or least I think I am. Thom has already shown me a couple things I have been doing wrong for years.) with using a repeating field, is that the database user needs to be able to easily reorder the elements.

But now Thom has got me thinking... Suppose the elements, which actually represent html files, have "page numbers," and I sort the relationship, and therefore the portal, by page number. The user could edit the page number within the portal. Now, where I need the calculation above is that a page needs to be able to find the relative URL(already in the record) of the page before and after it in order to link to it. Using Thom's method (which he's going to so generously share ) it can find itself in the portal and link back to the page above in the portal and link forward to the page below it in the portal. What'a ya think?

[ December 05, 2001: Message edited by: Drew Cover ]