ContactReview :: View topic

tt · GoldMine Apprentice

I am creating an import for information from Dunn and Bradstreet. Our current GM database has companies that we imported from D&B several years ago and that we want to update if a match is found. In Goldbox the Custom Match expression I am using is:

tgt->udnbno + smoothname(tgt->contact)

udnbno is a field previously created that has D&B's company number.

Where I need some help is in further limiting the records that will be updated based on the value in the Key1 field. We use the Key1 field as a "File Type" such as Client, Prospect, Suspect, New Lead, etc.

I want to only update records that have the value of "New Lead" or "Suspect" in the Key1 field and I am not sure how to do this in the Custom Match expression.

I was thinking of using the following expression but am not sure if would be allowed in the Custom Match field.

tgt->udnbno + smoothname(tgt->contact) + fieldcontains(tgt->key1,"New Lead|Suspect")

I would welcome any input as to whether the above expression will work, and whether it is correctly coded - especially New Lead with the space between the words. If I'm headed down the wrong path I'd appreciate any pointers to get me headed in the right direction

BobTaylor · GoldBox Guru

The FieldContains function evaluates to a logical value (TRUE/FALSE), and the matching expression must evaluate to a literal value. So no, you cannot use what you have proposed.

It seems to me that what you have described would be fulfilled by simply using the Filter

tt · GoldMine Apprentice

Hi Bob,

Thank you for helping me. I had a gut feeling that the FieldContains function would not work in a Custom Match expression. Thanks for verifying that for me.

I looked at the filter that you suggested and had a couple of questions.

First of all I am assuming that you are suggesting that the filter expression you suggested would be entered into the Filter Expression field in the Import/Export setup.

If my assumption is correct, it looks to me like your expression would be looking at the Source file to see if it has "Suspect" or "New Lead" in the Key1 field and if so it will update the record in the Target database. But what I want to do is is the opposite. I need a filter that will evaluate whether "Suspect" or "New Lead" is in the Key1 field of the Target (existing GM database) and if so update the record, and if not then bypass the record without updating it.

I know I could write a sql statement in a precise Query Match expression but I want to use the logic of the SmoothName function to have a safer query.

I suppose that I could do a 2 step match. The first would not update any records and would just write back the value of the Key1field from the Target to the Source anytime it finds a match. The second could then apply the Filter Expression you suggested and update records based on the filter. But I was hoping that there was a way to create a filter that would evaluate and filter the Target database in the first match. Is it possible to do that?

BobTaylor · GoldBox Guru

Now I understand you better. What version of GoldMine and GoldBox?

tt · GoldMine Apprentice

Goldbox-7 and GM 9.0.0.102

BobTaylor · GoldBox Guru

The bottom item on the form, "Include", is intended for that purpose. Enter a partial query like:

Contact1.key1 = 'New Lead' or Contact1.Key1 = 'Suspect'

I would suggest that you do a limited test of the procedure to make sure it works; I have not used this particular part of GoldBox in several years.

tt · GoldMine Apprentice

Bob,

Just wanted to check in and give you an update. Adding the filtering expression in the "Include" field of the Import setup worked like a charm. I ran a bunch of tests and no problems at all.

I've got the import setup just about finished. I did run into one other issue I'm not sure about. The import is going to attach an automated process through a "Supplemental (Generic-A/P)" Plug-In Tab. I've got all of the expressions mapped out for the plugin except fot the expression for the "Recid" field. I am not sure if it needs an expression or whether Goldmine will add it automatically. When I look at the Recid field in the Contsupp table all the values are unique which is why I'm thinking Goldmine will automatically populate the field.

BobTaylor · GoldBox Guru

Yes, GoldBox does handle all Recid issues transparently. Sync awareness is preserved.

tt · GoldMine Apprentice

Thanks for confirming my last post.

I've been doing some additional testing and came across an issue I'm not sure how to address. Currently I have the Custom Match expression as:

Tgt->UDNBNO+SmoothName(Tgt->Contact)

and the Include expression as:

Upper(Contact1U_Key1)='New Lead' or Upper(Contact1U_Key1)="Suspect"

This works great at limiting any update to only records that have New Lead or Suspect in the Key1 field.

The issue I came across in my testing is that if we have a contact in the Source file that matches on the Custom Match Expression above but has the value of "Client" in Key1 (or any other value other than "New Lead" or "Suspect") then the import will not find a match and will add the Source record as a new record in the Target.

This certainly would not work as an existing client would then have a duplicate record in Goldmine and new marketing campaingns would be initialized based on the new record. I need to be able to create an expression (or process) where if the Source record exists in the Target database (based on the Custom Match expression above) and has a Key1 value other than New Lead or Suspect then it is excluded from the import and will not added to the Target database.

I am not sure if this can be done by reworking of the Custom Match and Include expressions. I have a gut feeling that it can't. The other option I thought of would be to be to create a Write Back of the Key1 fields to the Source database. Then I could use a Filter expression in the import setup to only import any records where the Key1 field in the Source would be blank or New Lead or Suspect.

Thanks again for your help.

BobTaylor · GoldBox Guru

It sounds like GoldBox is performing as it should, although that's a bit surprising, because you have left the periods out out of your Include expression. Should be