Want to select a record based on matching two fields, one of them empty
I'm using FMPro 11 in single user mode on an OS X 10.5.8 Mac. I'm a newbie to FMPro but an old time Basic/Cobol/Fortran programmer so how hard can it be?!?
I have a table with multiple records per dive data recorder (DDR) each having specific issue and return dates as well as an ID for the DDR and an ID for the diver. I issue DDRs to divers recording the issue date, then record the return date when the diver is finished with his or her research. Later, I download depth, time and temperature data from the DDR, summarize that data into dives and assign them to a specific diver based on the date of the dive and the date of issue and return of the DDR (date of issue <= date of dive <= date of return).
I'm writing the FM script to return DDRs and want to find the record where the DDR ID matches an entry from the user and the date of return is null. That seemed more efficient than separate tables for issues and returns.
First I used the find mode in the DDR layout to enter the DDR number, and tried to setup a OnExitObject script to Constrain the Found Set to the record with an empty return date. But "= IsEmpty" won't pass the date format check in the automated script editor. Question: How do I match on a null field?
Second I tried to use a local variable entered via Show Custom Dialog but can't map the $DDRID to the find with the automated script editor (it wants to map to an actual field in a table.) Question: How can I use a local (or global) variable in a find script step?
Last question: Is there a way to edit scripts directly?
Any help would be greatly appreciated.