Choose function won't choose in FMP 7

[macwino]

Please pardon the length of this post, but I

[Fenton]

This is not usually what a Choose function is used for. If you replace "Choose" with "Case" I believe your calculation will work.

Be sure to uncheck "Do not evaluate if fields equal zero"

Choose is used to select from a "zero-indexed" series (begins with 0). Ex.:

Choose (number field, "", "A", "B", "C", "D", etc.)

(notice I put "" nothing as the 0 choice)

You could also write your calculation:

Case ( Zero QR= 1; Total Purchased-Count(Drink Notes::Notes) + Count(Drink Notes::N from Cellar); 0)

I'm not sure if that would be better or not. I didn't read your whole post. I'm supposed to be working :-)

[macwino]

Fenton, thanks for the input - but the problem remains.

If I change to the Case function and uncheck "Do not evaluate . . .", I get an accurate bottle remaining count where there's a simple calculation of bottles purchased less drink notes entered. However, for those records where the Zero QR box is checked I do not get 0 in the Quantity Remaining field as I want, but instead I get a true count of the purchases less drink notes. If I leave "Do not evalutate . . ." checked - which is how my script has always been written - the result is the same.

I also tried your alternative calculation and it too didn't work. With "Do not evaluate . . ." unchecked, I get a 0 entry where the Zero QR box is checked as I desire, but I also get a 0 entry for all those records where a true bottles purchased less bottles drunk calcuation should have been performed and not all bottles have been drunk. And if I check "Do not evaluate . . ." I wind up with 0 quantity remaining for the Zero QR records as I desire, but I get an empty field for those records where there should have been a true calculation performed.

Using my usual trial and error method, I also tried numerous variations on your suggestions and none gave me the results I desired and had with FMP 5.5. Any further thoughts?

[Fenton]

OK, I read your original (often a good idea when answering questions :-)

You need do this checkbox elimination calculation at the record level, not at the end. What you're doing now is basically turning on or off the entire calculation. You don't need to do this at the "aggregate" level (Count, Sum, etc. are Aggregate functions).

Instead of:

Case ( Zero QR= 1; Total Purchased-Count(Drink Notes::Notes) + Count(Drink Notes::N from Cellar); 0)

You want a field:

_cBottleDrunk = Case ( not IsEmpty(Zero QR); 0, not IsEmpty(Notes) and not N_from_Cellar = 1; 1; 0)

Uncheck "Do not evaluate if fields = 0" (I think)

Then just Sum that field. In other words, create the count at the granular level, don't wait until the end.

[macwino]

Fenton, thanks for the follow-up. I will give your suggestion a try this evening, as I've got to run off to - what else - a wine tasting.

What's intriguing to me is that my structure and scripts for this calculation worked just fine in FMP 3 through FMP 5.5. Only in FMP 7 have they become a problem.

I'll let you know how I make out tonight or tomorrow. Thanks for taking the time to help me.

[macwino]

Fenton, this problem is solved and your suggested field definition pointed the way. Thanks so very, very much.

I had to modify what you suggested a bit. Here's what I did. First, I was a bit overwhelmed by your syntax and your saying that I should "just Sum that field." Remember, I don't do this for a living; I'm just an amateur. But I tried creating a new field like you recommended and thought I'd worry about summing that field later. Well, the new field didn't give me any useable results; it gave me 0's and 1's, which may have been what you had in mind. In any event, I took your syntax, kept the first part dealing with the Zero QR field (which made sense to me), but modified the second part which I couldn't understand the logic of at all. What I did was substitute my basic quantity remaining calculation for the second part. What I wound up with was as follows:

Case (not IsEmpty(Zero QR); 0; Total Purchased-Count(Drink Notes::Notes) + Count(Drink Notes::N from Cellar))

I then placed this is my Quantity Remaining field and I got the results I'd been looking for. It made no difference whether or not the "Do not evalutate . . ." box was checked. The results were always the same.

I can't thank you enough for this assistance. I was at my wit's end trying to get this calculation to work. If you're ever up in the Napa Valley please get in touch and we'll create a Drink Note together and watch the Quantity Remaining decrease. Thanks again.