Calc on Repeating Field - for Thom esp.

[droosan]

Thom, you mentioned that you had a way to find the number of the portal row which matches a field, without using a script. That thread is now a bit buried so I wouldn't be surprised if you forgotten about it. But I am still very interested. I have set up the portal as described below but I can't imagine how to match row without a script.

I'll put the relevant stuff here so you don't have to go searching.

I thought about emailing you but I'd rather not, since you probably have a life.

=== The part of your (Thom's) post of particular interest ===

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....If any of these sound interesting, I can give you more details when I get back to where my files are.

========My Response============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?

[Thom]

To find in which portal row a given value appears, you can use the value as an index. A related field can then show other fields from the matching related record. The row number has to be stored along with the record. This means that each set of related records belonging to a different master record must have its own serial numbers.

If the portal is sorted by serial number, then those numbers can be changed to reorder the portal rows. However, if a value appears twice in the portal, the first one created, not the first in the sort order, will get looked up by a related match. We can take advantage of the "copy next higher value" feature of a lookup to get around this.

In this solution the master file is called CATEGORIES; the related file is WORDS. Here are the fields in each:

CATEGORIES

categoryID(number,auto-enter serial)

wordMatch(text)

wordKey(calc,text) =

categoryID & " " & wordMatch

wordKeyLookup(text,lookup)

whichRow(calc,text) =

If((LeftWords(wordKeyLookup,1) = categoryID)

and

(MiddleWords(

wordKeyLookup,

2,

WordCount(wordKeyLookup) - 2)

= wordMatch

),

TextToNum(RightWords(wordKeyLookup,1)),

0

)

WORDS

categoryID(number)

word(text)

serial(number,lookup)

serialNext(calc,number) =

Max( Category::serial ) + 1

wordKey(calc,text) =

categoryID & " " & word & " " & Right("00000" & serial, 6)

CATEGORIES has two relationships:

Categories = categoryID=::categoryID in WORDS

sort by ::serial, ascending

WordMatch = wordKey=::wordKey in WORDS

Set wordKeyLookup to lookup wordKey based on a match in WordMatch. If no exact match, then copy next higher value.

WORDS has one relationship:

Category = categoryID=::categoryID in WORDS

Set serial to lookup serialNext based on a match in Category. If no exact match, then use 1.

In CATEGORIES, create a portal based on Categories. Put the field Categories::word in the first row. Put the fields wordMatch and whichRow above the portal.

As you enter words in the portal, serial will automatically increment. To see the row number for a word, type the word in wordMatch and tab or click out of it. The row number will appear in whichRow. A '0' means there is no match.

Since the portal is sorted by serial, you can reorder the portal rows by renumbering serial. You can create scripts to move a row up or down.