Constraining on an Empty Date Field

[Venom71]

I'm new to FileMaker, but have coded in other DBMS' systems quit a bit in the past.

I am building a small database app for my partner for all her bills as a learning exercise in FM before I start to use it for work.

The Bills table has a field "Date Paid", and I would like to show her (on a tabbed form), only those records where the "Date Paid" field is still empty. (I don't want her to have to enter any "Find" parameters.)

Any assistance will be appreciated.

[Genx]

Welome to the forums

One way of doing what you want:

In your Bills table, create an extra field of type calculation. In that field, insert the following: If(IsEmpty(Date Paid) ; "1" ; "" )

..This basically says, if the Date Paid field is empty, so is our field, otherwise our field holds a value of 1

Choose the result to be number.

Now go to your main table, add an additional field called const1 or something. Make it a calculation, and simply insert "1" to be the calulation.

Then relate this field in your main table to the field we just made in your bills table.

This will filter all records to ones that don't have a date entered.

[Venom71]

Merci Genx and thanks for the Welcome mate. I'll give your suggestion a go.

[LaRetta]

Or simply:

not DatePaid

This boolean logic will work whether the DatePaid field is date or text. If it contains ANY number, it will fail the test and produce 0; otherwise 1. If it contains any text, it will pass the test but you are looking for a date so it will work. It saves one evaluation over If ( IsEmpty()). It seems small but every evaluation adds up particularly when a file contains very high record numbers.

If someone converted all their needless If() tests to boolean (and saved one evaluation each) it can add up in a hurry!

[Genx]

Mmmm, try what LaRetta said, I still havent gotten the hang of using the plain not function, i thought that only worked for numbers anyway.

[Genx]

Another interesting thing i only just worked out after over a year lol, the If Statement doesn't need to have an else condition.

i.e. you can write If(IsEmpty(Fieldx) ; 1)

[LaRetta]

I neglected to mention that you must uncheck 'Do Not Evaluate If All Referenced Fields Are Empty.'

'Not' isn't a function but rather an Operator which (in this case) turns it into boolean logic (which produces 1 if true and 0 if false). Dates are numbers.

[LaRetta]

And again, boolean logic says: IsEmpty ( theField )

Which produces 1 if the field is empty. Yes, many people add the default result (out of habit) on If() because it was required in versions less than 7. :wink2:

[comment]

i thought that only worked for numbers anyway

It does. But a date (or time or timestamp) IS a number. And when the argument is text, it is converted to a number, so that:

not "sometext" = not GetAsNumber ( "sometext" )

you can write If(IsEmpty(Fieldx) ; 1)

If the result is intended to be a number, you can also write just IsEmpty ( Fieldx ). Returns 1 if true, 0 if false.

---

*******, she types fast!

Edited July 27, 2006 by Guest

[Genx]

Oh right i wasn't even thinking about that one - "that one" being that IsEmpty will already bring about a 1 or 0 result.

As for the word operator, well, i can't think straight right now.