Simple? Tallying values total=total+current field

[CurtT]

I'm trying to add a script that will loop through the found records and total the value of the fields.
I've tried using the Insert Calculated result using the caclulation "field 1 total = field 1 total + field 1" to increment the count but it doesn't add the "field 1" value to it.

Any ideas on how best to do this?

The fp5 database contains inventory info. The script needs to tally usage for each inventory item for the month.
I plan to first search and find all records for that month and then extract the quantity of each item used in that period.

There are 24 unique items in the inventory and they don't have unique field names. Any of the 24 products can (and are) entered in any of one of the six Quantity-product groups. The fields in the database are in 6 groups of Product qty and product name:
Quantity1 Product1
Quantity2 Product2
Quantity3 Product3
Quantity4 Product4
Quantity5 Product5
Quantity6 Product6

The script I thought would work would find all the records for the month and use if-then loops to check the field "Product1" to see if it's a widget and if it is then copy the value in the corresponding quantity1 field and put it in a widget total field then go to the next found record, see if "Product1" also contains "widget" and if it does then copy that "quantity1" value and add it in a total field. The Insert calculated result doesn't add the second record's quantity1 value to the total though.

Does my total field need to a certain field type? I tried using a summary field but it only seems to be designed to add field values.

It seems simple but I'm at a loss as to how to do it. Any help or hints would be most appreaciated.

Thanks,
Curt

 

[lebisol]

"Does my total field need to a certain field type? "
YES it does (should be) have to be the same as the fields added.
Also I think that just declaring a "Total" field as a summary field wont cut it...you must create a calculation field (assuming that the other fields contain="Product1+2+3&quot that deoes the Sum(.....)
good luck
p.s. for "more" conversation on FM I would strongly suggest FM oriented group :

http://www.fmforums.com

:

 

[marty1452]

I thought this was a FM forum!

I'm going to try to assist you however I do not fully understand the database and the solution you need.

Regarding the field type, if you are in a record and define a calculation field, the value of that calculation is going to stay with the record your are entering or leaving. To do a calculation loop, if that is what you feel you need, I would define a global number field. The value of a global field is the same from record to record. Write the loop to find the records you are trying to add and then use a

setfield(global field, global field + found record field)

So as the loop progresses, the global field retains the value, the setfield adds the global fields current value plus the found record value and resets itself to a new value.

I hope this helps.

Marty

 

[lebisol]

"I thought this was a FM forum!"
well sure it is...I was just recomending it in case of a lack of responses here...thats all. The more resources the better...right?!

 

[CurtT]

Thanks for the help, I think I've got it sorted now. The key was the global field. I tried using a global field earlier but didn't have the calculation right.
For some reason I thought I needed "global field = global field + found record field" but I didn't need the inital "global field=" part as it was implied.

Thanks again for the speedy replies. I still have a lot of script to write but this will sure be a much faster way to get the totals than exporting csv files into Excel, cutting and pasting, sorting and doing totals in Excel.

Curt