Subject Re: filter on logical plus date field
From Mervyn Bick <invalid@invalid.invalid>
Date Thu, 6 Jan 2022 22:29:16 +0200
Newsgroups dbase.getting-started

On 2022/01/06 21:27, Rouke wrote:
> Thanks,
> set filter to not (returned=true or not isblank(dateofrtrn))
> seems to give the required result.
> Any idea how to do this in local sql?
> Where NOT (loans.RETURNED = True OR NOT isblank(loans.DATEOFRTRN))
> doesn't get accepted.

As Ken has said elsewhere in the thread, logical decisions can be
tricky.  :-(  Adding brackets into the mix can change the whole thing.
Sometimes you need them, most times you don't.  The tests inside the
brackets get evaluated first.

set filter to not (returned=true or not isblank(dateofrtrn))

The test inside the brackets will find all records with

     returned = true whether dateofrtrn has a value or not
     returned = false where dateofrtrn has a value.

The NOT outside the brackets negates the test inside the brackets so it
finds all records with

    returned = false where dateofrtrn does not have a value

set filter to returned = false and isblank(dateofrtrn)

will give you exactly the same result and requires less mental
gymnastics to work out what's happening behind the scenes.  :-)

Isblank() is a XDML function, not a localSQL function so it won't work
in a WHERE clause in a SELECT statement.

Try the following

   .... Where loans.RETURNED = True and loans.DATEOFRTRN is null