GoldMine Filter Syncs Too Many Records

Recently I upgraded from GoldMine 6.7 to 9.2.1.92. I have a filter defined that I use when syncing to Outlook. The filter count shows 1184 records, but it actually syncs 1544 records. What gives?

This is the SQL filter that returns a count of 1184:

WHERE c1.U_KEY5 LIKE 'YES%' 
  AND (c1.U_KEY1 = 'CUSTOMER (SRV.)' 
       OR c1.U_KEY1 LIKE 'FAMILY%' 
       OR c1.U_KEY1 = 'FRIEND' 
       OR c1.U_KEY1 = 'NEIGHBOR' 
       OR c1.U_KEY1 = 'PERS. BUS.' 
       OR c1.U_KEY1 = 'XXX')

But the sync log shows 1544 contacts created (and that corresponds to what showed up in Outlook). That’s exactly 360 more than expected, which corresponds to the “removed from filter” value, but what does that mean?

1.Outlook sync with old SQL clause in filter

Reviewing the filter in 6.7, I see the dBASE version is quite different. (I had been editing the dBASE version but not making corresponding changes to the SQL version.) This dBASE filter counts 1083 records, and that’s how many synced in 6.7:

"YES"$upper(contact1->key5)
.AND. 
("CUSTOMER"$upper(contact1->key1) 
 .OR. "FAMILY" $upper(contact1->key1)
 .OR. "FRIEND"$upper(contact1->key1)
 .OR. "NEIGHBOR"$upper(contact1->key1)
 .OR. "COLLEAGUE"$upper(contact1->key1)
 .OR. "DAD"$upper(contact1->key2)
 .OR. (
       ("PERS. BUS."$upper(contact1->key1) 
        .OR. "VENDOR"$upper(contact1->key1) 
        .OR. "COMPETITOR"$upper(contact1->key1) )
       .AND. 
       (     "(619)"$contact1->phone1
        .OR. "(858)"$contact1->phone1
        .OR. "(760)"$contact1->phone1
        .OR. "(951)"$contact1->phone1 
        .OR. "(619)"$contact1->phone2
        .OR. "(858)"$contact1->phone2
        .OR. "(760)"$contact1->phone2
        .OR. "(951)"$contact1->phone2 
        .OR. "(619)"$contact1->phone3
        .OR. "(858)"$contact1->phone3
        .OR. "(760)"$contact1->phone3
        .OR. "(951)"$contact1->phone3 
       )
      )
)

The Solution

I re-wrote the SQL query to match the dBASE query. After that, 1083 records were counted and synced in 9.2.1.92:

WHERE c1.U_KEY5 LIKE 'YES%' 
  AND (   c1.U_KEY1 LIKE 'CUSTOMER%' 
       OR c1.U_KEY1 LIKE 'FAMILY%' 
       OR c1.U_KEY1 LIKE 'FRIEND%' 
       OR c1.U_KEY1 LIKE 'NEIGHBOR%' 
       OR c1.U_KEY1 LIKE 'COLLEAGUE%' 
       OR c1.U_KEY2 LIKE 'DAD%' 
       OR ( (   c1.U_KEY1 LIKE 'PERS. BUS.%' 
             OR c1.U_KEY1 LIKE 'VENDOR%' 
             OR c1.U_KEY1 LIKE 'COMPETITOR%' )
           AND
            (   LEFT(c1.PHONE1,5) IN ('(619)', '(858)', '(760)', '(951)')
             OR LEFT(c1.PHONE2,5) IN ('(619)', '(858)', '(760)', '(951)')
             OR LEFT(c1.PHONE3,5) IN ('(619)', '(858)', '(760)', '(951)') )
          )
      )

The Cause

I finally figured out that if I OR’d the old and new SQL versions together, I got the 1544 count. This clause counts 1544 records:

WHERE (      
c1.U_KEY5 LIKE 'YES%' 
  AND (c1.U_KEY1 = 'CUSTOMER (SRV.)' 
       OR c1.U_KEY1 LIKE 'FAMILY%' 
       OR c1.U_KEY1 = 'FRIEND' 
       OR c1.U_KEY1 = 'NEIGHBOR' 
       OR c1.U_KEY1 = 'PERS. BUS.' 
       OR c1.U_KEY1 = 'XXX')      
 )
OR 
(       
c1.U_KEY5 LIKE 'YES%' 
  AND (   c1.U_KEY1 LIKE 'CUSTOMER%' 
       OR c1.U_KEY1 LIKE 'FAMILY%' 
       OR c1.U_KEY1 LIKE 'FRIEND%' 
       OR c1.U_KEY1 LIKE 'NEIGHBOR%' 
       OR c1.U_KEY1 LIKE 'COLLEAGUE%' 
       OR c1.U_KEY2 LIKE 'DAD%' 
       OR ( (   c1.U_KEY1 LIKE 'PERS. BUS.%' 
             OR c1.U_KEY1 LIKE 'VENDOR%' 
             OR c1.U_KEY1 LIKE 'COMPETITOR%' )
           AND
            (   LEFT(c1.PHONE1,5) IN ('(619)', '(858)', '(760)', '(951)')
             OR LEFT(c1.PHONE2,5) IN ('(619)', '(858)', '(760)', '(951)')
             OR LEFT(c1.PHONE3,5) IN ('(619)', '(858)', '(760)', '(951)') )
          )
      )
)

In other words, it seems that the 9.2.1.92 Outlook sync was using both the SQL clause and the dBASE clause, OR’ing them together, to determine which records to include. Once I updated the SQL clause to match the old dBASE clause, the sync included only the records counted by the filter.

The Solution

The Cause

Leave a Reply