This is the kind of thing that seems a bit daunting on first attempts, but it's a useful exercise to go through, as you'll pick up a load of useful techniques. This is the kind of trick I pull so often that the majority of the functionality is built into libraries which I use on every project.

They key thing to appreciate is the fact that we need to talk to the server from the client in order to build the data. Starting at the client side we can get the quot_description value. Then we need the server to execute the SQL request for us and to return the required values. Then we can consume the returned values at the client side and build our hyperlink.

In order to this, you'll need to build an ASP page (or a page built using the .Net SDK) which can accept a query string containing the quot_description. The ASP page can execute the SQL (by means of the CRM.CreateQueryObj() and SelectSql() functions) and then return the values. Using JSON might help here, so see my post on how to get JSON into the CRM server backend : https://community.sagecrm.com/partner_community/f/49/t/13389.aspx

As for making the AJAX call, have a look at this post here (I know it's aimed at an older version but it's the best fit) : https://community.sagecrm.com/partner_community/b/hints_tips_and_tricks/archive/2009/04/21/using-ajax-to-accessing-server-side-objects-client-side.aspx

If I have time when I get into the office tomorrow morning I'll see if I can knock up a quick working example.

Awesome Mr. Burke I appreciate the insight, until you can get a working example I will review what you posted here, and see if I can't attempt it on my own, I will post my findings... But please note I am very low level at this point in even building the ASP page...

Update, I cannot access those files I do not have the partner privileges.

Ah, yeah - I forget that some forum users don't have access to all the stuff.

No matter, let's hammer something together. Server-side stuff first.

We know that, given the quot_description, we can run the SQL query. Now - you know your data better than I do, but I'd always go for the quot_OrderQuoteID personally, as we know that's going to be unique. If you're happy that the quot_description is guaranteed unique on your system then that's fine, but I'm going to err on the side of caution and use the ID field.

This is the ASP page we need at the server. We'll save this in the CustomPages folder as GetQuoteHyperlinkData.asp

<%

try {

var quoteid = Request.QueryString("quoteID");
var oppoDescription = "";
var oppoProjName = "";
var oppoFolderName = "";


var sqlString = "SELECT oppo_description, oppo_prjctname, ";

sqlString += "CONCAT('20', LEFT(Oppo_Description, 2)) AS FolderName";

sqlString += " FROM Quotes JOIN Opportunity ON quot_opportunityid = oppo_opportunityid";

sqlString += " WHERE quot_orderquoteid = " + quoteid;

var qry = CRM.CreateQueryObj(sqlString);
qry.SelectSql();
while(!qry.eof){
oppoDescription = qry("oppo_description");
oppoProjName = qry("oppo_prjctname");
oppoFolderName = qry("FolderName");
qry.NextRecord();
}


var hyperLinkString = "//servername/Job Project Folders/" + oppoFolderName + "/" + oppoDescription + oppoProjName + "/";

Response.Write(hyperLinkString);
}
catch(e){
Response.Write(e.message);
}

%>

This is the simplest implementation I could think of. In reality, I'd probably send back each of the bits - the description, the project name and the folder - individually in a JSON object so I could examine them client-side to make sure they looked OK. But that's a fair bit more work. In this example all we are doing is picking the value of "quoteid" out of the URL which calls the ASP page. Then we create a SQL statement based on the quoteid value, and pass this to a CRM query object which can execute the SQL for us.

We'd only ever expect one row returned out of the SQL query, so the 'while(!qry.eof) ... qry.NextRecord()' construct might look a bit off. But it's just to make sure we properly iterate through the results that the object returns.

Assuming we get something out of the query (and let's face it, we *are* assuming - I'd probably want to do a bit more checking and validation here) then we just concatenate the whole lot together into a string and send it back as a text result to the client.

OK, probably a good time to test where we are at. So if you log into CRM and then open a new tab, you can copy the SID out of the CRM url and add it into a new URL to call the ASP page, which will need to look something like this:

http:////CustomPages/GetQuoteHyperlinkData.asp?quoteid=7756&SID=172069808526257

What you *should* see is something like this:

(My folder name is not what you'd expect, but I've just got garbage test data in there).

Happy days.

Right then, now we need to bang something together at the client side which will make that call for us. I'm not sure exactly where in CRM you want this functionality, but this example is designed to run on the Quote Summary screen.
I'd highly recommend putting this code into a .js file - in fact, let's assume it's in a file called Quotes_QuoteSummary.js in the CustomPages folder - and then just adding a script tag which loads it into the Custom Content of the QuoteSummary screen, like this:

OK, here's the client-side script:

crm.ready(function(){

var quoteID = crm.getArg("Key86");

var ajaxUrl = makeRequestString("GetQuoteHyperlinkData", "quoteid=" + quoteID);

$.ajax({
url: ajaxUrl,
dataType: 'text',
success: function(hyperlinkData){
alert(hyperlinkData);
},
error: function(e){
alert(e.responseText);
},
type: 'GET'
});

});

function makeRequestString(aspPage, queryString) {

var strQS = location.href.split(/\?/)[1];
var strAddr;

if (window.location.toString().toLowerCase().search('eware.dll')==-1){
strAddr = window.location.toString().split('CustomPages')[0];
} else {
strAddr = window.location.toString().split('eware.dll')[0];
}

var strURL = strAddr + 'CustomPages/' + aspPage + '.asp';
strURL += '?' + queryString;
strURL += '&' + strQS;
return strURL;
}

To explain : The crm.ready() handler ensures that the code runs once the page is loaded. Then we get the Quote_OrderQuoteID out of the page url - it'll be in there as Key86.

Note - if, as discussed at the start, you want to stick with sending the quot_description to the server than you could get hold of that like so:

var quoteDesc = crm.fields("quot_description").val();

The complicated part is how we construct the URL to the ASP page. That's what the makeRequestString function does and you can just assume it'll do its job (actually, I think I should probably refactor that function at some point, but that's my problem).

When we get the URL, then we'll ask jQuery to make an AJAX request. This is an async request so it won't hang up the UI (not that we'd expect it to take anything more than a few milliseconds to return), and if it returns sucessfully it'll run the 'success' function. If not, it'll run the 'error' function.

As you can see, I'm not doing anything useful with the hyperlink string beyond showing it in an alert box. But there you go - you've got a dynamically constructed hyperlink at the client, and you can do with it what you see fit.

Ok....

The snippet you've highlighted above is just an anonymous function that will run if the AJAX call returns a 'success' status. jQuery will pass the result of the call (in our case a string) into the function. If we'd passed a JSON object back from the server, then the function would expect a JSON object. You could equally do this:

success : mySuccessFunction

..then elsewhere in your .js file have:

function mySuccessFunction(hyperlinkData){

//do something

}

As for the other bits.... you'll need to lean on AJAX again to get it to test if the file exists. Assuming a url like this...

http://servername/Job Project Folders/2017/17-09-254 Test Project/fileName.txt

..(or just the path to the folder if directory browsing is enabled) then you can do this:

Same as above - if we find the file we do something in the 'success' function, if not we do something in the error function.

To that end, here's the client side code with more stuff added. There's a 'makeButton()' function which basically clones one of the buttons on the right hand side to make a 'show file' button. The AJAX call to test if the file exists then decides what happens if the button is clicked: either open in a new tab, or show a 'not found' alert.

OMG thank you so much I was able to replicate this, but I have a few questions and statements. I figured out that the Include statements you added here point to files that do not exist on my Server... I am also ripping your code apart to get a better understanding of what you did here.

Question 1

success: function(hyperlinkData){

alert(hyperlinkData);

}

In that code snippet from what you provided the "function" and "hyperlinkData" is what is confusing me, and I am sure I would be able to figure it in time, but I am pressed for time here. What I want to do with this string is provide a button which opened a new tab to a virtual directory I have set up to our Job Projects folder. Ultimately this button should first check to see if this Virtual directoy exists, if it does open it, if it doesn't provide a pop-up stating that it doesn't exist...

I do apologize for hitting you up like this and I will forever be indebted to you. I do not have a good business partner so I am limited to what resources I have available... Whatever else you could provide for me to grasp a better understanding will be highly appreciated...

That did... I found an issue with the http: I had to change it to https:, and remove it from the GetQuoteHyperlinkData.asp, but this works exactly as it is meant to... I just need to replicate all this on the Opportunity screen... The part that really got me was the AJAX portion, this is a whole new coding language to me... I will be spending some time trying to wrap my mind around all this, but you are a Saint at this point.