Creating records with a unique alphabetical field value, but constrained within a previously assigned numeric field

[Tony Morosco]

Greetings!  

Sorry if my title is vague, I am not sure exactly how to describe this problem.

I have taken a look in the Forums, and haven't found this question before. (If I did, please point me there.)  And of course I've looked through the documentation in FM extensively.

I am a botanical garden curator, and this database focuses on two tables: accessions of plants (groups of plants received at one time, with multiple individuals), and on locations of plants (individual plants of one accession, but planted in different areas).  For each individual Location (of a single Accession) I would like to assign it a unique qualifier for that accession.  These qualifiers are based on letters of the alphabet, so-as not to confuse them with the numerical accession numbers.  

For instance, for accession 2002-0101:

For the first Location record, I would like the qualifier to be "A" 

For the second Location record, I'd like for it to be "B"

For the third, I'd like it to be "C"

and after the last Z value, I'd like for it to assign them AA, AB, AC, etc.  I don't imagine there will be any values higher than AZ.

So I am looking for a way to come up with a script and/or assigned calculation for qualifier to do this when creating a new location record in the locations table.  

I've already assigned these letters to existing records in our database with a script.  Roughly, the existing script runs by:

• Sorting the Locations table,
• If the accession number is a new, the qualifier is set to "A",  
• If the accession number already exists, then based on the previous records existing qualifier value, assign the next qualifier value for the record. 
• Capture the accession number and qualifier in a variable, for use in the next record
• Move on to the next record.

I know that the plant collections registrar can come up with the qualifier in their head (de-novo), but frankly they don't need that headache searching through the records and coming up with a new qualifier to create a new location. 

I know this is rather complicated, but let me know if I should come up with two tables to let you know what is going on.  I am sure it is something simple that I am overlooking.

And please let me know if I am being confusing, or have not laid out the problem in a logical way.

Thanks,

Tony

[comment]

It seems there are two separate issues here:

1. How to number related records sequentially;
2. How to represent the sequential numbers as an alphabetic code.

The second problem can be solved using a custom function:
http://www.briandunning.com/cf/399

The first problem is actually the more difficult one. It is very easy to number related records (or any other set of records) sequentially ad hoc, using either an unstored calculation of Get (RecordNumber) or a summary field counting the records in a running count. However these numbers are not assigned permanently to the records being numbered, and may change when you delete a record or just change the content and/or the order of the current found set.

Assigning permanent sequential numbers to related records is much more difficult and error-prone. In fact, it's not possible to maintain consecutive numbering if records are allowed to be deleted. And it is quite difficult to prevent duplicates being created in a multi-user scenario.

OTOH, it's very easy to use Filemaker's native auto-entered serial numbering to assign a unique and permanent identifier to each record at creation (with no regard to which parent is the record assigned), and I would urge you to take advantage of that - even if these numbers are not consecutive when viewed in different contexts .

 

 

 

 

Edited July 11, 2015 by comment