Baffled by setOmit

[RCooke71]

Here is my code:

$compoundFind =& $fm->newCompoundFindCommand('MyJobs');

$findreq =& $fm->newFindRequest('MyJobs');

$findreq->addFindCriterion('JobStatus', 'Complete');

$findreq->setOmit(true);

$compoundFind->add(1,$findreq);

$compoundFind->addSortRule('CustomerNumber', 1, FILEMAKER_SORT_DESCEND);

$result = $compoundFind->execute();

I expect this to return all of MyJobs that are NOT marked as complete from MyJobs layout.

Instead I get ONLY the jobs that ARE marked as Complete.

It seems so simple, yet I am so confused.

[mishal]

I'm having the exact same problem! Is this a bug in the API?

[mishal]

Oh wow! So I figured out a trick... In your example RCooke71, it would look something like that:

$compoundFind =& $fm->newCompoundFindCommand('MyJobs');
$findreq1 =& $fm->newFindRequest('MyJobs'); // Get all
$findreq2 =& $fm->newFindRequest('MyJobs');
$findreq2->addFindCriterion('JobStatus', 'Complete');
$findreq2->setOmit(true); // Omit what you want
$compoundFind->add(1,$findreq1); // First add the "get all"
$compoundFind->add(2,$findreq2); // Put the omit at the very end
$compoundFind->addSortRule('CustomerNumber', 1, FILEMAKER_SORT_DESCEND);
$result = $compoundFind->execute();

In my tests, the order you add your findRequests to the compoundFind mattered...

If you add the omits first it returns the same wrong result!

 

Hope it helped!

[David Crowe]

The above does not work for me unless I add a constraint to findreq1. I get "Unknown error". If I add a constraint that will fetch all records (e.g. "*" in a mandatory field) then it works in any order. I think this means that although in the FM interface you can do a query with just a single omit constraint, you can't do this via PHP. You have to have a constraint that essentially fetches all records, and then add omit constraints to subtract from that. It's a royal pain for me because I don't know which fields are like this and there's not actually a guarantee that there always will be a mandatory field...

This also means that I have a bug in another web application. If someone enters an instruction to search for a field that does not contain a certain value ... this works ... unless it is the only constraint, in which case it retrieves every record that matches. I think FM PHP ignores "omit" on the first addFindCriterion.