New call-to-action
Results 1 to 17 of 17

Thread: Record Number as a Field

  1. #1
    Member
    Real Name
    Joe Nuccio
    Join Date
    Jun 2006
    Posts
    27

    Default Record Number as a Field

    I have a database with over 16,000 records. I need to be able to include the record number as a field in this database. I will then export the database to a CSV file where record numbers are important to me. It will be necessary to
    keep the record number field current as records are added.

    I know you can show record number in a report. But do not know if it is possible
    to include record number as a field in the db.

    Any suggestions would be appreciated.

  2. #2
    Member
    Real Name
    Duncan Greenshields
    Join Date
    May 2000
    Location
    Toronto, ON
    Posts
    216

    Default

    Joe;
    What you may want to do is create a field on your db (call it "record" for example), and have a field rule that automatically inputs the record number into the "record" field. Would this work for you?

    Duncan

  3. #3
    Member
    Real Name
    Joe Nuccio
    Join Date
    Jun 2006
    Posts
    27

    Default Yes

    Hi Duncan,

    I tried that very thing before I saw your posted reply. I also did a onetime global update to the new field using the expression for rec no. I now have what I needed.

    This seems to do the trick for me. Thank you very much for your sugggestion which does the trick.

    Joe

  4. #4
    VAR davej's Avatar
    Real Name
    Dave Jampole
    Join Date
    Apr 2000
    Location
    Haughton, LA ( just east of Shreveport, Louisiana)
    Posts
    2,524

    Default

    Joe,

    If you use the system-assigned record number, as opposed to a user created field populated by an auto increment number, you are asked for trouble since, as records are deleted, the system-assigned record number will continually change - record number 17 today might not be record number 17 tomorrow if you deleted any record numbered less than 17.

    Duncan's advice is spot on.

    Dave
    Dave Jampole
    www.customalpha.com

    Women and cats will do whatever they want. The sooner men and dogs realize that, the happier they will be.


  5. #5
    Member
    Real Name
    Joe Nuccio
    Join Date
    Jun 2006
    Posts
    27

    Default

    Dave,

    Do you mean I should use the following field rule for the record number field:

    value......... calculated

    expression.. RECNO()

    I had tried:

    value........ user entered

    Increment. records

    Please advise.

  6. #6
    VAR davej's Avatar
    Real Name
    Dave Jampole
    Join Date
    Apr 2000
    Location
    Haughton, LA ( just east of Shreveport, Louisiana)
    Posts
    2,524

    Default

    Joe,

    Set the field as a Character field, length 5 (or whatever length works for you). In Field rules, leave it at the default of User-entered; tab down to Increment and set that to Records and the next line (Auto-advance) to Yes.

    When you enter the first record, you'll see that its value is 00001, the next will be 00002, etc. If you want to add a letter (something like P0001, do that on the first record and the next will be - - - P0002.


    Dave
    Dave Jampole
    www.customalpha.com

    Women and cats will do whatever they want. The sooner men and dogs realize that, the happier they will be.


  7. #7
    Member
    Real Name
    Joe Nuccio
    Join Date
    Jun 2006
    Posts
    27

    Default

    Dave,

    I'm confused now. I thought that was exactly wht I had originally tried...,
    see my last post:

    "I had tried:
    value........... user entered
    Increment.... record"

    Seems that both methods I've tried in the last post accomplish the same thing.

    Where am I wrong? Which is the best way to go?

    We do not normally pack and delete records. This is an inventory dbf, so somtimes when the stock is depleted, we delete all the info in a record...creating a blank record which is then available for new items.

  8. #8
    VAR davej's Avatar
    Real Name
    Dave Jampole
    Join Date
    Apr 2000
    Location
    Haughton, LA ( just east of Shreveport, Louisiana)
    Posts
    2,524

    Default

    Joe,

    Quote Originally Posted by joe
    Dave,

    I'm confused now. I thought that was exactly wht I had originally tried...,
    see my last post:

    "I had tried:
    value........... user entered
    Increment.... record"

    Seems that both methods I've tried in the last post accomplish the same thing.

    Where am I wrong? Which is the best way to go?

    We do not normally pack and delete records. This is an inventory dbf, so somtimes when the stock is depleted, we delete all the info in a record...creating a blank record which is then available for new items.
    Sorry, I misread (or totally missed this message). We're saying the same thing. The User-entered, Increment record is the way to go.

    If I were running an inventory database, I would add new inventory items as new records at the end, even though the table contained items with zero balances. If when you deplete an item from inventory, and that item is will not be ordered any more, deleting the zero balance record could makes sense, EXCEPT if you ever need to go back to that item for any purpose.

    My personal opinion is that leaving the zero balance record is a better way to go, in that it helps reduce the possibility of someone fiddling with the inventory (and the associated accounting).

    Dave
    Dave Jampole
    www.customalpha.com

    Women and cats will do whatever they want. The sooner men and dogs realize that, the happier they will be.


  9. #9
    Member
    Real Name
    Joe Nuccio
    Join Date
    Jun 2006
    Posts
    27

    Default

    Thanks Dave,

    We seem to all be in agreement now.

  10. #10
    Member
    Real Name
    George Czar
    Join Date
    Oct 2005
    Location
    Georgia (USA)
    Posts
    136

    Default

    Quote Originally Posted by joe
    Hi Duncan,

    I tried that very thing before I saw your posted reply. I also did a onetime global update to the new field using the expression for rec no. I now have what I needed.

    This seems to do the trick for me. Thank you very much for your sugggestion which does the trick.

    Joe
    Hello I want to duplicate this almost exact thing -
    But when I add the field (GEinv) numeric, 6 and apply the field rule to auto-increment, simple expression the field is created but no data exists.

    I attempt to recalculate and go back into the list of records but it is still blank.

    Any suggestions?

    GEORGE

  11. #11
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default

    Quote Originally Posted by czar_g
    Hello I want to duplicate this almost exact thing -
    But when I add the field (GEinv) numeric, 6 and apply the field rule to auto-increment, simple expression the field is created but no data exists.

    I attempt to recalculate and go back into the list of records but it is still blank.

    Any suggestions?

    GEORGE
    Auto-increment happens on record enter, not on edit. Make the field user entered again, populate each record in the order you want for currently existing records, then change it back to auto-increment.

  12. #12
    VAR davej's Avatar
    Real Name
    Dave Jampole
    Join Date
    Apr 2000
    Location
    Haughton, LA ( just east of Shreveport, Louisiana)
    Posts
    2,524

    Default

    George,

    Quote Originally Posted by czar_g
    Hello I want to duplicate this almost exact thing -
    But when I add the field (GEinv) numeric, 6 and apply the field rule to auto-increment, simple expression the field is created but no data exists.

    I attempt to recalculate and go back into the list of records but it is still blank.

    Any suggestions?
    There is no Auto-increment option in A4V7 or 8, so I'm not sure what you are referencing. V7 field rules allow User-entered or Calculated; V8 add the third option of Summary.

    FWIW: Auto-increment is an Alpha Five field rule option, so I wonder if you are possibly talking about A5?

    Dave
    Dave Jampole
    www.customalpha.com

    Women and cats will do whatever they want. The sooner men and dogs realize that, the happier they will be.


  13. #13
    VAR davej's Avatar
    Real Name
    Dave Jampole
    Join Date
    Apr 2000
    Location
    Haughton, LA ( just east of Shreveport, Louisiana)
    Posts
    2,524

    Default

    Ignore my previous post. I had my mind (such as it is) somewhere else completely.
    Dave Jampole
    www.customalpha.com

    Women and cats will do whatever they want. The sooner men and dogs realize that, the happier they will be.


  14. #14
    Member
    Real Name
    George Czar
    Join Date
    Oct 2005
    Location
    Georgia (USA)
    Posts
    136

    Default

    Quote Originally Posted by Stan Mathews
    Auto-increment happens on record enter, not on edit. Make the field user entered again, populate each record in the order you want for currently existing records, then change it back to auto-increment.
    well not sure what transpired Stan = but after I had appended the records in a backup file I had that I use to test with - each had its own GEINV number just like I had wanted.

    perhaps it was since the table was empty and I appended the records for another table.

    go figure

    george

  15. #15
    Member
    Real Name
    George Czar
    Join Date
    Oct 2005
    Location
    Georgia (USA)
    Posts
    136

    Default

    Quote Originally Posted by davej
    George,



    There is no Auto-increment option in A4V7 or 8, so I'm not sure what you are referencing. V7 field rules allow User-entered or Calculated; V8 add the third option of Summary.

    FWIW: Auto-increment is an Alpha Five field rule option, so I wonder if you are possibly talking about A5?

    Dave
    yes dave - it is alpha 5 -
    i searched for adding records and didn't notice I was in another forum - sorry

    thanx
    george

  16. #16
    VAR davej's Avatar
    Real Name
    Dave Jampole
    Join Date
    Apr 2000
    Location
    Haughton, LA ( just east of Shreveport, Louisiana)
    Posts
    2,524

    Default

    George,

    Thank you, thank you. I thought I was going nuts.

    In A5, there is a slight 'issue' with field rules that can drive you crazy if you are not careful. You cannot do an update if the field is anything other than User entered.

    What I usually do when I'm adding a calculated field, is to set the type as Calculated, construct the calculation and then save the change. Now, change the field rule back to User entered, create and run the update operation for the existing records. Finally, go back and change the field to Calculated and that will take care of furute records.

    Dave
    Dave Jampole
    www.customalpha.com

    Women and cats will do whatever they want. The sooner men and dogs realize that, the happier they will be.


  17. #17
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default

    Quote Originally Posted by czar_g
    well not sure what transpired Stan = but after I had appended the records in a backup file I had that I use to test with - each had its own GEINV number just like I had wanted.

    perhaps it was since the table was empty and I appended the records for another table.

    go figure

    george
    An appended record counts as a record entered, thus the auto-increment rule works.

Similar Threads

  1. Record number puzzle
    By brett s in forum Alpha Five Version 5
    Replies: 3
    Last Post: 06-16-2004, 05:31 PM
  2. Record Number
    By tarlachmoorhouse in forum Alpha Five Version 5
    Replies: 2
    Last Post: 01-12-2004, 07:03 AM
  3. Record number changes on me.
    By Stephen Andrews in forum Alpha Five Version 5
    Replies: 3
    Last Post: 01-17-2003, 09:51 AM
  4. Record Number Selector
    By Jamin Dunivan in forum Alpha Five Version 5
    Replies: 0
    Last Post: 10-02-2002, 07:35 AM
  5. record number index
    By James Orrison in forum Alpha Five Version 4
    Replies: 6
    Last Post: 11-15-2001, 09:12 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •