serial numbers

[Lizzy]

Hi to all,

Wondering if anyone can help with an issue I've got with serial numbers. I'm new to databasing so it could be an easy thing... Maybe not!

What would the calculation steps be in creating a serial number that included letters as well as numbers, and those letters being the result of information checked in a radio set?

For eg... An Australian contact who attended a training event I would like their serial number to be A (for OZ)+ 001 (serial number)+ T (training event). With the result looking like this A001T

Thanks... :(

[aldipalo]

Hi Lizzy:

Welcome to the Forum. First, the general consensus is that you should have a straight forward serial number to use as your recordID. If you want to have this identification field in order to sort the data or to find all records with A****T for instance wouldn't it be easier to just find by Country = Australia AND Type = Training?

Now to answer your question.

Lets say you already have 3 fields

Country

Type

Serial#

Set up a calculated field "Identifier" with

Left(Country,1) & Serial# & Left(Type,1)

You should get:

Identifier = "A12345T"

HTH

Al

Edited April 11, 2007 by Guest

[Ender]

Hi Lizzy,

Your primary keys should not be dependent on user input. All it takes is bad data or a change in data to screw up the relationships it's used in.

If you like, you can use such a string as an alternative key, that's used to grab the primary key for the main relationship. But it's just as easy to use actual fields with readable names for this.

[Lizzy]

Ok!

I'll try both suggestions and see how it all works...

The database I'm working on atm, is for an organization who are very community orientated. My way of thinking is to have one core database, and then manage individual countries from the main. But alas, it seems that's not going to happen, for company reasons etc...

Until now, our serial numbers haven't been an issue, and as a contact is added their number is auto generated. They have recently created an online market place for their community, and the serial number is a core element to managing the accounts for this service.

Eventually, they will have the whole database online, but until then we have what we have. As their is a US, OZ & NZ Database, all with duplicate serial numbers, my thinking was to identify them with the addition of letters.

Any suggestions for other ways of handling the duplicates...?

It's very clear that they at this point, do not want to merge the three databases (it's actually 6, guys, girls, then countries...)

Thanks again!

[Lizzy]

Hi Al,

Just wanted to say thanks for the help with the serial numbering, that worked a treat. Amazes me how when I think something is going to be rather complex in FMP, it rarely actually is lol!

Cheers ;)