best way to update table 1

[Judi201]

Hi!

I am still learning how to use the power of cursors and want to move forward in understanding. (VFP9)

I have a grid with RecordSource an updateable cursor. Checkboxes select the rows to update in the table underlying the cursor. The cursor shows info from several tables, but I only need to update one. In fact I need to update the record that the row represents and then add a record with additional info. I am hoping one of you experts will suggest to me a neat way of doing this.

Thanks for any help.
Judi

 

[Judi201]

H1 Mike!

You told me way back when I was first trying to move from VFP DOS/2.6 to the VFP world to 'remember what I knew and try for new ways. That is what I am doing but I am still doubtful of using cursors. Displaying info that way - great but not yet manipulating them as I did tables.

Here is what I am trying to do. The new data is in the cursor. The update to the table and add a new record iwth additional data.

In my example for now I have a grid with RecordSource of cursor that contains 6 invoices unpaid. I select 3 to pay. So in the SAVE I want to:

***
SELECT PayTick
*  get only paid 
SELECT * ;
	FROM PayTick ;
	WHERE llPaid = .T. ;
	INTO CURSOR csrPaid NOFILTER 
** got just what we want - only paid invoices	
** so update ticket file - mark all paid and add credit record (payment)
SELECT csrPAid
DO WHILE !EOF()
	UPDATE Casting!ticket ;
		SET invctype = "PD"  ;
		WHERE  ticket.invcnumb = lcInvNo && maybe several of these
	APPEND BLANK IN ticket
	UPDATE Casting!Ticket ;
		SET Ticket.invcnumb = lcInvNo, ;   && caught above from grid column
			 Ticket.invctype = "PD", ;  && values ... grid.col.text1.value
			 Ticket.invcdesc = lcDesc , ;
			 Ticket.invccred = lnPayAmt, ;  
			 Ticket.custno = lnCustno, ;
			 Ticket.invcdate = ldPayDate ;
		WHERE ticket.invcnumb = lcInvNo
	SKIP IN csrPaid
ENDDO

I fear my attempt is laughable but it is what I am trying to do.

Give me one of you neat solutions.

Thanks for your suggestions - now and in the past.

Judi

 

[Judi201]

BIG OOOPS!
I posted a section I had started changing and had not finished. Please ignore. I can do better.

Judi

 

[Mike Lewis]

Judi,

I fear my attempt is laughable

Not at all. Your code is in fact very clear. It might not be the best way to go about it, but at least the code is readable and well-structured.

I know this has got nothing to do with your original question (and also that the code you posted is not finished code), but perhaps I can make a couple of suggestions for your future reference:

1. You don't need to SELECT a work area in order to do a SQL SELECT on it. In fact, you don't even need to open the table in question. So the SELECT PayTick (your first line of code) is redundant.

2. DO WHILE !EOF() ... SKIP ... ENDDO. That was a very common construct in the very early days, but it was replaced by SCAN ... ENDSCAN sometime around the Jummy Carter era <g>. SCAN ... ENDSCAN is not only less coding, but usually runs faster.

3. Rather than doing an APPEND BLANK followed by an UPDATE, you could just do an INSERT INTO.

These are all minor points, and won't make any difference to the outcome, but I think they are worth keeping in mind.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Judi201]

Mike,

I was hoping you were around. You tend to make these explanatory comments that are so helpful!

However, sadly, I am aware of the concepts you outlined. (Maybe not the Carter admin bit and the embarrassing thing is that in all other places I am using SCAN and I know that you don't have to SELECT area.

What happens is that when I can't make it work, I begin to gradually revert to 'Carter Days' ways of doing things. What I end up with is a hodgepodge of the 'eeeek' era. I must just hold on to what I know should work and make it work!

I have not used INSERT ever and for some reason just don't quite understand how to set it up.

As myearwood started this thread: What is so difficult? I don't know - MENTAL BLOCK?? Maybe.

What I need is to use a cursor to update one table. The problem is that I need to update one field in the record reflected in the grid and then create a new record to contain other info from the grid row. Would it be too much to ask you to suggest coding to do that?

It might not be the best way to go about it,

Do you mean the whole approach or just my use of DO WHILE..etc.

Thanks so much for any suggestion.

Judi

 

[Judi201]

Mike,

Super! Thanks so much. That is exactly what I needed to see. I can take that and go now. I don't know why it takes something like that for me to "get it'. Examples I look at usually have so many other things going on that I am not sure of the bare basics.

*Judi? Is llPaid a field?

Yes, it is as you said. The llpaid is caught from the click of the checkbox. The cursor field is lPaid simply created to hold the click value.

Do you mean there can be several records to mark as paid?

Yes, normally from 1 to 3 or 4.

I haven't tried this yet but I know I can take it and make it work. Thanks so very much for your time.

Judi

 

[Mike Lewis]

Judi,

I am aware of the concepts you outlined. (Maybe not the Carter admin bit ...

I was originally going to write " ... some time around the time of the Wilson government", meaning Harold of that ilk. Then I realised that any Americans in the forum would think I meant Woodrow. Even I haven't been around that long <g>.

Anyway, I see Mike Y beat to the reply, and explained it much better than I would have done.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Judi201]

Mike Y

It is a field, but I am confused.

There's no point to using m.llPaid, because it will always be .T. and that means you'd get every PayTick record.

The llPaid is only TRUE if the checkbox for that row WAS clicked. This worked to give me only selected rows data. However, I see the point that the field would probably be the best reference.

Thanks again.

Mike L: I got the point anyway (a looong time ago)

Judi

 

[Judi201]

Mike,
I am afraid you are losing me. I don't use m. ever so I guess I am not strictly using the standard. I only use first char for scope and second for type. And yes, you are right .T. for every row CHECKED which is what I want. I will try to improve.

My code works. Thanks for everything.

Judi

I just remembered that many, many years ago when I scattered and gathered I did use m., just not now.

 

[Judi201]

Mike,

OK, now I understand what you are telling me. Although I have created confusion by not answering your questions correctly and am actually doing better than it sounds like!

So let's just forget any answers above and let me say that I would NEVER (in my right mind) name a field with the ll or lc, etc.

If that had not been a "made up" field just to hold the check mark's value, I would not have stumbled there.

I have played around with the checkbox and the cursor behind it and I see now that you are right (of course ) and it is not the value of the checkbox variable but the FIELD that I need to consider.

If what I have said just now is correct, then I have got it.

Thanks for clarifying. Actually, my naming practices are not the culprit - rather understanding the issues I think.

Judi

 

[TamarGranor]

One more comment on all this. When you're checking a logical field or variable, you don't need to add "= .t.". The line in the query could be just:

WHERE lPaid

I also would never check whether something = .F.; instead I check: NOT whatever.

Tamar