7 Replies Latest reply on Apr 24, 2012 11:48 PM by StewartCampbell_1

# Help with IF and Calculations using repeating fields

### Title

Help with IF and Calculations using repeating fields

### Post

Hello - I am a newbie here, just trying out FMP12 having not used FMP since many years back, trying to remember it all...

I want to create a calculation for one repeating field based on another. The first repeating field are the original prices which can be in different currencies, sometimes USD sometimes EUR. The second, calculated repeating field are to be the final USD prices (rounded to nearest 100 and minus 10). I need an IF function so that if the original prices are already USD, result 1 takes them unchanged to the final prices - but if in another currency they get multiplied by the exchange rate factor (and rounded). So far I have the following formula which works for the first repeated field - but I cannot work out what to do for repetitions 2, 3, 4 etc. Do I have multiple IFs? Can you extend to more repetitions/results in the same IF formula?

If ( currency = "USD" ; GetRepetition ( prices original ; 1 ) ; (Round ( GetRepetition ( prices original ; 1 ) * currencies::currency rate to USD ; -2 ))-10)

Thanks in advance for any help!

• ###### 1. Re: Help with IF and Calculations using repeating fields

I have realised I maybe don't need an IF function because if price will be USD=USD then I can set the exchange to 1. So the formula that now works for the first repetition is:

Round ( GetRepetition ( prices original ; 1 ) * currencies::currency rate to USD ; -2 )-10

I still want to know to correct syntax to repeat this to the rest of the repetitions. Is it nested inside the round?

• ###### 2. Re: Help with IF and Calculations using repeating fields

Is currencies::currency rate to USD a single field or a repeating field? If it's a single field whose value is to be used with all repetitions of prices original, use:

Round ( Prices original  * extend ( currencies::currency rate to USD ; -2 ) - 10

And specify the same number of repetiions for this calculation field in the number of repetitions box as you have defined for prices original.

Haven't used extend in a real FileMaker database in years as using a table of related records is almost always a better option that using a repeating field.

• ###### 3. Re: Help with IF and Calculations using repeating fields

Thanks very much Phil - really appreciate your reply!  But where do you specify the number of repetitions for the calculation? The formula still works for the first repetition but does not affect the others.

If I can't work it out then maybe as you suggest better to rework the database to have multiple related fields instead of a repeater.

• ###### 4. Re: Help with IF and Calculations using repeating fields

Circled in Red:

• ###### 5. Re: Help with IF and Calculations using repeating fields

Hi there - that selection option is not available for me (Filemaker Pro 12, Mac). See screenshot...

• ###### 6. Re: Help with IF and Calculations using repeating fields

Actually it is. I checked that before I uploaded.

You have defined a number field with an auto-entered calculation. Click the strorage tab in field options and you'll see the same control for specifying repetitions. You may need to define this field as a field of type calculation instead of number.

• ###### 7. Re: Help with IF and Calculations using repeating fields

Thank you thank you!  That was the trick - to change the field type to a calculation. Then the repetition no.s showed up the same as your screen shot.