duplicate records

[nbrown]

I have a database that has three fields:

studentID

date

period

When someone creates a new record I need some kind of calculation to do this:

If a person puts in 000001 (studentID) 3/13/02 (date) and 4 (period) I need the calculation to make sure there isn't already a "period 4" on "3/13/02" for that specific "000001" student.

Thanks, Nic

[Chuck]

I had to do something similar some time ago, and I can't find the files I did it in, so I'm working from memory here.

Create a calculation text field that concatenates the three fields:

ConcateField = studentID & date & period

This will produce results like this:

"0000017309234"

Make sure the calc field has a text result. Basically the "730923" is the internal representation of the date.

Create a self-join relationship based on this field. I'll call this relationship Self_ConcatField

For each of the three fields validate using a calculation with the following as the calc:

code:

---

IsEmpty( studentID ) or IsEmpty( date ) or IsEmpty( period )

or

Count( Self_ConcatField::ConcatField ) <= 1

The three IsEmpty functions prevent the validation from failing if any of the fields are empty. The last part makes sure that there is no more than one related field via the relationship.

Again, I did this some time ago, so you may have to tweak the technique.

Chuck

[ March 14, 2002, 01:06 PM: Message edited by: Chuck ]

[nbrown]

Well, I created "ConcateField" as a text field, with an auto-enter calculation of:

ConcateField = Student ID & Date & Period

But the ConcateField shows only 3/15/2002 and nothing else.

Any ideas?

[andygaunt]

You may want to check the calculation type is set to text and not a date option in the calculation window.

[nbrown]

It seems to work.. when someone creates a record that is identical to the other, it now tells them it is a duplicate.. I would like tho for FileMaker to just delete the duplicate record, and perform a File, Close rather than giving them the option to "Revert Field" and change it.

[Chuck]

For that you would need a script. Basically the user would enter the information in the record and click a button that says something like "Submit". The submit script would check to see if there were any duplicates (you can use the same field and relationship for the validation technique). If there is a duplicate, then a dialog would come up alerting the user to the fact and then doing whatever you want, such as deleting the new record. Unfortunately you can specify a script to run if validation fails. Perhaps a plugin like oAzium Events could do this. I'm not sure.

Chuck