The "alphabet soup" used to label your tables makes your description of what you want to do hard to follow. Real table names are better.
I am trying to write a script that to keep a running tab and record the totals in another table.
A "running tab" of what exactly? Records in a table? Presumably, this would be a running total, but could be set up many different ways depending on what you have to use to compute that total and the results that you want.
And why do you want to record the totals in another table? That is sometimes necessary, but often is not necessary if the only reason is to produce a report by category.
so basically this is the situation
a customer purchases fruit apples oranges and bananas each day of the week they call in with an order but the price changes daily we do this with a price code each customers price but at the end of the week we tally all of this up in the past we have done this using access but want to move away from access as we are migrating away from microsoft in general so in the past we used 2 tables that looked like this
fruit QTY table
Name Mon Apples Mon Oranges Mon Bananas Mon Rate Tues Apple Tues Oranges Tues Bananas Tues Rate .... etc
Toms deli 10 1 13 a 10 4 5 c
Apple Oranges Bananas
a 1.15 2.15 3.15
b .75 .95 2.00
c 3.15 2.25 3.75
on top of this we had another table which held customer information and another which held there order information for the week by day
at the end of the week we tally it up and enter it in instead of making a daily order form so on monday and tuesday a customer might be rate code A while the rest of the week the were b
so what we did was ran a script it said monday apples x Apples:mon rate code + tues apples x Tues Apples x Apples: Tues Rate code.. etc
at the end we have an invoice that says apples $30 oranges $22 Bananas $12 Total $64
so I get setfield then I tried using case the check whist the rate was and then multiply the apples qty by the apples price but I'm Only defying a field so all of apples I want to be more specific in my calculation and say multiply it by apples field row a? is this making any more sense?
I'd start by restructuring your basic data model.
I'd set up a transactions table with these fields:
UnitPrice (Looked up from Price table [see below])
cCost (calculation field defined as Qty * UnitPrice
An Order or Invoice table would have these fields:
cOrderTotal (Calculation Field: Sum ( Transactions::cCost )
A Customer table would have a __pkCustomerID field plus any other fields needed to document a your customers
You'd have a Price table with:
Customers::__pkCustomerID = Orders::_fkCustomerID
Orders::__pkOrderID = Transactions::_fkOrderID
Price::PriceCode = Transactions::PriceCode AND
Price::_fkProductID = Transactions::_fkProductID
Products::__pkProductID = Price::_fkProductID
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
You can now calculate totals in more than one way. A summary field in the Orders table can tally up order totals for one or multiple customers and a summary report can use this same field to produce a variety of sub totals.
You can also use a summary field in Transactions to get totals with subtotals by _fkProductID as well as order totals. These totals can be for a single customer, or multiple customers--which can be broken down by customer or not as needed.
For a Tutorial on summary reports, see: Creating Filemaker Pro summary reports--Tutorial