LEDGER PAGE NO. 3

Joe Crescenzi said:

I was trying to empathize is that it should update a row, rather than insert one into a log.

Click to expand...

Chriss Miller said:

That's what I understood was asked for anyway: To update a pageno field in a ledger record, which obviously already exists.

And as it is for creating an index page as appendix of the report, perhaps in a separate report, perhaps in the same report which creates that data on the fly, there also is no need to avoid inserting new rows into another table, as this is temporary information about this report run and you can purge it after priting.

I'd consider SYS(2040) even more important, therefore, as it helps to act differently when a report is run with vs without preview. You might not want to preview the index pages, for example and add the data in the printing phase only. You might want the opposie and therefore do it in the preview phase, if there is one, then skip it in the printing phase or you program as you and I suggest with updates that don't create duplicate data.

Click to expand...

Joe Crescenzi said:

that whatever index he will be creating should be maintained using updates to existing records rather than inserts

Click to expand...

Chriss Miller said:

Mohinder Singh,

to me no further posts to a thread are a sign you got it going, but for sake of giving an example:
1. I created an FRX based on Northwind sample data of VFP9 (in the _samples+"Northwind" directory).
2. The report contains grouping by orderid and in the header band of the group I created a report field like this:
View attachment 1873
3. Running the report (orders.frx) with this code:

Code:

* base data for the report
Open Database _samples+"\Northwind\Northwind.dbc"

* pepare a cursor for reporting
Select *, Cast(0 as int) as pageno from Orders as or ;
   left join OrderDetails as od on od.orderid = or.orderid;
   left join Products as pr on od.productid = pr.productid;
   into Cursor ordersreport readwrite

* frx printing that data grouped by orderid
Report Form orders Preview

* Just for sake of verifying it worked list the orders and which pageno they got printed in
Browse Fields order=orderid_a, pageno

* function used in a report field that's part of the group header band
* with the expression UpdateOrderWithPageno(orderid_a,_pageno)

Function UpdateOrderWithPageno(tnOrderid, tnPageno)
    rn = Recno()
    Update ordersreport set pageno=tnPageno where orderid_a = tnOrderid
    Goto rn in ordersreport
    Return ''

I made use of the idea to not print anything at all in the report field that makes the call to the function which updates the report cursor, because I don't print the orderid, I print the orders customer and orderdate as header of the group, the orderid is a techincal database key I decide to never print.

And this is shown by the browse after the report run, I added the pageno field in the SQL Select query into the cursor that's also driving the report and you can see in the function I therefore also make sure I don't move the record pointer away from the current record by first storing current RECNO into a variable and going back to that record after the SQL UPDATE. Because in testing without ensuring to end on the same recno the report end prematurely.

Here's just the start of the final data:
View attachment 1874
That could now be used to print an appendix listing all orders and their respective page number.

Click to expand...