Goldmine to SalesLogix - Multiple Email Values

ATTENTION: This forum is no longer active. Please navigate to our new support site at https://support.starfishetl.com/
Viewing 4 posts (of 4 total)
Anosh Wadia
User - Author
Post count: 19
#1

Am hoping you can give me some advice here.

I'm trying to figure out a way to map the multiple Goldmine Email addresses using Starfish.

Goldmine allows virtually unlimited emails because it saves them under a table called CONTSUPP WHERE RECTYPE='P' and CONTACT='E-MAIL ADDRESS'

In SalesLogix to accommodate up to 5 of these email addresses into the fields called EMAIL , SECONDARYEMAIL, EMAIL3, EMAIL4 and EMAIL5.

So I would like the logic to be, if in the destination record, if CONTACT.EMAIL has data, then it should check CONTACT.SECONDARYEMAIL. If it is blank, it should write the email address to this field.
If CONTACT.SECONDARYEMAIL has data then it should check CONTACT.EMAIL3….and if blank, write the email to the EMAIL3 field….etc etc….up to EMAIL5.

If EMAIL5 is blank we can't save any more emails and in which case we would skip the origin record.

Any suggestions on how to achieve this with Starfish?

Comments (4)

Aron Hoekstra's picture Aron Hoekstra
Administrator
Post count: 2
#2

That's pretty tricky.. I think what you're going to have to do is set up 5 variables and have a series of lookups that get preformed through code, assigning the 5 variables.. in most cases, only one if the 5 variables will actually have a value.. turn on Skip Blank Writes so you don't overwrite anything with blanks

Here's a start.. this is untested and you'll have to change the fieldsnames/lookup etc..

The SmartQuery method is used below to perform the lookups all in one call.

Global Include:

Dim Email1, Email2, Email3, Email4, Email5

Per-Row VBscript Procedure..

  1. Sub VBscriptProcedure
  2. 	    Email1=""
  3. 	    Email2=""
  4. 	    Email3=""
  5. 	    Email4=""
  6. 	    Email5=""
  7.  
  8. 	    dim email_array
  9. 	    email_array = SmartQuery("Contact", "ContactID='@@ORG:ID'", "Email,SecondaryEmail,Email3,Email4,Email5")
  10. 	    
  11. 	    if email_array(0,0) = "" then
  12. 	        Email1 = "@@ORG:Email Address@@"
  13. 	    elseif email_array(0,1) = "" then
  14. 	        Email2 = "@@ORG:Email Address@@"
  15. 	    elseif email_array(0,2) = "" then
  16. 	        Email3 = "@@ORG:Email Address@@"
  17. 	    elseif email_array(0,3) = "" then
  18. 	        Email4 = "@@ORG:Email Address@@"
  19. 	    elseif email_array(0,4) = "" then
  20. 	        Email5 = "@@ORG:Email Address@@"
  21. 	    else
  22. 	        LogMessage "All email fields filled for this contact"
  23. 	        GotoNextRow
  24. 	    end if
  25. 	End Sub

Then map Email1, 2, etc to the appropriate fields..

ScriptedField = Email1

Log in or register to post comments
srijanapokharel's picture srijanapokharel
User
Post count: 27
#3

I really like how Aron suggested it.Its the best way to handle.

If I had to handle such situation, I generally create a SQL script using the "ROW_NUMBER (Transact-SQL)". It will returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Example Query:
select ROW_NUMBER() over (partition by a.ParentObjectId order by a.ParentObjectId) as 'RowNum',
a.ParentObjectId,
a.EmailAddress
from EmailSearch a

RowNum ParentObjectId EmailAddress
1 BCCE48CA-9ED5-E011-90E6-00155D0A4402 test1ATtest.com
2 BCCE48CA-9ED5-E011-90E6-00155D0A4402 test2ATgmail.com
3 BCCE48CA-9ED5-E011-90E6-00155D0A4402 test3ATyahoo.co

You can update the record matching on the Primary key with the this query in the Origin. In the Mapping Tab, I would write the following code.

Email1 Field:
If "@@ORG:RowNum@@" = "1" Then
Scriptedfield="@@ORG:EmailAddress@@"
End If

I would use similar code in other Email fields too. If there are more then 5 email address you can put them in notes/description which can be easily identifies by the RowNum.

Reference: http://msdn.microsoft.com/en-us/library/ms186734.aspx

Log in or register to post comments
Aron Hoekstra's picture Aron Hoekstra
Administrator
Post count: 2
#4

Wow, that's really clever Srijana! I'll have to keep ROW_NUMBER in mind, that seems really useful. Thanks!

Log in or register to post comments
Anosh Wadia's picture Anosh Wadia
User - Author
Post count: 19
#5

Got this working beautifully with Aron's help...Thanks!

I also added a check to ensure that if an email address already exists for a contact, it doesn't write the same address again in the other fields..

Note that this also requires you to add this line of code to the VBScript Global section of your map
Dim Email1,Email2,Email3,Email4,Email5

Thought I'd post this for the benefit of others out there.

  1. dim email_array
  2. If UCase("@@FXO:CONTACT@@") = "E-MAIL ADDRESS" Then
  3. 	email_array = SmartQuery("Contact", "USERFIELD1='" & ContactLookup & "'", "Email,SecondaryEmail,Email3,Email4,Email5")
  4. 	If UBound(email_array)>=0 Then 
  5. 		if "" & email_array(0,0) = "" then
  6. 			email1 = "@@ORG:CONTSUPREF@@"
  7. 		elseif "" & email_array(0,1) = "" then
  8. 			email2 = "@@ORG:CONTSUPREF@@"
  9. 			'Check if this address is a duplicate of an existing address, and if so, don't write it
  10. 			If email2=email1 then email2=""
  11. 		elseif "" & email_array(0,2) = "" then
  12. 			email3 = "@@ORG:CONTSUPREF@@"
  13. 			'Check if this address is a duplicate of an existing address, and if so, don't write it
  14. 			If (email3=email2) or (email3=email1) then email3=""
  15. 		elseif "" & email_array(0,3) = "" then
  16. 			email4 = "@@ORG:CONTSUPREF@@"
  17. 			'Check if this address is a duplicate of an existing address, and if so, don't write it
  18. 			If (email4=email3) or (email4=email2) or (email4=email1) then email4=""
  19. 		elseif "" & email_array(0,4) = "" then
  20. 			email5 = "@@ORG:CONTSUPREF@@"
  21. 			'Check if this address is a duplicate of an existing address, and if so, don't write it
  22. 			If (email5=email4) or (email5=email3) or (email5=email2) or (email5=email1) then email5=""
  23. 		else
  24. 			LogMessage "All email fields filled for this contact"
  25. 		end if
  26. 	End If

Log in or register to post comments
Viewing 4 posts (of 4 total)

Forum Login

Login or sign up for our forums to connect to the user community.

Reply

You must log in to post.

Not a Member? Register.