Skip to content


Update blank data (sometimes)

Brandon Aronson asked 5 years ago
Hello,�I have an issue where I am bringing over customer demographic information into Great Plains.  There will be times when I want to bring over cc data, and other times when I want to keep the existing that is currently in GP.For example, if Classid = null, then leave the existing credit card information on the record.  The data source I am coming from will have blank data in the credit card fields. I really like the idea of the update blank data checkbox, but I can\’t have it fire all the time. �Is there a way to programatically use the update blank data checkbox?Is there another functionality of smartconnect that I am missing, to help me accomplish this?Thank you.  I hope this makes sense.
Patrick Roth Staff answered 5 years ago
One solution to your issue is that you could make your own SQL connection in a script task.  Then in the script, for fields that you want to stay the same, you would query the table and return that value.
Then use that calculation as the input to the field. 
so your calculation might look like:
if _CLASSID = “” then
    ’empty so leave field empty by returning empty string
     return “”
     ‘have a class value so use the value from our data
end if
so now your code will either pass empty or else the value from your data.
So there isn’t a direct way to send in a blank in smartconnect other than marking that checkbo- so if you wanted to change the credit card number from xxxx to blank you’d have to map the field and set the ‘update blank data’.
but the interesting thing is, the only thing that really does is wrap the CDATA tag in econnect.
Microsoft talks about using it above.
so if we wanted to send a blank (to overwrite an existing value) then you could make a calculation that returns
![CDATA[ ]]
as noted in the link.  so you could actually just use the calculation and either set the calculation to your field or else return the tag above.
again note that this tag actually forces econnect to use a space and overwrite data.  not exactly what you said you were looking to do.
Delano answered 5 years ago
Hi Patrick,
I have a slightly different issue, which has to do with a blank date. I am moving CC info (number and expiry date) from CRM to GP. Not all customers pay with CC which means we don’t have CC info for all of them. My map fails when the CC expiry date is blank. I have tried a few different iterations of the following with no results.
if fn.ISEMPTY(_CC_EXPIRY__C) then
return “1900-01-01”
end if
I continue to receive the error: “Failed to convert parameter value from String to a DateTime.”
Do you have any suggestions?
Thank you
Lorren Zemke Staff replied 5 years ago

Delano, since the _CC_EXPIRY__C field would be a date field you will need to translate it to a string before using the ISEMPTY function.

You could try the following

If string.IsNullOrEmpty(_CC_EXPIRY__C.ToString()) then
return “1900-01-01”
return _CC_EXPIRY__C
End if

Delano replied 5 years ago

Thank you Lorren, that did it.

Steve Grant replied 4 years ago

Thanks Lorren… I needed this example and it works perfectly.

If you would like to submit an answer or comment, please sign in to the eOne portal.