Jump to content

Constraining on an Empty Date Field


Venom71
 Share

This topic is 5611 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5611 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.