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

[metadyne]

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...

[gmeredith17]

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

John - There isn't anything attached.

[metadyne]

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

OK! Here is the file...

[Tom Cone Jr]

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

John, my suspicions are confirmed. If you comment out the statement

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

[Tom Cone Jr]

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:

'--------------------  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.

[Mike Wilson]

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.

[csda1]

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

[metadyne]

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.

[Tom Cone Jr]

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

[csda1]

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

Hi John,

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.

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.

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.