Uploading Leads with Notes using Table level Scripts

3 minute read time.

Recently, we had a requirement where a client wanted to upload Leads with Notes. The way Data upload works for leads, we cannot map any field from upload file to tables other than the lead entity. Hence, we came up with below solution.

Requirements

  • 1. To be able to upload lead along with notes
  • 2. Notes can be uploaded later on for leads already in Sage CRM
    • In this case, new notes will be created for that lead
    • No changes to the previous notes (if any)
    • Upload file can have leads, which might not have notes for some leads being uploaded. That is
      • If the lead note is blank in upload file and any other field is being changed for that particular lead, then no lead note is inserted but previous notes still remain as it is ( updates on other fields would still work as users would like to process records — ignore or overwrite lead details).
      • In the same file if there are lead with notes column not blank create note for those leads

Assumptions

Standard lead deduplication defined to make sure there are no duplicates.

Credit: Jeff has discussed a clean way of updating 'opportunity assigned to' when 'company assigned to' is changed using table level script in this article using a Table Level Script. We used the same idea and extended this for our requirement - Using a lead Table Level Script to create new notes.

Solution

1. For the data upload, the lead entity would need to have a lead_latestNotes field with same data type as note_note on Notes Entity.This field will be used to temporarily hold data when doing Data upload.

2. Add following Table Level Script to Leads entity.

The Table Level Script will load the data stored under lead_latestNotes into notes table and link it with current lead.

Below code is for PostInsertRecord and UpdateRecord functions in a Table Level Script and a standard function getPrimaryKey() to get leadId.

Code for postInsertRecord function can be copied as it is to update function for notes to be uploaded on lead update.

//using whereclause to find leadid to simplify code for both postInsert and update record functions
function getPrimaryKey() {
strWhereClause = new String(WhereClause);
arrWhereClause = strWhereClause.split("=");
return arrWhereClause[1];
}

function PostInsertRecord() {
// Handle post insert record actions here
var strNoteTrigger = Values('lead_latestNotes');
// Declare variable to hold current value from lead_latestNotes field
if (strNoteTrigger != undefined)
// do not run below code if there is no value in lead_latestNotes field to prevent
//creating note when there is changes from CRM user interface not including lead
//notes
{
var strSQL = "Bord_Name='lead'";
// variable to hold lead entity for SQL where clause
var strNoteTableid = CRM.FindRecord('custom_tables', strSQL);
// Find correct table id for leads entity from custom_tables table
strNoteTableid = strNoteTableid.bord_tableid;
// store table id for lead entity into variable for later use

var strLeadId = getPrimaryKey();
// find current lead id for processing

var strLeadSQL = "lead_leadid = " + strLeadId;
// built SQL clause for lead id

var rcdLead = CRM.FindRecord('Lead', strLeadSQL);
// using lead id from above find actual record from SQL

var rcdNote = CRM.CreateRecord("Notes");
// Create new note record

rcdNote.Note_ForeignTableId = strNoteTableid;
// assign lead table id to note records — to map correct entity for the note

rcdNote.Note_ForeignId = strLeadId;
//assign lead id to notes foreign id — to map correct lead record note is
//linked to

rcdNote.Note_Note = Values('lead_latestnotes');
// assign note text to notes record

rcdNote.SaveChanges();
// save changes done to newly created notes record

Values("lead_latestnotes") = '';
//clear latest note so that if lead is updates notes doesn't get re-created
}
}

3. Now we are ready to upload leads and associated notes from upload file. Make sure to map Notes field in upload file to Lead_latestNotes.

Result of data upload


Notes

  1. Lead_latestNotes field does not have to be on any of the screens of Lead entity for upload to work.
  2. The Table Level Script code is for postinsertRecord function for new leads to be uploaded, as insert function will not have leadId to link to notes table


Results

Without showing Lead_notes field on any lead screens, we are able to load lead notes. This also worked as expected for a lead which is being updated and it creates new notes.

Hope this might be useful to the broader community, in case you come across similar requirement.

  • Hi Ravi,

    This is awesome! Nice work :)

    Can you help with assigning dates in postinsertrecord functions?

    I have come to the conclusion a date function will be required - which is fine, but is there a quicker - smarter way?

    Obviously, "GetVarDate" in all the examples in the manuals, only works for IE.

    Thanks Ravi - any pointers would be much appreciated!

    Best regards,

    Penny