multiple fields as unique keys

[toolUser]

Hi,

Sort of new to FM.

How do I create a unique key out of more than one field?

The problem;

I have field 1; the ACO's in multiple buildings, and Field 2; the buildings. The ACO's may be duplicated over building's, but the concatenated key; ACO|Buildings should be unique.

How do I accomplish that in FM7?

Thanks,

Jerry

[QuinTech]

Hi Jerry, first off, unless i'm missing something, you don't need a concatenated key. FM7 allows you to create relationships using multiple fields, something that was not possible in any prior version.

This key should be unique as it is, right? If you can't duplicate an ACO within a building (which is hinted at but uncertain from your initial post), then you will never have an ambiguous key.

Jerry

[toolUser]

Relationships? You mean I relate, say, the ACO field with the building field inside the table? Don't see how to do that.

I do see how to join two tables using multiple keys, but I don't see how to keep the Master key (ACO|Building) unique. That is, I want to insure there are not duplicate keys in the master table.

Thanks,

Jerry

[Ender]

Don't know what an ACO is, but maybe this will help.

[toolUser]

Thanks, but no, I don't want to talk about whatever relationship this table might have, yet. Perhaps I've been using the wrong words.

There may be duplicate "ACOs" in field 1 and there will be duplicate buildings in field 2. Therefore it is possible to enter a given ACO|building pair twice, a bad thing.

In most other databases I've delt with you simply concatenate the two fields into one key, and make it unique. Its not clear how to do that in FM7. Perhaps they have a different technique?

Thanks,

Jerry

[Ender]

You could use the same technique (ACO & "|" & Building), but it may not be necessary. If you ever have to create a relation to this table, you can use both as separate keys.

If you are worried about users entering duplicate ACO|Building records, you could do data entry through a portal, where it is easier to see a duplicate. Having a concatenated field by itself won't eliminate duplicates (you can't do validation on a calculation result).

There may be a field validation technique that could help avoid duplicates when users enter the ACO or Building.

[-Queue-]

You can't validate a calculated field, but you can create an additional field, auto-entered with a 1, that contains the validation you'd like to use with 'Validate only if field has been modified' deselected. Put this field on any layout where any field referenced in the calculation might be modified, disable entry to it, remove any borders, and set the font to match the background so that it's invisible. Create a self-relationship between the concatentated field and itself.

I would use a validation something like

not(Length(ACO) and Length(Building)) or Count(selfrel::id) = 1

If either field is empty, no validation occurs; otherwise, there had better only be one related record.

[Ender]

Queue,

I like your logic, so I had to try it. But I couldn't get it to show the validation error message at the proper times. It pops up even when the values are unique.

Maybe it's just me (validations aren't my thing.)

[-Queue-]

You're right, that = 1 should be <= 1. Note that this doesn't evaluate well when the Building or ACO fields are modified, apparently since the Count relies on their values (kind of a like a circular validation). So the warning only appears once another field is modified.

A scripted validation of the fields would be more foolproof since it involves related records.

[QuinTech]

Sorry, Jerry, i must have been drunk when i replied to your post with an answer to a completely different question. Sorry. Follow Ender & Queue's discussion, it looks pretty good.

J

[toolUser]

Thanks,

I'll play with it this week.

Jerry

[toolUser]

Ok, I see it now. Haven't quite got it implemented yet, the help file is not perfectly clear, but here is what it says:

+++++++++++++++++++++++++++++++++++++++++++++++

Identifying duplicate values using a self-join relationship

This procedure identifies "extra" instances of duplicated records. You specify the criteria that determine which is the primary record.

This procedure uses a self-join relationship and a calculation field referencing the relationship to determine which records are duplicates.

To find duplicate records except the first instance:

1.If you plan to delete the duplicate records that you find, make a backup copy of the file.

For more information, see Saving and copying files.

2.Identify a field that determines a unique entity in your file.

For example, in a Contacts database, the Last Name field is probably not a good choice, because you might have several people with the same last name. Social Security Number is a better choice. You can also create a calculation field (returning a text result) that combines data in several fields to make a unique identifier. An example formula is First Name & Last Name & Phone Number.

3.Define a self-join relationship.

Use your chosen identifying field as the match field in both tables in the relationship. For more information, see About self-joining relationships.

The primary record is the first matching record according to the sort order defined in the relationship.

4. Define two fields:

Counter, a text field with an auto-entered serial number (select Serial number and accept the default values for Next and Increment by).

Check Duplicates, a calculation field with a text result, with the formula:

If(Counter = table1::Counter, "Unique", "Duplicate")

5.Choose Records menu > Show All Records.

6.Click the new Counter field, choose Records menu > Replace Field Contents, and Replace with serial numbers. Again, accept the default values. Select Update serial number in Entry Options, and click Replace.

This will assign a serial number to all existing records in your database. Serial numbers will automatically be entered in new records.

7.Perform a find for Duplicate in the Check Duplicates field.

The first record in any series of duplicates now holds the value "Unique" in the Check Duplicates field, and all duplicate records within the same series are marked "Duplicate".