I get 0 when I use calculate sum(field)

[rundee]

Hi All,

I'm new in VFP and I would like to seek help from all experts out there. My question is why I do get 0 when using the following code:
SELECT INVDATA
CALCULATE SUM(qty_in) TO thisform.totalqty.value for itemcode=thisform.itemno.Value
thisform.totalqty.value=thisform.totalqty.value

Please help

Thank you in advance!

Rundee

 

[JRB-Bldr]

There are a lot of unknowns (at least for us) about your question.

Let's begin with just three of those 'unknowns'...

1. You run this code Where within your VFP Form (what Form Event)?

2. Have you put a code line SET STEP ON immediately after the SUM line so that your code will 'break' into a debug mode in which you can examine the resultant SUM value within the WATCH window?

3. What is your intent when you use the line: thisform.totalqty.value=thisform.totalqty.value

I am sure others will have other questions in order to get a more clear understanding of your situation, but those are where I'd like to start.

NOTE - the typical 'generic' syntax to use for a VFP SUM would be something like:
VFP code line syntax:
SUM InvData.Qty_In TO m.TotQty_In FOR InvData.itemcode = <some value here>
or by using SQL Query syntax:
SELECT SUM(Qty_in) AS TotQty_In FROM InvData WHERE ItemCode = <some value here>

Good Luck,
JRB-Bldr

 

[rundee]

Hi JRB-Bldr,

Yes I'm running the code within a form text1 (keypress event).
My only aim is to get the total qty from my table(invdata) under a specific supplier and display in a textbox. But then when I run the form it gives me a result of 0.
I believe miss something in my code.

Thanks for the reply. Hope you can help.

 

[Dan Freeman]

Since you're new and all, I note you're using CALCULATE, and not SUM or TOTAL (or a SQL Select).

Don't. Calculate is for deriving multiple values in a single traversal of the table and we have better ways to do even that. Calculate is old and, umm, wooly.

Now we return to our regularly scheduled pondering over whether the target control is bound, etc.

 

[Olaf Doschke]

Everything seems fine except the thisform.totalqty.value=thisform.totalqty.value line, which you should simlpy delete.
If you do this in thisform.itemno.Keypress, you will only get >0, once there is a valid itemno in thisform.itemno.value

Debug it, do like jrbbldr recommended and set a breakpoint (or set step on). I'd set it before even doing the CALCULATE.

Then check at that moment
1. Do the types of itemcode and thisform.itemno.Value match? Also, if thisform.itemno.Value is a string, checkout, if it has trailing or leading spaces.
2. Is there a filter set to Invdata, which in conjunction with the FOR clause results in no records being summed?

Bye, Olaf.

 

[Mike Lewis]

Hi Rundee,

In addition to the advice you've already been given, I would question why you need to do the calculation in the Keypress event. The CALCULATE command requires a complete pass of the table, and unless the table is very small, this will take a measurable amount of time. If you run it from the Keypress, it will execute each time the user hits a key. That's any key, including cursor movement keys, backspace, etc.

Is that you want? If not, consider moving the code to the LostFocus. That way it will run only when the user has finished entering the value.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Good advice Mike,

deducting from the names it's inventory data of just one itemcode, which might never grow to many records per itemcode. Field name qty_in suggests to me there are all single quantities of deliveries, which can grow up to a quite large amount of data over the time, anyway. Maybe there is some aggregation in that table or it's truncated to a certain time interval anyway. All just assumptions. An index on itemcode surely will help to keep query or calculate time low.

Anyway, as the calculation result is 0, the current problem rather seems no matching data. If itemcode is a numeric field type the textbox for itemcode entry must also be set to numeric, eg by initial value of 0. More likely is an alphanumeric itemcode, then what greathope123 saw in thread184-1718192 is biting you: Even the empty textbox will contain spaces. Then make it: ... FOR itemcode=ALLTRIM(thisform.itemno.Value).

If it worked before, then even just changing the width of the itemno field would cause more trailing spaces. If this string is longer than the field you get no match, as eg ? padr("1AB",20) = padr("1AB",30) returns .F.

Learn about the padding nature of char fields (storing "1AB" into a Char(20) field makes it pad with spaces), learn about string comparison in VFP, and you know why this does not match.

Bye, Olaf.

 

[wtotten]

In the texbox.Valid() or the textbox.Lostfocus() put this. make sure you have an index tag on the INVDATA table on the ITEMCODE field. That makes the query optimized.
As other have said, the Keypress event is not the right place to put this and it will be really slow (and it's a bad design).

LOCAL lnSelect && We are going to assign the currently SELECT-ed area to a variable so we can get back to it when we are done.
lnSelect = SELECT()
SELECT SUM(qty_in) AS sumqty_in ;
FROM invdata ;
WHERE itemcode = THISFORM.itemno.Value ;
INTO CURSOR ctemp1

thisform.totalqty.value = dtemp1.sumqty_in

SELECT (lnSelect) && SELECT the same area that we had when we started this routine.
RETURN .t.

Good luck.