If you do a lot of importing into GoldMine you might want to keep track of who imported what and when.

Here is what you would think would be a simple query to give us at least part of this:


select
source, count(source)
from contact1
where createby = 'goldbox'
group by source
order by count(source) desc


And, indeed, this works fine in SQL Management Studio. However, in GoldMine this gives an error and so we have to find another way.

GoldMine’s error message does give us a big hint by saying that it wants us to use the ‘HAVING’ clause. ‘HAVING’ will only work on an element in our ‘GROUP BY’ clause though so, if I want to filter by user name I must include the ‘createby’ field in my column and GROUP BY lists.

While I’m at it, I’ll add the creation date in there as well:


select
createon, createby, source, count(*)
from contact1
group by createon, createby, source
having createby='Goldbox'
order by createon desc, source


This will work nicely, ordering the list in descending date order.

What if multiple ‘ordinary’ users are importing records though? How can I tell an import from just regular new contacts? Well, perhaps we can say that we’re only interested if more than 2 records have the same source, and were created on the same day and by the same user:


select
createon, createby, source, count(*)
from contact1
group by createon, createby, source
having count(*) > 2
order by createon desc, source


Finally, we might want to ignore those records with blank source fields:


select
createon, createby, source, count(*)
from contact1
group by createon,createby,source
having count(*) > 2 and
source > ''
order by createon desc, source