Hi all,

I'm a fm 6 newbie. I have a database with a date and boolean checkbox (the field is specified as a number). I can get filemaker to search on the date ok, but i want to search on boxes that are NOT checked. Basically, the search needs to be for a specific date AND the box is UNchecked. I've tried Null values... The script I'm using looks like this for the date:

Enter Find Mode []

Insert Current Date [select, "MyDateField"]

Perform Find [replace found set]

Now, how do I find the empty checkbox? Should I be using a different search technique in the first place?

Thanks a billion!

-Will

Have you tried

Enter Find Mode []

Insert Calculated Result [select,

Just tried that, it didn't work either

Im in Sacramento too :-) small world...

Hi Will,

Yes it is a small world.

Maybe I missunderstood the problem.

Explain your find beyond the MyDateField part.

Lee

The MyDateField is for the date, not the checkbox. We'll call the checkbox "CheckboxField"

So I changed "CheckboxField" to a Text field, and when i enter find mode and put in the equal sign (=) it returns the proper files, but for some reason, it won't work with either insert text[select,"CheckboxField","="] or insert calculatedresult[select, "checkboxfield",""=""]. Thoughts?

Hi:

I'm in san diego. Not-so-small state. Did you guys watch the governor debates last night?

If I understand you correctly, you want to find for a certain date, AND if a field is emtpy. So go like this:

Allow user abort [off]

Set error capture [on]

Enter Find Mode []

Set Field [Date field, "your date"] - enter the date you are searching for

Set Field [Checkbox field, "="]

Perform Find

Lee had the right operator, but I don't think you're trying to find for the date field being emtpy.

I guess I should add that if you are finding a static date, then Set Field will work, but if you are looking for a calculated date, you should use Insert Calculated Result. And make sure the date field you are searching on is on the current layout when the find is executed.

Ken

It sounds like every record has a least one checkbox checked, is that correct?

If your checkbox field is a true boolean, then it should never be empty. You should auto-enter a zero in it and then search for that.

With the way you currently have it set up, you can use Set Field["checkbox", ""=""] and find on that. You also don't need an Insert for the date field if you're searching for a single date. A Set Field will suffice.

When I use ANY of the Insert or Set functions with either = or "=" I get a result back saying that "No valid criteria in this request..." My Exact Script looks like this:

Enter Find Mode []

Set Field ["CheckboxField", ""=""]

Perform Find [Restore, Replace Found Set]

I also tried:

Enter Find Mode []

Insert Text ["CheckboxField", "="]

Perform Find [Restore, Replace Found Set]

The latter returns every record, checked or not.

The checkbox field is a text type, and entering the = works in the regular find mode... Is there a special way of representing

Ken, do you mean a calculated date or a date range? Set Field works for any date, as long as it's a single result.

Why are you using the 'Restore' option and what criteria is restored? Print a hard copy of the script and see what is being restored. That is overwriting your find criteria.

The date function works fine for me, it's just the checkbox :-

Thanks for all the help guys :-)

I guess I worded it wrong.

If it's a calculated result, use Insert Calculated Result. (talk about stating the obvious, huh?)

For example, assume you have two date fields, date1 and date2. If I wanted to find in date2 for: date1 >= Status(CurrentDate) - 7, Set Field will not work. You have to use Insert Calculated Result. But if your date is static, either by entry of by another field's value, then Set field will work.

Hope that made sense.

Ken

Removing that "restore" thing worked Queue, *smacks forehead.

It works fine now. I miss SQL, lol :-)

Thanks again all!

Ah, that's what I thought. You mean single date and date range. ">= Status(CurrentDate) - 7" is a range, not a single date, and therefore requires the use of Insert Calculated Result; whereas Date( Month( Status(CurrentDate) ), 5, Year( Status(CurrentDate) ) ) is a calculated result but can use Set Field as it is a single date that results (yet it's not static)

I was just a bit confused over your definitions for the words. Thank you for helping me clarify.

Hi Will,

I had to leave for a little bit, and I just got back to find that you have resolved your problem.

It's nice to know that there are others in my area working with FileMaker too.

Best regards,

Lee