Link MS Access to GoldMine using GM+View 1

nikhilkomakula · Dec 29, 2009

I am novice in using GoldMine CRM software. I got a requirement here.

I need to extract relevant information from MS Access and show it to the GoldMine user. Tables in both MS Access and GoldMine are related with a customer ID. When I called the tech support guy, he suggested me to use GM+View but I am not able to get how to do that neither could find any examples on google.

Any help is greatly appreciated.

Thank you in advance.

DJHunt · Dec 29, 2009

You need to use script to connect to Access using the ODBC, and the script the query for the information. Your HTML code can then pull from the query results and display it.

DJ Hunt
Phone: (978)2-3333
WebSite: http://www.DJHunt.US

- The Hacker's Guide to GoldMine Premium
- One-on-One GoldMine Technical Support ( Fee Based )

http://www.DJHunt.US/Default.html

nikhilkomakula · Dec 29, 2009

Thank you for your reply.

Could you please suggest me what type of scripting I should use to do that.

DJHunt · Dec 30, 2009

Here is an example:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "

http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="

http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<script language="JavaScript" type="text/javascript">
//Global vars for reuse. If you don't want the user to get the AX warning
// right away (annoying when you are skipping thru records)
// you can make these instances only occur when the user performs an action.
var objGMXMLAPI = null;
var objMSXML = null;
var objMSXMLOut = null;

var gmCodeSection = null;
var gmContactValues = null;
var gmEnvironValues = null;

// this function here adds GM information into the DOM and inits all objects
function gmInit()
{
if (document && document.getElementById) // if the javascript parser contains DOM and getElementByID method
{
// is there a gmCodeSection Already?
gmCodeSection = document.getElementById("gmCodeSection");
if (!gmCodeSection)
{
// create the gmCodeSection as a div
var bod = document.body;
gmCodeSection = document.createElement("div");
gmCodeSection.id = "gmCodeSection";
bod.appendChild(gmCodeSection);

}

//init our GM objects
objGMXMLAPI = new ActiveXObject( "GMW.UI" );
objMSXML = new ActiveXObject( "Msxml2.DOMDocument" );
objMSXMLOut = new ActiveXObject( "Msxml2.DOMDocument" );
gmInitEnvironValues();
gmInitContactValues();

}
}
function gmInitEnvironValues()
{
if (gmCodeSection)
{
gmEnvironValues= document.createElement("div");
gmEnvironValues.id = "gmEnvironValues";
gmCodeSection.appendChild(gmEnvironValues);
gmEnvironValues.USERNAME = gmMacro("&UserName");
gmEnvironValues.USERFULLNAME = gmMacro("&UserFullName");
gmEnvironValues.SYSDIR = gmMacro("&SYSDIR");
gmEnvironValues.GOLDDIR = gmMacro("&GOLDDIR");
gmEnvironValues.COMMONDIR = gmMacro("&COMMONDIR");
gmEnvironValues.EXEDIR = gmMacro("&EXEDIR");
gmEnvironValues.SERIALNO = gmMacro("&SERIALNO");
gmEnvironValues.LICUSERS = gmMacro("&LICUSERS");
gmEnvironValues.SQLLICUSERS = gmMacro("&SQLLicUsers");
gmEnvironValues.SQLLICUSERS = gmMacro("&SQLLicUsers");
gmEnvironValues.VERSION = gmMacro("&Version");

}

}

// gmInitContactValues puts the current contact data in the DOM under gmCodeSection.gmContactValues.XXXXXXXXX
// where XXXXXXXXXXX is a GM field from C1 or C2 - like CONTACT1CONTACT
function gmInitContactValues()
{
//if we don't have a parent code section then we can't do anything
if (gmCodeSection)
{
//get the gmContactValues div if it exists
gmContactValues = document.getElementById("gmContactValues");
if (gmContactValues)
{
//if it exists - then we kill it to start fresh
gmCodeSection.removeChild(gmContactValues);
gmContactValues = null;
}

// create a new instance to populate
gmContactValues = document.createElement("div");
gmContactValues.id = "gmContactValues";
gmCodeSection.appendChild(gmContactValues);

//get the list of GMFields

var oGMData = new ActiveXObject( "GMW.GoldMineData" );
objMSXMLOut.loadXML(oGMData.ExecuteCommand('<GMAPI call="GetFields"/>'));
oGMData = null;
var nlFields = objMSXMLOut.selectNodes("GMAPI/data/data[@name='field']");
// do we have a number of fields?
if(nlFields.length)
{
//iterate thru the fields and add their values to our gmContactValues div
for (iNode = 0; iNode < nlFields.length; iNode++)
{
//get the individual field
var ndField = nlFields.item(iNode);

//set the table and field names from the XML returned
var sTable = ndField.selectSingleNode("data[@name='DBFName']").text;
var sField = ndField.selectSingleNode("data[@name='Field_Name']").text;

//get the value
var sValue = gmMacro(sTable + "->" + sField);
if ( sValue /* && sValue !='' */ )
{
eval("gmContactValues." + sTable + sField + " = sValue");
}

}
//since Email and WebSite aren't C1 or C2 - we add them ourself
gmContactValues.PRIMARYEMAIL = gmMacro("&EmailAddress");
gmContactValues.PRIMARYWEBSITE = gmMacro("&WebAddress");
}
}

}
function gmSkip(recCount)
// Performs a record object skip
{
//use the first global instance of Msxml2.DOMDocument to build the XML
objMSXML.loadXML('<GMAPI call="RecordObj"/>');
// pass the dirty work to helper function.
gmAddDataNode(objMSXML.documentElement, "Command", "Skip");
gmAddDataNode(objMSXML.documentElement, "argument", recCount);
// objGMXMLAPI will Execute the XML from objMSXML
objGMXMLAPI.ExecuteCommand( objMSXML.xml);
//Set the new contact information
setContactDiv();

}

function gmGoto(sRecID)
// puts the GM Record object on a specific Contact based on RecID
{
//use the first global instance of Msxml2.DOMDocument to build the XML
objMSXML.loadXML('<GMAPI call="RecordObj"/>');
// pass the dirty work to helper function.
gmAddDataNode(objMSXML.documentElement, "Command", "Goto");
gmAddDataNode(objMSXML.documentElement, "argument", sRecID);
// objGMXMLAPI will Execute the XML from objMSXML
objGMXMLAPI.ExecuteCommand( objMSXML.xml);
//Set the new contact information
setContactDiv();
}

function PopulateRows()
{
var sSQL ="";
sSQL = "SELECT ondate, Duration, Notes, ref " +
"FROM conthist WHERE ActvCode LIKE 'C%' AND ResultCode = 'COM' AND " +
"OnDate+30 >= getdate() AND accountno = '" +
gmContactValues.CONTACT1ACCOUNTNO + "' ORDER BY ondate DESC";
olMSXML = new ActiveXObject( "Msxml2.DOMDocument" );
olMSXML.loadXML(gmPerformSQLStream(sSQL));

var ondRows = olMSXML.selectNodes("GMAPI/data[@name='Return']/data[@name='Rows']/data[@Name='Row']");

if (ondRows)
{

var oTB = document.getElementById("tableBody");
if (oTB )
{

if( ondRows.length > 0)
{

for (i = 0; i < ondRows.length; i++)
{
addRowOfQBData(ondRows.item(i));
}

}
else
{
var sName = gmContactValues.CONTACT1COMPANY;
if (sName == '')
{
sName = gmContactValues.CONTACT1CONTACT;
}
var oTR = document.createElement("tr");
var oTD = document.createElement("td");
var oCS = document.createAttribute("colspan");
oCS.value = "3";
oTD.setAttributeNode(oCS);
oTD.innerText = sName + " has no Contract Support history entries.";
oTR.appendChild(oTD);
oTB.appendChild(oTR);
}
}
}
document.getElementById('PageTitle').innerText = "GoldMine Support History";
}
function addRowOfQBData(oNode)
{
if (oNode)
{
var oDurNode = oNode.selectSingleNode("data[@name='DURATION']");
var oCallDateNode = oNode.selectSingleNode("data[@name='ONDATE']");
var oCallTimeNode = oNode.selectSingleNode("data[@name='ONTIME']");
var oRefNode = oNode.selectSingleNode("data[@name='REF']");
var oNotesNode = oNode.selectSingleNode("data[@name='NOTES']");
var sDate = "";
if (oCallDateNode)
{
var sGMDate = oCallDateNode.text;
var sMonth = sGMDate.substr( 4, 2);
var sDay = sGMDate.substr( 6, 2);
var sYear = sGMDate.substr( 0, 4);
sDate += sMonth + "/" + sDay + "/" + sYear;
}

var oTB = document.getElementById("tableBody");
var oTR1 = document.createElement("tr");
var oTrClass = document.createAttribute("class");
oTrClass.value = "tddata";
oTR1.setAttributeNode(oTrClass);
var oTD = document.createElement("td");
var oTdWidth = document.createAttribute("width");
oTdWidth.value = "30";
oTD.setAttributeNode(oTdWidth);
oTD.innerText = sDate;
oTR1.appendChild(oTD);
oTD = document.createElement("td");
oTdWidth = document.createAttribute("width");
oTdWidth.value = "30";
oTD.setAttributeNode(oTdWidth);
if (oDurNode)
oTD.innerText = oDurNode.text;
else
oTD.innerHTML = " ";
oTR1.appendChild(oTD);

oTD = document.createElement("td");
if (oRefNode)
oTD.innerText = oRefNode.text;
else
oTD.innerHTML = " ";
oTR1.appendChild(oTD);
oTB.appendChild(oTR1);
var oTR2 = document.createElement("tr");
oTD = document.createElement("td");
oTdWidth = document.createAttribute("colspan");
oTdWidth.value = "2";
oTD.setAttributeNode(oTdWidth);
oTD.innerHTML = " ";
oTR2.appendChild(oTD);
if (oNotesNode)
{
oTD = document.createElement("td");
oTD.innerHTML = oNotesNode.text;
oTR2.appendChild(oTD);
}
oTB.appendChild(oTR2);
var oTR3 = document.createElement("tr");
var oTr3Class = document.createAttribute("class");
oTr3Class.value = "tddatad";
oTR3.setAttributeNode(oTr3Class);
oTD = document.createElement("td");
oTdWidth = document.createAttribute("colspan");
oTdWidth.value = "3";
oTD.setAttributeNode(oTdWidth);
oTdWidth = document.createAttribute("align");
oTdWidth.value = "right";
oTD.setAttributeNode(oTdWidth);
oTD.innerHTML = '<hr class="hhr"/>';
oTR3.appendChild(oTD);
oTB.appendChild(oTR3);

}
}
// Helper functions

function gmPerformSQLStream(sSQL)
{
olMSXMLp = new ActiveXObject( "Msxml2.DOMDocument" );

olMSXMLp.loadXML('<GMAPI call="SQLStream"/>');
gmAddDataNode(olMSXMLp.documentElement, "SQL", sSQL);
var loGMXMLAPI = new ActiveXObject( "GMW.GoldMineData" );
var sRet = loGMXMLAPI.ExecuteCommand(olMSXMLp.xml);
return sRet;
}

function gmMacro(sMacro)
// gets the value of a GM Macro.
{
//use the first global instance of Msxml2.DOMDocument to build the XML
objMSXML.loadXML('<GMAPI call="Macro"/>');
// use helper function to add the passed macro.
gmAddDataNode(objMSXML.documentElement, "macro", sMacro);

// load the Executed macro XML into variable
var sXML = objGMXMLAPI.ExecuteCommand( objMSXML.xml);

//load the variable into the global Out object.
objMSXMLOut.loadXML(sXML);

// return the value
return objMSXMLOut.documentElement.text;
}

function gmAddDataNode(elParent, sName, sValue)
// this function creates a DATA element under elParent,
// useing the passed values for the name and value.
{
if (elParent) //Is the parent valid?
{
// extract the document instance so we can use it
var doc = elParent.ownerDocument;
// create the new element named "data"
var elData = doc.createElement("data");
// name it
elData.setAttribute("name", sName);
//set the value.
elData.text = sValue;
//append it to the parent
elParent.appendChild(elData);
}
}

</script>

DJ Hunt
Phone: (978)2-3333
WebSite: http://www.DJHunt.US

- The Hacker's Guide to GoldMine Premium
- One-on-One GoldMine Technical Support ( Fee Based )

http://www.DJHunt.US/Default.html

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Link MS Access to GoldMine using GM+View 1

nikhilkomakula

Programmer

DJHunt

Technical User

nikhilkomakula

Programmer

DJHunt

Technical User

Similar threads

Part and Inventory Search

Sponsor