none
copy data from one table to another

    Question

  • Dear Experts

     

    I have 2 tables, lets say table 1 and table2, in table1 I would like to be able to right click and have a pop menu say "completed". I would like to be able to mark the selected row as completed and have it move from table1 (list) to table2 (completed)

    So basically if my customer is marked as completed i would not like to see it in table1, id like to move it over to table2.

    Tuesday, September 13, 2011 11:34 PM

Answers

  • The simplest way will be to present your data in the grid with the checkboxes. You will check the records that are completed and then there will be

    insert into Table2

    select * from ListData

    where Completed

     

    delete from Table1 where PK IN (select PK from ListData where Completed)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by mikelvfp Thursday, September 15, 2011 3:54 AM
    Wednesday, September 14, 2011 12:24 AM
    Moderator

All replies

  • The simplest way will be to present your data in the grid with the checkboxes. You will check the records that are completed and then there will be

    insert into Table2

    select * from ListData

    where Completed

     

    delete from Table1 where PK IN (select PK from ListData where Completed)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by mikelvfp Thursday, September 15, 2011 3:54 AM
    Wednesday, September 14, 2011 12:24 AM
    Moderator
  • Hi,

    you do not have to use two tables for this (depends on the number of records stored in table1, though).

    You can add a new boolean or integer field to table1 called 'completed' and in your listbox you display all records with completed = .F. or 0 and in the grid you display all records with completed = .T. or 1

    This can be done via SELECT statements or by using two different views.

    This will become even more useful as soon as you decide to use several levels of completion...


    Gruss / Best regards -Tom 010101100100011001010000011110000101001001101111011000110110101101110011
    Wednesday, September 14, 2011 6:01 AM
    Answerer
  • Thank you naomi, I had something like this and it keeps telling me completed is READ ONLY

    USE appointment EXCLUSIVE
    lcrecno = appointment.appointid
    insert into completed ;
    select * from appointment ;
    where not deleted("appointment") and ;
    appointment.appointid = lcrecno
    
    delete for appointment.appointid = lcrecno ;
    in appointment
    Pack 

    @ Tom, can you give me an example in code please

    Wednesday, September 14, 2011 12:09 PM
  • OK here comes some code...

    however you can't run this code. The form was saved as class and via 'View Code' Button transformed to a prg just to show you the codepatterns.

    You will have to extract the relevant code parts and build your demo form on them...

    DEFINE CLASS onetabletwogrids AS form
    
    	Top = 0
    	Left = 0
    	Height = 438
    	Width = 602
    	DoCreate = .T.
    	Caption = "Form1"
    	Name = "Form1"
    
    	ADD OBJECT grdfalse AS grid WITH ;
    		ColumnCount = 2, ;
    		DeleteMark = .F., ;
    		Height = 381, ;
    		Left = 0, ;
    		Panel = 1, ;
    		RecordMark = .F., ;
    		RecordSource = "crsFalse", ;
    		ScrollBars = 2, ;
    		SplitBar = .F., ;
    		Top = 23, ;
    		Width = 265, ;
    		AllowCellSelection = .F., ;
    		Name = "grdFalse", ;
    		Column1.ControlSource = "", ;
    		Column1.Name = "Column1", ;
    		Column2.ControlSource = "", ;
    		Column2.Width = 168, ;
    		Column2.Name = "Column2"
    
    	ADD OBJECT onetabletwogrids.grdfalse.column1.header1 AS header WITH ;
    		Caption = "ID", ;
    		Name = "Header1"
    
    	ADD OBJECT onetabletwogrids.grdfalse.column1.text1 AS textbox WITH ;
    		BorderStyle = 0, ;
    		Margin = 0, ;
    		ForeColor = RGB(0,0,0), ;
    		BackColor = RGB(255,255,255), ;
    		Name = "Text1"
    
    	ADD OBJECT onetabletwogrids.grdfalse.column2.header1 AS header WITH ;
    		Caption = "Some value", ;
    		Name = "Header1"
    
    	ADD OBJECT onetabletwogrids.grdfalse.column2.text1 AS textbox WITH ;
    		BorderStyle = 0, ;
    		Margin = 0, ;
    		ForeColor = RGB(0,0,0), ;
    		BackColor = RGB(255,255,255), ;
    		Name = "Text1"
    
    	ADD OBJECT grdtrue AS grid WITH ;
    		ColumnCount = 2, ;
    		DeleteMark = .F., ;
    		Height = 381, ;
    		Left = 337, ;
    		Panel = 1, ;
    		RecordMark = .F., ;
    		RecordSource = "crsTrue", ;
    		ScrollBars = 2, ;
    		SplitBar = .F., ;
    		Top = 23, ;
    		Width = 265, ;
    		AllowCellSelection = .F., ;
    		Name = "grdTrue", ;
    		Column1.ControlSource = "", ;
    		Column1.Name = "Column1", ;
    		Column2.ControlSource = "", ;
    		Column2.Width = 168, ;
    		Column2.Name = "Column2"
    
    	ADD OBJECT onetabletwogrids.grdtrue.column1.header1 AS header WITH ;
    		Caption = "ID", ;
    		Name = "Header1"
    
    	ADD OBJECT onetabletwogrids.grdtrue.column1.text1 AS textbox WITH ;
    		BorderStyle = 0, ;
    		Margin = 0, ;
    		ForeColor = RGB(0,0,0), ;
    		BackColor = RGB(255,255,255), ;
    		Name = "Text1"
    
    	ADD OBJECT onetabletwogrids.grdtrue.column2.header1 AS header WITH ;
    		Caption = "Some value", ;
    		Name = "Header1"
    
    	ADD OBJECT onetabletwogrids.grdtrue.column2.text1 AS textbox WITH ;
    		BorderStyle = 0, ;
    		Margin = 0, ;
    		ForeColor = RGB(0,0,0), ;
    		BackColor = RGB(255,255,255), ;
    		Name = "Text1"
    
    	ADD OBJECT label1 AS label WITH ;
    		Caption = "unfinished", ;
    		Height = 17, ;
    		Left = 0, ;
    		Top = 5, ;
    		Width = 265, ;
    		ForeColor = RGB(255,0,0), ;
    		Name = "Label1"
    
    	ADD OBJECT label2 AS label WITH ;
    		Caption = "finished", ;
    		Height = 17, ;
    		Left = 337, ;
    		Top = 5, ;
    		Width = 265, ;
    		ForeColor = RGB(0,128,0), ;
    		Name = "Label2"
    
    	ADD OBJECT command1 AS commandbutton WITH ;
    		Top = 410, ;
    		Left = 515, ;
    		Height = 27, ;
    		Width = 84, ;
    		Caption = "Exit", ;
    		Name = "Command1"
    
    	PROCEDURE updatemytable
    		LPARAMETERS vValue as Boolean
    		* Update the base cursor with a new value
    		IF vValue = .T.
    			REPLACE completed WITH vValue FOR field1 = crsFalse.Field1 IN myTable
    		ELSE 
    			REPLACE completed WITH vValue FOR field1 = crsTrue.Field1 IN myTable
    		ENDIF 
    		* and having this one, re-read the cursors
    		Thisform.UpdateGridCursors()
    	ENDPROC
    
    	PROCEDURE updategridcursors
    		* re-read left grids cursor
    		Thisform.grdFalse.RecordSource = [crsDummy]
    		SELECT * FROM myTable INTO CURSOR crsFalse WHERE completed = .F.
    		Thisform.grdFalse.RecordSource = [crsFalse]
    		* re-read right grids cursor
    		Thisform.grdTrue.RecordSource = [crsDummy]
    		SELECT * FROM myTable INTO CURSOR crsTrue  WHERE completed = .T.
    		Thisform.grdTrue.RecordSource = [crsTrue]
    	ENDPROC
    
    	PROCEDURE Release
    		* Clean up
    		USE IN SELECT( [myTable] )
    		USE IN SELECT( [crsFalse] )
    		USE IN SELECT( [crsTrue] )
    		USE IN SELECT( [crsDummy] )
    	ENDPROC
    
    	PROCEDURE Load
    		* This cursor would usually be a table ;-)
    		* From scratch we have to fill in some records
    		CREATE CURSOR myTable ( field1 i , field2 C(10) , completed L )
    		INSERT INTO myTable	( field1 , field2 , completed ) VALUES	(  1 , [Record01] , .F. )
    		INSERT INTO myTable	( field1 , field2 , completed ) VALUES	(  2 , [Record02] , .T. )
    		INSERT INTO myTable	( field1 , field2 , completed ) VALUES	(  3 , [Record03] , .T. )
    		INSERT INTO myTable	( field1 , field2 , completed ) VALUES	(  4 , [Record04] , .F. )
    		INSERT INTO myTable	( field1 , field2 , completed ) VALUES	(  5 , [Record05] , .F. )
    		INSERT INTO myTable	( field1 , field2 , completed ) VALUES	(  6 , [Record06] , .F. )
    		INSERT INTO myTable	( field1 , field2 , completed ) VALUES	(  7 , [Record07] , .F. )
    		INSERT INTO myTable	( field1 , field2 , completed ) VALUES	(  8 , [Record08] , .F. )
    		INSERT INTO myTable	( field1 , field2 , completed ) VALUES	(  9 , [Record09] , .F. )
    		INSERT INTO myTable	( field1 , field2 , completed ) VALUES	( 10 , [Record10] , .F. )
    		* Dummy cursor for safe grids
    		CREATE CURSOR crsDummy ( field1 i , field2 C(10) , completed L )
    		*  createing recordsources for the grids
    		SELECT * FROM myTable INTO CURSOR crsFalse WHERE completed = .F.
    		SELECT * FROM myTable INTO CURSOR crsTrue  WHERE completed = .T.
    	ENDPROC
    
    	PROCEDURE grdfalse.RightClick
    		* Update the base table/cursor
    		Thisform.UpdateMyTable( .T. )
    	ENDPROC
    
    	PROCEDURE grdtrue.RightClick
    		* Update the base table/cursor
    		Thisform.UpdateMyTable( .F. )
    	ENDPROC
    
    	PROCEDURE command1.Click
    		* Close the form
    		Thisform.Release
    	ENDPROC
    
    ENDDEFINE
    
    

     


    Gruss / Best regards -Tom 010101100100011001010000011110000101001001101111011000110110101101110011
    Wednesday, September 14, 2011 1:45 PM
    Answerer
  • What is Completed here? It is a table or a cursor? If the latter, was it created with READWRITE clause?
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, September 14, 2011 2:00 PM
    Moderator
  • @ Naomi, Completed is a table, i havent used the readwrite clause I honestly never used the READWRITE clause can you show me?

     

    @ Tom, looks nice im not sure this is what i am looking for but i will try it for my own practice and i will get back to you.. much Thanks for the help ...... 

    Wednesday, September 14, 2011 2:16 PM
  • If completed is a table, it should work as long as you didn't include it into the project.

    For the cursor to become read write, you just need to add this keyword in the SELECT statement, e.g.

    SELECT SomeFields ;

    FROM SomeTables ;

    WHERE SomeCondition ;

    INTO CURSOR Test ReadWrite


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, September 14, 2011 3:13 PM
    Moderator
  • Two things Naomi, sorry for the troubles, finally I got it to work, but one problem though, this might be an easy fix but

     

    1. In my grid colomn where I placed my checkboxes, I dont actually see the checkboxes unless i click on the actual coloumn where the checkbox is.

     

    2. Whatever I have copied from table1 is now scrambled in table2.

     

    much thanks for your help

    Wednesday, September 14, 2011 11:46 PM
  • 1. Set sparse property of the column to .f.

    2. I'm not sure I understand the second problem - what do you mean?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, September 15, 2011 2:20 AM
    Moderator
  • for example: In table1 i have a field called "name", well in table2 I also have a field called "name" but instead I find the contents of "Name" in table1 in lets say "address" of table2 and so forth

     

    Thursday, September 15, 2011 3:06 AM
  • I suggest to be explicit, e.g.

     

    insert into table2 (field1, field2, field3)
    
    select field1, field2, field3 from table1 
    
    where Completed = .t.
    
    
    

     

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, September 15, 2011 3:24 AM
    Moderator
  • thanks Naomi your awesome
    Thursday, September 15, 2011 3:53 AM
  • I tried insert into table1 select * from table2 but it says that syntax error.

    Why?

    Thursday, September 22, 2011 7:59 AM
  • I tried insert into table1 select * from table2 but it says that syntax error.

    Why?

    Naomi wrote

     

    I suggest to be explicit, e.g.

    insert into table1 (field1, field2, field3)
    select field1, field2, field3 from table2 

     


    Systems Analyst
    Thursday, September 22, 2011 8:47 AM
  • Also, this syntax only works in VFP8 and up. Hopefully you're using the latest VFP version.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, September 22, 2011 12:24 PM
    Moderator
  • No I am using vfp6. How is the syntax in vfp6?

     

    thanks

    Thursday, September 22, 2011 12:42 PM
  • Check VFP6 Help

    INSERT - SQL

    INSERT INTO dbf_name [(fname1 [, fname2, ...])]

    VALUES (eExpression1 [, eExpression2, ...])

    -or-

    INSERT INTO dbf_name FROM ARRAY ArrayName | FROM MEMVAR

     

    In VFP6 is not possible to use

    insert into ....  

    select .... from ...

     

     


    Systems Analyst
    Thursday, September 22, 2011 12:56 PM
  • See Vladimir's answer. If you need to insert data from another table you need to do (in VFP7 and less):

    select TargetTable

    append from dbf('Source')

    and the structures of the tables must match.

     

    In VFP8 and 9 the syntax is ANSI SQL.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, September 22, 2011 1:48 PM
    Moderator