Subject Re: Appending records From Rouke <moc.liamg@rekkab.ekour> Date Tue, 03 Oct 2023 02:09:37 -0400 Newsgroups dbase.getting-started Thanks Mervyn, I'm working my way through this. RE: > 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. Yes that would have been my next question. Let me ponder that too. Mervyn Bick Wrote: > 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. > > >