Inserting a result depending on field numerical range

[alcimedes]

I've been trying to figure out how to insert a value into an empty field, based on the number range of another field. I don't have access to FMP developer.

 

I've tried two ways to accomplish this, so far with no luck for either.

 

The first attempt was to set my result field as a calculation field with the following calculation using nested If statements:

 

If ( Average Donation per Month 5 & Average Donation per Month

 

What I was going for there is if an average donation for someone is between 0 and 5 the field will show a Dynamic Ask of 5. If the Average Donation per Month is between 5 and 10 then the Dynamic Ask should be 10. Else 25.

 

The result I get for all records regardless of the value in Average Donation per Month is 5.

 

I also tried a script that used a combination if If, insert calculated result, else if etc.

 

The If statements look like:

 

Donor calculations::Average Donation per Month > 0 & Donor calculations::Average Donation per Month ≤ 5

 

insert calculated result 5

 

Else if:

 

Donor calculations::Average Donation per Month > 5 & Donor calculations::Average Donation per Month ≤ 10

 

insert calculated result 10

 

The results here are always an insert of 5.

 

I'm sure it's a syntax problem on my end, but I've pretty much run dry on other ways to do this.

 

(Short of a Find based on range 0 ... 5 and inserting the results on all found records, then another find based on range 5 ... 10 and another insert on all found records etc. but that seems to be a very inelegant solution.)

[AHunter3]

If ( Average Donation per Month 5 & Average Donation per Month

 

 

Is "Dynamic Ask" the name of this very field? (I assume not; FileMaker will not let you define a field in terms of itself). What the heck IS "Dynamic Ask"?

 

Oh, and kill that ampersand. You want "and", not "&". The ampersand is strictly for concatenation.

 

"New" & " " & "York" = "New York",

 

but you would use

 

Case (Leftwords (cityname, 1) = "New" and Rightwords (cityname, 1) = "York", "New York".

 

They aren't at all interchangeable.

 

The type of formula you want would be more akin to:

 

Case ( Average Donation per Month 5 and Average Donation per Month

[Maarten Witberg]

try

 

Dynamic Ask = calc, number result =

Case

(

Average Donation per Month ≤ 5 ; 5 ;

Average Donation per Month ≤ 10 ; 10 ;

25

)

[alcimedes]

Is "Dynamic Ask" the name of this very field? (I assume not; FileMaker will not let you define a field in terms of itself). What the heck IS "Dynamic Ask"?

 

I work for a non-profit. A dynamic ask in this case is what we email out to our supporters when we ask for money. The amount we ask them for however is variable depending on past donation history.

 

I'll give those formulas a shot and see if they work. I kludged something together using the range based Find just to get something going, but I always like cleaner code.

[alcimedes]

try

 

Dynamic Ask = calc, number result =

Case

(

Average Donation per Month ≤ 5 ; 5 ;

Average Donation per Month ≤ 10 ; 10 ;

25

)

 

That's exactly what I needed to start with. I had to add the range to each line, but that was easy.

 

I ended up with this:

 

Case
(
Average Donation per Month  <  5 ; 5 ;
Average Donation per Month  ≥  5 and Average Donation per Month ≤ 10 ; 10 ;
Average Donation per Month  > 10 and Average Donation per Month ≤ 25 ; 25 ;
Average Donation per Month  > 25 and Average Donation per Month ≤ 30 ; 50 ;
Average Donation per Month  > 30 and Average Donation per Month ≤ 40 ; 75 ;
Average Donation per Month  > 40 and Average Donation per Month ≤ 80 ; 100 ;
Average Donation per Month  > 80 and Average Donation per Month ≤ 100 ; 125 ;
Average Donation per Month  > 100 and Average Donation per Month ≤ 125 ; 150 ;
Average Donation per Month  > 125 and Average Donation per Month ≤ 150 ; 175 ;
Average Donation per Month  > 150 and Average Donation per Month ≤ 200 ; 200 ;
Average Donation per Month  > 200 and Average Donation per Month ≤ 250 ; 250 ;
Average Donation per Month  > 250 and Average Donation per Month ≤ 500 ; 500 ;
Average Donation per Month  > 500 and Average Donation per Month ≤ 750 ; 750 ;
Average Donation per Month  > 750 and Average Donation per Month ≤ 1000 ; 1000 ;
Average Donation per Month  > 1000 and Average Donation per Month ≤ 2500 ; 2500 ;
Average Donation per Month  > 2500 ; 5000 ;
)

[Maarten Witberg]

I don't think you need the range. Filemaker will evaluate Case () from one statement to the next, stopping at the first test that returns True. So if the amount is smaller than 10 it will move on at

[alcimedes]

You are correct. Took out the range and it was just as happy as with it. Thanks!