if you need to find records with an AND search on those fields:
Enter Find Mode [ ]
Set Field [ YourFirstField ; "*" ] // this will find any record with YourFirstField NOT empty
Set Field [ YourSecondField ; "=" ] // this will find any record with YourSecondField empty
Perform Find [ ] // the combined requests will make an AND search, finding ONLY records with both criteria
If this is a manual find, one cannot find on "=" in a checkbox so you would manually do this once entering find mode:
1) Type * in the text field
2) Select Requests > Add New Request
3) Check your checkbox and then specify 'Omit'
4) Perform Find
Thanks for both of your answers, but I can't get either to work.
If I do a manual find, it tells me I have no records meeting the criteria, which is wrong.
In total I have 90 records. 25 of them have an entry in Text Field "Action" and 6 of those have unfinished Actions ie do NOT have "Yes" in the "Finished" Field (Label="Done") which means it should have a null value (be empty).
When I do the manual find as you suggested, LaRetta, (putting * in the field, not @) it tells me I have no records. So, I went back to "Show All", and ran the two parts of the manual Find separately. This gave me 9 records (there are in reality 25), one of which has nothing in at all (the find should only show those records that DO have an entry. If I ran the Find the other way, checking the "Yes" checkbox in the "Finished" Field and the "Omit" button the search gives me 83 entries ie all those records that don't have Finished marked because they don't have any Actions to be done.
As for the script version, Ray, do I insert that into my Report script, or somewhere else? If so, where?
Thanks again for the help! :-)
The technique I suggested does work so I suspect something else is going on. Are either of these fields related fields? Or are they in portals? When you view the fields in layout mode, do they have :: in front of the field name? Something isn't right somewhere. If the manual find won't work, neither will the scripted one.
Can you post up a link to your file for us to check it? If you don't want to do that, can you zip and send the file?
I'm unsure where you got the @ ... it certainly wasn't something I ever suggested. An asterisk is used (without quotes) to find anything in a field (not empty) and a simple = finds empty fields. But you cannot find using = on a checkbox which is why I suggested using omit.
Yes, the Report does use related fields. It takes the Client Name from the dClients table, and the Action To Dos from the related table. The report is based on the table, dContactEvents, and adds the Client Name from the related table dClients. I could show you the field relationships if I could upload an image, but for some reason that seems to be disabled on anything other than a first post. It's straightforward though. Clients is the main table, joined One to Many to dContactEvents.
You can upload your file to any free file sharing site such as 4Shared.com. It is free. Then provide a link here to it.
However, you haven't said specifically where the two fields you are searching reside. Remember that the results of a search of related records will only show the number of LOCAL records which match the search request. I suspect your finds are acting logically but need specific information. File would be best. :^)
The fields I am performing the find on are both in the "Many" table, dContactEvents; the results should show those two fields "dContactEvents.Action" and "dContactEvents.Finished", plus the dContactEvents.Date field in dContactEvents and the ::ClientName field from the "One" table, dClients.
Due to having live data in the db which I do not know how to duplicate or remove, I am unable to share the original db with you. So, I copied over the Tables, tried to copy the Layouts, Value lists and reports setup - and of course, it worked as intended.
Can you please tell me how to send you the original db without the data in?
Select File > Save a Copy As and specify clone (no records). YOu will need to upload the file somewhere - this site doesn't accept uploads directly. Then provide a link here to your file. Please specify on its opening layout what layout and fields you are having problems with, and point to your specific script as well.