Calculating a Grid Cell for All Rows

[stanlyn]

Hi,

I need to calculate the value of a cell (textbox control) on a grid for all rows shown in the grid. The grid is tied to an underlying table and a tableupdate() saves the rows. Buffering is 5, VFPA. I do not want to commit the data, then read the underlying table. Instead, I need to get the un-committed values by iterating the grid.

With the table buffering on (as indicated by 5), and as I understand it there is a copy of the data in the database (committed), and a copy in the grid cursor (uncommitted, dirty). It is this dirty data I need to calc.

I currently query the database to get the cursor, but calcing the grid column/cell from that cursor returns 0.00 when the grid1.column1.text1.value is 1234.45 (not zero)

Thanks for your help,
Stanley

 

[Mike Lewis]

Stanley,

My advice would be to forget about the grid when doing the calculation. Just focus on the underlying table. After all, the grid is merely a window for viewing the data. It is the table or cursor that actually contains the data.

The fact that the buffer is dirty does not matter. When you read a value from the cursor, you will get the most up-to-date value, regardless of whether or not the updates have been committed.

With that in mind, all you need to do is to calculate the sum of the relevant field in all records of the cursor. Here is one way to do that:

[tt]SELECT TheCursor
SUM TheField TO lnSum
[/tt]

Keep in mind that the SUM command will move the record pointer. This in turn will cause the highlighted row in the grid to change. So to avoid confusing the user, save the record pointer before you do the sum, and restore it afterwards.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

No problem, Stanlyn.

You can do SUM field TO variable, for example, that reads from the cursor/buffer. Or any other normal way to access fields from the workarea reads the buffered values. The only difference is with SQL. You should know SQL always a) uses a new separate workarea, no matter if the DBF is open already or not and b) SQL alaways reads from the DBF. But there is a clause WITH Buffering= .T./.F., so you can SELECT SUM(field) FROM table [highlight #FCE94F]WITH buffering=.T.[/highlight] WHERE ... INTO CURSOR Result, or similar, to let SQL read from the buffer.

Chriss

 

[stanlyn]

I must have something else wrong as I'm not getting the expected results. Here is more info.

The underlying table is dbf. Only sql is in the row source with type=3 that lives in the combobox's dropdown method (embedded in the grid). From there I do a lookup to another dbf and if found I update the rest of the fields within the grid row. These rows represents line items of an invoice where each row is a separate line item. So, for example, when leaving the "qty" field, it should recalculate a new total for the invoice, after first calculating the row where the qty was changed. I do the calculations in the lostfocus events of the controls that needs calculated by calling a form level method (CalcTotals).

This only works correctly after a tableupdate() and I need it to work before committing. For example, after changing the grid cell to $20 and doing a "replace field with text1.value", the cursor field still shows $0 (its value before the edit).

The calculation involves calculating values in the other rows as well. When adding a new line item row, I do an "insert into" with enough fields populated so it is clearly associated with the invoice, then drop the user onto that new row and when a revert is issued the row goes away (correct behavior).

The sell price is zero for all rows and we start editing them as shown below the code screenshot below.

Here is the code that does some of the calcs.

This is after some changes to the grid values and the calc runs. The browse window still shows them as zero. Note they are not zero in the grid. The grid's rowsource is "AR_InvoiceDtl" as seen in the code screenshot where I use it again as an alias so I don't move the pointer off the row being calculated. To correctly show, I have to do a tableupdate() which is not what I want as a tablerevert would fail to undo the changes if I were to do a tableupdate first.

I need to get access to the values as shown in the grid.

Thanks,
Stanley

 

[Chris Miller]

You are setting variables in your calculation code. How are you expecting them to become shown in the grid, if they are not fields of records?

Using an SQL rowsource does also create/populate a workarea. It's not connected back to the DBF with buffering, though, so I wonder how the buffering issue you mentioned plays a role in all of this. But all in all, you have to put the data you want to show up in a grid into some workarea. I don't use SQL as rowsourcetype and that has good reasons, too, but if you stick to it, there will be some workarea like "query" that will hold the grid data to process.

Or you simply force it into this, by not using SQL-query as rowsourcetype and instead do the query INTO CURSOR and bind that to the grid. And then you know your workarea name as you specify it and you know from where to read or into which workarea to write. It all becomes known. Using READWRITE in the query to generate the grid cursor, it's also writable. If you want to write back to the underlying table neither doing the SQL yourself or as rowsourcetype will do the job well, you are then best served with an updatable view. You should know that very well.

Edit:
Looking into your code, you don't sum what's in the grid, you sum from a workarea dtl which you opened by USE AGAIN. That's clearly not summing the values in the grid, it sums the values of the dbf. You have to find out which workarea name is used by your grid, which you feed with a rowsourcetype SQL query. Well, that still will be a workarea name, as I already stated. So that's what you want to find out. Or better yet, change this all to a more manageable situation by doing the query for the grid yourself in a view or into cursor readwrite, so you know the workarea name.

Chriss

 

[stanlyn]

Chris,

The rowsourse for the grid is AR_InvoiceDtl (invoice detail records) and is the base dbf. I use the again alias dtl so calculations can be done across all records without moving the pointer away from the grid row being edited. The last screeenshot show that the numbers entered into the grid has made their way to the dtl cursor as shown in the browse window that is below the grid. I put that up so you can see the values. It is these values that is needed and my code should see them, but it does not.

You have to find out which workarea name is used by your grid,

At what point would the again alias cursor values receive changes made to the base table?
Base table AR_InvoiceDtl field value: 10.00
use 'AR_InvoiceDtl' again alias 'Detail'
"Detail' has zeros. It is my understanding the values are equal. I just use the again alias to work on without moving the pointer in the ui. As said before, a tableupdate() shows the data calcs as expected, but lose the tablerevert functionality.

When changing a value in the base table via the grid, what is needed for the changes to show up in the 'again' table which is where I need to calculate? I was thinking immediate, but not sure how the table buffering stuff affects it. This is the first time I've used buffering against native tables.

Looking at the last screenshot, the grid shows the 1st row from 'AR_InvoiceDtl' has a value of 20.00 Notice the record pointer is on the 2nd row now and after changing its value to 30.00 and leaving the cell triggering the calculation routine in the lostfocus. At that point, I browse the dtl cursor by selecting it from the data session window just to see what its values are and as you can see, they both are zero, not what I expect.

Stanley

 

[Chris Miller]

so calculations can be done across all records without moving the pointer away from the grid row being edited

Well, you don't read from the buffered values when you use a table again. Because the buffer is only part of the workarea, it's not part of the DBF. So yes, you see zeros in the dbf you use again. You have to scan the workarea used for the grid or do SQL on it WITH BUFFERING=T., there's no way around that. You can move back to the original record after that, if you use the SCAN option, or you don't, when you use SQL for the sum.

Chriss

 

[Chris Miller]

Here's all situations I could think of and their results. You can experiment yourself like this, stanyln. And in fact you did one experiment and looked into the browse of the DBF used again, seeing that your idea of how the buffer should work is wrong, but still not gettting to the conclusion your idea about how buffering works is wrong.

Edit: Extended code with two phases: Phase 1 without filter, phase 2 with filter:

Clear
Create Table test.dbf (id int autoinc, number int)
Insert into test (number) values (1)
Use
Use test Shared Alias testworkarea
Set Multilocks On
CursorSetProp("Buffering",5)
Insert into testworkarea (number) values (2)
Insert into testworkarea (number) values (3)
Locate for id = 2 && intentionally setting current record into the middle.

? 'record positioning aspect. Displaying ID, number:'
? testworkarea.id, testworkarea.number
* solution 1 for summing:
Select Sum(number) as total from testworkarea with (buffering=.t.) into cursor testworkareabuffersum
? testworkarea.id, testworkarea.number

* no solution, summing from dbf('testworkarea') = test.dbf
Select Sum(number) as total from testworkarea into cursor testworkareasum
? testworkarea.id, testworkarea.number

* no solution, summing from test.dbf
Select Sum(number) as total from test into cursor testsum
? testworkarea.id, testworkarea.number

* no solution, summing from test.dbf used again
Use test In 0 Again Alias testagain
Select testagain
lnTestagainTotal = 0
Scan
   lnTestagainTotal = lnTestagainTotal + number
EndScan 

* solution 2 for summing:
Select testworkarea
lnMemorizedID= id
lnTestworkareaTotal = 0
Scan 
   lnTestworkareaTotal = lnTestworkareaTotal + number
EndScan 
Locate for id = lnMemorizedID
? testworkarea.id, testworkarea.number

* Notice how things change with a filter condition:
Set Filter To id>1

* no soluton, not taking filter into account
Select Sum(number) as total from testworkarea with (buffering=.t.) into cursor testworkareabuffersum2
? testworkarea.id, testworkarea.number

* no solution, summing from dbf('testworkarea') = test.dbf
Select Sum(number) as total from testworkarea into cursor testworkareasum2
? testworkarea.id, testworkarea.number

* no solution, summing from test.dbf
Select Sum(number) as total from test into cursor testsum2
? testworkarea.id, testworkarea.number

* no solution, summing from test.dbf used again
Use test In 0 Again Alias testagain2
Select testagain2
lnTestagainTotal2 = 0
Scan
   lnTestagainTotal2 = lnTestagainTotal2 + number
EndScan 

* solution 2 for summing still working:
Select testworkarea
lnMemorizedID= id
lnTestworkareaTotal2 = 0
Scan 
   lnTestworkareaTotal2 = lnTestworkareaTotal2 + number
EndScan 
Locate for id = lnMemorizedID
? testworkarea.id, testworkarea.number

* soluton 3, taking filter into account by where
Select Sum(number) as total from testworkarea with (buffering=.t.) where id>1 into cursor testworkareabuffersum3
? testworkarea.id, testworkarea.number
? 'no record pointer problems, if using SQL or repositioning to memorized id.'

? 'sums resepcting buffer:'
? testworkareabuffersum.total
? lnTestworkareaTotal
? 'sums respecting buffer and filter'
? testworkareabuffersum3.total
? lnTestworkareaTotal2

? 'sums not respecting buffer, filter, or both:'
? lnTestagainTotal
? testworkareasum.total
? testsum.total
? lnTestagainTotal2
? testworkareasum2.total
? testsum2.total
? testworkareabuffersum2.total, 'should be 5, filtered' && 6, but should be 5

The only errata I have to make about my previous posting is that the WITH clause of SQL is needing the brackets. WITH Buffering=.t. causes command/phrase not recognized, it must be WITH (Buffering=.t.), but otherwise this is a solution aside from scanning the buffered workarea itself. In case you also need to respect the filter on the buffered workarea, you can make it a where clause and can use the SQL solution, too, otherwise this code example shows you only get the buffered AND filtered values directly from the woarkarea.

And by the way, working with memorizing an ID or RECNO() to go back to is a normal solution, too. It's also the reason to use table buffering, so you can move around in the buffered workarea. You won't even see the record pointer of the grid flicker, if you do that and finally go back. You could make sure using the lockscreen property of the form while doing the calculation.

Chriss

 

[Chris Miller]

In summary and essence, you have 2 solutions for getting the calculation right:

Solution 1:
calculate within the workarea itself, scanning it
Advantage: filter is respected without needing to think about it.
Disadvantage: You need to go back to the initial position. You may need to use lockscreen to absolutely make sure there is no visual side effect from moving in the workarea itself.

Solution 2:
calculate using SQL FROM the workarea WITH (buffering=.t.)
Advantage: record position doesn't change
Disadvantage: Filter is only respected if you add it in as a where clause.

Notice in solution 1 you can also easily go back to the initial id with a SEEK, having an index on the primary key or you go back to a recno you memorize for that matter. All xbase code done on a work area by nature respects the buffer and also the filter, so you don't need to concentrate on such things. Tablebuffering is essental to use this, but you want to use table buffering anyway, to be able to revert back to the inital data in all records.

Notice in solution 2 the SQL always is a simple single table SQL, as you have all your buffered data in the one single workarea you query. It doesn't matter whether the data came from a view or sqlpassthrough query involving complex SQL. The buffer is part of the one workarea only. It's no wonder but just assures you never get into the territory of complex SQL. The filter can also be generally read back by SET('FILTER'), so there also is a generic way to have the necessary where clause to make use of this solution despite of its disadvantage. It will then give you its advantage to never change the record position in the workarea. So that feature is still true for SQL explicitly told to get data from a workarea and its buffer WITH (buffering=.t.).

Chriss

 

[stanlyn]

Chris,

I'm working thru your examples and to describe a little more.

memorizing an ID or RECNO() to go back to is a normal solution

I do this a lot, however I don't want to write all the code to ensure the flow stays on the tabbing path while in the grid, which would require a lot of jumping around. I need a generic approach, something simple to implement and maintain. When adding a new child record most apps bring up another form to do that. I'm going away from that so there are a lot less forms. Instead, I'm doing it all in the grid, which I find this approach gives the user their desired speadsheet experience. I have it all working with lookups and flow, except for this calculations process that updates the row itself and the parent (invoice) record.

I have yet to work through (just started) your examples and wanted to share more about the purpose.

More as I work thru it...

Thanks, Stanley

 

[Chris Miller]

the flow stays on the tabbing path while in the grid

Well, just because activating a cell moves the record pointer to that record, moving the record pointer does not move focus elsewhere. Not directly.
If you don't go back, then you finally end up in another record that has a final effect, but not directly. So if you finally go back to the same record, you're still in the same cell of the grid.

I just experimented with this and used a grid text1 interactivechange event to move around in the grid cursor and come back, finally. It didn't do anything for the focus and even the text cursor position within the active cell.

You wouldn't even work in such events like InteractiveChange or ProgrammaticChange, as the workarea including its buffer only changes after the valid event stores the cells value into the controlsource, but this demonstrates how you can't simply induce from knowing that clicking a grid cell changes the record pointer the change of the record pointer also affects the focussed grid control.

Chriss

 

[Chris Miller]

After your 16 hours schedule (or do you do this as a hobby by now?) I think you'll not got on with this topic.

I have to add one warning about how you can go wrong either way: If you don't also take into account that the currently focused cell and its control value will neither be in the buffer nor in the DBF. So while my code shows you your only two chances are scanning the workarea or doing SQL with (buffering=.t.) you can only get at a correct sql aggregation result, if you take care for the current cell value to go into the buffer.

It's not very hard to do, it just needs one simple REPLACE, but you could easily overlook it and then throw buffering overboard.
Edit: I notice that I already said it in another way in my previous post. I'll not delete this, though, it can't be stressed out often enough, that this minimum buffering of a control towards its controlsource always happens, with or without buffering and within or outside of a grid. One known solution to it is setting focus to the control itself, so it cycles through its own valid,lostfocus, and gotfocus events and in the valid stores its value back to the controlsource and also into the buffer.

Chriss

 

[stanlyn]

So if you finally go back to the same record, you're still in the same cell of the grid.

Are you saying that when leaving a cell and I change the record in code, then set it back, I will be positioned at the next control in the tab path without explicitly doing a setfocus? If so, then that could be generic enough.

Right now, I dealing with a similar situation with a combobox control and its 3 values, ControlSource, Value, and DisplayValue as well as allowing new entries and incremental searching. That's a different question for a different thread at a different time, if needed. OK, I just started a new thread about it.

Thanks, Stanley

 

[Chris Miller]

Here's a short demo of a made up example where I compute and show the total length of text in all fields of a buffered table.

The point it demonstrates is that while you type in a grid cell the total text length is computed by a scan loop of the grid rowsource from the buffered data (alternatively with SQL in a commented code section) and the focus and even the text cursor isn't influenced by that. There are more things to say, but I leave it up to you to check this out.

Cd (Justpath(Sys(16)))
Local loForm
loForm = Newobject("demoform")
loForm.Show()
Read Events

Define Class demoform As Form
   Height = 300
   Width = 380
   Caption = "Demo Form"
   
   Add Object Grid1 As MyGrid With ;
      RowSource = "tblBufferDemo",;
      ColumnCount = 2, ;
      Height = 200, ;
      Left = 20, ;
      Top = 20, ;
      Width = 340, ;
      Name = "Grid1"

   Add Object lblLength As Label With ;
      Height = 24, ;
      Left = 20, ;
      Top = 240, ;
      AutoSize = .T., ;
      Caption = "Total Text Length:"


   Add Object txtLength As TextBox With ;
      Value = 0, ;
      Height = 24, ;
      Left = 116, ;
      Top = 236, ;
      ReadOnly = .T., ;
      Controlsource = "Thisform.Grid1.TotalTextLength"

   Procedure Load()
      Local lnI

      Use In Select("tblBufferDemo")
      Erase tblBufferDDemo.*
      Create Table tblBufferDemo(cText1 Char(10), cText2 Char(10))
      For lnI = 1 To 10
         Insert Into tblBufferDemo Values ('Edit','This')
      Endfor
      Use
      
      Set Multilocks On 
      Use tblBufferDemo
      CursorSetProp("Buffering",5)
   EndProc
   
   Procedure Destroy()
      Clear Events
   Endproc

EndDefine

Define Class MyGrid as Grid
   MemberClass = "MyGridColumn"
   MemberClassLibrary = Sys(5)+Sys(2003)+"\"+Program()
   TotalTextLength = 0
   
   Procedure TotalTextLength_access()
      * a computation with the buffered data
      Local lnRecno, lnLen
      lnRecno = Recno()
      lnLen = 0
      Scan
         lnLen = lnLen+Len(Alltrim(cText1))+Len(Alltrim(cText2))
      EndScan

      Go lnRecno
      Return lnLen
      
      * alternative:
      * Select Sum(Len(Alltrim(cText1))+Len(Alltrim(cText2))) From tblBufferDemo With (Buffering=.T.) Into Array laLen
      * Return laLen[1]
   EndProc 
EndDefine 

Define Class MyGridColumn As Column
   Procedure Init()
       * needs adaption if other controls are used as currentcontrol or dynamiccurrentcontrol
       BindEvent(This.Text1,"ProgrammaticChange",This,"ValueChange")
       BindEvent(This.Text1,"InteractiveChange",This,"ValueChange")
   EndProc     

   Procedure ValueChange()
      * Store current control value into buffer
      * otherwise controlsource is only updated when the column control valid event happens.
      * Might need adaption to different controls, but works for a variety of them
      Replace (This.ControlSource) With Evaluate('This.'+This.CurrentControl+'.Value')

      * Trigger textlength determination from the data including the current value
      Thisform.txtLength.Refresh()
   EndProc
EndDefine

Some remark/opinion: The SQL code is obviously a little more concise than scanning, the pro about not using the WITH (buffering=.T.) is only, that it also works with older VFP versions.
And, to answer in advance what you might think: The trick is not that the computation is indirect via the textbox refresh, you could also do without the property_access method and compute the textlength directly in the ValueChange() method. As long as you go back to the initial recno, there is no control focus change happening. Comment the [tt]Go lnRecno[/tt] and the record change to the bottom record will reflect in the grid. But this only happens after a few more events the form and grid go through, not because of changing the record. It's not a twoway dependency of record pointer and control. If you click on a cell, that makes it the current record, but changing recno does not directly affect the current control. It does happen evventually, but only after the interactivechange event has ended and further events are happening, the grid will relfect the reord change, if there is one. That's why this old well known trick to memorihhze the position, do whatever you need and go back works. And not only because of buffering, that has more to do with the events related to the UI and display than with backend and things like the record position.

Chriss