Thanks Helpful Harry! What worked was to just using the script step
"Replace all contents" and fiddling around with that. I place that
script permanently in my db solution but have also taken precaution of
ticking the "Prohibit modification of value" for that field which
doesn't seem to affect the numbering of new records that happens
automatically now.

Re-sequencing all the records in the db takes a millisecond, it seems,
and so saved me a ton of work. I just made sure that my records were
sorted in a preferred order before doing this. They'll get out of any
sort of sort order from now on as records are added, meaning the
serial numbers for correlate from this point forward between
alphanumerica values, but at least the first 2464 records do! <g>

Thanks. D

----------

Serial numbers should never mean anything. They should not be used for
visible reference, only for identification between T.O.'s, calcs, etc. This
is called abstraction.


Hou je goed / keep well,

Ursus

On 12/19/2010 11:36 AM, Ursus wrote:
And in fact I'd go as far as to say it was bad form to sort them before
numbering. Your numbers will go in creation order from now on, and the
first batch of records should have been numbered that way, as well. You
will likely never again see the numbers in order the way you happened to
sort them before numbering. But you WILL likely see them again in their
"natural" order, which will be confusing to see when the numbers are so
far out of sequence when viewing in their natural, creation order.

There are 2 perspectives about that. Conventional 'theory' dictates
'they should never mean anything'. But in practice if an invoice table
has a primary key defined as a the usual auto enter serial (with a
alpha prefix), unique, non-null, immutable, non-user-modifiable sting
e.g. INV000001... why would one NOT use that as the invoice number that
appears on the layout and on printed invoices?

To not use it requires creating essentially a duplicate field, which is
technically redundant, and hence non-normal.

One further perspective is that the 'visible reference' aspect of the (?
Codman ) dictate, is met by the behind the scenes FileMaker generaterd
serialID.

As for the 'has no meaning' chestnut, well it uniquely identifies the
individual record, ergo it has meaning. It is used for relationships,
finds etc. It has meaning at some level. Maybe this aspect is just
perpetuated grammatical misapplication. I for one, would be interested
in hearing a take on this from anyone with an appropriate academic insight.

On 2010-12-20 13:28:35 -0800, 105 <cortical (AT) internode (DOT) on.net> said:

Why not? Because of one factor....users. And their cute little habits.

Like not treating invoice numbers as sacrosanct data (which they should
never be) used for relating entities in their databases. Users don't
know from key fields.

They want to do things like add an "A" to the end of the first invoice,
and refund or cancel the invoice and change the number to indicate
that, "yes we know we can click a cancelled field but this makes it
easy to see in a list." Or they'll duplicate a number, because "the
customer just wanted to add one item even though the original invoice
was closed, and pay for it on one check, so we just duplicated the
number, it's okay."

ANY data a user can see they will want to edit or delete or duplicate.
They will if you don't watch them every second. They get all squirrely
about gaps in their sequences and start asking you to re-use numbers
they delete. That way lies madness.

Thus any data a user can see should not be used for a key field. A key
field, in addition to being unique, non-mutable, and automatically
generated, should be HIDDEN from the user. Serial fields that are
visible to users might parallel key fields, but should never be the
same fields.

At least not in my databases. Sorry experience has taught me.
--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

On 2010-12-20 13:28:35 -0800, 105 <cortical (AT) internode (DOT) on.net> said:

Of course there is some meaning, even if it's 2103 comes after 2102 and
before 2014.

I personally encode a text string derived from the creation timestamp
into my key strings. Makes it easy to sort in creation order when
that's necessary.

What I always took the "chestnut" to mean is that it *has no meaning in
regard to the content of the record itself.* It doesn't matter if it's
a person record, an invoice, or a line item, or a join record, the key
field doesn't contain any info regarding what's on the record. It
might encode the table it's in, and in some distributed solutions where
synchronization is necessary, developers encode the NIC number or
machine ID into the keys.

And it definitely shouldn't have any meaning to the users.
--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

On 21/12/10 1:00 PM, Lynn Allen wrote:
They don't have to.


1. a key field, or something like an invoice number, is never editable,
so they can't add 'A'
2. they cant duplicate a number if they can't get at it; it is an auto
enter unique serial after all, and non editable (in browse mode...)



Gaps; status: active or cancelled.




As it has me.

On 21/12/10 1:06 PM, Lynn Allen wrote:

Yes I get the interpretation, but the reverse should hold true
concurrently; which it doesn't; normal data is dependent on the key
value. Don't want to go off on semantic tangents, but it is a something
of a fuzzy zone.




It doesn't matter if it's a
A numeric serial doesn't, but for the camp that still think natural
'keys' are acceptable, it does matter I would think. There are those
semantics again.


It might
It always will unless, as you say, it is invisible. So may as well work
with it.