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]

Nevermind on the question about the bottom script, I see that it is just running through the cust table selecting each line sequentially.

 

[Imaginecorp]

This is the way I would do it.
You are using reserved VFP word for variables i.e. Change, date etc. This could lead to problems
I do not know what (invoice) or (Change) are, if they are variables. DO NOT use Clear all

Clear All &&& If no variables have values If they do use Close data all
Set Exclusive on
CRFDate = {}
CRFDate = Ctod(Inputbox("Enter a date","How far back to correct credits?",Dtoc(Date())))

Select 0
use cash_trac 
Update cash_trac ;
	SET cash_tra.how_paid  = "1",;
	cash_tra.inv_ref = (Invoice)+"CreditFix",;
	cash_tra.Totl = (Change),;
	cash_tra.Amount = (Change),;
	cash_tra.Change = 0.00 ,;
	cash_tra.how_paid9 = " " ;
	Where cash_tra.how_paid9 = "7" And cash_tra.Date >= CRFDate

*** do not need to have numbers for Select (1,2,3) 0 will select the next work space
Sele 0
Use Cust 
Sele 0
Use Invoice 
Sele 0
Use invnum Excl
Sele Cust
Scan
	If Cust.bal_due <> 0
		Insert Into cash_tra;
		(Date,Time,Amount,InitialS,How_paid,Totl,Cust_num,Amt_chrged) ;
			values (Date(),Substr(Time(),1,5),Cust.bal_due,'IMP','6',;
			cust.bal_due,Cust.Cust_num,Cust.bal_due)
	Endif
	Select Cust
Endscan

Again: You will have to fine tune

 

[Imaginecorp]

[quote}
Use invnum Excl
[/quote]

Sorry: remove the "Excl" the table are being opened exclusive with Set Exclusive On

 

[steve4king]

OK.. lots of little questions now about your suggestion. (which seems longer than a cyclic check that I am currently using)

Why use "set" rather than "repl"? How do the functions differ?

(FYI. invoice and change are both column names in cash_tra)
What does 'clear all' do? When no table is defined?

When I changed the script, 'a->' didn't work. I had to change it to the name/alias for the table. In the second script I quoted.. how did "a" become the alias? for cust?

CRFDate = {}
CRFDate = Ctod(Inputbox("Enter a date","How far back to correct credits?",Dtoc(Date())))

What is the purpose of the first line?

Also, can I select the same table twice into different workspaces to compare different records sequentially?
IE.

SELE 1
use cash_tra in 20
use cash_tra in 21
SELE 20
GO top
DO while .not. eof()
    IF how_paid9 = "7"
        SELE  21
        SET Cust_num = 20->Cust_num,;
            Amount = 20->Change,;
            how_paid = 20->how_paid9,;
        use counter    
        set count +1        
        Sele 1
    Endif
Messagebox((counter.count) "credits corrected")

untested.. just an example.

(By the way.. thanks again guys for all the input, two weeks ago I would have had not even the slightest clue about what I just typed.. It may not be quite right but so much better)

 

[Imaginecorp]

By the way What version of Fox are you using? If you are using DOS none of this will work

Why use "set" rather than "repl"? How do the functions differ?

Yes you could use a Replace rather than an Update. A Update - SQL is a little faster and because of the Where Clause will update the whole table.

What does 'clear all' do? When no table is defined?

Nothing. I did not know this was a single program and no table has been opened before

Also, can I select the same table twice into different workspaces to compare different records sequentially?

Yes

A->

This is an Alias. In the olden days we used to refer to the first table opened as "A". If I remember correctly A-> is DOS. Now you could use "A.change". "A" became Cus table as it was the first table opened.

CRFDate = {}

This tell VFP that CRFDate is a Date variable and anything in it should be considered a Date. Setting it to "{}" clears what ever it may have contained.

Your sample code:
1. You do not need to set numbers i.e. 20, 21 etc. Just Select Cash_tra as many times as you want. BUT with an Alias
2. A Scan..Endscan is faster than a Do While and you do Not have to Set a SKIP to go to the next record. Nor do you have to do a Go Top
3. It seems like you are updating from the same table, If so you can open the same table multiple times
Select 0
use cash_tra
select 0
use cash_tra again alias cashtra2
......
......
this is easier to remember than 21

Use Counter

You cannot open a table within a Do While or any loop without a Select 0, but it will open as many times as there are records in your table. If all you are doing with is incrementing a counter.
You Cannot display a MEssagebox in a Do While Loop as it will wait for an Input from the user after each and every record unless you use a Timeout 1 (wait for 1 second). This is not advisible as it will slow the loop down

nNum = 0
Do While not eof()
 if how_paid.....
 ......your code
  nNum = nNum + 1
  WAIT (Transform(nNum)+ "Credits corrected") Window Nowait
 endif
.....
....
enddo

4. If it is Only one Table being updated, rather than a DO While do an Update using the above explanation (Number 3) after you have updated the values in the Second Cash_tra and there is a common field which has not been updated. For the following example lets say there is and it is Cust_ID.

Select 0
Use cash_tra
Select 0
Use cashtra2
Update cashtra2 ;
	set cashtra2.cust_num = cash_tra.cust_num,;
	cashtra2.Amount = cash_tra.Change,;
	cashtra2.how_paid = cash_tra.how_paid9 ;
	from cash_tra ;
	where cashtra2.cust_id = cash_tra.cust_id ;
	and cash_tra.how_paid9 = "7"

Thats it...

Again: This is to give you an idea, you will have to fine tune

 

[Imaginecorp]

A question:
You are obviously not a programmer (IS/IT Management). What happened? Did the programmers quit?
Welcome to our world...

 

[steve4king]

In answer to your last question, no I am not a programmer heh. Our programmer(yes just one) did not quit, but he is too busy working on older issues to deal with this.. Unfortunately this is a poor solution, a bandaid and it will still need to be fixed later.

ahh yes, I forgot to include skip and enddo.. (I wrote that message while sitting through a USELESS sql class)

I didn't have access to this when I wrote on Saturday. But this is what I had written previously and used.. (didn't have counter added in yet)

CRFDate = CTOD(INPUTBOX("Enter a date","How far back to correct credits?",DTOC(DATE())))
SELE 1
USE cash_tra excl 
SELE 2
USE invoice excl
SELE 3
USE invnum excl
SELE cash_tra
GO top
DO while .not. eof()
	IF how_paid9 = "7" and date >= (CRFDate)
		SELE invoice
		APPE blank
		REPL date with cash_tra->date,;
			Time with cash_tra->time,;
			cashgame with .F.,;
			Taxable with "N",;
			Taxable2 with "N",;
			Type with "P",;
			Invoice_no with "CREDIT",;
			Clerk with cash_tra->Initials,;
			SKU with "Trans_credit",;
			Cust_num with cash_tra->Cust_num,;
			Price with cash_tra->Change,;
			Total with Cash_tra->Change,;
			Desc with "Credit Record Fix",;
			zamount with cash_tra->Change,;
			qty with 1
		SELE 1
		ENDIF
	Skip
ENDDO eof            
CLOSE DATA ALL

use cash_tra
copy to tempcash9 for how_paid9 = "7" and Date>=(CRFDate)
repl all how_paid9 with "1" for how_paid9 = "7" and Date>=(CRFDate)
close data all
Use tempcash9
repl all how_paid with "1"
repl all inv_ref with (Invoice)+"CFix"
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 tempcash9
close data all
use tempcash9 excl
dele all
pack

Knowing now that I can select the same table multiple times I see I could have made this a bit shorter, and used the first method to update both tables rather than switching.

And as far as the counter goes.. I dont need to write that to a table or cursor? I can just update a reference?
and I just want to display that counter at the end.. I meant to put it after the enddo.

What is the difference between "close data all" and "clear all"?

Again perhaps a silly question..

Select 0
use cash_tra
select 0
use cash_tra again alias cashtra2

"again" is a valid part of this statement?

What is the purpose of the "select 0"? It is being selected even before a table is used then you select it again before the next table is used.
Why would you select the same number? Is it necessary to select 1,2,3.. etc as I did in my program?

 

[Imaginecorp]

In my book, there Is No such thing As a silly question. We were All In your situation (learning) And beleave me, I, For one have asked sillier questions in my day.

"Again" Is a Valid Part Of This statement?"

Again: Is Just What it Says, "Use the Table Again"

Alias = But With a different Name

Select 0 = (Work Space) Rather than the programmer trying to remember how many Tables he/she may have Opened In an Application, We let VFP Select the Work Space. This way you Do Not have To remember And If 2 Tables are Opened In the Same Work Space the First will will Shut Down. Select 0, opens a Table In the Next available higher Work Space. In your example; VFP would have Opened the Tables In the Same Order you have hard coded

The following is based on My assumption you want to learn programming and this is not the only 'bandaid" you will apply: If so here are some pointers:

Even though your Code will Work, there Is a lot Of redundancy And a lot Of repetation. it Is strightly
a Single User Program. Especially as all tables are being opened Exclusive (excl) If ever you And your programmer want To Access it At once you will Not be allowed. there are Much faster And more effecient ways To Do This.

Scans ...Endscans are faster And cleaner And Do Not need a Skip

You Just cannot Do a Skip without checking For Eof()
If Eof()
Exit
Else
Skip
Endif
otherwise you get an error. EOF() is an "imaginary" record and the table cannot skip if it is already at the End of File.

INSERT - SQL is faster AND cleaner than a Append Blank

Closing and opening tables is not advisable in large applications it is unnecessary. Now you have to remember which one is open and which is closed.

There is No need to Copy a table. If the original was part of a DBC and the copy is not specified as part of a DBC, the new table will be a Free table. If the original had long firld names (>10) the new will not. they will be truncated to 10

There is no need to copy a table and make changes and then the append them back to it. You can make changes to the original based upon criteria. You cannot "Use" (open) a table without designating a Work Space

TRY TO ovoid using VFP reserved words like date, time type etc. preface them with dDate, cTime etc

TRY this code OR use it as an example: 
* = your code commented out
**** = my explanations

[i] CRFdate = {}
CRFDate = Ctod(Inputbox("Enter a date","How far back to correct credits?",Dtoc(Date())))
SET EXCLUSIVE ON
SET CENTURY ON &&& display the complete year
Sele 0
Use cash_tra Excl
Sele 0
Use invoice Excl
Sele 0
Use invnum Excl
Sele cash_tra[/i]

*Go Top
*Do While .Not. Eof()
**** only records that match the criteria (filter) will be scanned

[i]SCAN FOR how_paid9 = "7" And Date >= (CRFDate)[/i]

 *If how_paid9 = "7" And Date >= (CRFDate)
 *Sele invoice &&& no need to select this if doing an insert
 *Appe Blank
 **** Dont need "->"

[i]INSERT INTO invoice;  (date,time,cashgame,taxtable,taxtable2,type,Invoice_no,Clerk,;
  SKU,Cust_num,Price,Total,;
  Desc,zamount,qty,HOW_PAID9);
  Values;
 cash_tra.Time,.F.,"N","N","P","CREDIT",cash_tra.InitialS,;
  "Trans_credit",cash_tra.Cust_num,cash_tra.Change,cash_traChange,;
  "Credit Record Fix",cash_tra.Change,1,"1")[/i]

****INCLUDE HOW_PAID9 IN THE INSERT RATHER THAN DOING A REPLACE ALL LATER
 *Repl Date With cash_tra->Date,;
 *Time With cash_tra->Time,;			
 *cashgame With .F.,;
  *Taxable With "N",;
  *Taxable2 With "N",;
  *Type With "P",;
  *Invoice_no With "CREDIT",;
 *Clerk With cash_tra->InitialS,;
 *SKU With "Trans_credit",;
 *Cust_num With cash_tra->Cust_num,;
 *Price With cash_tra->Change,;
 *Total With cash_tra->Change,;
 *Desc With "Credit Record Fix",;
 *zamount With cash_tra->Change,;
 *qty With 1
 *Sele 1
 *Endif
 *Skip
 *Enddo Eof

 [i] Sele cash_tra
ENDSCAN  [/i]

*Close Data All

*Use cash_tra
*Copy To tempcash9 For how_paid9 = "7" And Date>=(CRFDate)
*Repl All how_paid9 With "1" For how_paid9 = "7" And Date>=(CRFDate)
*Close Data All
*Use tempcash9

[i]SELECT cash_tra
replace how_paid With "1",inv_ref With (invoice)+"CFix",;
Totl With (Change),Amount With (Change),invoice With "CREDIT",;
Change With 0.00,how_paid9 With " " ;
FOR how_paid9 = "7" And Date>=(CRFDate) ALL [/i]

*Repl All how_paid With "1"
*Repl All inv_ref With (invoice)+"CFix"
*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 tempcash9
*Close Data All
*Use tempcash9 Excl
*Dele All
*Pack

[i]CLOSE ALL[/i]

Again: this is to give you an idea, you will have to fine tune

 

[Imaginecorp]

sorry: should have removed the "Excl" as all tables are being opened exclusive

 

[steve4king]

Wow.. so many ways to do the same thing..

Wish I had more time outside of work to play around with this. Learning on the job when programming isn't my primary task is tricky.

 

[steve4king]

SCAN FOR how_paid9 = "7" And Date >= (CRFDate)
INSERT INTO invoice; (date,time,cashgame,taxtable,taxtable2,type,Invoice_no,clerk,;
SKU,Cust_num,Price,Total,;
Desc,zamount,qty,HOW_PAID9);
Values;
cash_tra.date,cash_tra.Time,.F.,"N","N","P","CREDIT",cash_tra.Initials,;
"Trans_credit",cash_tra.Cust_num,cash_tra.Change,cash_tra.Change,;
"Credit Record Fix",cash_tra.Change,1,"1")

I didn't know it could be done this way. Smaller but a little harder to keep track of. Other than being less lines, does it have any advantage to running insert line by line?

set date = cash_tra->date,;
            Time = cash_tra->time,;
            cashgame = .F.,;
            Taxable = "N",;
            Taxable2 = "N",;
            Type = "P",;
            Invoice_no = "CREDIT",;
            Clerk = cash_tra->Initials,;
            SKU = "Trans_credit",;
            Cust_num = cash_tra->Cust_num,;
            Price = cash_tra->Change,;

Also the reason I did a replace all howpaid_9 with 1 as a seperate command.. and I'm not certain your example addresses it.. but I wanted to be able to use it as a filter until they were all complete. And I am changing the original not the new insert lines.

 

[Imaginecorp]

does it have any advantage to running insert line by line?

You have no choice. You are inserting a record in the Invoice table, taking information from every record that meets the criteria in Cash_tra

Now if you had a common field in both tables (see my post dated Feb 25. Point 4) you would not need a Scan..Endscan just an Update with a Where clause.

...And I am changing the original not the new insert lines.

That is exactly what the Replace code does. change the original Cash_tra table

SELECT cash_tra
Replace how_paid.......
............. ALL

 

[steve4king]

OK, I've found time to put it together in the format you suggested.. (a little deviation on the counter because I did not know if it would work without a "do while")

nNum = 0
CRFdate = {}
CRFDate = Ctod(Inputbox("Enter a date","How far back to correct credits?",Dtoc(Date())))
SET CENTURY ON
Sele 0
Use cash_tra
sele 0
Use cash_tra again alias cash_tra2
Sele 0
Use invoice
Sele cash_tra
do while .not. eof()
	IF how_paid9 = "7" and date >= (CRFDate)
		nNum = nNum+1
		endif
	skip
ENDDO eof
go top
SCAN FOR how_paid9 = "7" And Date >= (CRFDate)
	INSERT INTO invoice; (date,time,cashgame,taxable,taxable2,type,Invoice_no,clerk,;
		SKU,Cust_num,Price,Total,;
		Desc,zamount,qty);
		Values;
		(cash_tra.date,cash_tra.Time,.F.,"N","N","P","CREDIT",cash_tra.Initials,;
		"Trans_credit",cash_tra.Cust_num,cash_tra.Change,cash_tra.Change,;
		"Credit Record Fix",cash_tra.Change,1);
	INSERT INTO cash_tra2; (date,time,amount,initials,how_paid,invoice,amount1,;
		totl,cust_num,list,change,amt_chrged,fin_chg,fin_paid,cur_fin_cg,;
		exchange,exchange2,due_30,due_60,due_90,totamt_due,amt_posted,;
		new_charge,prev_bal,curr_pays,deposit,networkid,inv_ref);
		Values;
		(cash_tra.date,cash_tra.Time,0.00,cash_tra.initials,"1","CREDIT",0.00,;
		cash_tra.Change,cash_tra.Cust_num,0.00,0.00,0.00,0.00,0.00,0.00,;
		0.0000,0.0000,0.00,0.00,0.00,0.00,0.00,;
		0.00,0.00,0.00,0.00,cash_tra.networkid,cash_tra.invoice+"CF");
Endscan
repl all how_paid9 with "1" for how_paid9 = "7" and Date>=(CRFDate)
MESSAGEBOX(nNum)

I get two errors while attempting to compile the program. The counter works fine by itself.. in-fact, If I take out both of the inserts it compiles fine..

 

[Imaginecorp]

Steve4King:

I have NO clue what you are trying to do and why... I gave you code samples which you will not even attempt to understand, modify or use...

My advice, with all due respect, wait for your programmer to do this when he has some time...

 

[steve4king]

I am confused at your response.. I DID attempt to understand and utilize the code sample you gave.. That is the part that returns an error during the compile. I thought I did understand it for the most part.

SCAN FOR how_paid9 = "7" And Date >= (CRFDate)
    INSERT INTO invoice; (date,time,cashgame,taxable,taxable2,type,Invoice_no,clerk,;
        SKU,Cust_num,Price,Total,;
        Desc,zamount,qty);
        Values;
        (cash_tra.date,cash_tra.Time,.F.,"N","N","P","CREDIT",cash_tra.Initials,;
        "Trans_credit",cash_tra.Cust_num,cash_tra.Change,cash_tra.Change,;
        "Credit Record Fix",cash_tra.Change,1);
    INSERT INTO cash_tra2; (date,time,amount,initials,how_paid,invoice,amount1,;
        totl,cust_num,list,change,amt_chrged,fin_chg,fin_paid,cur_fin_cg,;
        exchange,exchange2,due_30,due_60,due_90,totamt_due,amt_posted,;
        new_charge,prev_bal,curr_pays,deposit,networkid,inv_ref);
        Values;
        (cash_tra.date,cash_tra.Time,0.00,cash_tra.initials,"1","CREDIT",0.00,;
        cash_tra.Change,cash_tra.Cust_num,0.00,0.00,0.00,0.00,0.00,0.00,;
        0.0000,0.0000,0.00,0.00,0.00,0.00,0.00,;
        0.00,0.00,0.00,0.00,cash_tra.networkid,cash_tra.invoice+"CF");
Endscan

Does this not make sense? It is very similar to your example. I just added some fields to it.

 

[steve4king]

And the second table.

 

[TamarGranor]

What errors are you getting?

Tamar

 

[steve4king]

Tamar, I wasn't able to determine the errors I was getting during compile because I did not have access to VFP, I was doing this through "MODI COMM prgName" and "COMP prgName".

I finally got a copy and found out that it is a nesting error that shows up on the "SCAN FOR how_paid9 = "7" And Date >= (CRFDate)" line. I do not know why it is giving that error.. 'Endscan' is right below the inserts..

Mike, your script worked beautifully, I did have to change the last line from "MESSAGEBOX m.nNum" to "MESSAGEBOX(nNum)" but the numeric fields did not default to 0. They all came in blank. I did not try removing the Logical entry, but If I do append blank, it shows neither .T. nor .F.

Why did you place "m" in front of nNum and CRFDate? and why did substituting that for the parentheses work for everything except the last line?

I apologize for the persistent questions but I really want to understand how this works. (otherwise I would have just used my original ugly script at the top.. it worked but I knew I lacked understanding and it could be better.

Thanks again for all the help guys.

 

[steve4king]

Aside from the questions posed above the below script is pretty much final.. I omitted anything inconsequential.

Two little issues,
1. If I run through the command through VFP (set step on)
everything checks out just fine.
But if I try to compile it, it tells me Error in line 51: Command contains unrecognized phrase/keyword.
line 51 seemed pretty innocent so I made a gap.. same error and line 51 is now just empty space.

2. Using the cancel command breaks the overall application, not just my little utility. (DO cancelled appears when trying to click anything afterward) Is there something I should use other than cancel? I tried exit and that gives me a nested level error.

CLOSE DATABASES all
nNum = 0
CRFDate = Ctod(Inputbox("Enter a date","How far back to correct credits?",Dtoc(Date()),0,"01/01/9999","01/01/9999"))
IF CRFDate = CTOD("01/01/9999")
    CANCEL
endif
use cash...
....
SCAN FOR how_paid9 = "7" And Date >= m.CRFDate
    INSERT INTO invoice;
       	(date, ....
    REPL how_paid9 with "1"
    nNum = m.nNum + 1
Endscan
cCount = STR(nNum)+" Credits corrected"
MESSAGEBOX (cCount,0,"Results")

CLOSE DATABASES ALL