Running totals that reset

[deystad]

Have what is probably a simple problem for you folks, but not for me.

Building a database to record multiple data entries we manually record each time we run a new job through our chemical etching process. There are currently 17 fields per record, but for now need help keeping a running total of metal dissolved in bath (Total Metal, below). The total has to reset itself to zero each time we start a new bath.

Here's a sample of what it might look like isolated to just the pertinent records:

(Sorry for the underscores, but the forum software doesn't like spaces)

Bath #__Job #__Weight-B/4__Weight-After__Diff'nce___Total Metal

1______1_______40_________32__________8_________8

1______2______120_________80_________40________48

1______3_______75_________40_________35________83

1______4_______60_________45_________15________98

2______1______90__________65_________25________25

2______2______40__________25_________15________40

2______3_____120__________40_________80_______120

Etc., I'm sure you get it (hope the math is correct!).

Using a summary field seemed like the obvious solution, but don't know how to reset and don't want the field to change if we sort the records differently or do a find, e.g., on all occurrences of Total Metal of say, 40 + or - 2.

If you (or I) need anything else, let me know.

Probably have more questions later when we get to doing finds based on multiple fields and ranges within fields (hoping this possible).

Using FM Pro 8.5 v2 on Mac.

Thanks in advance for your time.

[comment]

Do you have another table where each bath is a unique record?

[deystad]

No. Not sure what use that would be, of course, I'm a novice, so I could be missing something.

[comment]

Actually, it doesn't matter - though I am curious where do you get your Bath# from. Anyway, see if the attached helps:

cRunTotal.zip

[deystad]

The bath number field is set in the Field/Control Setup dialog box as an Edit Box with "Auto-complete using previously entered values".

Each time a new bath is started we would manually put the new bath number in when we enter the first set of data in their respective fields (i.e., first record for the new bath). After that the field would auto-complete with same number until it was manually changed when new bath was started.

The bath number is arbitrary (we don't currently number our baths) and will just will be a sequential number. I thought it might come in handy for resetting the cumulative total. Also, perhaps later when a few years of data are available, it could be useful doing some sorting or compiling some cost data.

Anyway, looking at your example, with sRunTotal and cRunTotal are you showing me two different ways to do the same thing or are they both necessary? I don't have any more time to spend on this right now, but will look at it more to understand the commands, syntax, etc.

35 years ago I was Fortran programmer for a couple years, so I know what I want to do, just need to learn the language. This is fun!

Thank you so much!

[comment]

with sRunTotal and cRunTotal are you showing me two different ways to do the same thing or are they both necessary?

sRunTotal is a summary field; it provides the result you want as long as the records are sorted and all records of the bath are found. cRunTotal is a calculation field based on a relationship; it provides the same result regardless of found set and/or sort order.

[deystad]

I'm thinking the cRunTotal is better for my uses.

The purpose of this database is to be able to look for records matching three or four criteria. Let's say, a year from now the current bath has 58 oz. of dissolved metal in it and we want to run a 1/4" thick plate weighing 32 oz. and we want it to etch away .085" of metal.

To accomplish the .085" etch depth we need to set the time the plate will spend in the bath, the temperature and the speed of the paddles that splash the acid on the plate. The other important variable is the total amount of dissolved metal in the bath at the start (as the total amount of dissolved metal in the bath increases the longer the etch time must be to accomplish the same depth).

Therefore, if we have 3,000 records a year from now, we can sort (Find) on a total dissolved metal range within 2 or 3 of ounces of our current value and see all similar runs (I'm guessing there would be 5-6 records found) and their results. From these we can more closely estimate our time, temp. and speed variables.

Since these found records will no longer be grouped by bath number (which is irrelevant), I'm thinking the summary cumulative Total Metal will get messed up? Seems like it gets almost circular or dependent on found records?

Now I'm wondering if the cRunTotal calculation would also get messed up and change based on the found records.