dme query problem on DS

Try changing your OUT=XML to OUT=CSV. Here is an example I use:

//*********************************************************************************
// Do a DME call to get the Buyer info
//*********************************************************************************
function getBuyerInfo(sBuyerCode)
{
var sDME = ""

//Read Values from Form
var sProcureGroup = lawForm.getDataValue("PCY-PROCURE-GROUP")

//Construct DME for getting Buyer Name
// sDME = "http://" + window.location.host + "/servlet/Router/Data/Erp?PROD=" // version 9
sDME = "http://" + window.location.host + "/cgi-lawson/dme.exe?" // version 8
sDME += "PROD=" + strPDL
sDME += "&FILE=BUYER&INDEX=BUYSET1"
sDME += "&KEY=" + sProcureGroup + "=" + sBuyerCode
sDME += "&FIELD=EMAIL-ADDRESS" + ";PO-LIMIT" + ";NAME" + ";BUYER-GRP"
sDME += "&OUT=CSV&DELIM=~&NOHEADER"

//Return value from table
var objhttp = new ActiveXObject("Msxml2.XMLHTTP")
objhttp.Open("GET", sDME, false)
objhttp.Send("UserInfo")
sReturn = objhttp.responseTEXT

aRecs= sReturn.split("~")
vBuyerEmail = aRecs[0]
if (vBuyerEmail == "") // set to purchasing email if blank
{vBuyerEmail = "purchasing@4j.lane.edu"}
vBuyerPOLimit = aRecs[1]
vBuyerName = aRecs[2]
vBuyerGroup = aRecs[3]
if (vBuyerGroup == "") // set to Financial Services if blank
{vBuyerGroup = "FINSV"}

I've formatted my query to work properly in portal with the csv now but i dont understand the "GET" process.  Specifically, this part: 

Can you elaborate?

//Return value from table

var objhttp = new ActiveXObject("Msxml2.XMLHTTP")

objhttp.Open("GET", sDME, false)

objhttp.Send("UserInfo")

sReturn = objhttp.responseTEXT

Leave the OUT=XML as is and parse the returning XML: There will be a tag RECORD for each record returned and there will be COL tags for each field in the same sequence the fields are defined in your DME query, so EMPLOYEE is the first, PLAN-NAME for the second etc.

var vProd = portalWnd.oUserProfile.getAttribute("productline");
var vPlan = ""
var vEmp = portalWnd.oUserProfile.getAttribute("employee");


var vDmeString = "?PROD=" + vProd + "&FILE=EMTATRANS&INDEX=ETTSET1&KEY=200=" + vEmp + "===&FIELD=EMPLOYEE;PLAN-NAME;DATE;TYPE;TA-HOURS&SELECT=TYPE^%3DA&OUT=XML";

var vDMEInfo = portalWnd.httpRequest(portalWnd.DMEPath + vDmeString);

if (!vDMEInfo || vDMEInfo.status) {
return false;
}
var vObjDMEXML = new portalWnd.DataStorage(vDMEInfo);
var vRecords = vObjDMEXML.document.getElementsByTagName("RECORD");
if (vRecords.length > 0) {
// if only one record just reference vRecords[0]. if more than one do a do loop instead
var vCols = vRecords[0].getElementsByTagName("COL");
var vEmployee = vCols[0].firstChild.data;
var vPlan = vCols[1].firstChild.data;
}

Leave the OUT=XML as is and parse the returning XML: There will be a tag RECORD for each record returned and there will be COL tags for each field in the same sequence the fields are defined in your DME query, so EMPLOYEE is the first, PLAN-NAME for the second etc.

var vProd = portalWnd.oUserProfile.getAttribute("productline");
var vPlan = ""
var vEmp = portalWnd.oUserProfile.getAttribute("employee");


var vDmeString = "?PROD=" + vProd + "&FILE=EMTATRANS&INDEX=ETTSET1&KEY=200=" + vEmp + "===&FIELD=EMPLOYEE;PLAN-NAME;DATE;TYPE;TA-HOURS&SELECT=TYPE^%3DA&OUT=XML";

var vDMEInfo = portalWnd.httpRequest(portalWnd.DMEPath + vDmeString);

if (!vDMEInfo || vDMEInfo.status) {
return false;
}
var vObjDMEXML = new portalWnd.DataStorage(vDMEInfo);
var vRecords = vObjDMEXML.document.getElementsByTagName("RECORD");
if (vRecords.length > 0) {
// if only one record just reference vRecords[0]. if more than one do a do loop instead
var vCols = vRecords[0].getElementsByTagName("COL");
var vEmployee = vCols[0].firstChild.data;
var vPlan = vCols[1].firstChild.data;
}

Gary Davies
www.automated-workflow.com

Thanks Gary, i ended up getting your method to return values.  I need to order by date though the query results.  Is there a method to sort the results?  Then to pass the results via variable into a User Designed form that i've placed on the portal page the variable created in the portal page i'm trying to use:  function FORM_OnInit() but i dont think it's being referenced properly.  I need to put the data returned into a text3 box and then inquire on the form.

Use either SORTASC or SORTDESC (Ascending or Descinding) in your DME call like such

SORTDESC=DATE;

The result will return in DATE order most recent date first.

Gary Davies
www.automated-workflow.com

Thanks!  That worked for the sort by date order.  Do you know how i pass on a variable to a form from the portal query?

map the varialbes from the properties of the portal page.

I have been asked to expand on the comment about parsing more than one record.  In the example I had the line var vCols = vRecords[0].getElementsByTagName("COL") which assumes only 1 record passed back.  If you have more than one you need to loop through the returned array of vRecords and parse the COL fields for each record like such:

for (var i=0;i

     var vCols = vRecords[i].getElementsByTagName("COL")

   .... parse each field the same way and process them for each record

}

The difference is you will need to set vCols for each record and then parse each occurance of vCols for the fields on the record within the loop when more than one record returned.

Gary Davies

www-automated-worklfow.com

OK for some reason the for loop did not get displayed properly

it should be

for (var i=0;i

so that it will loop through setting i to the record number until there are no more records

Gary Davies

www.automated-workflow.com

The less than character is not letting this post to show properly, so one more time but I will substitue the less than character with the phrase "less than"

for (var i=0;i "less than" vRecords.length;i++)

Gary, thanks for answering that question. I have been asking for an example for a while on here. Final it makes sense!