importing data from a dissimilar table

[steve4king]

I wrote a simple script to retroactively correct a problem with credits being recorded incorrectly.

close data all
dele file Temp.dbf
CRFDate = CTOD(INPUTBOX("Enter a date","How far back to correct credits?",DTOC(DATE())))
use cash_tra
copy to temp for how_paid9 = "7" and Date>=(CRFDate)
repl all how_paid9 with "1" for how_paid9 = "7" and Date>=(CRFDate)
close data all
Use temp
repl all how_paid with "1"
repl all inv_ref with (Invoice)+"CreditFix"
repl all Totl with (Change)
repl all Amount with (Change)
repl all invoice with "CREDIT"
repl all Change with 0.00
repl all how_paid9 with " "
close data all
use cash_tra
appe from temp

Then I realised that I needed to also create records in another table. I dont need much information, but the field names are not the same.

Perhaps a silly question, but how do I reference a specific record in a diferent table. I have modified the code below for a similar purpose and it works fine for that. But I do not know how to adapt it to create new records in invoice.dbf containing information from specific cash_tra.dbf records

SELE 1
use cust excl
SELE 2
USE cash_tra excl
SELE 3
USE invoice excl
SELE 4
USE invnum excl
SELE cust
GO top
DO while .not. eof()
	IF bal_due <> 0
		SELE  cash_tra
		APPE blank
		REPL date with date(),;
			Time with substr(time(),1,5),;
			Amount with a->bal_due,;  &&how does it know which customer to reference?
			Initials with 'IMP',;
			How_paid with '6',;
			Totl with a->bal_due,;
			Cust_num with a->Cust_num,;
			Amt_chrged with a->bal_due,;
....
..

What does 'a->' do?

 

[steve4king]

It looks like the problem is "too many arguments" between both of the inserts. According to the help file the maximum expressions is 24 and adding any of the numeric columns puts me at 25. Since it is combining both inserts... Hmm I need to figure out a way around it... I'll keep playing with it.

 

[dgrewe]

Steve
To avoid you Line 51 error problem, try this in your SCAN....ENDSCAN

SCAN FOR how_paid9 = "7" And Date >= m.CRFDate
SCATTER MEMVAR
M.fieldname1=....
M.fieldname2=.....
etc....
select INVOICE
GATHER MEMVAR
SELECT CASH
nNum = m.nNum + 1
Endscan

 

[steve4king]

Thanks for the help guys, the command works fine now. Much shorter than the original and I learned a ton in the process.

Mike, you were right about the new records not needing numerals and logical values unless they were greater than zero. I just had to reindex the file for the default values to appear.

DWGrewe, Thanks for that. I've seen the scatter/gather used a lot but haven't tried to implement that on anything yet. Will play around with that on the next run.

(not completely sure how to get along without "do while" yet. Told it is "oldschool" but it seems pretty fast to me (cycled 100000 records in under two seconds)
Will save that for another day haha.

Thanks guys.

Steve W.