6 Replies Latest reply on Mar 31, 2015 10:38 AM by planteg

# Calculation fields with repetitions

Hello everyone,

I am wondering if it's possible to have different calculations for different repetition of a field  ? Maybe that can't be done, but here is how I came to think about this.

For the purpose of creating a report, I will use a table with calculation fields, some kind of virtual table. For you to make an image, think about a pay slip where you display numbers for a specific pay as well as totals for up to that pay. Then you should display two amounts per category. To get these numbers, I will have Data_1 and Data_2 which will be filled by two ExecuteSQL() calls. Then field_1 will be GetValue ( Data_1; 1), field_2 will be GetValue ( Data_1; 2), etc.

I could double the number of fields_n, but instead I thought of having two repetitions: repetition 1 for this pay, and repetition 2 for up to this pay. This is where I run into  the wall: how can I set a different calculation for each of the repetition, for example:

field_1[1] = GetValue( Data_1; 1)

field_1[2] = GetValue( Data_2; 1)

I am looking for a way to do it as calculation fields, but maybe I can achieve this only trough a script - it's worth asking .

Thanks

Gilles Plante

• ###### 1. Re: Calculation fields with repetitions

Have you considered using a Virtual List technique?

• ###### 2. Re: Calculation fields with repetitions

Mike,

this is something similar I am doing, except that instead of saving the list to a global variable, the list is in a field. I thought about using repeated fields instead of doubling the number of fields. The advantage of using a calculation in a field is that I can use a Let and use the technique to prevent errors if a table or field is renamed (using ExecuteSQL() defensively). I would like my table to have only one row (global fields).

Gilles Plante

• ###### 3. Re: Calculation fields with repetitions

Uh … a Virtual List parses a list into fields using calculations.

http://www.mightydata.com/blog/virtual-list-in-three-easy-steps/

http://www.seedcode.com/virtual-list/

• ###### 4. Re: Calculation fields with repetitions

Make list of formulas, and evaluate it like

Let ( [

f = List (

"GetValue( Data_1; 1)" ;

"GetValue( Data_2; 1)"

)

] ;

Evaluate ( GetValue ( f ; Get ( CalculationRepetitionNumber ) ) )

)

If every formulas have same format, you can simplify with listing up different part only.

Simplest form is something like

Evaluate ( "GetValue ( Data_" & Get ( CalculationRepetitionNumber ) & ";1)" )

• ###### 5. Re: Calculation fields with repetitions

Mike,

This is almost what I am doing, except that instead of having a global variable holding the SELECT result, I set it to a field.

user19752,

that does not work for me. Data_1 and Data_2 are properly filled, but the repeating calculation fields are empty. A few of the calculation fields are not repeating and calculated with GetValue ( ... ) and these are correctly filled. . Strange.

Thanks

Gilles Plante

• ###### 6. Re: Calculation fields with repetitions

I solved the issue .

I had to change user195752 a little bit:

Let ( [

f = List (

"GetValue( Extend( Data_1 ); 1)" ;

"GetValue( Extend ( Data_2 ); 1)"

)

] ;

Evaluate ( GetValue ( f ; Get ( CalculationRepetitionNumber ) ); [Data_1; Data_2] )

)

Explanations:

• I have to use Extend because Data_1 and Data_2 are not repetition fields (I may not have to use it for Data_1
• The second parameter is needed to trigger the recalculation when either Data_1 or Data_2 changes

It may be hard sometimes to get how things work in FileMaker, but what nice things one discovers . Many things can be done without coding anything, just define a field the proper way !

Gilles Plante