Alpha Video Training
Results 1 to 25 of 25

Thread: Auto-increment field rule fails when writing to a table in code

  1. #1
    Member
    Real Name
    Alex Muir
    Join Date
    Nov 2000
    Location
    Stocksfield UK
    Posts
    450

    Default Auto-increment field rule fails when writing to a table in code

    I have a table Custlab with a field Rateid set as auto-increment. In a script I have this:


    Tsr = table.open(“StdRates”)

    Tcl = table.open(“Custlab”)


    While .not. tsr.fetch_eof()

    Tcl.enter_begin()

    Tcl.Rate = tsr.Rate

    Tcl.Enter_end()



    Tsr.fethc_next()

    End while

    Even if you replace Tcl.enter_begin with Tcl.enter_begin(.T.) and the same with Tcl.enter_end, the auto-increment field rule is not followed. The workaround is to set the Rateid manually.

    Has anyone else hit this?

    Many thanks

    Alex

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default Re: Auto-increment field rule fails when writing to a table in code

    Nope. Seems to be working fine here. See the attached example.

    Perhaps there's something wrong with your script?

    In my own work, for example, I'd say

    tcl.enter_end(.t.)
    rather than
    Code:
    tcl.enter_end()
    -- tom

  3. #3
    Member
    Real Name
    Ernie Storms
    Join Date
    Mar 2008
    Location
    Wimberley, Texas
    Posts
    566

    Default Re: Auto-increment field rule fails when writing to a table in code

    Alex,
    Yes, I have. I usually use a numeric for auto-increment field and have the "Simple Default" (in field rules) set to 1 and set at beginning of entry. However, sometimes a value of "0" gets entered in the first record. I use a "work around" to get: recs = tbl.records_get(). Then when adding records I use:
    Code:
    if recs = 0 then
        tbl.auto_field = 1
        recs = 1
    end if
    I may be doing something wrong in the field rules, but it seems to work for me.
    Ernie

  4. #4
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,212

    Default Re: Auto-increment field rule fails when writing to a table in code

    I have had problems with autoincrement using a numeric field as you describe. I always make it a character field and have no problems. I do not believe a the value in a field given the auto incrment field rule will ever be used for mathmatical calculations, and therefore do not believe there is any reason not to make it a character field.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  5. #5
    Member
    Real Name
    John Castle
    Join Date
    Oct 2005
    Location
    Tyneside, England
    Posts
    178

    Default Re: Auto-increment field rule fails when writing to a table in code

    My auto-increment fields are also character as I have read that this is more reliable.

    Here is a sample of the code I use to write a set of standard labour rates on one table to each customer who uses the rates as opposed to special rates:

    parentform.Commit()
    tcl = table.open("custlab")
    tcl.delete_range("Stdlab = .T.")

    dim nRateid as N
    tcl.fetch_last()
    nRateid = tcl.Rateid

    tl = table.open("labchg")

    tc = table.open("customer")
    query.options = ""
    query.order = "recno()"
    query.filter = "Stdlab = .T."
    qc = tc.query_create()

    if qc.records_get() > 0 then
    while .not. tc.fetch_eof()
    tl.fetch_first()
    while .not. tl.fetch_eof()
    tcl.enter_begin(.T.)
    tcl.Rateid = nRateid
    tcl.Custid = tc.Custid
    tcl.Chgrate = tl.Chgrate
    tcl.Flatrate = tl.Flatrate
    tcl.Details = tl.Details
    tcl.enter_end(.T.)

    tl.fetch_next()
    nRateid = nRateid + 1
    end while

    tc.fetch_next()
    end while
    end if

    tc.query_detach()
    tc.close()
    tl.close()
    tcl.close()

    As you can see I tried the .T. option on table.enter_begin/end in the hope that it would follow the field rules for the auto-increment field, but it makes no difference. I also 2 other instances with different tables where the same is true.

  6. #6
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default Re: Auto-increment field rule fails when writing to a table in code

    John, it's very hard for these old eyes to decipher your code without indented formatting. The message board supports code tags that greatly enhance readibility.

    Having said that I suggest you submit sample data. Your script may be failing because of timing issues. Code that requires interaction with the hard drive will run in its own process, while the script keeps running. i.e. the script does not pause while the range is deleted, or the query is run... windows multi-tasks... if the logic of your script assumes that the script itself will pause after each statement resuming again only after all disk activity is finished then your logic is based on a false assumption.

  7. #7
    Member
    Real Name
    John Castle
    Join Date
    Oct 2005
    Location
    Tyneside, England
    Posts
    178

    Default Re: Auto-increment field rule fails when writing to a table in code

    Sorry the original text I pasted in was indented so I don't know why the internet lost it.

    I run larger scripts using functions but not something this small and simple.

    Are you saying that I should use functions even at this simple level? I understand that some code does not run in the order it is written but where do you stop using functions? You could use them every few lines otherwise...

  8. #8
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,644

    Default Re: Auto-increment field rule fails when writing to a table in code

    Quote Originally Posted by metadyne View Post
    Sorry the original text I pasted in was indented so I don't know why the internet lost it.
    As Tom noted, use the code tag option for highlighted text, or the 'internet' will lose the formatting....
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  9. #9
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default Re: Auto-increment field rule fails when writing to a table in code

    John, the code tags are accessible when using the advanced reply editor. Click the Go Advanced button to open the advanced message reply editor.

    I can't say that functions will solve your problem here. I think it's likely. Are you running the code over a network? Is there much latency in the disk activity? Do you get the same results running locally?

    Does my simple example also fail on your machine?

    What are you doing differently?

  10. #10
    Member
    Real Name
    Geoff Meredith
    Join Date
    Aug 2006
    Posts
    637

    Default Re: Auto-increment field rule fails when writing to a table in code

    You could use them every few lines otherwise...
    Why would that be a problem? This method of programming has been recomended by Ira and others many times and seems to be very sensible advice especially where there could be timing issues.

  11. #11
    VAR csda1's Avatar
    Real Name
    Ira J Perlow
    Join Date
    Apr 2000
    Location
    Boston, Massachusetts, USA
    Posts
    3,530

    Default Re: Auto-increment field rule fails when writing to a table in code

    Hi Geoff,

    Quote Originally Posted by gmeredith17 View Post
    Why would that be a problem? This method of programming has been recomended by Ira and others many times and seems to be very sensible advice especially where there could be timing issues.
    Despite the fact that functions are a good idea (see my CSDA tips page), and help keep things in sync, I doubt that's an issue here. Functions help keep things in order, by forcing the current thread (session) to wait for the function to complete. Some things can start new threads (e.g. opening a form/browse), but most code does not.

    What is important here is where is the code invoked from? E.g. from a button press of a form, a modeless dialog box, etc. What is the table that the form/browse (if any) is based on? Is the record being updated currently in change mode? The context and what has locks on records is important here.
    Regards,

    Ira J. Perlow
    Computer Systems Design


    CSDA A5 Products
    New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
    CSDA Barcode Functions

    CSDA Code Utility
    CSDA Screen Capture



  12. #12
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default Re: Auto-increment field rule fails when writing to a table in code

    Ira, I share your concern about things John hasn't mentioned. It's not clear exactly what aspect of his script is failing. I've assumed the records are being entered but that the auto-inc fields are empty in each new record. This assumption may be wrong altogether. He hasn't described the problem in detail. It's also possible that his method of "seeding" the first record in the table might be the issue. Can't say without sample data and the forms, so we can run it in context.

    Perhaps he should run the script in debug mode. Because that runs one statement at a time, perhaps that will give us more clues... It's not clear he attempted debug mode yet.

    --tom

  13. #13
    Member
    Real Name
    John Castle
    Join Date
    Oct 2005
    Location
    Tyneside, England
    Posts
    178

    Default Re: Auto-increment field rule fails when writing to a table in code

    OK here is the script again (the indents don't seem to work in the preview so I used dots instead)

    parentform.Commit()
    tcl = table.open("custlab")
    tcl.delete_range("Stdlab = .T.")

    dim nRateid as N
    tcl.fetch_last()
    nRateid = tcl.Rateid

    tl = table.open("labchg")

    tc = table.open("customer")
    query.options = ""
    query.order = "recno()"
    query.filter = "Stdlab = .T."
    qc = tc.query_create()

    if qc.records_get() > 0 then
    ...while .not. tc.fetch_eof()
    ......tl.fetch_first()
    ......while .not. tl.fetch_eof()
    .........tcl.enter_begin(.T.)
    .........tcl.Rateid = nRateid
    .........tcl.Custid = tc.Custid
    .........tcl.Chgrate = tl.Chgrate
    .........tcl.Flatrate = tl.Flatrate
    .........tcl.Details = tl.Details
    .........tcl.enter_end(.T.)

    .........tl.fetch_next()
    .........nRateid = nRateid + 1
    ......end while

    ...tc.fetch_next()
    ...end while
    end if

    tc.query_detach()
    tc.close()
    tl.close()
    tcl.close()

    The script is run from the save button in the form containing the standard labour rates. I have run the code in debug mode and can confirm that the auto-increment field in the table being written to remains at 0 each time a record is written.

  14. #14
    Member
    Real Name
    John Castle
    Join Date
    Oct 2005
    Location
    Tyneside, England
    Posts
    178

    Default Re: Auto-increment field rule fails when writing to a table in code

    Just to answer some of the questions Ira asks

    The script is run from the form containing the standard labour rates table only. The table is saved first before the script is run. The problem is that the auto-increment field in the table to which I am writing is not being filled unless I fill it myself using the variable nRateid in the script.

    If I enter a record manually in the destination table, the auto-increment field is filled correctly.

    The app is being run on a standalone PC where the testing is done and on a network live. The network uses Windows 2003 SBS and Windows XP workstations to SP2 with enough RAM on each. I do not know about latency.

  15. #15
    Member
    Real Name
    Geoff Meredith
    Join Date
    Aug 2006
    Posts
    637

    Default Re: Auto-increment field rule fails when writing to a table in code

    John,

    When you reply to a message if you press the go advanced button at the bottom of the page it will display a text editor with advanced options. If you copy and paste your code into it and then while it is still highlighted click on the code button of the advanced editor (#) it will wrap code tags around the script you have inserted. Then if you click on preview post you will see that your text formatting is still in place. Once you are happy with the wording and layout of your reply press the submit reply button. This will save you the time of having to add dots to keep the indents.

    Geoff

  16. #16
    Member
    Real Name
    John Castle
    Join Date
    Oct 2005
    Location
    Tyneside, England
    Posts
    178

    Default Re: Auto-increment field rule fails when writing to a table in code

    Here is the stripped down app.

    Go to the Labchg form and press save. This runs the code above but without setting the Rateid field which should auto-increment by itself according to the field rules.

    When the code has run, open the table Custlab. You will see some records with the Rateid field filled - these are special labour rates manually added for selected customers rather than the standard rates used by most.

    Below these rates you will see many more records without the Rateid field filled - these are the standard rates written to each customer by the code above.

    Hope that makes everything clear...

  17. #17
    Member
    Real Name
    Geoff Meredith
    Join Date
    Aug 2006
    Posts
    637

    Default Re: Auto-increment field rule fails when writing to a table in code

    John - There isn't anything attached.

  18. #18
    Member
    Real Name
    John Castle
    Join Date
    Oct 2005
    Location
    Tyneside, England
    Posts
    178

    Default Re: Auto-increment field rule fails when writing to a table in code

    OK! Here is the file...

  19. #19
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default Re: Auto-increment field rule fails when writing to a table in code

    John, my suspicions are confirmed. If you comment out the statement
    Code:
    tcl.delete_range("stdlab = .t.")
    the records are entered normally, and the auto-inc field rule supplies the appropriate field values to the Rateid field in the CustLab table.

    In addition the calc field rule for the StdLab field in the Custlab table contributes to the timing issue. Each time a new record is entered in Custlab the Lookup() function you chose to use opens a new instance of the customer table, searches for the current cust_id, and retrieves the StdLab field value. This is very slow. This requires a lot of disk activity. Since you are entering 9 new records for each customer, the Customer table gets opened and closed 9 times for each. Not a good design. Especially since the customers have already been filtered so that all of them have the same ".t." stdlab field value.

    So, my suggestions would be:

    a) delete the desired records in a separate process, then close the custlab table

    b) look for a faster less disk intensive way to stuff .t. values into the stdlab field of each new custlab record.

    -- tom

  20. #20
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default Re: Auto-increment field rule fails when writing to a table in code

    John, here's an example that overcomes the timing issue using a custom user defined function to delete records from the Custlab table:

    Code:
    '--------------------  snippet from Save Btn ----------------
    hourglass_cursor(.T.)
    Del_StdLabs()  '<========= add this line to call custom udf
    
    tcl = table.open("custlab")
    'tcl.delete_range("Stdlab = .T.")   '<======= comment this line out
    ....
    ....
    
    '--------------------------- end snippet --------------------
    
    
    
    Here's the udf:
    
    FUNCTION Del_StdLabs AS L ( )
    	tcl = table.open("custlab")
    	tcl.delete_range("Stdlab = .T.")
    	tcl.close()
    END FUNCTION
    This does not solve the slow disk intensive work your lookup() calc field rule imposes... you might consider abandoning the calc field rule, and replace it with equivalent code in your data entry form. If you do this, don't forget to modify the Save btn script to stuff Trues in each new custlab record.

    Recap: The auto-inc field rule was failing in John's script because of conflicts with the <tbl>.delete_range() method that was running while the script tried to enter new records to the same table.

  21. #21
    "Certified" Alphaholic Mike Wilson's Avatar
    Real Name
    mike wilson
    Join Date
    Apr 2005
    Location
    Grand Rapids, Michigan
    Posts
    4,212

    Default Re: Auto-increment field rule fails when writing to a table in code

    John,
    A few things.
    Save yourself endless time of constantly having to place the form in design mode, selecting button, select event, select onpush, etc to get to the code by placing the code in a script or as a function in the code tab and a calling command for the script or function on the form button. This way you can address the code just by pressing the code editor tab (bottom of screen) without having to constantly be changing the form into design mode. This will save you an inordinate amount of time.

    1. The first line should be TOPPARENT.COMMIT()

    2. You don't have dimmed pointers for the tables. While it will work without, I would add dim tcl as p, dim tl as p, and dim tc as p at the top.

    3. After the tcl.delete_range(), add tcl.close() and follow it with tcl=table.open("custlab") and it will work for you. It seems it doesn't like working the autoincrement with the table having just been made to delete a bunch of records.

    4. Since you are making a pointer to the queries (i.e.qcl = tcl.query_create()) you can use qcl.drop() instead of tcl.query_detach()

    Hope this helps.
    Mike W
    __________________________
    "I rebel in at least small things to express to the world that I have not completely surrendered"

  22. #22
    VAR csda1's Avatar
    Real Name
    Ira J Perlow
    Join Date
    Apr 2000
    Location
    Boston, Massachusetts, USA
    Posts
    3,530

    Default Re: Auto-increment field rule fails when writing to a table in code

    Hi Tom,

    Some things can start new threads (e.g. opening a form/browse), but most code does not.
    Well obviously, this is one of those cases that does! Good detective work!

    And of course, the easy solution is encapsulating the time sensitive code in a function!

    But I also wanted to comment on the query.order being "recno()" rather than blank. I doubt in that case (but I could be wrong and it might be version sensitive) the query will use LQO, even if there is an index with "Stdlab". If there isn't, an index should be added. Although I don't think I ever actually tried LQO on a logical index. That too may be an issue (but if it is, convert it to a number or character value index). But, getting LQO will allow the delete_range and the query to go much faster
    Regards,

    Ira J. Perlow
    Computer Systems Design


    CSDA A5 Products
    New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
    CSDA Barcode Functions

    CSDA Code Utility
    CSDA Screen Capture



  23. #23
    Member
    Real Name
    John Castle
    Join Date
    Oct 2005
    Location
    Tyneside, England
    Posts
    178

    Default Re: Auto-increment field rule fails when writing to a table in code

    I tried closing the table after deleting the records then re-opening it and as you say the auto-increment works.

    I have now made the delete records into a UDF in case the write records process starts before the delete records process ends.

    When should I use UDFs? Are there any rules on when the next process begins before the last has finished? I use UDFs for larger scripts where I am writing etc to multiple tables but have not for small scripts like the above.

    Concerning the lookup Stdlab field in custlab table, would changing this calculated field to one with a default value containing the same lookup reduce disc activity? Past experience shows that scripts ignore default values.

  24. #24
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default Re: Auto-increment field rule fails when writing to a table in code

    John, on behalf of the folks who've been helping you here, "you're welcome".

    1) the size (length) of your script is irrelevant. The key is whether the script initiates some external process that involves activities with peripheral devices, such as your hard drive, printer, thumb drive, etc. Whenever this occurs Windows will multi-task the "work" you've asked the external device to perform. Your script will keep running and won't wait for the process to complete unless you wrap that process in its own UDF.
    In my own work I will confess to being a bit sloppy. I tend to only wrap things like this in their own functions when I have a problem. This approach is also consistent with my personal snow-skiing style, which others have called the "ski until you crash" approach.

    2) Concerning the lookup Stdlab field in custlab table, would changing this calculated field to one with a default value containing the same lookup reduce disc activity?
    The default values field rule will not work for you. If the calc field rule is abandoned your script should simply assign the desired field value to the field, like it's doing for the other fields in each new record.

    You tell me, which of these do you think involve less disk activity:
    a) assign a field value (.t.) to a field in a new record that has already been started; or
    b) open a new instance of the Customer table; open all the indexes for that table; set the cust_id index primary; read through the index to search for the single record that matches the current customer id; retrieve the Stdlab field value from the related table record; assign it to a field in a new record that has already been started; and then close the index files; and finally close the new instance of the Customer table. [All of this work has to be done each time the lookup() function is called. It's why this function has a reputation for being slow.]

    The default values field rule will not be triggered when new records are entered into the table by your script. It's not an "engine level" rule.

    I prefer the approach to filling the stdlab field previously mentioned. You will need to modify your data entry form to supply the desired field value there, when data entry occurs through the form, instead of through your Save btn's script.

    -- tom

  25. #25
    VAR csda1's Avatar
    Real Name
    Ira J Perlow
    Join Date
    Apr 2000
    Location
    Boston, Massachusetts, USA
    Posts
    3,530

    Default Re: Auto-increment field rule fails when writing to a table in code

    Hi John,

    Quote Originally Posted by metadyne View Post
    When should I use UDFs?
    (see my CSDA tips page). In general, any time you want to isolate code from other code having side effects or want to use/reuse code multiple times, or maintain similar code (same but with different parameter values) in one place. Also, to force completion of a process.

    Quote Originally Posted by metadyne View Post
    Are there any rules on when the next process begins before the last has finished?
    No there is not. In general, if you open another independent Alpha 5 window (such as a layout like form or browse) or a modeless dialog box, there are running independently, called threads/sessions. There are lower level functions (apparently table.delete_range() for one) that opens another thread/session. There is no list, and in most cases not important. Bt if not sure, just encapsulate it in a function.


    I use UDFs for larger scripts where I am writing etc to multiple tables but have not for small scripts like the above.

    Quote Originally Posted by metadyne View Post
    Concerning the lookup Stdlab field in custlab table, would changing this calculated field to one with a default value containing the same lookup reduce disc activity? Past experience shows that scripts ignore default values.
    No. Calculated Field Rules, default values etc values will be seen on your system view while in change/enter of the record, but the final values are applied to the record on the disk only at save time (Xbasic or keyboard).

    Some of the field rules are applied at any level (table vs form), while some are applied only at the form level. Either way can be done via XBasic, so both form and table methods will apply the rules applicable to their level.
    Regards,

    Ira J. Perlow
    Computer Systems Design


    CSDA A5 Products
    New - Free CSDA DiagInfo - v1.39, 30 Apr 2013
    CSDA Barcode Functions

    CSDA Code Utility
    CSDA Screen Capture



Similar Threads

  1. Writing Lookup Field Rule to Runtime?
    By Keith Hubert in forum Alpha Five Version 9 - Desktop Applications
    Replies: 3
    Last Post: 12-13-2008, 06:37 PM
  2. skip field rule after auto increment
    By rwaldsmith in forum Alpha Five Version 8
    Replies: 2
    Last Post: 12-21-2007, 06:19 PM
  3. Auto increment field rule
    By kasiawatson in forum Alpha Five Version 8
    Replies: 16
    Last Post: 07-09-2007, 02:53 AM
  4. field rule for table, need simple calculation code
    By randbo in forum Application Server Version 8
    Replies: 2
    Last Post: 04-05-2007, 02:46 AM
  5. Auto Increment Field Rule
    By Sigrist in forum Alpha Five Version 6
    Replies: 9
    Last Post: 09-17-2004, 09:04 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
  •