I ran a Google group search and could not make sense of previous
posts. The search results only gave me bits and pieces of the puzzle.
I would like to create an auto-enter alpha-serial in FileMaker, which
seems straight-forward. Second, this ID field would have a smart-sense
coding. This idea would involve lookup code, which would know the last
entered alpha-numeric serial and generate the next number. 

The alpha numeric helps distinguish the ID among a large number of
related tables.  This lookup code would also alleviate large data
block deletions and gaps in the serial number. I am looking for the
rationale behind the design strategy. This explanation will help me
understand Filemaker better.

Previous posts mentioned lookup tables, which could be outside
database. When should I use outside lookup table? How do file
references relate to cross platform usage? The concept remains foreign
to me since I have primarily developed in Access. In Access you would
use the dlookup  function; generate a counter +1; then concat the
alpha sequence. 

On Thu, 11 Nov 2004 00:52:54 -0800, Robert wrote:
>  I ran a Google group search and could not make sense of previous
>  posts. The search results only gave me bits and pieces of the puzzle.
>  I would like to create an auto-enter alpha-serial in FileMaker, which
>  seems straight-forward. Second, this ID field would have a smart-sense
>  coding. This idea would involve lookup code, which would know the last
>  entered alpha-numeric serial and generate the next number. 
> 
>  The alpha numeric helps distinguish the ID among a large number of
>  related tables.  This lookup code would also alleviate large data
>  block deletions and gaps in the serial number. I am looking for the
>  rationale behind the design strategy. This explanation will help me
>  understand Filemaker better.
> 
>  Previous posts mentioned lookup tables, which could be outside
>  database. When should I use outside lookup table? How do file
>  references relate to cross platform usage? The concept remains foreign
>  to me since I have primarily developed in Access. In Access you would
>  use the dlookup  function; generate a counter +1; then concat the
>  alpha sequence. 

please specify 'alpha-numeric' to me.

- range A-Z?
  A, B, ..., Z, AA, AB, ..., ZZ, AAA, ...?

- range A-Z, 0-9?
  0, 1, ..., 9, A, B, ..., Z, 0A or 1A, 0B or 1B, ...?

- range A-Z, a-z?

- range A-Z, including �, �, �?

You might do a conversion from numbers to letters easily by some
formulas.

Robert <robert-neville310@y@ho0.com> wrote:

> Previous posts mentioned lookup tables, which could be outside
> database. When should I use outside lookup table? How do file
> references relate to cross platform usage? The concept remains foreign
> to me since I have primarily developed in Access. In Access you would
> use the dlookup  function; generate a counter +1; then concat the
> alpha sequence.

Robert,
If you are looking for a unique internal reference number, you can do
the same in FileMaker, using an auto-entered serial number concatenated
with creation time and other info. The discussion about lookup tables is
important if the number is not for internal use only and gaps and
deletions could have some significance.   

-- 
Hans Rijnbout
Utrecht, Netherlands

Robert wrote:
> I ran a Google group search and could not make sense of previous
> posts. The search results only gave me bits and pieces of the puzzle.
> I would like to create an auto-enter alpha-serial in FileMaker, which
> seems straight-forward. Second, this ID field would have a smart-sense
> coding. This idea would involve lookup code, which would know the last
> entered alpha-numeric serial and generate the next number. 
> 
> The alpha numeric helps distinguish the ID among a large number of
> related tables.  This lookup code would also alleviate large data
> block deletions and gaps in the serial number. I am looking for the
> rationale behind the design strategy. This explanation will help me
> understand Filemaker better.
> 
> Previous posts mentioned lookup tables, which could be outside
> database. When should I use outside lookup table? How do file
> references relate to cross platform usage? The concept remains foreign
> to me since I have primarily developed in Access. In Access you would
> use the dlookup  function; generate a counter +1; then concat the
> alpha sequence. 
> 


It sounds like you really just want a simple auto enter serial with a 
letter prefix. LUT's are useful in some circumstances, but are probably 
not what you want.

Field opitions, Auto Enter serial   AFG00001, increment 1.

the alpha prefix can be anything , and it works on both text and number 
fields. Using an an alphanemueric serial one avoids sort issues that 
arise from just numbers, e.g.
1
10
2
3
4


Chris Brown
Neurosurgery
University of Adelaide

I had the following in mind for a unique identifier. First add an
alpha part like below. It would be two or three alphas. Second, add
leading zeros which would help me sort by the ID field. But this
scenario creates issues if your data set exceeds the leading zero
maximum. Any advice on the situation would help. Third, gaps and
deletions are significant since I import numerous records and often
delete large dataset at one go.  The dataset may contain duplicates
from incorrect formatting; it may arise from a less than perfect
import routine. For example, you create an import routine and validate
for most scenarios. But simple mis-spelling may create duplicates;
like a company names with and out Inc. Many similar scenarios exist
and true validation is a monumental task. 

CompID = CP00001
ContID = CT0001
Proj = PJ0001 

The database should have a flexible design with the ability of rolling
back. User verification may happen after the import or several months
afterward. So I need an approach to convert a find set to the same
unique identifier and compile all the data. This point relates to an
entirely different post; but it gives you an idea about my rational. I
have already created the model in Access, which creating update
queries in code. Again, I am in the planning stages and learning the
Filemaker world. Thanks for all the support.

On Thu, 11 Nov 2004 00:52:54 -0800, Robert
<robert-neville310@y@ho0.com> wrote:

>I ran a Google group search and could not make sense of previous
>posts. The search results only gave me bits and pieces of the puzzle.
>I would like to create an auto-enter alpha-serial in FileMaker, which
>seems straight-forward. Second, this ID field would have a smart-sense
>coding. This idea would involve lookup code, which would know the last
>entered alpha-numeric serial and generate the next number. 
>
>The alpha numeric helps distinguish the ID among a large number of
>related tables.  This lookup code would also alleviate large data
>block deletions and gaps in the serial number. I am looking for the
>rationale behind the design strategy. This explanation will help me
>understand Filemaker better.
>
>Previous posts mentioned lookup tables, which could be outside
>database. When should I use outside lookup table? How do file
>references relate to cross platform usage? The concept remains foreign
>to me since I have primarily developed in Access. In Access you would
>use the dlookup  function; generate a counter +1; then concat the
>alpha sequence. 

Robert wrote:
> I had the following in mind for a unique identifier. First add an
> alpha part like below. It would be two or three alphas. Second, add
> leading zeros which would help me sort by the ID field. But this
> scenario creates issues if your data set exceeds the leading zero
> maximum. Any advice on the situation would help. 

Allow enough leading zeroes for data projection 10yrs hence.



Third, gaps and
> deletions are significant since I import numerous records and often
> delete large dataset at one go.  The dataset may contain duplicates
> from incorrect formatting; it may arise from a less than perfect
> import routine. For example, you create an import routine and validate
> for most scenarios. But simple mis-spelling may create duplicates;
> like a company names with and out Inc. Many similar scenarios exist
> and true validation is a monumental task. 

Scrub your import data before importing. Create a temporary rel from the 
file to be imported TO the existing master file. Create a table layout 
and add the reletaed ID. Sort and find the ommissions/errors, adjust 
accordingly. If you are then importing using ID=ID (s), there is no good 
reason for stuff ups. Another illustration of why (all) data records 
should be ID based.



> 
> CompID = CP00001
> ContID = CT0001
> Proj = PJ0001 
> 
> The database should have a flexible design with the ability of rolling
> back. User verification may happen after the import or several months
> afterward. So I need an approach to convert a find set to the same
> unique identifier and compile all the data. This point relates to an
> entirely different post; but it gives you an idea about my rational. 
> 
> 

Do not follow your rationale re waiting months to verify. Errors 
compound, as I recall a Blood Bank director saying. 'there is no such 
thing as a single error'.