For various reasons I have to duplicate some information in my database. I've made a script that takes details from one record and uses them as the basis for a new record elsewhere in the database. My question is: can I add some kind of failsafe to stop me inadvertantly creating a new record with the same information twice?

There are two key fields - 'Date' and 'Supplier' - that I've been trying to use to acheive this. Each of fields does not contain a unique value, but the pairings of these two fields are always unique - for example, there would only ever be one instance of '01/01/2006 and Acme Supplier'. I have been trying to create an 'If' script step along the lines of...

'If ths combination of ''Date and Supplier'' already exists then don't add a new record'

...but I can't figure out the FM language to make it happen. Does anybody have any suggestions?

Add another table occurence of the table, and define a relationship between them:

table <=> table self_join =

table::Date = table self_join::Date

AND table::Supplier = table self_join::Supplier

You can then count the number of records that have the same Date and Supplier by using a Count() function:

Same Date_Supplier Count (calculation, number result) = Count(table self_join::recordID)

If this count is more than 1, then a duplicate exists.

Hi Ender. Thanks - I'll try that this afternoon and let you know how it goes.