Simple loop not working

[crebma]

I am extremely new to FileMaker, and I am trying to write a simple script that will add all the values of a particular field throughout the database (MA) and divide it by the number of records (count) to get the average (MAA). I realize that there is an average function, but it's not working either, for some reason. Right now I have two test records in, the first being "5" and the second being "3". I set MAA to a global number, and I set MA to a number. But when run it from the first record, it gives me "5", and when I run it from the second record, it gives me "3". There is something I obviously am misunderstanding, so I am going to post the code here. Hopefully you guys can help me.

Go To Record/Request/Page [First]

Set Field [CM::MAA;0]

Set Field [CM::count;0]

Loop

Set Field [CM::MAA;CM::MAA + CM::MA]

Set Field [CM::count;CM::count + 1]

Go To Record/Request/Page [Next; Exit After Last]

End Loop

Set Field [CM:MAA;CM::MAA / CM::count]

Any help would be greatly appreaciated, as I am dumbfounded. Thanks!

[crebma]

I forgot to mention, the values for MA are entered through a pop-up menu.

[RalphL]

The fields MAA and count should be global fields.

[comment]

Why not simply define a summary field as average of MA?

[crebma]

because that is beyond me. (hahaha!) anyhow, i will look more into this. i read the help files on summaries a little bit and quickly got lost. in response to ralph, they are global fields, which is why this is so confusing to me. anyhow, even if i do figure out how summaries work, i am one of those people that must have the answer to the first issue, no matter how irrelevant. this will plague me until i get it straightened out!

thanks anyhow!!!!

[crebma]

alright, i don't know why i thought that would be so hard, but i did it. now it says, "this action cannot be performed because the field is not modifiable." or something to that effect. could it be because the data for MA is entered through a pop-up menu? is there a way for me to define the value list to equate numerically?

edit: after it gives the not modifiable junk, it does bring up the layout which displays the averages. and now, instead of 5 or 3, it gives me 3.5. which i'm pretty sure is NOT the average of 3 and 5, but hey, it's been awhile, things may have changed. :]

[comment]

Why don't you attach your file so we can see the problem?

[RalphL]

I made a sample file using you field names and your script, it gives me 4 for an answer. Also made summary field average. It also gives me 4.

Are your fields MA, MAA & Count number fields?

[crebma]

I figured out that problem; I was trying to average in too many places. I used a summary field in the end, but the trouble was that I was also still using the sveraging script. So, that was extremely dumb on my part.

The thing is, I have to do it for a buch of different variables, and some of them are things like, "excellent" or "poor" and so on (Rating Cards). These things are chosen from a pop-up menu. Is it possible for me to set up the value list so that the text values are equal to numbers? For example, could I make it see "Excellent" as 4, and "Good" as 3 so that it will figure out which one is the most common, round it, and tell me how we're doing?? I just need it to be able to get thrown into an equation, I guess is what I'm trying to say. Anyhow, thanks for all help thus far, it is GREATLY appreciated.

[-Queue-]

You can use a calculation (or auto-enter calculation with 'do not replace existing value' deselected) number field with

Case( Rating = "Excellent"; 4; Rating = "Good"; 3; Rating = "Average"; 2; Rating = "Poor"; 1 )

[crebma]

Wonderful!! It works beautifully! Thank you so much!!!

[crebma]

Acutally, upon coming in today to put on the finishing touches, I realized that this is not working as beautifully as I had hoped. When you select "Excellent," (or any other option), and it translates it into 4, it immediately forgets that Excellent was chosen. Thus, the calculations no longer work. So the field will say '4', but the averaging calculation sees nothing once the database is restarted. How on Earth should I avoid this? In order to make the calculations work correctly, I have to go through and re-select everything in the database. As you know, this is in no way efficient.

This is the last issue with the whole thing. After this averaging business gets taken care of, it will be completely finished, and I will be completely relieved. At least until I start one of the next six databases my boss has lined up. :]

[-Queue-]

The calculation should be in a separate field, not overwriting your Rating field.

[crebma]

you're right. suppose i should've taken that way in the first place instead of trying to take the easy route. haha, thank you so much.

[-Queue-]

You're welcome. Perhaps I should have also been more explicit in differentiating the fields. I hope you do not have too much clean up work because of this.

[Jack Jarvis]

I have a similar problem trying to calculate averages of fields in a record. I use this formula:

(field1 + field 2+ field 3+field 4)/ 4

This works, but if say, field 2 is empty it still counts it as a zero and it skews the average.

Any ideas? Thanks.

Jack J

[-Queue-]

Try

Sum( field1, field2, field3, field4 ) / Count( field1, field2, field3, field4 )

[Ender]

Queue, I think you are slipping in your prolificity.

How about:

Average ( field1, field2, field3, field4 )

[-Queue-]

Good call! I had forgotten that Average only considers non-blank values. Actually, I do not think I have ever used Average (or had need to use it) in a solution yet. So I can plead ignorance instead of slippage on this one. :)

[Jack Jarvis]

Many thanks Queue!! That worked perfectly!! Keep up the good work!

Jack J.

[Jack Jarvis]

Thanks much to Ender also! That average formula was much less work. I had tried to use these before, but there must be something in the spacing of the () and the ,.

Thanks, gentlemen.

Jack J.

California

Edited July 27, 2005 by Guest