11 Replies Latest reply on Jan 15, 2017 12:59 PM by BruceRobertson

# Repeating fields puzzle: how to combine two calculations in one?

For some time I have tried to combine two calculations with repeating fields into one calculation, without success.

• I have a FIELD_A  (10 repeating fields containing text)

• I want to know in which repeating field occurs the first time a certain TEXT

• I have build a first calculation (with 10 repetitions) CALC_TEMP = Case ( FIELD_A = "TEXT": 1 ); then a second calculation that gives the first occurrence of the TET:  FINAL_CALC = RepNumber ( CALC_TEMP ; 1 ; 1 )

Could this be done in one calculation, for instance with the LET function? I believe that someone could suggest a clever solution. Thanks.

• ###### 1. Re: Repeating fields puzzle: how to combine two calculations in one?

The really clever solution would be to use records instead of a repeating field.

But you can List( ) the repeating field, then use

Let ( [

theList = List ( fieldA ) ;

pos = ValueCount ( Left ( theList ; Position ( theList ; "searchText" ; 1 ; 1 ) ) )

] ;

GetRepetition ( fieldA ; pos )

)

That doesn't work if there are gaps in the repeating field. Also note that this returns the first occurrence; if you want to get the first repetition that is equal to the search text, you need to use

Let ( [

theList = List ( fieldA ) ;

pos = ValueCount ( Left ( theList ; Position ( Char(13) & theList & Char(13) ; Char(13) & "searchText" & Char(13) ; 1 ; 1 ) ) )

] ;

GetRepetition ( fieldA ; pos )

)

1 of 1 people found this helpful
• ###### 2. Re: Repeating fields puzzle: how to combine two calculations in one?

Broken out you could use a Let:

Let([

value = "TEXT" ;

r1 = If ( GetRepetition ( FIELD_A ; 1 ) = value ; 1 ; "" )  ;

r2 = If ( GetRepetition ( FIELD_A ; 2 ) = value ; 2 ; "" )  ;

r3 = If ( GetRepetition ( FIELD_A ; 3 ) = value ; 3 ; "" )  ;

etc...

r10 = If ( GetRepetition ( FIELD_A ; 10 ) = value ; 10 ; "" )

];

List ( r1 ; r2 ; r3 ; etc... ; r10 )

)

The above would return a list of matching repetitions

Better yet, set up a custom function that can do it recursively so you don't have to "hardcode" everything. Probably a bit too complex to explain here, but here is a similar custom function that already exists:

The above custom function in your case would be:

FindRepetition ( "TEXT" ; FIELD_A ; 1 ; 10 ; 0 ; 0 )

Or, Find the repetition where TEXT matches in FIELD_A, starting at the first repetition, ending at the 10th repetition, Do not allow partial match, do not be case sensitive.

1 of 1 people found this helpful
• ###### 3. Re: Repeating fields puzzle: how to combine two calculations in one?

Thank you, erolst!

Yes, the first lesson to everyone is to try to build the solution with few repeating fields as possible, I agree!

• There are slots of the repeating field that are empty. In those cases, as you said, the calculation does not fit

• Your calculation is giving me the result of the contend of the repeating field, not the repeating field number (3 or 5, etc). Maybe I am missing something.

I can be more specific on the context of the solution: in repeating fields we write various medicines and comments during the menstrual cycle of a woman. I want to know what is the cycle day CD (repeating field number) when a certain drug, Pregnyl,  is written [Ex: CD1: empty; CD2: Ultrasound; CD3: empty ; CD4: Pregnyl; CD5: ultrasound. The result should be 4].

Thank you anyway for approaching the solution.

• ###### 4. Re: Repeating fields puzzle: how to combine two calculations in one?

Thank you, Mike. I think that your suggestions will work, although the first may be laborious (we can have 30 repetitions for ex). But the Custom Functions may be more adequate, I will try to find the right one. Thank you for fast answering.

• ###### 5. Re: Repeating fields puzzle: how to combine two calculations in one?

Few as possible is zero.

Get rid of the repeating fields.

Learn to use a related record set.

If you have to ask how to use repeating fields - you shouldn't be using them.

You're just beginning your list of ordinary data activities that can be performed easily with related record sets but which will present you continuing difficulties as you discover the need for the next feature; and the next reporting feature after that.

1 of 1 people found this helpful
• ###### 6. Re: Repeating fields puzzle: how to combine two calculations in one?

I would prefer the solution suggested by erolst, but it does not work in cases of empty rep. fields

The Custom Calculation suggested by Mike works perfectly.

Here is a sample of the file, showing both solutions. Hope this could help other users that are syill using rep. fields

Thanks to all contributors.

• ###### 7. Re: Repeating fields puzzle: how to combine two calculations in one?

Bruce, I agree with you, but don't need to be so dramatic. If repeating fields still exist in FMP even in version 15, there are many people that are still using them. Not everybody can be so expert as the most clever ones here. That's why we learn from each other. Cheers and thanks for your comments

• ###### 8. Re: Repeating fields puzzle: how to combine two calculations in one?

kamasoto wrote:

I would prefer the solution suggested by erolst, but I could not reach the right answer.

To get the desired repetition number, all you need to do is change the calculation to

Let ( [

theList = List ( fieldA ) ;

ValueCount ( Left ( theList ; Position ( theList ; "searchText" ; 1 ; 1 ) ) )

)

or

Let (

theList = List ( fieldA ) ;

ValueCount ( Left ( theList ; Position ( Char(13) & theList & Char(13) ; Char(13) & "searchText" & Char(13) ; 1 ; 1 ) ) )

)

1 of 1 people found this helpful
• ###### 9. Re: Repeating fields puzzle: how to combine two calculations in one?

See attached mod to your file, using related records.

1 of 1 people found this helpful
• ###### 10. Re: Repeating fields puzzle: how to combine two calculations in one?

Dear Bruce. I appreciate your positive and educational approach. In this way I think many can learn the optimal way for doing things in FileMaker. I recommend others to download your file, because it contains many tips and a broad evaluation of the problem. Well done. Thank you for your effort and have a great week. Candido

• ###### 11. Re: Repeating fields puzzle: how to combine two calculations in one?

Thanks. Glad you took a look at it.