Using REPLACE command to affect multiple fields with FOR

[dantheinfoman]

Hi All,

I thought this would be a no-brainer, but it isn't working no matter how I try it. I want to set APPROVBILL to .T., to put the date and time and whoever ran the macro to approve all the items in the current date range for that employee.

When I click the button and run this code:

REPLACE tk_JOB.APPROVBILL WITH .t.,;
		tk_job.approvdat WITH DATE(),;
		tk_job.approvtim WITH LEFT(TIME(),2)+SUBSTR(TIME(),4,2),;
		tk_job.approvby WITH m.gcUserID;
	for tk_JOB.job = ThisForm.Pageframe1.Page3.CBOJob.Value AND ;
	BETWEEN(tk_JOB.WORK_DATE,m.Begins,m.Ends) IN tk_JOB

. . . it merely marks all those records in date range for that job that APPROVBILL = .T., however it doesn't put the date, time or the userID in the other 3 fields for those records.


I'm sure it's a syntax thing. I tried putting the scope of ALL in it like this:

REPLACE tk_JOB.APPROVBILL WITH .t.,;
		tk_job.approvdat WITH DATE(),;
		tk_job.approvtim WITH LEFT(TIME(),2)+SUBSTR(TIME(),4,2),;
		tk_job.approvby WITH m.gcUserID;
                [b]ALL[/b] ;
	for tk_JOB.job = ThisForm.Pageframe1.Page3.CBOJob.Value AND ;
	BETWEEN(tk_JOB.WORK_DATE,m.Begins,m.Ends) IN tk_JOB

. . . but that didn't help either.

Can anybody tell me if there's a way to do this. Else, I will just run a SCAN FOR in order to make this work.

Thanks

Dan

 

[dantheinfoman]

Nevermind, I'm an idiot.

I've been looking at the wrong button. It worked all along and I've been clicking the "approve all for employee" button instead of the Job one.

Please disregard.

Dan

 

[Olaf Doschke]

Does happen to all of us. I remember scratching my head because any code change I made in some class didn't run and even break points didn't work. It turned out the project had a stray class in some other lib with same name.

In regard of your idea to apply ALL: This is valid, but unneccessary, the scopes ALL, NEXT x, RECORD y or REST can be combined with FOR and WHILE conditions, but if you add a FOR condition this is applied to ALL records anyway, not only to the current.

Simple test:

CREATE CURSOR crsTest (id I)
APPEND BLANK
APPEND BLANK
APPEND BLANK
REPLACE id WITH RECNO() FOR RECNO()>1 IN crsTest

It does not only change the current record (which is the third record) to id=3, it also sets id=2 in recno() 2.

Despite of this, it has already paid for me to be verbose in code, I didn't change all older code, but in newer code even add a NEXT 1 scope to my REPLACES aside of using the IN clause.
This way I never have a simple [tt]REPLACE field WITH value[/tt] anywhere in code, my replaces always also state in which table/workarea they work and in which scope. It makes code longer, but that's not making it take longer - neglectible if at all. Some combinations are even compiled to same object code. The IN is much more important to me, because you also can't UPDATE without specifying the target table name. It's integral part of that command for a good reason.

So in the end not at ALL a bad idea to try this.

Bye, Olaf.

 

[dantheinfoman]

Thanks, Olaf.

As always, I've learned something valuable from your reply! Thanks for taking the time, sir! I will start using the IN clause at the end of my replaces.

Dan

 

[Olaf Doschke]

Psst, even with IN clause, you can still update more than the one table due to active RELATIONS also moving record pointers in other workareas alongside. REPLACE is really much more powerful than the usual change of the value of a single field in the current row of the current workarea.

For example MS SQL Servers T-SQL can join further tables in an UPDATE-SQL, but only to use them as source of data, targets you specify in the list of SETs of an UPDATE-SQL command only can be columns of the main table you UPDATE. And that is even true for updatable views taking in data from multiple tables, though a View seems like a table, you can't update view columns originating from different source tables.

But while that sounds powerful, I actually haven't used this possibility RELATIONS offer for writing. The instruments of triggers and transactions account for that you can seemingly update more than one table in one go. In the end every byte is still written after the other anyway, RELATIONS just help to read or write things while at a record anyway.

Bye, Olaf.