Subject Re: Appending records From Mervyn Bick <invalid@invalid.invalid> Date Tue, 26 Sep 2023 13:17:36 +0200 Newsgroups dbase.getting-started On 2023/09/26 08:12, Rouke wrote: > While in a form with: .... >     this.EXPENSES1 = new DATABASE(this) .... >     this.EXPENSES2 = new QUERY(this).... > I want to access expenses.dbf to generate the next order number, then leave expenses.dbf again and append a new record to the database and replace field biolinc_on with the newly generated order number. > > In Visualdb5.7 I did it this way: .....> > In dBASE 2019 I try: >     Use Expenses again in select() alias Expenditures order BioLinc_ON >     Select Expenditures >     Set filter to left(Expenditures->BioLinc_ON,3)='AQL' >     Go bottom >     Lc_OrderNo = left(BioLinc_ON,3)+str(val(substr(BioLinc_ON,4))+1,4,0,'0') >     this.expenses2.rowset.append() >     this.expenses2.rowset.fields["biolinc_on"] = Lc_OrderNo It is possible to mix XDML and OODML commands in a program to access tables but it can lead to major problems if one doesn't get it exactly right.  Although using OODML commands usually means a bit more typing than with XDML rather stick with just using OODML. To come back to the error your code generated, in OODML "this" is a reference to the object that is trying to execute the code.  If the function that is trying to execute your code is not a method of the form or an event handler assigned to one of the form's events then "this" refers to some object other than the form.  If that object doesn't have an EXPENSES2 property dBASE flags an error.  In this case "form" instead of "this" would be appropriate and should work. There are also two syntax errors. OODML programming is not difficult but it is different.  If you haven't already worked through the dBASE tutorial you should do so.  It takes a bit of time but it is time well spent. A rowset object does not have an append() method, it has a beginAppend() method. One can't assign a value direct to a field object, one needs to assign the value to the field object's value property.    form.expenses2.rowset.beginAppend()    //Adds a blank record to the rowset in memory    //This does NOT get written to the table until there is    //an implicit or explicit execution of the query's save() method.    form.expenses2.rowset.fields['biolink_on').value = Lc_OrderNo    //Similar commands for the remaining fields in the table    form.expenses2.save()    //Write the new record back to the table To use OODML instead of XDML to determine the next order number add a new query to your form.     this.order_number = new QUERY(this)     with (this.order_number)        left = 20.0        width = 7.0        height = 1.0        database = form.expenses1        sql = "select max(biolinc_on) as last_order from expenditures where biolinc_on like :biolinc"        params['biolinc_on'] = '%' //Rowset will have multiple records.        active = true     endwith When the query is requeried with, say, 'AOL%' as the parameter it will return a single record with a single field for a given category of order. Add a function to the form to get the new order number    function get_new_order_no(cOrder_type)      local cLast_Order_No,cNew_Order_No      form.order_number.params['biolinc_on'] = cOrder_type+'%'      form.order_number.requery()      cLast_Order_No = form.order_number.rowset.fields[1].value      //There is only one record with one field so we can use the      //field's ordinal number instead of the field's name      cNew_Order_No = left(cLast_Order_no,3)+str(val(substr(cLast_Order_no,4))+1,4,0,'0')       return cNew_order_No In your code, to fetch a new order number    cOrder_type = 'AQL'    Lc_OrderNo = class::get_new_order_no(cOrder_type)    form.expenses2.rowset.beginAppend()    form.expenses2.rowset.fields['biolink_on').value = Lc_OrderNo    //Similar commands for the remaining fields in the table    form.expenses2.save() WARNING: Whether you use XDML or OODML, this approach is only usable if your program has a single user.  If multiple users can access the database there is a possibility of duplicate order numbers.  The more users, the higher the probability of duplicates.  Until a user actually gets round to writing the new order number back to the expenditure table, which in practice could take minutes, there is a possibility that any number of other other users will make the same calculation for a new order number. If your program has multiple users I suggest you read the comments in SeqValue.cc in the dUFLP and then incorporate SQLseqValue.cc into you program.  Unfortunately this is going to mean major changes to your OODML code. With multiple users you will need to determine the next order number and save it so that no one else can use it.  Once you have an unused order number that no one else can duplicate you can take as long as you like before you write it back to your expenditure table.  The order numbers may well be out of sequence in the expenditure table but this is easily taken care of with an index. Mervyn.