Changes to the Notes History Common script in SalesLogix 7.5.1

I uncovered an interesting little change introduced in the SalesLogix 7.5 service pack 1 that led me to uncover some strange behavior with how attachments are stored against activities and history in the SalesLogix LAN client. 


A customer recently had an issue where as when right clicking on a History record and choosing “View Attachment” they would receive a dialog saying there was no attachment for the selected History record.  However, if they opened the History detail and went to the Attachment tab, there was an attachment listed.


The code that runs when the menu items are selected on the History tabs in Accounts, Contacts, and Opportunities is from the Notes History Common VBScript, which is an include file on all of the tab forms.  If we look in the ode of this script in SalesLogix 7.5.1 we can see that the following code has been added (Code shown in blue, additional code shown in red):



Sub PopupMenuClick(Sender, Item)
    Dim strSQL
    Dim objRS


    Select Case Item.MenuIndex



Case 4 ‘View Attachment
     If grdHistory.GetCurrentField = “” Then
        MsgBox Application.Translator.Localize(“Please select a record.”),,”SalesLogix”
     Else
        Set objRS = objDB.GetNewRecordSet
        strSQL = “SELECT h.ATTACHMENT, a.FILENAME, a.ATTACHID, a.DESCRIPTION ” & _
                 “From HISTORY h ” & _
                 “Inner Join ATTACHMENT a ” & _
                 “On (h.HISTORYID = a.HISTORYID) OR (h.HISTORYID = a.ACTIVITYID) ” & _
                 “Where h.HISTORYID = ‘” & grdHistory.GetCurrentField & “‘”  ‘DNL


Hmm, strange SQL join  going on here.  What gives?


Well if we look in the ATTACHMENT table we can see there are two columns contained in the table that join to either an Activity or History record, called ACTIVITYID and HISTORYID. 


SalesLogix Attachment table


Now if we actually examine the contents of the table we see something a little strange:



select * from attachment where isnull(historyid,”)<>”



SalesLogix Attachment table showing HISTORYID



select * from attachment where activityid like ‘h%’


SalesLogix Attachment table showing ACTIVITYID


Apparently, from what I can surmise, if you create a new unscheduled History record and add an Attachment the ATTACHMENT record gets created with HISTORYID populated as you would expect.  If you create an Activity, then add an Attachment and then complete the Activity the ATTACHMENT record has the ACTIVITYID value of the original scheduled activity replaced by the HISTORYID of the created History record, rather than having the ATTACHMENT row be updated as you would expect (where the ACTIVITYID is cleared and the HISTORYID column is populated).


Rather than correcting the strange behavior of how Attachments get linked to History records, the script mentioned above was adjusted to handle the behavior.


Keep in mind, the ACTIVITY column in the ATTACHMENT table is new as of 7.5.


While this particular code addresses the issue in the LAN, it would not address the potential issue of the web based Entity model where the entities can only be linked in defined relationships using one field.  If you needed to have a relationship from HISTORY to ATTACHMENTS (to see all Attachments on a History item in the entity model) you would not be able to use the built in relationship functionality.  Instead you would need to build this relationship in code.

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!