Alpha5 1.0.2 Indexe

[Robert Femia]

I have a numeric field that is created by "recno()" function. When I use this field as an index, it is sorted numerically but any revised or new fields are listed at the end in the order they were revised or added, out of numerical order.

I have a similar field that is "user entered" and this sorts in numerical order--1 thru the highest number.

Any answers?

[Chris Ohlsen]

RE: Alpha5 1.0.2 Indexe

I've tried this out and here's what I get. I had two fields, one named "BLURB" and the other named "THEREC". The latter I had as a numeric field, and set it (via Field Rules -> Type) as "Calculated", the expression being "RECNO()". Then I created an index on the field, named "BY_THEREC". I added and altered records, both with the index set to "BY_THEREC" and with it set to something else (eg "BY_BLURB", or the default - "Record-number order"). Nothing untoward happened.

I suspect, though, that if I delete a record and "pack" the database I'll get trouble... yes, I do. If I delete record 3 (of 5), and pack the database, I'm left with four records, with THEREC values of 1,2,4,5. If I then add a record (the 5th) it gets assigned a THEREC value of 5. So now I have five records, with THEREC values of 1,2,4,5,5.

And if I alter the record whose THEREC value is 4, then as it's (now, since the "pack"ing) the 3rd record, its THEREC value (being re-calculated) automatically changes from 4 to 3. So now I have records with THEREC values of 1,2,3,5,5.

However, if I choose "Re-evaluate Rules" (from the Field Rules panel), it seems that the jumble gets sorted out: the THEREC values are all re-calculated (and are changed from 1,2,3,5,5 to 1,2,3,4,5).

Is this the sort of behaviour you're getting? Have I set up my database properly to mimic yours? Is there another way of indexing your database that'll still do what you want (eg an auto-increment field)?

Chris Ohlsen (London, UK)

[Thomas Henkel]

RE: Alpha5 1.0.2 Indexe

Using recno() as a base for an index is NOT very good practice for just the reason you are having. Recno is automatically assigned, but if you delete and pack your database, then your index goes to h*** in a handbasket. An alternative would be to assign a starting value to the first record in the database, then make the field "auto increment". This way, the key field is NOT tied to the record number, but is generated automatically. If you start with "1" then auto increment, for the most part, your key fields will mimick the recno(), but in the case of deleted and packed records, your key will just take the next value and not mimick an already used recno().

Tom

[Robert Femia]

RE: Alpha5 1.0.2 Indexe

Chris:
What I was trying to accomplish was to avoid manually numbering an existing file with over 1,000 entries.

I ended up manually numbering in an auto increment field because time was a factor.

Thanks for your reply anyway.

Bob

[Robert Femia]

RE: Alpha5 1.0.2 Indexe

Tom:

What I was trying to accomplish was to avoid manually numbering an existing file with over 1,000 entries.

I ended up manually numbering in an auto increment field anyway because time was a factor and, as you mentioned, RECON()was not the way to go.

Thanks for your reply.

[Chris Ohlsen]

RE: Alpha5 1.0.2 Indexe

Would any of these approaches have worked?

(i) Add a calculated field (the calculation being recno()) to the pre-existing database (which I assume contains records already). Run "Re-evaluate field rules" to get this new field filled in for all the records. Then - via the Field Rules card - change the field from "Calculated" to an 'ordinary' one... *if* this is allowed, and now set the field as "auto-increment" (so that any further records added will be auto-incremented).

(ii) Add a new field, and use the "Update" facility to update all records, the expression on the Update Card being "recno()." Then set the field (via Field Rules) as auto-increment.

(iii) After adding the ID field to the database structure, run a script resembling the following (in "pseudo-code"):

set index to record-number order
fetch first record (as determined by the index)
counter = 1
while not end-of-file
change begin
ID field = counter
change end
counter = counter + 1
fetch next record
end while

Then set the field (via Field Rules) as auto-increment.

I have used technique (iii) with success, but can't recall if I've tried either of the first two.

Chris Ohlsen

[RJGowan]

RE: Alpha5 1.0.2 Indexe

Robert:
For future reference, the second (ii) option in Chris' message (id 1291) above, which included a one-time "update" of the database to replace the new field with value recno(), followed by setting field rule for new field to auto-increment, would surely have accomplished very easily what you required. I would establish index on new field after above update procedure (though I'm not sure it makes any difference whether before or after update). As Tom points out, it is not good practice to include recno() as part of an expression that is to be used on a recurring basis; however, for the one-time use described in Chris' option ii), it is certainly useful and time-saving.

another Robert