If you have more than one GoldMine database (an archive, for example) you might find it useful to be able to search both (or more) databases at the same time. Consider this scenario:

  1. I archive all my old contacts, i.e. those who were created more than 5 years ago, and those with no history for the last 3 years. (We'll cover how to do this in a future article.)
  2. I'm looking for Joe Schmoe. I know it's been a while since I've done anything with Joe, but I really don't know if it's been so long that he would have been archived.
  3. My choices are to search for Joe in my current open (Main) database and, if not found, open the archive. But this is somewhat awkward and inconvenient. I want a way to search both at the same time.

The solution:

GoldMine's dashboards are based on SQL queries, and querying outside the currently open database is permitted. We'll use a union query here to search the currently open GoldMine database, plus the archive, and return an aggregate data set, complete with address and phone number.

In this article we'll simply show you the query; in a future article we'll explore the other steps needed to build this query in to a dashboard, and we'll update this article with a link to it. Meantime, all you GoldMine dashboard gurus can get straight to work.

Here it is:

select top 1000  'Production' as DB, company,contact,address1,city,state, zip, phone1,lastname, accountno from contact1
where 1=1 <<company>> <<contact>>

union all

select top 1000  'Archive' as DB, company,contact,address1,city,state, zip, phone1,lastname, accountno from goldminearchive..contact1
where 1=1 <<company>> <<contact>>

union all

select '','','','','','','','','','xyzzy'
where NOT exists (select accountno from CONTACT1 where 1=1 <<company>> <<contact>>)

order by company, contact

Note, the <<company>> token works out to company like    '%<<value>>%' and the contact token is similar. The company and contact values are driven by events from text-input fields in the dashboard. Click this link to see a video of this dashboard in action.

Comments

Great... thanks for this!