How to compute for total price in a grid? 3

[Mandy_crw]

Hi everyone... I have form done in designer, i created a grid (source is a cursor) that has unit price, quantity and total price... the unit price is read only column, then the quantity is being inputted by the user. In column5 i want to have the product (unit price * qty) to be displayed as the user inputs the quantity... I have been figuring it out but seems no luck... Please help... thanks....

 

[mjcmkrsr]

Hi Mandy,

You'll have to update the cursor and refresh you grid - have a look at the code snippet below.

CREATE CURSOR csrTotalSales (cName C(15), iSales I, nPrice N(10,2))

INSERT INTO csrTotalSales (cName, iSales, nPrice) VALUES ("Greg" , 45, 4.22)
INSERT INTO csrTotalSales (cName, iSales, nPrice) VALUES ("Sophie" , 20, 5.33)
INSERT INTO csrTotalSales (cName, iSales, nPrice) VALUES ("Stella" , 15, 6.78)
INSERT INTO csrTotalSales (cName, iSales, nPrice) VALUES ("John" , 30, 10.25)
INSERT INTO csrTotalSales (cName, iSales, nPrice) VALUES ("Johnny" , 30, 11.58)
INSERT INTO csrTotalSales (cName, iSales, nPrice) VALUES ("Jenny" , 20, 56.00)

SELECT *, CAST(iSales * nPrice as N(10,2)) as nTotalPrice FROM csrTotalSales INTO CURSOR csrUpdateSales

BROWSE 

*!* Now change the sales figures for Stella

UPDATE csrTotalSales SET iSales = 25 WHERE cName = "Stella"

*!*	... and update the cursor

SELECT *, CAST(iSales * nPrice as N(10,2)) as nTotalPrice FROM csrTotalSales INTO CURSOR csrUpdateSales

BROWSE 

CLOSE ALL

hth

MarK

 

[Chris Miller]

I don't know if MarK's answer really helps you.

You can use the interactivechange event of the text1 textbox in the grid column of the qty field and update the total column there.

Just a 1 liner:
REPLACE total WITH This.value*price

Chriss

 

[Mike Lewis]

Hi Mandy,

As you know, a grid basically displays what is in its underlying cursor. So, if you want it to display the total price, you should create a field for the total price in the cursor, then make that the control source of the relevant column.

Then, in the InteractiveChange of the quantity column, update the total price in the cursor:

[tt]SELECT csrCursor
REPLACE TotalPrice WITH UnitPrice * Quantity[/tt]

That will place the correct value in the cursor. You now just need to set focus to grid to make the new value visible.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mandy_crw]

Hi Mike and Chriss... it worked, but the only thing is that i need to input the qty twice for the correct answer to come out... suppose I input 2 in the qty, the first answer is zero, when I input 2 again the secon time, the right answer comes out... in the database where grid is populated there is an intial value of zero, is this the reason? if so im trying to remove the zero in the table but i cannot remove it... What could be causing the problem? Thanks in advance...

 

[Chris Miller]

As mike already said, setting focus to the grid would help to update the display after the replace updates the data. So

This.Parent.Setfocus() in th text1.interactivechange or generally thisform.gridname.setfocus() anywhere else you do update data and want to display that change.

Changing data alone in some cases causes the display to update at the same time, but it's not guaranteed. And that is what cases that delay. It's not just a delay of fractions of a second it's a delay that needs the second interaction. Well, another hurdle is that the value of quantity only changes once you leave the cell, because the value only becomes the data of the controlsource field if the valid event says it's valid and that only happens when you move away. That's why in this case I recommended THIS.VALUE*price and not quantity*price. Quantity is still the old quantity (0).

Chriss

 

[Mike Lewis]

the value of quantity only changes once you leave the cell

That's correct. I didn't think of that.

So, for that reason, perhaps the REPLACE should be done in the textbox's LostFocus rather than the InteractiveChange. The reason I didn't suggest that before was that the LostFocus would also fire if the user simply clicks or tabs anywhere in the grid, without necessarily changing a value. But that might not be a serious problem.

I;m not sure about this, Mandy. You might need to experiment.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi,

You definitely should place the update/replace command in the LOSTFOCUS() event of the Textbox. You may play around with the code below, replace LOSTFOCUS with INTERACTIVECHANGE in the textbox class and see how that works - and sometimes not.

PUBLIC goForm1

goForm1=NEWOBJECT("form1")
goForm1.Show

Read Events

Close all
Clear All

RETURN


**************************************************

DEFINE CLASS form1 AS form
	AutoCenter = .T.
	Caption = "Grid with calculated columns"
	MinHeight = This.Height
	MinWidth = This.Width
	MaxWidth = This.Width
	Themes = .F. 
 
	ADD OBJECT grid1 AS grid WITH ;
		ColumnCount = 3, ;
		Left = 12, ;
		Top = 36, ;
		Width = ThisForm.Width - 24, ;
		Height = ThisForm.Height - 48, ;
		BackColor = RGB(210, 210, 210), ;
		Anchor = 15
		
		PROCEDURE grid1.Init
		
			UPDATE csrData SET f4 = f1 * f3
			
			LOCATE 

			This.RecordSource = "csrData"
			
			 WITH This.Column1
			  .Header1.Caption = "F1"
			  .ControlSource = "F1"
			  .AddObject("txtF1", "txtTextBox")
			  .CurrentControl = "txtF1"
			  .txtF1.Visible = .T.
			  .Sparse = .F.
			 ENDWITH

			 WITH This.Column2
			  .Header1.Caption = "F3"
			  .ControlSource = "F3"
			  .BackColor = RGB(210, 210, 210)
			  .ReadOnly = .T.
			 ENDWITH

			 WITH This.Column3
			  .Header1.Caption = "F1 * F3"
			  .ControlSource = "F4"
			  .BackColor = RGB(210, 210, 210)
			  .ReadOnly = .T.
			 ENDWITH
		 ENDPROC 
		 
PROCEDURE Destroy
	CLOSE ALL
	Clear Events

ENDPROC

PROCEDURE Load
	LOCAL i

	CREATE CURSOR csrData (f1 I, f3 I, f4 I)

	FOR i = 1 TO 500
		INSERT INTO csrData VALUES ( Int(Rand()*101), Int(Rand()*99), 0)
	ENDFOR
	
ENDPROC

ENDDEFINE

*********************************************

DEFINE CLASS txtTextBox as TextBox

	BackColor = RGB(0, 210, 210)
	
	PROCEDURE LostFocus()
*!*		You have different choices to update - all three approaches work

*!*			LOCAL liRecNo
*!*			
*!*			liRecNo = RECNO()
*!*			
*!*			UPDATE csrData SET f4 = f1 * f3 WHERE RECNO() = liRecNo 

*!*			Replace f4 WITH f1 * f3 && also works
		
		Replace f4 with This.Value * f3 && also works
			
	ENDPROC 
ENDDEFINE 

*********************************************

MarK

 

[Mandy_crw]

Thanks Mark... My form is designed in the designer.. so basically it already done... I followed the advises of Mike and Chriss...but anyway.. thank you so much Mark... God bless...

 

[Chris Miller]

Mandy,

I think MarK wanted mainly to showcase the options you have in Lostfocus, so just this section of his code (which you could put into your designer form, too):

	PROCEDURE LostFocus()
*!*		You have different choices to update - all three approaches work

*!*			LOCAL liRecNo
*!*			
*!*			liRecNo = RECNO()
*!*			
*!*			UPDATE csrData SET f4 = f1 * f3 WHERE RECNO() = liRecNo 

*!*			Replace f4 WITH f1 * f3 && also works
		
		Replace f4 with This.Value * f3 && also works
			
	ENDPROC

I was saying:

the value only becomes the data of the controlsource field if the valid event says it's valid and that only happens when you move away

I don't know if MarK was still trying to also prove to me that it's not necessary to insist on THIS.VALUE and compute with the field value. Well, the varieties taking f1 and f3 for the calculations work in lostfocus, as it happens after valid and after the field is updated. The reason I ask for using THIS.VALUE is as in this case it's the most interactive way of showing to the user the total is computed from quantity*price, while you type in the quantity already. That might cause multiple updates, if your quantity is >10 or even >100, but it's a nice feedback to the user he can see before hitting enter or leaving the quantity cell.

And while it goes against the notion to mainly compute with the data and not control.value. It's an exception to the rule here because the control.value only gets to the field late, after valid. And notice I talked about programming in the Interactivechange event, which is when the field still does not have the value stored.

To me, it's a matter of knowing how and when to program against rules of thumbs instead of making rules so strict, that you avoid thinking about cases when bending or even breaking rules is a good alternative. And here it is, even though I just recently was quite strict towards sashaDG in his thread about adding new data directly in the table/alias/cursor he targets instead of letting that go through the control.value (in

https://www.tek-tips.com/viewthread.cfm?qid=1818610).

There's more to Sahsha's case as it includes buffering. And you can step on your own foot, if you add the buffering a control does to values on top of the workarea buffering mode.

In essence, I would never establish strict rules just to always have black/white or good/bad decisions about what you do and program faster by not thinking through all possibilities you have and pick the best one. It may make you a faster programmer but not allow the full potential. So never stop rethinking about things, even if you think you already exhausted all possibilities and condensed them into your rules you then strictly follow.

There are surely pros and cons to anything. And you could object that computing a total only makes sense after the quantity is fully entered. I think it's neat you already can see the total while you type the quantity.

Chriss

 

[Chris Miller]

There's yet another aspect to it.

A question to you, Mandy: Do you only display this total in the grid, or is it also stored into a table, eventually?

Because there are rules about data design about normalization and redundancy you can also bend or break for good reasons, but I don't see a good reason to save a total. This only introduces the chance to have a modified total that ends up not being price*qty by error.

It also has a pro: It allows total<price*qty if that's intended by rebates, but that's also possible to store separately as rebate amount or percentage by the offers valid at that time, so you also store that as information to check against later and verify data integrity. And that's simpler if you don't just store the effective total including the rebate. So even thinking of such a use case, I'd never store the total, but compute it in reports, the printing of bills or receipts or delivery slips. So there you also have a reason to not do it, even though it could be differing from price*qty, you'd rather generate total as qty*price-rebateamount or qty*price*(1-rebatpercent/100), for example. rebates can have different rules, also like 3 for 2 or minus the cheapest of 4 or whatever, which all are easy to store with an absolute rebateamount.

It's fully okay to display the total, of course. No question, there.

Chriss

 

[mjcmkrsr]

Hi Chris,

I don't know if MarK was still trying to also prove to me that it's not necessary to insist on THIS.VALUE and compute with the field value.

I did not and do not want to prove anything to you. I just wanted to state that all three options work after LOSTFOCUS and only the last option (This.Value) works in INTERACTIVECHANGE.

1) That might cause multiple updates, if your quantity is >10 or even >100, 2) but it's a nice feedback to the user he can see before hitting enter or leaving the quantity cell.

ad 1) True. ad 2) It's not a nice fb - it is irritating since most other programs like EXCEL or MSWORD (needs to hit the F9 key) or OpenOffice require some kind of confirmation (ENTER, TAB ...) before they calculate.

MarK

 

[Chris Miller]

That's okay, MarK, I also didn't feel offended and didn't intend to overrule anything. Just add to the learnings and discussion.

It's a matter of taste, actually, whether direct calculations are a nice feedback or not. I don't make the working of other programs ruling over what I intend to show. I think it's nice, in case it's not just something you buy at low quantities like mostly 1 but by unit, say grams of oz, then you can play with it without needing to leave and reenter.

It truly is a matter of taste what you do. But it surely is a showcase for when this.value actually plays a role in interactivechange.

Indeed, if there is a change not coming from the user, you want to compute this, too. So programmaticchange should do it, too. And if you come to think of that, even lostfocus might not happen in that case, as a programaticchange doesn't need to focus a control, thus there are doesn't need to happen a lostfocus.

Last not least a change can happen on the level of the fields without using the grid at all, and that's a final reason to only display the total instead of also storing it, as you can have code setting a qty without updating the total and then you'd have a wrong total.

It's also a good reason to initialize fields with qty 1 and total=price, but not the end of all cases.

Chriss