Script to replace a row from one table with another conditionally 1

[steve4king]

I have two tables.. one of them is a backup of the other, (but unfortunately the program we have here has not updated the writing of the backup file.. so they are not identical)

Regardless; what I need to do is create a script that will..

use table1
repl all with (table2) for field1 = "1234" IF no records in Field2 contain 1234)

The reason for the check is the system creates a reference record in certain circumstances and places the info from field1 of the original record, into field2 of the reference record.

Thanks,

Steve.

 

[JRB-Bldr]

Since one table is a backup of the other one, I can assume that there is one or more fields in common.

You should look over the suggestions in thread1252-1322963 where the individual wants to find either Orphan records or Duplicate records from 2 data tables.

You can use a similar procedure to find the records in Source table which do not appear in your Backup.

Then once you have identified the missing records you can SCAN/ENDSCAN through those missing records in the Source table and use something like:

SELECT Table1
SCAN FOR <whatever defines missing records>
   * --- Copy Source Record ----
   SCATTER MEMVAR

   * --- Go To Destination Table ---
   SELECT Table2
   * --- Create New Record & Populate With Source Data ---
   APPEND BLANK
   GATHER MEMVAR

   SELECT Table1
ENDSCAN

Good Luck,
JRB-Bldr

 

[steve4king]

Hrmm.. unfortunately I am not YET knowledgable enough to adapt this to my needs. (not without a lot of time) The developer for our software is too busy dealing with other issues at the moment to help with something like this. The command window within the program allows only one line scripts as well.

Is there anyway to throw this into one line or link commands in a single line. (like && in linux)
Here is the manual method:



* Command window scripts

use cash_tra
set stat on
loca for inv_ref = "123456"
* If status bar at bottom of screen reads EOF/(last invoice number) then continue.
* If it displays a number, the invoice HAS been paid off.

dele for invoice = "123456"
appe from zmirrcash for invoice = "123456"

* Replace 123456 with whichever invoice number you think has not been paid but program says it has.

(In case you are curious, this is necessary because of database corruption which prevented records from being written correctly.)

Thanks for the help.

 

[bborissov]

Version of Fox?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[steve4king]

program is built with v9 sp1

 

[JRB-Bldr]

The way to create a little program which will execute multiple lines of VFP code is to create a new Program (PRG) file such as

MODIFY COMMAND "C:\Temp\Temp.prg"

This will open an Edit window into which you put your code.
Then, from the top level menu Program | Compile which will compile the 'program' which you just created.

Then close the Temp.prg window.

To execute the multi-line program, go back to the Command Window

DO "C:\Temp\Temp.prg"

Good Luck,
JRB-Bldr

 

[bborissov]

Can you please, post some example data and what you want at the end. That is because I read your question again and again and I am not sure I understand it (my fault not your)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[steve4king]

GREAT!!!!!!
Now I just need to figure out the if/then issue.
But that will help with so many other scripts we have been running manually.

 

[bborissov]

With VFP9 you could use EXECSCRIPT() command:

TEXT TO MyText NOSHOW
     USE SomeTable 
     REPLACE ALL Field WITH 0 IN SomeTable
     USE IN SomeTable
ENTEXT
EXECSCRIPT(MyText)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[steve4king]

Alright.
example
Table 1 = Cash_tra

*|invoice| Amt_chg|totamtdue|inv_ref|
1| 123456|  12.00  |  0.00  |       | 
2| ACCT  |   0.00  | 12.00  | 123456|
3| 654321|   5.00  |  0.00  |       |

Table 2 = Zmirrinv

*|invoice| Amt_chg|totamtdue|inv_ref|
1| 123456|  12.00  | 12.00  |       | 
2| 654321|   5.00  |  5.00  |       |

(not actually code, it just kept the spacing correct)

I have an invoice that shows that it is paid off (123456) I am not sure that it should be paid off. I want the program to check to see if INV_REF mentions "123456" if it does not then replace it with correct data from zmirrcash. If it does I want it to leave the record alone.

in this example above. I want the Cash_tra to look like this afterward:

*|invoice| Amt_chg|totamtdue|inv_ref|
1| 123456|  12.00  |  0.00  |       | 
2| ACCT  |   0.00  | 12.00  | 123456|
3| 654321|   5.00  |  5.00  |       |

 

[bborissov]

First make veeeeeeeeery good backup, then try:

UPDATE Cash_tra SET Amt_Chg   = Zmirrinv.Amt_Chg,;
                    totamtdue = Zmirrinv.totamtdue;
FROM Cash_tra;
LEFT JOIN Cash_tra Tbl1 ON Cash_tra.Invoice = Tbl1.inv_ref;
INNER JOIN Zmirrinv     ON Cash_tra.Invoice = Zmirrinv.inv_ref;
WHERE Tbl1.inv_ref IS NULL

not tested


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[steve4king]

There are a lot more fields.. I just included ones pertinent to the example.
I need to copy the entire record.
I was thinking something like..

USE CASH_TRA
      LOCATE FOR INV_REF = "UserinputA"(user input)
      
      IF FOUND()
            MESSAGEBOX("Payment Posted on (cash_tra.postdate)")
            * do nothing
      IF NOT FOUND()
            CLOSE DATA ALL
            USE CASH_TRA 
            DELE FOR INVOICE = (USERINPUTA)
            APPE FROM ZMIRRCASH FOR INVOICE = (USERINPUTA)
            CLOSE DATA ALL
            MESSAGEBOX("INVOICE CORRECTED")

 

[steve4king]

Also Boris, that looks like SQL not Foxpro..

 

[bborissov]

VFP has also SQL engine
So this is pure VFP code

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Unfotunatly there is a bug in DELETE -SQL command, but you could try:

USE CASH_TRA IN 0
SELECT * FROM ZMIRRCASH WHERE INVOICE = (USERINPUTA) INTO CURSOR crsTest

SELECT CASH_TRA
LOCATE FOR INV_REF = "UserinputA"(user input)
IF NOT FOUND()
   DELETE FROM CASH_TRA WHERE INVOICE = (USERINPUTA)
   APPEND FROM (DBF([crsTest]))
ENDIF

If you have proper indexes that will be run very fast. Check Rushmore optimization in HELP.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[steve4king]

USE CASH_TRA IN 0
SELECT * FROM ZMIRRCASH WHERE INVOICE = "579869" INTO CURSOR crsTest
SELECT CASH_TRA
LOCATE FOR INV_REF = "579869"
IF NOT FOUND()
   DELETE FROM CASH_TRA WHERE INVOICE = "579869"
   APPEND FROM (DBF([crsTest]))
ENDIF

(two errors during compile.(I don't know what error) when running line by line, append from (DBF([crsTest])) "c:\whatever\dbf([crstest]) not found" and of course the if statement cannot be done line by line.)

LOCATE FOR INV_REF = "579869"
IF NOT FOUND()
   DELETE FROM CASH_TRA WHERE INVOICE = "579869"
   APPEND FROM ZMIRRCASH WHERE INVOICE = "579869"
ENDIF

(one error during compile.. no idea why.

 

[JRB-Bldr]

Since your Cash_Tra record for Invoice = "12345" does not appear changed in your end-result sample, I am not clear as to what you are wanting, but here is one way to cycle through the records (similar to that suggested in thread1252-1322963 ).

The following might not do EXACTLY what you want, but you should be able understand the process and to modify the code accordingly to get your desired results.

* --- Create an Index for Table 2 ---
USE Zmirrinv IN 0 EXCLUSIVE
SELECT Zmirrinv
INDEX ON Invoice TAG Invoice
USE

<Do Whatever>

* --- Now check two tables ---
USE Zmirrinv in 0
SELECT Zmirrinv
SET ORDER TO Invoice

* --- Set up Relation between tables ---
* --- based on 'Invoice' ---
USE Cash_tra in 0
SELECT Cash_tra
SET RELATION TO Invoice INTO Zmirrinv

* --- Scan thru Table 1 for ONLY Those records where Inv_Ref empty AND table2 record exists ---
SELECT Cash_tra
SCAN FOR EMPTY(Cash_Tra.Inv_Ref);
      AND !EMPTY(Zmirrinv.Invoice)
   
   * --- Get Table2 values ---
   SELECT Zmirrinv
   mnAmtChg = Zmirrinv.Amt_Chg
   mnTotAmtDue = Zmirrinv.TotAmtDue

   * --- Put values into Table1 ---
   SELECT Cash_tra
   REPLACE Cash_tra.AmtChg WITH mnAmtChg,;
           Cash_tra.TotAmtDue WITH (Cash_tra.TotAmtDue - mnTotAmtDue),;
           Cash_tra.Inv_Ref WITH Invoice
ENDSCAN

On another note...
To get HELP from VFP go to the Command Window and type HELP APPEND
One valid form of APPEND FROM syntax would be

APPEND FROM DBF('crsTest')  && where crsTest is the Alias of the new records' table

Good Luck,
JRB-Bldr

 

[steve4king]

I think I have adapted this to work just fine. (of course now the affected party no longer cares..) Oh well, I'm learning a ton. Thanks for all the help guys.

But a simple question..
what does "..in 0" mean?

USE CASH_TRA IN 0

 

[JRB-Bldr]

VFP opens data tables into one of 255 'workspaces' which can be individually referenced by it's workspace number or by the table's ALIAS.

The IN 0 tells VFP to open the table into the next available workspace. The alternative would be to individually specify each workspace prior to opening the table.

SELECT 5  && Select workspace
USE ThisDBF  && Open Table

<Do Whatever>

* --- Go back to table ---
SELECT 5   && Or [I][B]Select ThisDBF[/B][/I]

When you use the IN 0 you typically do so when you have no intent of referencing the table via its workspace number (its difficult to keep track of what table is in what number), but instead by its ALIAS name.

VFP will default the table's name to itself, unless otherwise specified when opened

USE Cash_Tra IN 0 [B]ALIAS Cash[/B]
SELECT Cash

Good Luck,
JRB-Bldr

 

[TamarGranor]

To add to what JRB-Bldr said, in VFP, there's no reason ever to worry about work areas (what he called "workspaces") by their numbers. You can always refer to them by the alias of the table.

Tamar