Set Quote reference id when creating quotes from COM API

By | September 28, 2011

Whenever we create quotes through standard screen, it calls the internal methods to generated Quote reference id based on reference format and updates the new quote record with the same. When we create quote using CreateRecord method in COMP API we don’t get this value automatically set. Below is the function you can use in your COM API methods to get the reference number for any quote generated using CreateRecord method. You just have to pass QuoteId for newly created quote as input.


//’Function to get quote reference number
function GetQuoteReference(QuoteId)
  //’Get quote reference format
  var QuoteRefFormat = “”;
  var qryQuoteRefFormat = ” select cast(parm_value as varchar(25)) as QuoteRefFormat from custom_sysparams where parm_name=’QuoteFormat'”
  RecQuoteRefFormat = eWare.CreateQueryObj(qryQuoteRefFormat);
    QuoteRefFormat = new String(RecQuoteRefFormat(“QuoteRefFormat”));
    if(QuoteRefFormat==”null” QuoteRefFormat==”undefined”)QuoteRefFormat=””;

  //’Get related opportunity and quote data
  var qryQuotes = ” select * from vQuotes (nolock) where Quot_OrderQuoteId='”+QuoteId+”‘”;
  var RecQuotes = eWare.CreateQueryObj(qryQuotes);

  //’Count of quotes for thsi opportunity
  var qryOtherQuotes = ” select count(*) as LastQuote from vQuotes (nolock) where quot_OpportunityID = ‘”+new String(RecQuotes(“oppo_opportunityid”))+”‘ and COALESCE(quot_IsQuote, 0) <> 0″
  var RecOtherQuotes = eWare.CreateQueryObj(qryOtherQuotes);
  var Count = “0”
    Count = new String(RecOtherQuotes(“LastQuote”))
    if(Count==”null” Count==”undefined”)Count=”0″;

  //’Get the current user recordset
  var UserId = new String(eWare.GetContextInfo(“User”,”user_userid”));
  if(UserId==”null” UserId==”undefined”)UserId=”0″;
  var qryUser = ” select * from users (nolock) left join channel (nolock)”;
  qryUser += ” on user_primarychannelid = chan_channelid”;
  qryUser += ” where User_Deleted is null”;
  qryUser += ” and User_UserId = ‘”+UserId+”‘”;
  var RecUser = eWare.CreateQueryObj(qryUser);

  //’Get the date parameters
  var qryCurrDate = ” select datepart(dd,getdate()) as Day,datepart(mm,getdate()) as month,substring(cast(datepart(yy,getdate()) as varchar(5)),3,5) as year”;
  var RecCurrDate = eWare.CreateQueryObj(qryCurrDate);

  //’Get quote reference number
  var QuoteReference = “”;
  QuoteReference = QuoteRefFormat;
  regExpression = new RegExp(“#C”,”gi”);
  QuoteReference = QuoteReference.replace(regExpression,new String(RecUser(“user_primarychannelid”)));
  regExpression = new RegExp(“#H”,”gi”);
  QuoteReference = QuoteReference.replace(regExpression,new String(RecUser(“chan_description”)));
  regExpression = new RegExp(“#L”,”gi”);
  QuoteReference = QuoteReference.replace(regExpression,new String(RecUser(“user_logon”)));
  regExpression = new RegExp(“#U”,”gi”);
  QuoteReference = QuoteReference.replace(regExpression,new String(UserId));
  regExpression = new RegExp(“#O”,”gi”);
  QuoteReference = QuoteReference.replace(regExpression,new String(RecQuotes(“oppo_opportunityid”)));
  regExpression = new RegExp(“#I”,”gi”);
  QuoteReference = QuoteReference.replace(regExpression,new String(QuoteId));
  regExpression = new RegExp(“#N”,”gi”);

  //We add count here considering the quote is just now created
  QuoteReference = QuoteReference.replace(regExpression,new String(Count));
  regExpression = new RegExp(“#D”,”gi”);
  QuoteReference = QuoteReference.replace(regExpression,new String(RecCurrDate(“day”)));
  regExpression = new RegExp(“#M”,”gi”);
  QuoteReference = QuoteReference.replace(regExpression,new String(RecCurrDate(“month”)));
  regExpression = new RegExp(“#Y”,”gi”);
  QuoteReference = QuoteReference.replace(regExpression,new String(RecCurrDate(“year”)));
  return QuoteReference;

Happy scripting!! 🙂

If you find this content useful, please drop us an email at