Whats wrong with this calculation?

[Chuck J]

I am attempting to create a script to find records with an empty date field using the "If" function. The complete script is

Show all records

If( IsEmpty(Finished), "", 0)

End If

Sort [Restore, No Dialog]

The script always return all records

Doing a manual search I find 254 records with

no dates.

What am I doing wrong?

[aaa]

Your script does not find Empty Records. If you want find empty records you can do such:

Enter Find mod

Set Field[Finished,"="]

Perform Find

I attached file for example.

aaa.zip

[-Queue-]

aaa's method will not work for date fields in version 6. Instead, you can use

Enter Find Mode [ ]

Insert Text [Finished, "="]

Perform Find [ ]

The Finished field must be on the current layout when the find script occurs.

[Chuck J]

The problem continues. The field is a date field, I can't seem to find empty date fields.

There in lies the problem.

Any other suggestions?

[-Queue-]

You can find empty date fields. Use the above script steps and make sure the field is on the layout when the script runs.

[Chuck J]

I tried the script and an error message pops up "The date in this field must be a valid date in the range of years yadda yadda"

[aaa]

First: "Use the above script steps and [color:red]make sure the field is on the layout when the script runs"

Second:You must instead Set Field script use Insert Calc Result or Insert Text

Edited October 5, 2005 by Guest

[LaRetta]

Forgive me for stepping in, Chuck, but ... first off, the opening thread script has no Find. Of course you will get all records because you've requested it. Second, if you're still trying to incorporate any of your If( IsEmpty(Finished), "", 0) portion, it will break. Your result (if date not empty), inserts a 0 into the field which is invalid also.

Your subject is "What's wrong with this calculation" but your opening script doesn't even have a SCRIPT ACTION (ie, Insert Calculated Result[] etc.)

Simply, you are missing something. Please post your EXACT script. We could spend time guessing for two weeks and get nowhere. These people are spending their time trying to solve your problem - at least spend time giving them back specific information.

LaRetta

[-Queue-]

See attached for a modified version of aaa's sample file, using the script steps I posted above.

qqq.zip

[Chuck J]

What seems to work consistantly is

Enter Find Mode

Insert Text [Finished, "="]

Perform Find []

Show Omitted

Apparently "=" finds literal text so at this point FileMaker is finding all records with an entry in the Finished field, the "Show Omitted" shows all the empty fields.

Am I correct in this assumtion?

Thank you everyone for your help.

FMForum is "da bomb"

[LaRetta]

Hi Chuck,

Did you try the file provided? Although I would have used Insert Calculated Result[], Insert Text[] does indeed work, as the file suggests.

I think you may be missing something else here. I wonder if you are aware of using Keep vs. Replace? It seems that versions prior to 7 were wonky in this regard; I recall struggling with the concept and pulling my hair out frequently over it.

FMForum is "da bomb"

Agreed! I'd have sunk long ago if not for the people on this forum.

LaRetta

[Chuck J]

LaRetta,

I must admit I am sometimes confused regarding "Keep vs. Replace", When to use it and what it does.

I know that when I revise a "Find" or "Sort" or other actions in a script that I need to Replace that action. What confuses me is if I revise a script and do something like adding a calculation to the script, I usually select "Replace" unless I haven't changed anything else.

I have been using FileMaker since Ver. 3 and I am still confused about when to use Keep or Replace.

[-Queue-]

What seems to work consistantly is

Enter Find Mode

Insert Text [Finished, "="]

Perform Find []

Show Omitted

Apparently "=" finds literal text so at this point FileMaker is finding all records with an entry in the Finished field, the "Show Omitted" shows all the empty fields.

Am I correct in this assumtion?

No. "=" does find literal text, but when used by itself, it signifies empty. So FM will find all records where the specified field is empty.

When you add the Show Omitted step, it should then show all records where the specified field is not empty. Add the step to my sample file, and it will return all records which do contain a date. This is the same result you would get if you used

Enter Find Mode [ ]

Insert Text [Finished, "="]

Omit Record

Perform Find [ ]

which omits all records where the date field Finished is empty.

Keep and Replace are not an issue here, because there is no print, import/export, sort, or find setting which can be effected. If you had 'restore' selected for, say, Perform Find, and you opened and modified, then closed the script, you would have the option of keeping the original settings for the step or replacing them with the most recently performed find's criteria. It works the same way for the other settings which can be kept or replaced.

[LaRetta]

Hi JT,

Yes, actually that was it (Restore)

I was able to break the demo file (leaving the script intact) by storing a prior find and I thought that might be the problem (since it still didn't work for Chuck and I knew the script was right). I had just forgotten which of the changes I tweaked caused the failure. Which must be why I've had problems with it in the past also. I'm so glad FM changed that with vs. 7. I was just figuring it out in 6 when 7 came out.

L

[-Queue-]

Well, yes, if Restore is selected, then Keep and Replace can be an issue. But there should be none selected in this script, except for his sort order. So, to clarify, if Chuck has Restore selected for the Enter Find Mode or Perform Find step, he should definitely remove it. Afterward, there will be no Keep or Replace option for the 'Find' criteria; so it will not be an issue.