loop in field calculation...

[rheric]

...without FileMaker Advanced, so I don't have access to Custom Functions.

Is there a way to create a "while" loop in the calculation for fields?

What I'm trying to do is this: one field in Table A is the Interface table, showing a portal of related records from Table B, which contains Names. I've figured out how to filter the portal by a pull-down of set values, but I also want to be able to filter the portal based on a text search field, so that users can search by last name, first name, etc. I've gotten it to work when users enter full names (first or last), but I need to now populate the matching field in names with partial strings of either name. For example, what I have now is:

NameParameterMatch=

John

Smith

John Smith

All

What I need is:

NameParameterMatch=

J

Jo

Joh

John

S

Sm

Smi

Smit

Smith

John Smith

All

Any ideas how to create a calculation in this field that will loop through and create each line appropriately?

Thanks in advance for the help.

[comment]

You can use a repeating calculation field for this. Use Get ( CalculationRepetitionNumber ) as you would use i in a "for i" loop.

[rheric]

Thank you for the quick reply, but I'm afraid I'm not quite understanding.

Do I use a repeating calculation field to populate each repetition with one value from the list I want to create? If so, how do I dynamically set the number of repetitions for that field so that it has the same number of repetitions as there are letters in the name (or do I just set the maximum repetitions to something higher than would ever realistically be needed)?

Thanks.

[comment]

Yes, each repetition holds one resulting value.

You cannot dynamically set the number of repetitions of a field, so just set it to say 50, and control the number of COMPUTED repetitions through i.

[rheric]

I think I understand, but I must be doing something wrong. I set the calculation field to repeat to 50, and I put in the following calculation:

Let ( $i = ( Length (Name) ) );

If ( Get (CalculationRepetitionNumber) <= $i;

Left ( Name;

$i - ( $i - (Get (CalculationRepetitionNumber)))

);

" ")

)

It only populates the first repetition.

[Søren Dyhr]

First off doesn't script variables make much sense here, secondly do you need to use this:

http://www.filemaker.com/help/FunctionsRef-337.html

I would suggest a declaration in the vicinty of:

Let([

ts=Extend ( name );

tt=Length(LeftWords(ts;1));

tz=tt+2];

Case(

Get ( CalculationRepetitionNumber ) ≤ tt;Left(ts;Get ( CalculationRepetitionNumber ));

Middle(ts;tz;Get ( CalculationRepetitionNumber )-tz+2)))

The full part and the All havn't been included yet, but it should reside in the same case statement, think of the first hit in the reading direction makes it somewhat easier to define the following since they never make it that far.

--sd

Edited January 24, 2007 by Guest

[Raybaudi]

Hi

you can try this:

Let(

string = Extend ( Name ) ;

Case (

Get ( CalculationRepetitionNumber ) ≤ Length ( string ) ;Trim ( Left ( string ; Get ( CalculationRepetitionNumber ) ) ) ;

""

)

)

BTW for NAMES that have spaces inside, the result will repeat ( sometimes ) a word, but the relationship may work as well.

[comment]

Any non-repeating field you use in a repeating calculation must be extended using the Extend() function.

A couple of non-essential notes:

1. You don't need to prefix Let() variables with $;

2. You have many unnecessary parentheses;

3. For me, the letter i in a recursive calculation is reserved for the index/iterator;

4. Why is your default result a space? If you meant an empty result, you don't have to specify it.

This is how I would write this:

Let ( [

text = Extend ( Name ) ;

i = Get ( CalculationRepetitionNumber )

] ;

Case ( i ≤ Length ( text ) ; Left ( text ; i ) )

)

[rheric]

Excellent!

That worked great. I was making it too complicated, obviously.

I actually got it to build a list of partial strings for both first name and last name (and the value "All" for the portal filtering) by using:

Let ( [

first = Extend ( firstName ) ;

last = Extend ( lastName ) ;

nfirst = Length ( first ) ;

nlast = Length ( last ) ;

total = ( nfirst + nlast ) ;

i = Get ( CalculationRepetitionNumber )

] ;

Case ( i ≤ nfirst ; Left ( first ; i ) ;

i > nfirst and i ≤ total ; Left ( last ; i - nfirst ) ;

i = total + 1 ; "All"

)

)

One last question: I have one more field that I would like to work into this repeating calculation. I have a field that stores which sports a user plays; it uses a value list which allows for the selection of multiple sports. If an athlete plays more than one sport, I am having trouble getting the calculation to:

1. List the sport values appropriately.

2. Recognize how many values are selected in that sport field so that it only populates that many more repetitions. My first attempt took the first value from that field and filled the remainder of the 50 repetitions with just that value.

Edited January 24, 2007 by Guest

[comment]

But, if I understand correctly, the sport field is already a return-separated list. If so, you could just throw it in 'as is' in any one of repetitions, for example:

...

i = total + 1 ; "All" & ¶ & Extend ( Sportsfield )

...

[rheric]

That was it!

Thank you so much for the help! Solved a *lot* of frustration, and gets us what we need, and then some! (Plus I'm learning a ton about FileMaker!)

Thanks again!