Filtered cursor used in SQL Select not honoring original filter

[BugZap13]

I have 2 DBF's, Employer and Employee. I create cursors which select the records to be presented in comboboxes on a form. Initially the employer cursor is positioned to a 'All' selection and the employees are not filtered showing all employees in their corresponding comboboxes. When a user selects an employer, a "Set Filter" command is issued in the INTERACTIVECHANGE method against the cursor used for the employee pulldown. All good so far. I then have a button on the form to create a report. Before displaying the report another cursor for the report is run using the filtered employee cursor.

SELECT * FROM FilteredEmployee LEFT JOIN Employer ON EMPLOYER.ID = FilteredEmployee.EmployerID

I was expecting that the FilteredEmployee cursor would only supply records for the selected employer. Instead all employees for all employers are selected. The SQL select ignored the filter set on the FilteredEmployee cursor. Is there some SET command I am missing or do I need to do this another way?

TIA, Mark

 

[TamarGranor]

Right. SQL SELECT doesn't know anything about SET FILTER. Xbase and SQL commands are just separate.

Frankly, unless the Employees table is huge, it's better to avoid SET FILTER altogether and simply re-select the cursor for the Employees dropdown when the value in the Employer dropdown changes. Put the relevant condition in the WHERE clause.

Tamar

 

[Chris Miller]

As Tamar said, SQL doesn't take a filter into account. SQL always opens the source DBF(s) in new workareas, even if you select from a workarea, i.e. using an alias from a cursor,view or other workarea in the FROM.

I don't know how you feed the report, it seems you let it run on the final cursor, but you could use the current workarea as the data source for a report too, that's how the FOR clause of the REPORT FORM command works. So just SELECT FilteredEmployee and report on that. To "join" Employer fields you could set a relation from FilterdEmployee to Employer.

Select Employer
Set Order To Id
SELECT FilteredEmployee
SET RLEATION TO EmployerID INTO Employer
REPORT FORM...

Or, as Tamar suggests, make the filter a where clause, you could use SET('FILTER') to determine it, like

SELECT FilteredEmployee
lcFilter = SET('FILTER')
SELECT * FROM FilteredEmployee LEFT JOIN Employer ON EMPLOYER.ID = FilteredEmployee.EmployerID WHERE &lcFilter INTO reportcursor

I'd be cautious, though, the SET('FILTER') clause could be problematic in special cases, I remember it would in some cases not return a working expression. You might construct the where clause just like you set the filter, instead.

Chriss

 

[BugZap13]

Thanx for the input Tamar and Chriss. I am going to redo my thinking and set the controlsource of the two combobox's to a public variable and use those values to build the report cursor rather than rely on the underlying cursors used for the combobox's themselves.

Again, Thanx for the input

 

[Chris Miller]

I don't see how that is related to the SQL you do for a report. A combobx bound to a table or cursor in a workarea will respect the FILTER, that's not a problem.

You know best what works, but I'd not recommend using public variables.

Chriss

 

[TamarGranor]

I don't know the right answer for your problem, but I am confident it doesn't involve public variables because they are virtually never the right answer. (I'd say never, but I use them in certain circumstances for things I do when giving demos.)

See if this article gives you some ideas:

http://www.tomorrowssolutionsllc.com/Articles/Cascading combos and lists.PDF

Tamar

 

[BugZap13]

Chriss I used the underlying cursor of the employee table which was filtered to the selected employer as the driving table in a subsequent select used for the report. Originally I was expecting that the filtered cursor could be used. As you and Tamar pointed out the filtered technique will not work. I have shifted to using private variables as the controlsource of the combobox's and then used to build the cursor for the report.

Tamar I agree with your statement regarding public variables. I used "public" above just to indicate the scope of the variable used. In reality the form is called from another program which declares the variables as private and are accessible to the form. Your article pointing out the use of arrays could have been used. I elected not to use arrays due to the size of the employee table.

Unfortunately, my FoxPro skills are getting rusty. This was a change to an app written 20+ years ago.
Again Thanx

 

[Chris Miller]

Originally I was expecting that the filtered cursor could be used. As you and Tamar pointed out the filtered technique will not work. I have shifted to using private variables as the controlsource of the combobox's and then used to build the cursor for the report.

What do the combobxes have to do with the report? You can't use comboboxes within a report, so they are used in the form that's starting the report, right?

So what is your problem with filtered data there? Combobxes set to a filtered wortkare do respect the filter. It's only a query for the report data that has to be extended with the corresponding WHERE clause again to only have the filtered data in the report, besides I showed you can also drive the report from the filtered workareas not using a query but instead SET RELATION.

Chriss