Multiple Unique Serial Numbers

[dnice240]

Hello, I am making a database that tracks companies started by alumni. I am trying to make an area for "related contacts" where the user can find other alumni that have the same values for certain field (ie. graduation year). I figured I could do this with a portal and giving each value that I want to use in the search criteria a unique serial number. My question is how do I create a field that will autogenerate a serial only if the value of a different field is unique?

For example, if I wanted to find companies in the same city. Right now each company has a unique serial number that is linked to the whole company record. I would need some type of function for the company city that would say -

If Company City is Unique

Create a new serial number in Company City ID

Else

Give Company City ID the appropriate serial number

How do I do this, or what do you suggest? Thanks in advance for the help. It is very much appreciated.

[bcooney]

I think that you're on the right track. How about a table just for Cities [__kP_CityID and CityName].

Then, in the alumni and company tables you have _kF_CityID.

Assign each of the City fields to use a value list made from the Cities table [__kP_CityID and CityName].

Now you can have a portal on the Alumni form that relates to other alumni that have the same CityID (it's a self-join, Alumni::Alumni by CityID). Also you can have a portal from Alumni to Companies, also by CityID.

The admin, I suppose, would maintain the Cities table, because that is the only way to guarantee that it is clean. For example, a user might enter NY and New York. Well, they're unique to a computer, but we know better.

If anyone has a better approach, please chime in.