Hi all, migrating to GP2013 from GP10 I understand there is now internet information that needs populating in Customer Cards for electronic invoicing. Specifically the email addresses in the To... and Cc... fields.
The problem is I see now way of mass populating these using Integration Manager. Has someone found a way around this rather than manually going through every customer card manually populating? I see it's SY01200 table that holds the data.
Thanks.
*This post is locked for comments
I'm attempting to enter a default email for new employees. Let's call it "default@me.com" for example. However, integration manager runs without error and the field is not populated. First, I tried this with a script:
If DocumentIsNew then
Currentfield = "default@me.com"
Else Currentfield.SetToDefault
End if
When that didn't work - I tried to hardcode it - just to see if it would work. I chose "constant" and source = default@me.com.
Still, integration runs smoothly - but no value in email. Note that on the addresses folder, for addressID I'm using constant and source = PRIMARY
Any help is greatly appreciated. Some pics attached.
Thanks!
Thank you Frank, that was exactly what I needed and has solved my issue. Ian, thanks for all your work!
Daniel, here's what the mapping needs to look like. I tested this and it worked in my system. One caveat - you can't use Table Import to update records, only insert. Also, there is no data validation using Table Import. If your source data is bad, you'll have bad data in SY01200, so make sure you manually validate your source data should you decided to use this method. Take a back up of your database first, as well, just to be safe.
Due to problems on my dev box I haven't been able to test the following as a whole although the VBA is based off another one I did recently and the SQL has been used individually.
The following goes against the Before Integration script hook:
Share
Report
All responses (
Answers (
' BEFORE INTEGRATION
Dim oCon
Dim sINTERID
Set oCon = CreateObject("ADODB.Connection")
oCon.ConnectionString = "database=" & GPConnection.GPConnIntercompanyID
GPConnection.Open(oCon)
SetVariable "gblConn", oCon
The following goes against the After Document script hook:
' AFTER DOCUMENT
Set oCmd = CreateObject("ADODB.Command")
With oCmd
.ActiveConnection = GetVariable("gblConn")
.CommandText = "SELECT TOP 1 COUNT(EmailToAddress) AS Count FROM SY01200 WHERE Master_ID = '" & SourceFields("VendorEmails.VENDORID") & "' AND ADRSCODE = '" & SourceFields("VendorEmails.ADRSCODE") & "'"
Set rsQuery = .Execute
If Not (rsQuery.EOF and rsQuery.BOF) Then
If rsQuery("Count") > 0 Then
.CommandText = "UPDATE " & _
"SY01200 " & _
"SET " & _
"EmailToAddress = '" & SourceFields("VendorEmails.EmailToAddress") & "', EmailCcAddress = '" & SourceFields("VendorEmails.EmailCcAddress") & "', EmailBccAddress = '" & SourceFields("VendorEmails.EmailBccAddress") & "' " & _
"WHERE " & _
"VENDORID = '" & SourceFields("VendorEmails.VENDORID") & "' AND ADRSCODE = '" & SourceFields("VendorEmails.ADRSCODE") & "'"
Set rsUpdate = .Execute
Else
.CommandText = "INSERT SY01200 " & _
"(Master_Type,Master_ID,ADRSCODE,INETINFO,EmailToAddress,EmailCcAddress,EmailBccAddress) " & _
"VALUES " & _
"('VEN','" & SourceFields("VendorEmails.VENDORID") & "','" & SourceFields("VendorEmails.ADRSCODE") & "','','" & SourceFields("VendorEmails.EmailToAddress") & "','" & SourceFields("VendorEmails.EmailCcAddress") & "','" & SourceFields("VendorEmails.EmailBccAddress") & "')"
Set rsInsert = .Execute
End If
End If
rsQuery.Close
Set oCmd = Nothing
End WithThe above VBA assumes your integration record source is called VendorEmails and you have the following columns:
The script checks if the email information exists and if it does then it does an update, otherwise it does an insert.
Daniel, I'll see if I can knock together an example for you later today or tomorrow.
I couldn't get table import to work. Get telling me there were mandatory fields even though they don't need populating when you look at other records already completed...
I don't understand the VB post, how is that done?
The other thing to do is to map to the existing fields and then in SY01200 use an update script:
UPDATE SY01200 SET EmailToAddress = INET1
Once you have the records in the SY01200 table, you could then use an insert script to take care of the remaining EmailCcAddress and EmailBccAddress.
Mail merge in Word can be used to populate the script as needed.
I have a video on Mail Merge (related to creating macros - another good way of updating data) if it is of use: www.youtube.com/watch
There are no equivalent fields in GP for these. These internet address are simply per individual. The 'To' and 'cc' addresses come into play per email not per individual.
You might want to consider using Table Import. Have a look at SY01200 in the company database. That's where email addresses are stored.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156