Is there a way to validate summary fields?

[walkerjk]

I have a summary field that I need to use a "unique" validation on. Anybody know how to do this?

Thanks in advance.

[Chuck]

Unique to what? Summary fields don't belong to a single record, they summarize the data across records for a found set. So if you have a summary field called Total_of_Sales which is defined to be (of all things) the total of the Sales field, then this will calculate the sum of the Sales field for the found set. (It actually gets a bit more complicated than this, because the fields that it totals will also depend on what kind of part the field is placed in on the layout and how the found set is sorted.) As you can see, this doesn't belong to a single record.

Perhaps what you are trying to do is to accomplish something else, or perhaps you are using the Sum function to total data in a related database. Please provide some more detail as to what you are doing and why, and we might be able to point you in the right direction.

Chuck

[walkerjk]

The summary field in my DB actually just strings together data from fields in one record at a time. I am using this to create unique control numbers (based on how the user populates the fields of said record) to be put on illustrations in a technical manual. However, it just occurred to me that if a user were to enter data identical to data previously entered, I would wind up with 2 (or more) illustrations with the same control number, which defeats the purpose of a control number.

These are some example fields on a typical record:

Manual Type "MM"

Chapter "12"

Section "34"

Subject "56"

Sheet Number "001"

The summary field (called "Control Number") would then return a result of:

"MM123456001"

If, for instance, the user creates a duplicate record and forgets to change the sheet number to "002" then I have 2 records with the same Control Number. (Which reminds me, I also need the sheet number to be serialized when someone does duplicate a record - which would alleviate the problem in this particular example.)

Thanks for your help!

[Chuck]

Ah, we have a difference in terminology. In FileMaker, summary fields mean something other than what you are using the term for.

There is a way to do this involving some complex looksup, but one way I can think of is to build a self-join relationship for your calculated field that has itself on both sides of the relationship. Then create a field that uses the Count function to find out how many related records there are. If there is ever more than 1, then the field isn't unique. You would have to use some scripting for this, since I don't know of a way to validate a calulation without a script.

Chuck

[billholt]

Key,

There's an example file of how to do this in the instructional demo section of my site. It's called something like repRecord.fp3. I don't have the direct address at hand, but the front door is here.

Bill Holt

[Moon]

Piggy backing on Chuck's suggestion, I have found this technique works well:

As Chuck said, create a self-join relationship. We'll call it SelfCntrlNum, where Control Number in you file equals Control number in your same file.

If your solution does not have one, create a serial number field "SerialNmbr", (number, auto enter, starting with 1.) Make it unique and do not let it be modified once you have set the serial numbers in your existing records.

Now create a calc field with a text result, DupeIdentifier.

If (SerialNmbr = SelfCntrlNum::SerialNmbr, "", "Duplicate").

In the record whose serial number matches its own serial number, nothing displays. If the Control Number is in a record that matches a related record (which has a different serial number), "Duplicate" is returned. Based on what is returned you can implement any other steps or actions.

(Editited ruthlessly (TWICE!) after reading my original garbled explanation.)

[ June 06, 2001: Message edited by: Moon Mullins ]

[ June 06, 2001: Message edited by: Moon Mullins ]