10-27-2009 03:46 AM
I have a character field that I know is empty but when I do a lookup for "does not contain data" it only returns about 2/3rds of the records that are blank.
I have apx. 33k records, there are only 2k records that have data in this field, but only 24k records are returned in the 'does not contain data' lookup.
I tried to do a "Replace Field" and didn't type any information in the field to replace but that didn't fix it.
Seems to be the strangest thing.
We are ACT 2010 Premium
10-28-2009 01:53 PM
I would first try running maintenance on your database by going to Tools/Database Maintenance/Check & Repair. Run the utility twice in a row. If this does not work, then you may have to replace the field with a new one:
- Create a new character field to replace the current on and add it to your layout
- Do a lookup for "Contains Data" on that field, then from the list view, highlight and omit the contacts that appear to have no information in that field
- With the remaining contacts, use Edit/Copy Field to copy the data from the old field to the new field
- Do a "Does Not Contain" lookup on the new field to see if you get the expected results
- If so, then remove the old field from your layout and delete it if needed
Note: Effective 6/1/13, Sage no longers provides support for the Act! software. This is now provided by Swiftpage.
10-28-2009 05:27 PM
As a diagnostic measure you could also run a "Fields Usage Analysis Report" with ACTDiag
to see exactly how many of the fields in question appear as non-empty in the datastructure. Run
this on the machine hosting the database.
Start > Run > ACTDiag
Databases > Database List
Click on the Database Name
Actions > Database Structure > Fields Usage Analysis Report
11-02-2009 08:47 AM
The specific field in this scenario happens to be in the 2nd spillover
table. What we found was that even though there are roughly 35,000 records
in this database, there were only a little more than 6,000 records in that
It appears as if when you have spillover tables, the "does not contain data"
lookup only searches contacts that exist as rows within that spillover
table. And contacts only exist within the spillover table if they have one
of the fields in the spillover table filled in or did so at one time. I can
see this causing a lot of problems for a lot of people.
By doing an Edit-Replace and populating the field in question with an X for
all the contacts that did not previously have it filled in, those contacts
were added as rows to the spillover table. We then removed the X from that
field via Edit-Replace and the contacts stayed in the spillover table,
allowing the "does not contain data" lookup to run properly. But now on new
records added to the database we'll have a similar problem until this issue
This is a bug in ACT! 2010 and can be reproduced in the ACT2010Demo database:
Create 5 new character fields (test 1-5) in an "untouched" copy of the
ACT2010 demo database with a length of 450 characters each. This will
ensure that a spillover table is created and that the 5th field at the very
least is in that spillover table. Then perform a lookup for test 5 "does
not contain data". You will get a result of no records. This is because
test 5 is the first field in that spillover table and since no records have
that field or any other field in the spillover table filled in, it won't
find any. In 2009 this feature works without issue. In 2008 and 2007 the
lookup "empty field" feature works without issue in this fashion. It's
seems just 2010 is having problems with this.