Calculations in a Local View

[SimplyES]

I have a form with a grid showing data from a single table, which holds (amongst other things) the daily work start and finish times filtered per employee, per month. The same data is used in relatively complex calculations which are also displayed on the same form. In order for the display to make sense to the user, the calculated items need to appear in columns part way across the main grid. Thus far I have acheived this by populating a cursor with the results of the function/method making the calculations and showing these in a second grid sneakily superimposed over the main grid so that it looks like its part of the main grid. That's worked fine for a few years. Sometimes 'It works' can mean 'leave it the hell alone even if there might be a better way'. However, we have now been asked to show a lot more information on the same form - bascially, combining two forms. I've rebuilt the form using a page frame, intially with these grids on page1. I have carefully re-addressed all elements to the pageframe and page but, for some reason, what worked before no longer does. VFP constantly hung mid-process whenever I selected an employee. Using a View instead of a simple SQL SELECTed cursor seems to have got me past that and it seems to me there must be a better way to display the calculated data, via the View, into a single grid but I cannot find any information regarding calculated fields in Views. Is that because it can't be done or just because I'm too dumb to find the right clues (careful response needed!)?

 

[Mike Lewis]

Yes, you can certainly have calculated fields in a view. A view is essentially a SQL SELECT statement with some wrapping around it. Clearly, you can have calculated fields in a SELECT, so there is no reason that you can't have them in a view. The only caveat is that the user won't be able to update the calculated columns (at least, not directly), but from what you have told us so far, that doesn't seem to be a problem.

That said, I can't see any great advantage in using a view rather than simply generating a cursor from a SELECT in this case. A view would make it easier to update the underlying tables, but, again, I assume that won't be an issue for you.

Next point: I have no idea why you had to resort to superimposing one grid on top of another. There should be absolutely no need to do that.

A grid is essentially a "window" into a cursor (or table, or view). Columns in the grid correspond to fields in the cursor. So provided you set the cursor up with the actual data that you want to show, then the grid will be able to display it quite happily.

Let me give you an greatly over-simplified example of how you would go about this:

* Create a cursor
SELECT Field1, Field2, Field1 + Field2 AS TotalField FROM MyTable INTO CURSOR csrCalc
     && TotalField is an example of a calculated field.

* Now use the cursor to populate the gird
THISFORM.Grid1.RecordSource = "csrCalc"

That's basically all there is to it. The result will be a grid with three columns; the first two contain actual fields from the underlying table; the third contains the result or a calculation. I know your case is more complicated than this, but the principle is the same.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[JRB-Bldr]

Another way to approach a re-calculation where the results need to be updated into an existing field appearing in a Grid is as follows.

1. Have your Form's Grid display a ReadWrite Cursor or a Temp Data Table that is derived from your 'live' table.
2. Then when the user makes changes on the form, they click a button ("RECALCULATE") or it could be done automatically via the AfterRowColChange Method.
3. That button's Click method would first DELETE all of the records in the ReadWrite Cursor/Temp Data Table or set some extra Logical field (maybe "NoDisplay") to "True" and then APPEND new records from the re-calculate's SQL Query execution.
4. And then do a Grid.Refresh
5. Then the Grid could display only those 'new' records which now contain the newly calculated field values.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

The important point of what JRB-Bldr says is, you keep the same cursor attached to the grid. Requerying only works with a view cursor, if you instead query into cursor and then at some point repeat this query INTO CURSOR even with the same alias name, there is a step in the process, where the old cursor is closed and the new is named as the old one, at which the grid loses its binding and blanks out. A view must be handled as JRB-Bldr suggests to do with a self-made cursor, VFP never closes it, but zaps and refills.

And that's where I'd differ from JRB-Bldr, I'd not delete the rows, but ZAP the cursor for a refresh of data from the DBF. Or neither DELETE and ZAP, if you only want to update some calculated columns, you can also do that with REPLACE or UPDATE and keep all other data as initially fetched.

That said you don't say you have a grid problem. The aspect of calculations and updating fields does not depend on using a view or not, a view just may allow a simple TABLEUPDATE() to store changed data, while a cursor done with a query INTO CURSOR has no connection back to the DBF. The view including calculated fields producing the result you want to show has the plus you only need to REQUERY() to get it refreshed. But only, if you disallow editing or don't buffer, not even row buffering. When there are changes in a view buffer, you have to TABLEUPDATE or TABLEREVERT to save or cancel the buffered changes before you are allowed to REQUERY. That rule protects the changes and forces you to explicitly think about the buffer before refreshing from DBF files.

That's also a minus of views, as you can only get fresher DBF data (changes from other users, for example) after saving own changes and not keep the own session changes buffered until the user really wants to save or cancel. To be able to do that, you have to do complicated selects and updates, after which you'd also need to change the buffer state of data you manually refreshed, because the buffer thinks of your updates as updates, while they are just the current values of the DBF. The only reason to "forgive" the VFP team for having implemented this rule is, the general refresh can possibly have the same types of conflict you can have in TABLEUPDATEs, concurrent changes of same fields from other users. This time, that conflict would occur in your buffer and display rather than in the DBF. And in case it would occur, it also will occur when you TABLEUPDATE() later.

You don't say, whether you want to allow editing of the data or not. If not, nothing speaks for a view, a view is just helping to keep the query in a central place to reuse anywhere, but it also causes trouble in database changes, when the view definition doesn't match its underlying tables anymore. And that only reveals itself when using views after a DBC upgrade. The same applies to queries in code, but code then is easier changed, views not matching their underlying tables don't only refuse to be used, they also refuse to be edited in the view designer. Keeping code just in one place for not needing to change multiple locations in code also is no magic with OOP and business classes/objects.

Even from that perspective of using views vs coded queries or biz classes, it's still not just a matter of taste, as you can't simply TABLEUPDATE() a readwrite cursor. So in the end, you have to decide pros and cons of views vs coded queries. You can program the necessary updates of DBFs, but then you actually reimplement what the VFP TABLEUPDATE() function already can do for you. You can program the necessary updates of the cursor, but then you reimplement what the REQUERY() function already could do for you. That's the curse with cursors.

Bye, Olaf.