Avoiding Duplicate Records

[J. Williams]

Brand new to this Forum and very new at FM (Pro 5.0v3).

I have an existing FM inventory for plan sets relating to highway construction. Contract numbers are unique. Records are based on contract numbers. I'm having problems with folks adding new records (and information) for contracts that already have an existing record. How do I stop this?

[BobWeaver]

In the field definition options, you can set the unique validation option for the contract number field.

[ESpringer]

J. Williams,

Indeed, setting the "unique value" option is needed!

You can also make it less likely that people will *try* to duplicate values by including, on the most basic layout, a virtual "hot index" of records by contract number (perhaps sorting most recent at the top). Each contract number could work as a button, like a hyper-text-link, to go to the relevant record.

To do this, you need to use a simple field to tag everything to be included in the index with a constant value. A number field with "1" auto-entered in every record would work well. Then, set up a self-join relation, where every record in your file of contracts is connected to every other record that shares the same constant "1" value. (Or you could have a calculation so that only open projects are tagged with "1")

Then, within any layout, you an set up a portal for this self-join relation (with a prominent "Existing contract numbers:" heading), and in each row include the contract number, perhaps with a brief descriptor, and define the project number field as a button which takes the user to the related record based on the self-join. (If you make the text of that field blue and underlined, every user will recognize immediately that it's a hot link.)

[John Caballero]

Alternately, you could have contract numbers be auto-enter serial numbers (you can set this in the Options in the field definition).

[ralph.nusser]

Make a Self Join relationship in the contract database

Relation Name Contract Number

Left Side Contract Number

Right Side Contract Number

Validation: not IsValid(SelfJoin Contract Number::Contract Number)

Make it strict that nobody is allowed to enter dubs.

Make a text warning: The Contract Number you typed in already exists in the database. Please check.

[color:"red"] See attached example

ContractNumber Dubs Prevention.zip

[LaRetta]

Hi JWilliams,

You've been given a lot of information - and I plan to add to it!

Contract numbers are unique. Records are based on contract numbers. I'm having problems with folks adding new records (and information) for contracts that already have an existing record

If Contract numbers are unique, I assume it assigns a new Contract number when a new record is created or do Users have to type in the Contract number? Be sure it's set to Unique as Bob suggested.

But ... What you may be describing is Users creating a new Contract for someone that already has a Contract established (example).

If so, you need to determine what criteria would determine if a Contract already exists. For instance, if Bill Smith already has a Contract, Users would be able to create a new Contract (thus assigning a new ContractNumber) and still enter Bill Smith's name as the Contract Owner.

This example may not fit your situation, but I'm hoping you get where it leads. Once you determine what makes a contract 'Unique', you can address the problem in various ways, depending upon your answer back to us.

If the Name should be unique, you can handle that in Field Definitions Validation on the Name field. But if more than one Name can have more than one Contract (within the db) but ONLY if other fields are unique for that name, you'll need to adjust accordingly.

Then you can use the various techniques listed here.

LaRetta

[J. Williams]

Thanks all - users have to create the new record based on the number from the contract, which is unique. So far the unique validation is working to prevent duplicates. I'm going to try to work through some of the other suggestions too so I can learn the options. Thanks again for all the help.