This shouldn't be hard....

[mnelson]

I'm stumped. I have five fields for tracking numbers of students in a class each week. The fields are defined as follows: Count1 is text. Count2 is text with auto-enter calculation: Count2=Count1. Count3 is auto-enter calculation: Count3=Count2, etc.

What happens is when you enter a number in Count1, it automatically pops into Count2 thru Count5. This is as it should be. But then what I want is when you MODIFY say, Count3, I want Count4 and Count 5 to have the new info. The only way to get it to work is first go and delete any values in those fields (tedious).

I've tried defining fields as number, instead of text; I've tried a self-join and a Lookup instead of Specify Calculation, and I've tried different Validation criteria. This shouldn't be hard, but I'm fairly new, and I just can't get this worked out. Help!

[Keith M. Davie]

...but it is not working.

It is a design problem.

What is it you are trying to accomplish in regards of tracking students?

[CobaltSky]

What you are trying to achieve can be done in several ways, but perhaps the best way is by the use of look-ups referenced to a series of appropriately triggered relationships.

The method involves creating a separate trigger calculation (for use as the key field of each relationship) which will recalculate when each of the fields in turn is modified. A separate relationship based on each trigger field can then be used as the basis of the lookup to populate the successive fields.

The kind of formula which you can use for the field dependent trigger would be along the lines of:

Case(IsValid(count1), Status(CurrentRecordID), Status(CurrentRecordID))

---

(where the target of each relationship is a stored and indexed calculating field with the formula defined as Status(CurrentRecordID)).

The set-up to get all five fields working as you desire gets a little complex, but it really just extends the logic described above. To make it a little easier, I've knocked together a working model for you to look at (it's unlocked, so you'll be able to pull it apart to see how I've set it up).

I've attached a copy to this message, or (if you have trouble retrieving it) there's a copy you could download from my server at the following URL:

http://www.nightwing.com.au/FileMaker/count.zip

[Rigsby]

Cool solution Cobalt, but I have to support Keith on this. Perhaps the task in itself is pointless. Many times is a complex script or calculation the result of approaching a task the wrong way. We still don

[CobaltSky]

Thanks Rigsby, but I think I'll be quite happy to let mnelson be the judge of whether or not the task is pointless?!

[mnelson]

Thanks CobaltSky. I didn't go into further detail because I was trying to keep the question brief, just the mechanics, not the reasoning. However, the tracking sheet is a request by office staff. Right now they have a huge spreadsheet where they keep track of numbers of students in each class, and also number of male and number of female in each class. The reason they do this is we are a very transient school, kids coming and going all the time. So in order to keep the classes balanced, they have to track it. They are doing this totally by hand, they want to do it digitally. I have the numbers pop in automatically for the week so that if they don't change, they don't have to rewrite the number, like they do now. If it DOES change, however, I want the rest of the week to show the change. It almost works the way I want it to, except right now, you have to go ahead in the week, delete the values for total number, number of boys and number of girls, before the new value will pop into the box. I should note that the entire school is viewed at once in Table view, so to the staff it resembles their current spreadsheet.

[CobaltSky]

Hi mnelson,

Thanks for the explanation. Actually, having worked in education myself for periods of time, I surmised that your purposes were along the lines you've indicated - in fact I've seen a few challenges of this type before in my travels .

Hopefully the demo download and description will set things working the way you require...

All the best,

[Keith M. Davie]

CobaltSky, that was a very good read of the problem. And thank you for providing the execellent sample file solution. Very cool design solution.

[CobaltSky]

Thanks Keith,

Glad you found the demo interesting - and I appreciate the compliment!...

[mnelson]

Cobalt,

I emailed you privately, but wanted to say here, too: that was exactly what I needed, and Thank You.