Add attachment to opportunity using C# DLL in ACT2013

[julienIDtech]

Hello dear ACT Developers Community,

I'm currently working on a PHP interface that links a MySQL Database on a web-server to an ACT 2013 Database. The PHP code uses a DLL named ComBridge to call certain functions of the Act Framework. The DLL is developed in C#.

I managed to create a contact and an opportunity, but fail when tying to add an attachment to the opportunity. Would love to get some help on this... I searched the knowledgebase for related posts, but none of the solutions I found solved my problem.

I've tried 2 different methods in the DLL. Both of them are called through following PHP call:

$attachment = $testComBridge->SetAttachmentToOpportunity($actFramework, $oppGuid, $contact, $pdfPath, $pdfDisplayName, FALSE);

*** METHOD 1:

public string SetAttachmentToOpportunity(ActFramework actFramework, string guidOpportunityClient, string guidContact, string docPath, string nameAttachmentToDisplay, bool localAttachment)
{
// Get the opportunity
OpportunityList opportunityListClient = GetAnOpportunityCLientByGuid(actFramework, guidOpportunityClient);
Guid oppGuid = new Guid(guidOpportunityClient);
Attachment myFile;
if (localAttachment == true)
{
//Create attachment as a file
myFile = actFramework.SupplementalFileManager.CreateAttachment(AttachmentMate.Activity, docPath, nameAttachmentToDisplay, false);
}
else
{
// Create attachment as a link
myFile = actFramework.SupplementalFileManager.CreateShortcutAttachment(AttachmentMate.Activity, docPath, nameAttachmentToDisplay, false);
}
//Add attachment to the opportunity
actFramework.SupplementalFileManager.AddAttachment(myFile, oppGuid);
return "";
}

*** METHOD 1 RESULT:
Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Act.Framework<br/><b>Description:</b> The INSERT statement conflicted with the FOREIGN KEY constraint "ATTACHMENT_ACTIVITYID_FK". The conflict occurred in database "Bioseb28b", table "dbo.TBL_ACTIVITY", column 'ACTIVITYID'. The statement has been terminated.

*** METHOD 2:
Note: this method is based on following post:
http://community.act.com/t5/Act-Developer-s-Forum/Attach-Excel-document-to-Contact-in-PlugIn/td-p/16...

public string SetAttachmentToOpportunity(ActFramework actFramework, string guidOpportunityClient, string guidContact, string pathWhereDocAreStored, string nameAttachmentToDisplay, bool localAttachment)
{
// Get opportunity
OpportunityList opportunityListClient = GetAnOpportunityCLientByGuid(actFramework, guidOpportunityClient);;
Attachment myFile;
Opportunity o = opportunityListClient[0];

History h = actFramework.Histories.CreateHistory();
Attachment att = actFramework.SupplementalFileManager.CreateAttachment(AttachmentMate.History, pathWhereDocAreStored, "This File", false);
HistoryType ht = actFramework.Histories.GetHistoryType("Library Document");
h.OpportunityList = opportunityListClient;
h.CreateUserID = actFramework.Contacts.GetMyRecord().ID;
h.HistoryType = ht;
h.Regarding = "Test";
h.Details = "";
h.StartTime = System.DateTime.Now;
h.EndTime = System.DateTime.Now.AddDays(2);
h.ManageUserID = actFramework.Contacts.GetMyRecord().ID;
h.Update();
return "";
}

*** METHOD 2 RESULT:
Fatal error: Uncaught exception 'com_exception' with message '<b>Source:</b> Act.Framework<br/><b>Description:</b> Unable to insert a history'

Thanks a lot in advance for your help!

Cheers,

Julien

[jimdurkin]

Because that DB threw an FOREIGN KEY constraint error. I would try it on a different DB.

-- Jim Durkin

[julienIDtech]

Thanks Jim!

I will try this and let you know. But if it's a database-related problem, is there any way to "fix" the database to make these functions work? I've tried to verify and repair the database using ActDiad already, to no avail...

Julien

[jimdurkin]

If it's database related and you have tried all the repair options in the ACTdiag too you need to send the DB to act! for repair.

Even though the backbend SQL MDB files are soooo much improved over the older DB$ file formats these SQL DB still get corrupted at times.

Here is the page to contact the DB service guys http://act.com/support/database-services/

Hope this helps

-- Jim Durkin

[knif]

Based on the looks of it, I wouldn't consider it a DB corruption problem. I'm not sure what fields are needed for the key "ATTACHMENT_ACTIVITYID_FK," but since the message specifically calls out the field dbo.TBL_ACTIVITY.ActivityID, I would say that you have added an attachment, but that attachment needs to be linked to an Activity (not sure if an Opportunity counts as an Activity). Based on what is listed in Method #1, no activity was made, thus the foreign key constraint wasn't adhered to. Just my 2 cents.

[julienIDtech]

Hi Jim,

Thanks for your reply. Knowing that the database I'm using is an old database that has been updated from ACT5 to 6 to 7 to 2010 to 2011 to 2013, it could be that the whole structure is a bit messed up. It might be worth it to check with the ACT support indeed if they could at least check it.

Cheers,

Julien

[julienIDtech]

Hello Knif,

Thanks a lot for sharing your 2 cents

What I don't understand is why do I need to create an activity in order to just add a document to an opportunity?

Julien

[julienIDtech]

Hello,

So I found the solution to my problem, and wanted to keep you guys updated....

The problem was not linked to the integrity of the database - it was linked to the fact that I created an attachment on an activity-type attachmentmate, while later trying to add the attachment to an history... Using an attachmentmate.history instead solved the problem.

Here is the final code, for those interested.

//Creates an attachment object containing the PDF file, and adds the attachment to the opportunity's history
public string SetAttachmentToOpportunity(ActFramework actFramework, string guidOpportunityClient, string guidContact, string pathWhereDocAreStored, string nameAttachmentToDisplay, bool localAttachment)
        {
            try
            {
                // Recuperation of the opportunity as a table from the the opportunity GUID
                OpportunityList opportunityListClient = GetAnOpportunityCLientByGuid(actFramework, guidOpportunityClient);
                Attachment myFile;                                                      //Creation of the attachment object
                HistoryList historyListOpp = opportunityListClient[0].GetHistories();   //Recuperation of the history list for the opportunity
                Guid historyGuid = historyListOpp[0].ID;                                //Creation of the history object as a GUID

                if (localAttachment == true)
                {
                    //Create attachment as an embedded file in the history
                    myFile = actFramework.SupplementalFileManager.CreateAttachment(AttachmentMate.History, pathWhereDocAreStored, nameAttachmentToDisplay, false);
                }
                else
                {
                    // Create attachment as a link in the history
                    myFile = actFramework.SupplementalFileManager.CreateShortcutAttachment(AttachmentMate.History, pathWhereDocAreStored, nameAttachmentToDisplay, false);
                }
                //Add attachment to the opportunity's history
                actFramework.SupplementalFileManager.AddAttachment(myFile, historyGuid);

                return "Attachment successful";

            }
            catch (System.Exception E) //In case of any error, log the error into the log file with the date and return an error message
            {
                string errorDate = "Date/Time" + DateTime.Now;
                System.IO.File.AppendAllText("C:\\ComBridgeLog.txt", errorDate);
                System.IO.File.AppendAllText("C:\\ComBridgeLog.txt", E.ToString());
                return "Attachment failed";
            }
        }

Thanks again for your help!

Julien