How To Replace Data To Another Table With Conditions 1

[wrgt9740]

Hi. I have two tables named tblItems and tblReport, and I'd like to replace the data of tblReport with tblItems so I could see the comings and goings of a specific item.

tblItems has fields Date, Item, Quantity, and Status. Status is either "I" for incoming, or "O" for outgoing. Quantity represents the number of items incoming / entering warehouse, or outgoing / leaving warehouse.
tblReport has fields Date, Incoming, and Outgoing. Both Incoming and Outgoing fields are number types.

What I wished to do was to generate a summary of movement of a specific item, for example "ballpen", from tblItems, then copy the Date to tblReport where there was an entry of "ballpen".

Data for Incoming and Outgoing fields would depend on the Status: if the Status is "I", then Quantity of tblItems would be copied to Incoming of tblReport. If Status is "O", then Quantity would be copied to Outgoing. tblReport for "ballpen" would look like this:
[tab]DATE[tab][tab][tab]INCOMING[tab][tab]OUTGOING
[tab]01/15/20[tab][tab][tab]10
[tab]01/16/20[tab][tab][tab][tab][tab][tab][tab][tab][tab]2
[tab]01/17/20[tab][tab][tab][tab][tab][tab][tab][tab][tab]5
[tab]01/18/20[tab][tab][tab]10

Does anyone have suggestions on how to execute this efficiently? I was able to get the results I wanted, but it involved creating a cursor, INSERT INTO cursor, UPDATE cursor if Status is "I", UPDATE cursor if Status is "O", then finally INSERT INTO tblReport. This is the only way that I could think of, as I didn't receive any proper training on FoxPro, nor on other programming languages. I only educated myself through reading books from the Internet, and researching in forums.

Thank you for taking the time to read this.

 

[mjcmkrsr]

Hi

I'd suggest three tables: Item, Item_In and Item_Out. This allows you also to easily add the running totals. See sketch of code below

LOCAL liAmount, lcItemCode

CREATE CURSOR ITM (cItemCode C(5), cItemDesc c(20))

INSERT INTO ITM VALUES ("AAAAA", "Balpen")
INSERT INTO ITM VALUES ("AAAAB", "Ink")
INSERT INTO ITM VALUES ("AAAAC", "Paper")

CREATE CURSOR ITEM_IN (cItemCode C(5), dInDate D, iQuantitiy I)

INSERT INTO ITEM_IN VALUES ("AAAAA", {^2020-12-01}, 25)
INSERT INTO ITEM_IN VALUES ("AAAAA", {^2020-12-02}, 12)
INSERT INTO ITEM_IN VALUES ("AAAAB", {^2020-12-03}, 34)
INSERT INTO ITEM_IN VALUES ("AAAAC", {^2020-12-03}, 125)
INSERT INTO ITEM_IN VALUES ("AAAAC", {^2020-12-04}, 32)

CREATE CURSOR ITEM_OUT (cItemCode C(5), dDateOut D, iQuantity I) 

INSERT INTO ITEM_OUT VALUES ("AAAAA", {^2018-01-01}, 12)   
INSERT INTO ITEM_OUT VALUES ("AAAAA", {^2018-02-12}, 14)   
INSERT INTO ITEM_OUT VALUES ("AAAAB", {^2018-03-15}, 10)   
INSERT INTO ITEM_OUT VALUES ("AAAAB", {^2018-03-15}, 10)   
INSERT INTO ITEM_OUT VALUES ("AAAAC", {^2018-04-20}, 11)   

SELECT ITM.cItemCode, ITM.cItemDesc, Item_In.dInDate as Date_In_Out , Item_In.iQuantitiy AS QIn_Out ;
	FROM ITM ;
		JOIN Item_In ON ITM.cItemCode = ITEM_IN.cItemCode ;
	UNION ALL SELECT ITM.cItemCode, ITM.cItemDesc, Item_Out.dDateOut as Date_In_Out, Item_Out.iQuantity * (-1) AS QIn_Out ;
	FROM ITM ;
		JOIN Item_Out ON ITM.cItemCode = ITEM_OUT.cItemCode ;
	ORDER BY 1, 3 ;
	INTO CURSOR csrTemp 
	
SELECT *, CAST(0 as I) AS iOnStock FROM csrTemp INTO CURSOR csrOnStock READWRITE 

SELECT csrOnStock

liAmount = 0
lcItemCode = cItemCode

SCAN
	IF cItemCode = lcItemCode
		liAmount = liAmount + QIn_Out 

	ELSE
		lcItemCode = cItemCode
		liAmount = QIn_Out

	ENDIF 

	Replace iOnStock WITH liAmount
	
ENDSCAN 	

BROWSE FIELDS cItemCode :H = "ItemCode", ;
	cItemDesc :H = "Description", ;
	dDate_In_Out : 15 :H = "Date", ;
	iQIn_Out :H = "In/Out", ;
	iOnStock :H = "On Stock"

CLOSE ALL
CLEAR ALL

RETURN

hth

marK

 

[wrgt9740]

Wow! This is a lot of new information for my limited foxpro knowledge. I'll need some time in analyzing this. Thank you for your help.

In case you'd like to know about the background, our company is still using a database program based on Clipper, and I'm trying to duplicate it with foxpro using my free time. What I could not duplicate so far is the programmer's method for this program. This is what he did, from what I understand more or less:

select tblReport
append blank
replace date with tblItems.date
do case
case Status = "I"
replace Incoming with Quantity
case Status = "O"
replace Outgoing with Quantity
end case

I couldn't copy how the programmer did this in foxpro. I failed using SQL SELECT, IIF, ICASE, and REPLACE methods.

Again, thank you for your code. I'll study it well.

 

[NasibKalsi]

try this

select tblItems

* ------------
set date ansi
set century on
copy to temp
* If you want to view just 1 item then
* copy to temp for item = "ballpen"

t_date = set("DATE")
use temp in 0 alias temp
select temp
index on ITEM + DTOC(date) + STATUS to temp
total on ITEM+DTOC(DATE) + STATUS to tottemp
use tottemp exclusive
browse fields ITEM, DATE, STATUS, QTY
*
* if you like to see the way you have shown then add the following fields
alter table tottemp add field INCOMING i(10)
alter table tottemp add field OUTGOING i(10)
replace all INCOMING with QTY for STATUS = "I"
replace all OUTGOING with QTY for STATUS = "O"
go top
brow field ITEM, DATE, INCOMING, OUTGOING

set date &t_date

nasib

 

[Mike Lewis]

Depending on which version of Clipper you were using, you might find that most of your code will run almost unchanged in FoxPro. Certainly the code you posted is valid in FoxPro except that you would need to make the following small modifications:

select tblReport
append blank
replace date with tblItems.date
do case
  case [highlight #FCE94F]tblItems.[/highlight]Status = "I"
    replace Incoming with [highlight #FCE94F]tblItems.[/highlight]Quantity
  case [highlight #FCE94F]tblItems.[/highlight]Status = "O"
    replace Outgoing with [highlight #FCE94F]tblItems.[/highlight]Quantity
[highlight #FCE94F]endcase[/highlight]

There are more concise ways of doing this in VFP, but try the above first.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

This code is almost 1:1 the same in Foxpro, just write ENDCASE in one word. You could do an SQL-Insert instead, but REPLACE is okay for setting just two fields of a new record.

Here's your code with comments (intentionally too verbose, just to explain everything, as I don't know what your knowledge deficits are). Better copy this over into the FoxPro editor, syntax coloring makes this readable:

* prerequisite: tblItems and tblReport are open (used or in the data environment of a form)
* in tblItems you're either scanning or are located on a specific record you want to process
select tblReport && make tblReport the currently selected workarea
append blank && add a new record into the table of the currently selected workarea (tblReport). 
* Fields in tblReport are filled with default values, typically empty date for date fields, 
* unless the table definition defines another default like DATE() for today.
replace date with tblItems.date && in the new record copy over current record tblItems.date into the tblReport.date field
do case
case Status = "I" && here I guess you need tblItems.Status, just Status means tblReport.Status
* or - if no tblReport.Status field exists - a variable Status.
replace Incoming with Quantity && Incoming is set. Again, Quantity might mean tblReport.Quantity or a variable.
case Status = "O"
replace Outgoing with Quantity
endcase

As we don't know your table structures and field names of both tables, it's guesswork where Status and Quantity are located. My guess is you want to copy over tblItems.Quantity into either tblReport.Incoming or tlRepot.Outgoing, depending on tblItems.Status.

Then you better do this:

select tblReport
append blank
replace date with tblItems.date
do case
case tblItems.Status = "I"
replace Incoming with tblItems.Quantity
case tblItems.Status = "O"
replace Outgoing with tblItems.Quantity
endcase

Or do an insert:

do case
case tblItems.Status = "I"
Insert Into tblReport (Date, Incoming) Values (tblItems.date, tblItems.Quantity)
case tblItems.Status = "O"
Insert Into tblReport (Date, Outgoing) Values (tblItems.date, tblItems.Quantity)
endcase

There are pros and cons: While REPLACES remain better readable even with many field/value pairs, an INSERT has the advantage of not needing an APPEND BLANK as a first step. It means more than you might imagine: It means double checks of table and field rules, double index updates, double triggers - first by APPEND BLANK and then by a REPLACE. Even worse when doing multiple replaces. APPEND can do mass processing with APPEND FROM a DBF, but you then first need to prepare that source DBF, ie with a SELECT. INSERTS can also use an SQL-SELECT as the source, SQL has more advantages, even though I wouldn't underestimate the value of readable code, especially when it comes to code maintenance or extending code.

Chriss

 

[wrgt9740]

Holy cow! This is a lot of information for me! I'm having a nose bleed!

Kidding aside, thank you guys for your contributions. This is a great opportunity for me to learn more about foxpro.

NasibKalsi, thanks for sharing your code. I'll have to read up on some commands there that I'm not familiar yet.

Mike Lewis, you're right, some clipper codes of the programmer are similar to foxpro. Reading on how to program in foxpro helped me in understanding the clipper codes, which i was not able to when i opened the PRG files for the first time.

Chris Miller, your guess is correct in that "you want to copy over tblItems.Quantity into either tblReport.Incoming or tlRepot.Outgoing, depending on tblItems.Status." I was also able to use the INSERT INTO ...; SELECT ...; FROM ... sql method in another part of my project instead of using the programmer's APPEND BLANK ... REPLACE ... method. I found the sql method somewhat easier to use.

I sure hope I'll be able to create my own (and a better) version of the company's database program soon. I think I'm making progress with foxpro. I tried to learn Java and Visual Basic before, but I had a hard time understanding them.

 

[Mike Lewis]

Just to add one further bit of advice. Given that you want to improve the existing system (rather than simply port it out of Clipper and into VFP), the key area to work on is the design of the database, that is, the layout of the tables, the relationships between them, the use of indexes and primary keys, and so on.

I've had a lot of experience in improving and modernising old applications, and the weak link is invariably the database. I appreciate that re-designing the database might be too big a job right now, especially as it would involve extensive changes to the code, but if you have the time (and energy) available, that would be the best place to start.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi,

In addition and just for fun

PUBLIC go_Form 

go_Form = CreateObject("frmForm")
go_Form.Visible = .T.
go_Form.Show

READ Events

CLOSE ALL
CLEAR ALL

*****

DEFINE CLASS frmForm As Form
	DIMENSION aItems[1]
	cItemCode = ""

	Height = 360
	Width = 780
	MinWidth = This.Width
	MinHeight = This.Height
	MaxButton = .F.
	MinButton = .F.
	AutoCenter = .T.
	Themes = .F.
	
	ADD OBJECT cboItems as Combobox WITH ;
		Top = 18, ;
		Left = 12, ;
		Height = 24, ;
		Width = 180, ;
		Style = 2, ;
		ColumnCount = 2, ;
		ColumnWidths = "150, 0"
		
		PROCEDURE cboItems.Init()
			Select cItemDesc, cItemCode FROM ITM ;
				ORDER BY 1 ;
				INTO ARRAY ThisForm.aItems
			
			DIMENSION ThisForm.aItems[ALEN(ThisForm.aItems, 1) + 1, 2]
			
			AINS(ThisForm.aItems, 1)			
			
			ThisForm.aItems[1, 1] = "All"
			ThisForm.aItems[1, 2] = "ZZZZZ"
			
			WITH This
				.RowSourceType = 5
				.RowSource = "ThisForm.aItems"
				.Requery()
				
			ENDWITH

			This.Value = ThisForm.aItems[1,1]
			
		ENDPROC 
		
		PROCEDURE cboItems.LostFocus()
			ThisForm.cItemCode = ALLTRIM(This.List[This.ListIndex,2])

		ENDPROC
		
		PROCEDURE cboItems.InterActiveChange()
			This.LostFocus()
				
		ENDPROC 
	
	ADD OBJECT pgfBuySell as PageFrame WITH ;
		Top = 60, ;
		Left = 12, ;
		Width = ThisForm.Width - 24, ;
		Height = ThisForm.Height - 72, ;
		Anchor = 15, ;
		PageCount = 4
		
		PROCEDURE pgfBuySell.Init()
			LOCAL loPage, loFrame
			
			FOR I = 1 TO This.pageCount
				loPage = This.Pages(i)
				loPage.AddObject("grdBuySell","grdBase")
				loPage.Caption = ICASE(i = 1, "Bought", i = 2, "Sold", i = 3, "Bought/Sold", "Stock")

			ENDFOR 
		ENDPROC 
		
	PROCEDURE POneClick()
		SELECT ITM.cItemCode, ITM.cItemDesc, Item_In.dInDate , Item_In.iQuantity ;
			FROM ITM ;
				JOIN Item_In ON ITM.cItemCode = ITEM_IN.cItemCode ;
			WHERE IIF(ThisForm.cItemCode = "ZZZZZ", .T., ITM.cItemCode = ThisForm.cItemCode) ;
			ORDER BY 1, 3 ;
			INTO CURSOR csrTemp
			
		IF _Tally > 0 

			LOCATE 
				
			WITH This.pgfBuySell.Page1.grdBuySell
				.ColumnCount = -1
				.RecordSource = "csrTemp"
				.Visible = .T.
				
				.Column1.Header1.Caption = "Code"
				.Column1.Width = 60
				.Column2.Header1.Caption = "Description"
				.Column2.Width = 90
				.Column3.Header1.Caption = "Date"
				.Column3.Width = 78
				.Column4.Header1.Caption = "Units"
				.Column4.Width = 60
			ENDWITH 
		ELSE
			WITH This.pgfBuySell.Page1.grdBuySell
				.Visible = .F.
			ENDWITH 
			
			= MessageBox("Nothing Bought", 16, "Buy", 3000)
		ENDIF 

		This.Refresh()

	ENDPROC 
	
	PROCEDURE PTwoClick()
		SELECT ITM.cItemCode, ITM.cItemDesc, Item_Out.dDateOut, Item_Out.iQuantity * (-1) ;
			FROM ITM ;
				JOIN Item_Out ON ITM.cItemCode = ITEM_OUT.cItemCode ;
			WHERE IIF(ThisForm.cItemCode = "ZZZZZ", .T., ITM.cItemCode = ThisForm.cItemCode) ;
			ORDER BY 1, 3 ;
			INTO CURSOR csrTemp 
			
		IF _Tally > 0
			
			LOCATE 

			WITH This.pgfBuySell.Page2.grdBuySell
				.ColumnCount = -1
				.RecordSource = "csrTemp"
				.Visible = .T.
				
				.Column1.Header1.Caption = "Code"
				.Column1.Width = 60
				.Column2.Header1.Caption = "Description"
				.Column2.Width = 90
				.Column3.Header1.Caption = "Date"
				.Column3.Width = 78
				.Column4.Header1.Caption = "Units"
				.Column4.Width = 60
			ENDWITH 
		ELSE
			WITH This.pgfBuySell.Page2.grdBuySell
				.Visible = .F.
			ENDWITH 
			
			= MessageBox("Nothing Sold", 16, "Sell", 3000)
		ENDIF 

		This.Refresh()
	ENDPROC 
	
	
	PROCEDURE PThreeClick()
		LOCAL liAmount, lcItemCode
		
		SELECT ITM.cItemCode, ITM.cItemDesc, Item_In.dInDate as dDate_In_Out , Item_In.iQuantity AS iQIn_Out ;
				FROM ITM ;
					JOIN Item_In ON ITM.cItemCode = ITEM_IN.cItemCode ;
			WHERE IIF(ThisForm.cItemCode = "ZZZZZ", .T., ITM.cItemCode = ThisForm.cItemCode) ;
			UNION ALL ;
				SELECT ITM.cItemCode, ITM.cItemDesc, Item_Out.dDateOut as dDate_In_Out, Item_Out.iQuantity * (-1) AS iQIn_Out ;
				FROM ITM ;
					JOIN Item_Out ON ITM.cItemCode = ITEM_OUT.cItemCode ;
			WHERE IIF(ThisForm.cItemCode = "ZZZZZ", .T., ITM.cItemCode = ThisForm.cItemCode) ;
				ORDER BY 1, 3 ;
				INTO CURSOR csrTemp 
				
		IF _Tally > 0
				
			SELECT *, CAST(0 as I) AS iOnStock FROM csrTemp INTO CURSOR csrOnStock READWRITE 

			SELECT csrOnStock

			liAmount = 0
			lcItemCode = cItemCode

			SCAN
				IF cItemCode = lcItemCode
					liAmount = liAmount + iQIn_Out 

				ELSE
					lcItemCode = cItemCode
					liAmount = iQIn_Out

				ENDIF 

				Replace iOnStock WITH liAmount
					
			ENDSCAN

			LOCATE  	

			WITH This.pgfBuySell.Page3.grdBuySell
				.ColumnCount = -1
				.RecordSource = "csrOnStock"
				.Visible = .T.
				
				.Column1.Header1.Caption = "Code"
				.Column1.Width = 60
				.Column2.Header1.Caption = "Description"
				.Column2.Width = 90
				.Column3.Header1.Caption = "Date"
				.Column3.Width = 78
				.Column4.Header1.Caption = "Units"
				.Column4.Width = 60
				.Column5.Header1.Caption = "OnStock"
				.Column5.Width = 60

			ENDWITH 
		ELSE
			WITH This.pgfBuySell.Page3.grdBuySell
				.Visible = .F.
			ENDWITH 
			
			= MessageBox("Nothing Bought/Sold", 16, "Buy/Sell", 3000)
		ENDIF 

		This.Refresh()
	ENDPROC 	
			
	PROCEDURE PFourClick()
		SELECT ITM.cItemCode, ITM.cItemDesc, Item_In.iQuantity AS iQIn_Out ;
				FROM ITM ;
					JOIN Item_In ON ITM.cItemCode = ITEM_IN.cItemCode ;
			UNION ALL ;
				SELECT ITM.cItemCode, ITM.cItemDesc, Item_Out.iQuantity * (-1) AS iQIn_Out ;
				FROM ITM ;
					JOIN Item_Out ON ITM.cItemCode = ITEM_OUT.cItemCode ;
				ORDER BY 1 ;
				INTO CURSOR csrTemp 
				
		SELECT cItemCode, cItemDesc, SUM(iQIn_Out) ;
			FROM csrTemp ;
			GROUP BY 1, 2 ;
			ORDER BY 1 ;
			INTO CURSOR csrTable

		LOCATE  	

		WITH This.pgfBuySell.Page4.grdBuySell
			.ColumnCount = -1
			.RecordSource = "csrTable"
			.Visible = .T.
			
			.Column1.Header1.Caption = "Code"
			.Column1.Width = 60
			.Column2.Header1.Caption = "Description"
			.Column2.Width = 90
			.Column3.Header1.Caption = "OnStock"
			.Column3.Width = 60

		ENDWITH 

		This.Refresh()
	ENDPROC 
	
	PROCEDURE Init()
		BINDEVENT(This.pgfBuySell.Page1,"Click",This,"POneClick")
		BINDEVENT(This.pgfBuySell.Page2,"Click",This,"PTwoClick")
		BINDEVENT(This.pgfBuySell.Page3,"Click",This,"PThreeClick")
		BINDEVENT(This.pgfBuySell.Page4,"Click",This,"PFourClick")

	ENDPROC 
	
	PROCEDURE Load()
		CREATE CURSOR ITM (cItemCode C(5), cItemDesc c(20))

			INSERT INTO ITM VALUES ("AAAAA", "Ballpen")
			INSERT INTO ITM VALUES ("AAAAB", "Ink")
			INSERT INTO ITM VALUES ("AAAAC", "Paper")
			INSERT INTO ITM VALUES ("AAAAD", "Chalk")
			INSERT INTO ITM VALUES ("AAAAE", "Pencil")

		CREATE CURSOR ITEM_IN (cItemCode C(5), dInDate D, iQuantity I)

			INSERT INTO ITEM_IN VALUES ("AAAAA", {^2020-12-01}, 72)
			INSERT INTO ITEM_IN VALUES ("AAAAA", {^2020-12-02}, 12)
			INSERT INTO ITEM_IN VALUES ("AAAAB", {^2020-12-03}, 38)
			INSERT INTO ITEM_IN VALUES ("AAAAB", {^2020-12-04}, 18)
			INSERT INTO ITEM_IN VALUES ("AAAAB", {^2020-12-05}, 3)
			INSERT INTO ITEM_IN VALUES ("AAAAC", {^2017-12-03}, 45)
			INSERT INTO ITEM_IN VALUES ("AAAAC", {^2020-12-04}, 32)
			INSERT INTO ITEM_IN VALUES ("AAAAD", {^2020-12-04}, 23)

		CREATE CURSOR ITEM_OUT (cItemCode C(5), dDateOut D, iQuantity I) 

			INSERT INTO ITEM_OUT VALUES ("AAAAA", {^2020-12-04}, 12)   
			INSERT INTO ITEM_OUT VALUES ("AAAAA", {^2020-12-05}, 11)   
			INSERT INTO ITEM_OUT VALUES ("AAAAA", {^2020-12-06}, 7)   
			INSERT INTO ITEM_OUT VALUES ("AAAAA", {^2020-12-07}, 3)   
			INSERT INTO ITEM_OUT VALUES ("AAAAA", {^2020-12-08}, 14)   
			INSERT INTO ITEM_OUT VALUES ("AAAAB", {^2018-03-16}, 10)   
			INSERT INTO ITEM_OUT VALUES ("AAAAB", {^2018-03-15}, 9)   
			INSERT INTO ITEM_OUT VALUES ("AAAAC", {^2018-04-20}, 11)
	
	ENDPROC 
		 
	PROCEDURE Destroy()
		CLEAR Events
		ThisForm.Release
	ENDPROC
ENDDEFINE

DEFINE CLASS grdBase AS Grid
		Top = 12
		Left = 12
		Height = 240
		Width = 732
		RowHeight = 24
		BackColor = RGB(0, 250, 250)
		AllowRowSizing = .F.
		HeaderHeight = 21
		AllowHeaderSizing = .F.
		DeleteMark = .F.
		Anchor = 15
		Visible = .F.

ENDDEFINE 
*****

hth

marK

 

[Chris Miller]

On the topic of SQL vs xBase code, queries of data are easier to port to an SQL backend, very obviously, but even when the backend changes to a server only working in its SQL version, you're working with cursors that are able to feedback changes to the source tables and can choose to work as usual on the VFP side cursors, ie usual data binding, changing data in cursors via xBASE code, using concepts like relations, master/slave grids, etc. That code can stay untouched, which means that logic within forms, which previously processed data in DBFs and now cursors can stay as is, if you make it right, the step to update the backend is just an extra TABLEUPDATE() call per table to update, sometimes nested, in the right order to not violate referential integrity and within a manual transaction.

So it's not unusual to find a mix of xBASE (or Clipper) with SQL. As long as you only change states of the cursor, as that only is about the in-memory frontend side representation of the data. The SQL necessary for updating the real backend is generated by TABLEUPDATE() on a record by record basis with the help of some information you give about field mapping, keyfields, etc.

Chriss

 

[wrgt9740]

Hi everyone. I've been playing around with this problem, and I've found three solutions (sounds like I'm over-analyzing).

First solution is to create a cursor, update, then transfer data to tblReport:
CREATE CURSOR csr(date, item, qty, status, incoming, outgoing)
INSERT INTO csr (date, item, qty, status) ;
SELECT date, qty, status ;
FROM tblItems ;
WHERE item = 'ballpen'

UPDATE csr ;
SET incoming = qty ;
FROM csr ;
WHERE status = 'I'
UPDATE csr ;
SET outgoing = qty ;
FROM csr ;
WHERE status = 'O'

INSERT INTO tblReport ;
SELECT date, incoming, outgoing ;
FROM csr

Second solution is to use scan, append blank, and replace:
SCAN
IF item = 'ballpen'
SELECT tblReport
APPEND BLANK
REPLACE date WITH tblItems.date

DO CASE
CASE tblItems.status = 'I'
REPLACE incoming with tblItems.qty
CASE tblItems.status = 'O'
REPLACE outgoing with tblItems.qty
ENDCASE
ENDSCAN

Third solution is to add column, update, then delete column of tblReport:
ALTER TABLE tblReport ADD COLUMN status, qty
INSERT INTO tblReport(date, status, qty) ;
SELECT date, status, qty ;
FROM tblItems ;
WHERE item = 'ballpen'

UPDATE tblReport ;
SET incoming = qty FROM tblReport WHERE status = 'I'
UPDATE tblReport ;
SET outgoing = qty FROM tblReport WHERE status = 'O'

ALTER TABLE tblReport DROP COLUMN status
ALTER TABLE tblReport DROP COLUMN qty

I haven't studied mjcmkrsr's code yet to see if I could come up with a more efficient solution.

Mike Lewis, my goal for this project is to learn how the company's database work, and learn foxpro along the way. But in the future if I'm forced to upgrade from foxpro, what programming language should I go to?

Chris Miller, from what I understand, it would be better to with the cursor solution? And use SQL code as much as possible?

 

[Chris Miller]

I'd not use the third option, as ALTER TABLE creates new files and copies over all data, this will be the least effective.

The cursor solution (1) is your best, but looking back to one of your previous posts I can show you how to use VFPs IIF(). You could also use ICASE(), but only need it for more than 2 cases.

Insert Into tblReport (Date, ;
                             Incoming, ;
                             Outgoing) ;
Select Date, ;
   IIF(status='I', Quantity,0), ;
   IIF(status='O', Quantity,0), ;
   From tblItmes WHERE item='ballpen'

There's an issue, if you're exacting: The other field isn't getting its default value, if it's NULL. But as that's the single statement solution I'd be pragmatic about this. If the default is NULL for those fields, simply put it in there.

And assuming tblReport also has an item column, you don't even need the WHERE clause, just add item to both the INSERT field list and the SELECT.

Chriss

 

[mjcmkrsr]

Hi,

1) Chris already showed how to optimize the first (and best) solution

2) If you'd adopt the second solution you might want to change the syntax a little bit. In addition, please DON'T use reserved words for field names (e.g. Date, ... check under reserved words in the help file)

*!*	Make sure both tables are open

SCAN FOR tblItems.Item = "Ballpen"
	APPEND BLANK IN tblReport
	REPLACE tblReport.date WITH tblItems.date

	DO CASE
		CASE tblItems.status = 'I'
			REPLACE tblReport.Incoming with tblItems.qty
		
		CASE tblItems.status = 'O'
			REPLACE tblReport.Outgoing with tblItems.qty
	
	ENDCASE
ENDSCAN

There is however a gotcha with both approaches - each time you'll run them you'll append/insert ALL the data for "Ballpen" from tblItems. If that's not what you want you may want to make sure to only append the latest data by filtering them - e.g. with a logical UpdatedStatus field in tblItems.

hth

marK

 

[wrgt9740]

Chris Miller, I didn't know ALTER TABLE does that. Also, I tried inserting IIF() and ICASE() in my first solution, but an error "command contains unrecognized phrase/keyword" keeps coming up. It's all right if I don't fix this, as my first solution is working fine.

mjcmkrsr, I won't be appending/inserting to tblReport that would make it quite big. Every time I run this program, tblReport will be zapped, then data will be inserted. By the way, the programmer might not have known about not using reserved words.

 

[mjcmkrsr]

Hi,

Every time I run this program, tblReport will be zapped

May I ask why you're using a table then? Creating a CURSOR e.g. csrReport instead of the tblReport (see code below) would be faster and less risky.

From Hacker's Guide to Visual Foxpro 7.0 by T. Granor, T. Roche et al

ZAP

This may be the single most dangerous command in all of FoxPro, but it's incredibly handy when you're working interactively. ZAP permanently removes all records from a table. The name supposedly stands for "Zero And Pack." ZAPped records cannot be recalled.

Usage
ZAP [ IN cAlias | nWorkArea ]

Although you can ZAP in a work area other than the current one, we really don't recommend it. Using this command by itself is like striking matches; using it in another work area is like striking matches in a gas station.

ZAP is not a good database citizen. It doesn't call the Delete trigger for a table in a database. Instead, it neatly avoids all the work you've done to make sure the integrity of your database is maintained. This is a major flaw.

Due to the above and for lots of other reasons, never use ZAP in a program. It's just too risky. See PACK for suggestions on alternatives. Actually, the one case where ZAP is acceptable is when you're working on a temporary file in the first place, so no permanent data is at risk. In particular, it's a great way to re-create a cursor without losing the grid that's based on it. Simply ZAP and APPEND FROM your data instead of doing a SELECT.

So why do we think it's incredibly handy? When you're manipulating data manually—perhaps parsing older data to create a normalized database—it's clean and simple to ZAP the target table between tests. Outside this kind of situation, we strongly recommend you avoid ZAP.

ZAP does respect SET SAFETY, so if you have it on, you are warned before you throw all your data in the garbage can.

*!* Create csrItems to simulate your tblItems

CREATE CURSOR csrITEMS (cItemName C(20), dDate D, cStatus C(1), iQuantity I)

		INSERT INTO csrITEMS VALUES ("Ballpen", {^2020-12-01}, "I", 2)
		INSERT INTO csrITEMS VALUES ("Ballpen", {^2020-12-02}, "O", 12)
		INSERT INTO csrITEMS VALUES ("Ballpen", {^2020-12-03}, "I", 138)
		INSERT INTO csrITEMS VALUES ("BallPen", {^2020-12-04}, "O", 18)
		INSERT INTO csrITEMS VALUES ("BallPEN", {^2020-12-05}, "O", 3)
		INSERT INTO csrITEMS VALUES ("Paper", {^2017-12-03}, "I", 45)
		INSERT INTO csrITEMS VALUES ("Paper", {^2020-12-04}, "O", 32)
		INSERT INTO csrITEMS VALUES ("BALLPEN", {^2020-12-04}, "I", 23)

*!* Now create cursor csrReport

CREATE CURSOR csrReport (cItemName C(20), dDate D, iIncoming I, iOutGoing I)

INSERT INTO csrReport (cItemName, dDate, iIncoming, iOutgoing) ;
	Select cItemName, dDate, ;
		IIF(cStatus='I', iQuantity, CAST(0 as I)), ;
		IIF(cStatus='O', iQuantity, CAST(0 as I)) ;
	From csrItems ;
		WHERE UPPER(cItemName) = "BALLPEN" ;
		ORDER BY 2

BROWSE FIELDS ;
	cItemname :H = "Item", ;
	dDate :H = "Date", ;
	iIncoming :H = "Incoming", ;
	iOutgoing :H = "Outgoing"

*!* Close csrReport and have it vanish in the haze

Use in csrReport

*!* Now browse csrItems - tblItems in your case

select csrItems

BROWSE

CLOSE ALL

hth

marK

 

[wrgt9740]

mjcmkrsr, the programmer treated tblReport as a temporary table, where every time a user wanted to make a report, records from tblReport will be deleted and new data will be populated, so zapping tblReport is not actually risky. I realized after reading your post, I don't have to be limited by tblReport and other temporary tables of the programmer, and your suggestion of using csrReport sounds like a good idea.

In your code, what is CAST(0 as I) for?

I reviewed my code on implementing IIF, and there was a missing comma. Now a new error comes up: "mismatch data type." I'll take a more in-depth look into this on my next free time.

 

[mjcmkrsr]

Hi,

1) Since VFP-SQL uses the first found record to determine the data type and size of the column, CAST() makes sure that the type (in this case I for Integer) and size are right. You may want to check by running the code without the CAST() function and see the difference

2)I usually use the UPPER() function when filtering since I never know how the user typed in the item (e.g. ballpen or Ballpen or BallPen or BALLPen or ...). Again you may check by removing the UPPER() function


hth

marK

 

[mjcmkrsr]

Hi

so zapping tblReport is not actually risky.

Yes it is! Imagine you're working on the tblReport and another user ZAPs it to use it for UPPER(Item) = "PAPER". The least you get is an error - the worst a corrupt table! CURSOR is the way to go.

from Hacker's Guide to Visual Foxpro 7.0 by T. Granor, T. Roche et al

Create Cursor

This command lets you create a temporary table. Cursors created this way are read-write (like those created for views, but unlike those created by SELECT-SQL without the READWRITE clause). Cursors are not part of a database, but can have several features normally found only in database-contained tables.

Usage
CREATE CURSOR Alias
( Fieldname1 Fieldtype1 [( nSize1 [ , nDecimals1 ] )
[ NULL | NOT NULL ]
[ CHECK lFieldRule1 [ ERROR cRuleText1 ] ]
[ DEFAULT eDefault1 ]
[ UNIQUE ]
[ NOCPTRANS ] ]
[ , Fieldname2 ... ] )
| FROM ARRAY aFieldArray

Parameter
Value
Meaning

Alias
Name
The alias to assign to the newly created cursor. [highlight #FCE94F]This is not a file name and does not have to be unique across users.[/highlight]

Fieldnamex
Name
The name of the xth field in the cursor.

Fieldtypex
Single letter
The letter denoting the type of the xth field in the cursor. See Help for a list.

nSizex
Numeric
The size of the xth field.

nDecimalsx
Numeric
The number of decimal places in the xth field.

lFieldRulex
Logical
The field-level rule for the xth field.

cRuleTextx
Character
The error message to use when the field-level rule for the xth field is violated.

eDefaultx
Expression
An expression that evaluates to the default value for the xth field.

aFieldArray
Array
An array containing definition information for the cursor.


Cursors are really handy when you just need to work with some data temporarily, then throw it out. You don't have to worry about unique names or about erasing files. Create the cursor, populate it, use it, and then close it, at which point it goes away as if it had never been. You can change the data in cursors created with CREATE CURSOR, so they're good for tasks like grabbing a set of records and letting a user mark those to be printed. The original data is left untouched, but you get what you need.

...

hth

marK

 

[wrgt9740]

ok, you've convinced me to use cursors instead of tables. corrupted tables scare me.

this hacker's guide to visual foxpro seems interesting. maybe I need to read it.

 

[Mike Lewis]

this hacker's guide to visual foxpro seems interesting. maybe I need to read it.

Yes, it is interesting. And yes, you need to read it (or, at least, refer to it from time to time). I have my copy on my desk in front of me as I type this.

The most recent version is for VFP 7.0, so it is now out of date. Despite that, it is still very useful. The book is out of print (copies are sometimes available from Amazon and elsewhere), but there is an on-line version at

https://hackfox.github.io/.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads