Can I use the QBF selection criteria from a form to limit rows in a report?

Question:
I like the automatic QBF facility that comes with all default Power Object applications. Is there any way to pass the selection criteria to a report?

Answer:
Yes, there is, but it may not be intuitively obvious. In trying to use a form to specify selection conditions for a report through its QBF facility, you run into two obstacles. The first obstacle is that you do not have any way of getting to the selection clause created by use of the QBF facility in a form. The second obstacle is the limited way available to impose selection criteria on a report.

To overcome the first obstacle, you will need to use some code and possibly the help of a temporary database table. Although it is true that you cannot get access to the WHERE clause created by the QBF activity, you do have access to all the rows that were returned as a result of the QBF selection criteria. You will have to create a list of values that uniquely identify the rows in the recordset for the form.

The unique identifiers could be either the unique key value for the row or, if you are using an Oracle database, the ROWID for the row, which not only uniquely identifies the row, but is a very fast way to access rows. If you are going to use a list of ROWIDs, you will have to create a temporary table in the database with a single column to hold the ROWIDs. You will first make sure that you have all the rows in the database by using the FetchAllRows() method for the recordset. You will then delete all the rows in the temporary table with an EXEC SQL statement.

After clearing the table, you will walk through the recordset and write the ROWID to the temporary table. In the example below, I have used an EXEC SQL statement to accomplish the insert. When you have completed writing all the values, you COMMIT the inserts to the database.For this example, I am using a form called frmMain and a temporary table called TEMP_ROWID that contains a column called TEMP_ROWID. The code for this is:

        DIM nCount As Integer        DIM nPointer As Integer        DIM nROWID As Integer        DIM sSQL As String        frmMain.GetRecordset().FetchAllRows()        EXEC SQL AT MLDATA delete from TEMP_ROWID        nCount = frmMain.GetRecordset().GetRowCount()        nPointer = 1        DO WHILE nPointer <= nCount                nROWID = frmMain.GetRecordset().GetColVal("ROWID")                sSQL = "insert into TEMP_ROWID (" & nROWID & ")"                EXEC SQL AT MLDATA :sSQL                nPointer = nPointer + 1        LOOP        EXEC SQL AT MLDATA commit

You have all your ROWIDs safely in their temporary table. Your final step is to call the report and set the limitation on the rows for the report. The only way to impose a selection condition on a report is through the DefaultCondition of the report. Set the DefaultCondition of the report to "ROWID = (SELECT TEMP_ROWID FROM TEMP_ROWID)" . The subSELECT will return the ROWIDs from the temporary table and limit the rows in the report to the same rows from the original table.You could accomplish the same effect by building a string of all the ROWIDs and setting the DefaultCondition for the report to "ROWID IN (" concatenated with the string of ROWID values and terminated with ")". This method might be a little more efficient, since it would not require a query to the database to fetch the ROWIDs, but I would be worried that you might have too many values and exceed the size limit for the DefaultCondition property.