Category Archives: Javascript

Javascript to Expand Calendar Events

This javascript snippet will expand all SharePoint 2010 calendar events.

Create a CEWP on the page and add insert the code below.

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>

<script type="text/javascript">

function expandCal() {

setInterval(expandCalCheck, 900);

}

function expandCalCheck() {

var a=$("a.ms-cal-nav", $("div.ms-acal-rootdiv:last>div")).get(0);

if (a) {

for (var r=0; r<6; r++) {

var a=$("a.ms-cal-nav", $("div.ms-acal-rootdiv:last>div").get(r)).get(0);

if (a)

if (a.innerText.indexOf("more") >= 0)

a.click();

}

}

}

ExecuteOrDelayUntilScriptLoaded(expandCal, "sp.ui.applicationpages.calendar.js");

</script>

TradeStation Group, Inc. is the parent company of five separate but affiliated operating subsidiaries, TradeStation Securities, Inc. (Member NYSE, FINRA, NFA and SIPC), IBFX, Inc. (Member NFA), IBFX Australia Pty Ltd, which is authorized and regulated by the Australian Securities and Investments Commission, TradeStation Technologies, Inc., a trading software and subscription company, and TradeStation Europe Limited, a United Kingdom, FSA-authorized introducing brokerage firm. None of these companies provides trading or investment advice, recommendations or endorsements of any kind. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Forex products and services are offered by the TradeStation Forex divisions of IBFX, Inc. (Member NFA) and IBFX Australia Pty Ltd (ASIC registered).

How to Create a Dictionary in SharePoint

Overview
This tutorial will help you build a dictionary in SharePoint using an auto suggest feature that calls up terms from the centralized term store.
No server side web part development; everything in this article to create the dictionary was developed using JavaScript, SPServices, CSS and a custom list. Using the SharePoint Client Object Model would be a better choice for this type of project and has extensive capabilities of making your site more dynamic and functional.

Using SPServies
SPServices is a JQuery library abstract SharePoint web services and make them easy to use it. If you have not worked with SPServices,
check out the website for more detailed information about its functionality. It works entirely client side and requires no server install.

Download the jquery files from the site and upload them to your SharePoint server. For this project, I placed the files under the Site Assets directory so they could be accessed anywhere in the Site Collection.

Getting Started
Starting with HTML and some CSS, I created a web page similar to the image below. If a user clicks on a letter, all the terms and definitions starting with that letter will appear below inside a div element. Note that there are a few linked Javscript and external CSS files including the main jquery library, SPServices.js, and the autosuggest jquery files. Dictionary.js and dictionary_autosuggest.js are two Javascript files that contain the client side functionality of the search features. More details are explained later in this article.


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">

<head>

<title>TS Dictionary</title>

<script type="text/javascript" src="/style%20library/services-plugin/js/jquery.js"></script>

<!-- autocomplete files -->

<link href="/Site Assets/jquery-ui-1.10.0.custom.css" rel="stylesheet"/>

<script src="/Site Assets/jquery-ui-1.10.3.custom.min.js"></script>

<script src="/Site Assets/jquery.SPServices-0.7.2.min.js" language="javascript" type="text/javascript"></script>

<script type="text/javascript" src="/site Assets/dictionary.js"></script>

<script type="text/javascript" src="/site Assets/dictionary_autoSuggest.js"></script>

<style type="text/css">

#author_alphabetic {
text-align:center;
}
#author_alphabetic ul {
}

#author_alphabetic li {
float:left;
margin:2px 2px 0 0;
}

#author_alphabetic a {
background-color:#3d7fac;
position:relative;
width:40px;
height:40px;
border:1px solid rgb(61, 127, 172);
text-decoration:none;
font-size:22px;
font-weight:bold;
font-family: "Verdana";
float:left;
display:inline;
color:white;
line-height:2.0;
}

#author_alphabetic a span {

/* background:url(../image/alpha_br.png) no-repeat right bottom; */
width:75px;
height:75px;
display:block;
position:absolute;
top:-2px;
left:-8px;
}

#author_alphabetic a:hover {
background:rgb(61, 127, 172);
z-index:100;
}

#author_alphabetic a:hover span.first {
display:block;
font-size:22px;
line-height:2.0;
font-weight:bold;
color:white;
cursor:pointer;
width:40px;
height:40px;
top:-1px;
left:-1px;
background:rgb(119, 165, 197);
border:1px solid rgb(255, 255, 255);
}

#author_alphabetic a:hover span span {
display:block;
position:relative;
left:1px;
top:-4px;
width:41px;
height:41px;
}

.ui-autocomplete {
max-height: 400px;
overflow-y: auto;
/* prevent horizontal scrollbar */
overflow-x: hidden;
}

/* IE 6 doesn't support max-height * we use height instead, but this forces the menu to always be this tall */*
html .ui-autocomplete {
height: 100px;
}
.myInput {
position:relative;
left:40px;
width:300px;
border:0px;
max-width:240px;
height:42px ;
background: url("/style%20library/enetThemeimages/input6.png") 0 0 ;
background-repeat: no-repeat;
line-height:32px;
padding-left:5px;
font-family:"Arial",Georgia,Serif;
color: #8a8484;
font-size:14px;
size:250px;
padding-right: 5px;
top:-20px;
}

.myButton{
position:relative;
left:45px;
background: no-repeat url("/style%20library/enetThemeimages/searchbutton.png") 0 0 ;
width:97px;
height:42px;
border:0px;
cursor:pointer;
top:-20px;
}
</style>

<body>
<h3 style="padding-left:105px;padding-top:-30px; position:relative;top:30px; width:470px; color:#787878">Click search or use the enter key to call up a dictionary term. </h3>
<div style="position:relative; top:-20px; left:4px; background-color:transparent; width:100px; height:80px">
<img src="/style%20library/enetThemeImages/glass.png" />
<div style="background-color:transparent; width:500px; height:100px;position:relative; left:110px;">
<table border=0><tr><td>
<input type="text" id="txtSearch" class="myInput" value="Search the dictionary..." onClick="clearMe()" onkeypress="searchKeyPress(event);" />
</td><td>
<input type="button" id="btnSearch" value="" onclick="getTerm();" class='myButton' /> </td>
</table>
</div>
</div>
<div id="author_alphabetic" style="position:relative; top:10px; left:-14px">
<ul>
<li><a href="#" onclick="getVals('A')" title="A"><span class="first"><span>A</span></span></a></li>
<li><a href="#" onclick="getVals('B')" title="B"><span class="first"><span>B</span></span></a></li>
<li><a href="#" onclick="getVals('C')" title="C"><span class="first"><span>C</span></span></a></li>
<li><a href="#" onclick="getVals('D')" title="D"><span class="first"><span>D</span></span></a></li>
<li><a href="#" onclick="getVals('E')" title="E"><span class="first"><span>E</span></span></a></li>
<li><a href="#" onclick="getVals('F')" title="F"><span class="first"><span>F</span></span></a></li>
<li><a href="#" onclick="getVals('G')" title="G"><span class="first"><span>G</span></span></a></li>
<li><a href="#" onclick="getVals('H')" title="H"><span class="first"><span>H</span></span></a></li>
<li><a href="#" onclick="getVals('I')" title="I"><span class="first"><span>I</span></span></a></li>
<li><a href="#" onclick="getVals('J')" title="J"><span class="first"><span>J</span></span></a></li>
<li><a href="#" onclick="getVals('K')" title="K"><span class="first"><span>K</span></span></a></li>
<li><a href="#" onclick="getVals('L')" title="L"><span class="first"><span>L</span></span></a></li>
<li><a href="#" onclick="getVals('M')" title="M"><span class="first"><span>M</span></span></a></li>
<br><br><br><br><br>
<li><a href="#" onclick="getVals('N')" title="N"><span class="first"><span>N</span></span></a></li>
<li><a href="#" onclick="getVals('O')" title="O"><span class="first"><span>O</span></span></a></li>
<li><a href="#" onclick="getVals('P')" title="P"><span class="first"><span>P</span></span></a></li>
<li><a href="#" onclick="getVals('Q')" title="Q"><span class="first"><span>Q</span></span></a></li>
<li><a href="#" onclick="getVals('R')" title="R"><span class="first"><span>R</span></span></a></li>
<li><a href="#" onclick="getVals('S')" title="S"><span class="first"><span>S</span></span></a></li>
<li><a href="#" onclick="getVals('T')" title="T"><span class="first"><span>T</span></span></a></li>
<li><a href="#" onclick="getVals('U')" title="U"><span class="first"><span>U</span></span></a></li>
<li><a href="#" onclick="getVals('V')" title="V"><span class="first"><span>V</span></span></a></li>
<li><a href="#" onclick="getVals('W')" title="W"><span class="first"><span>W</span></span></a></li>
<li><a href="#" onclick="getVals('X')" title="X"><span class="first"><span>X</span></span></a></li>
<li><a href="#" onclick="getVals('Y')" title="Y"><span class="first"><span>Y</span></span></a></li>
<li><a href="#" onclick="getVals('Z')" title="Z"><span class="first"><span>Z</span></span></a></li>
</ul></div>
<span style=" line-height:32px"> <a href="#" onclick="getAllVals()" title="Show All" align="absmiddle">Show All</a> </span>
<br /><br><br><br><br><br>
<div id="MainDiv" style="background-color:transparent; padding-left:40px; ">
<div id="ShowMeTerm" style="background-color:transparent; font-size:12px; color:blue; width:700px" valign="top"></div
<div id="ShowMeDef" ></div></div></body></html>

Create Dictionary List

After the htm file was uploaded to the WFE, I created a custom list named ‘Dictionary’ and added a column called ‘Definition’ with a type of multilines of text. I used the Title column as the term to define. Added a few list items so content could be available for testing.

Create Term-Set

On the top level site of the site collection, go to Site Actions > Site Settings and under Site Administration click on > Term store management. On the left pane in the Taxonomy term store create a new term-set called ‘Dictionary.’ Give it a description and allow the submission policy be checked as Open. Add the same terms as you did in the custom list. Save changes.

Go back to the Dictionary list settings to add a new metadata column called ‘MetaData.’ In the term-set settings tree-view, select the Dictionary term-set to link the metadata to the term-set.

Once this is completed, a new field in the form called ‘MetaData’ will be added. Create a new .aspx page on your site where you created the Dictionary custom list. Add the Dictionary custom list to the page and publish. When you click ‘Add new item’, you can manually add new terms and definitions to the list. At this point, you have a SharePoint page that displays an input field a search button and A-Z boxes. Notice a div entitled ‘ShowMeTerm. ’ This is where the definitions will be displayed dynamically on the page.

Binding the AutoSuggest Feature to the input field

In the Javscript below, it starts off with variable declarations. As the function getKeywords gets initiated, the script is calling SharePoint client object model and begins to load term-set from the metadata columns “TSDictionary” that you created earlier. If the term-set is correctly found, then the function OnLoadSuccess is processed followed by two more functions; GetTermsRecursive and getVals(‘A’).

GetTermsRecursive calls the SPServices Jquery script which then queries the term-set. GetData function gathers all the data in the term-set into an array (“mySortedArry”) and then becomes binded to the input field “txtSearch.”

mySortedArry = myListTerms.split(",");
var myListTerms2 = String(mySortedArry);
$("#txtSearch").autocomplete({ source: myListTerms2.split(",")

The getVals() function is initiated when a user clicks on any of the A-Z letters. This function uses a CAML to query the Dictionary custom list and select all the terms and definitions that start with the letter in the variable letter. For example, getVals(‘G’) will display all the terms beginning with G.

<View><Query><Where><BeginsWith><FieldRef Name=’Title’/>’ + ‘<Value Type=’Text’>’ + letter + ‘</Value></BeginsWith></Where><OrderBy><FieldRef Name="Title" Ascending="True" /></OrderBy></Query></View>

The function onQuerySucceeded() gets called to display the results of the CAML query inside the “ShowMeTerm” div by using innerHTML.

In a similar manner, the Show All link will call up the getAllVals() function and by using another CAML query, will

display every result returned from the Dictionary list.

Dictionary_autosuggest.js



JSRequest.EnsureSetup();

    var myGlobalSearch ;
    var clientContext;
    var oList;
    var camlQuery;
    var listItemInfo = '';
    var listItemInfo2 = '';
    var listItemEnumerator;
    var termPresent;
    var srchTerm;
    var myListTerms= '';
    var TaxTerms = {
        termId: "",
        termName: "",
        child: null
    }

 

    //---------init  begin taxonomy ----------------->

    var TaxConfiguration = {
        SspId: "",
        GroupId: "",
        TermSetId: "",
        Configuration: "",
        ParseConfiguration: function () {

 

            xmlDoc = $.parseXML(this.Configuration);
            xml = $(xmlDoc);
            var properties = xml.find("Property");

            for (i = 0; i < properties.length; i++) {
            propertyName = properties[i].firstChild.textContent == undefined ?
            properties[i].firstChild.text : properties[i].firstChild.textContent;
            propertyValue = properties[i].lastChild.textContent == undefined ?
            properties[i].lastChild.text : properties[i].lastChild.textContent;
               if (propertyName == propertyValue) {
                    propertyValue = "";
                }

 

                switch (propertyName) {
                    case "SspId":
                        this.SspId = propertyValue;
                        break;
                    case "GroupId":
                        this.GroupId = propertyValue;
                        break;
                    case "TermSetId":
                        this.TermSetId = propertyValue;
                        break;
                }
            }
        }
    }

 

    // goes to this function

    function GetTermsRecursive(sspId, termSetId, myListTerms) {   
        var terms = new Array();
        var returnValue;
        $().SPServices({
            operation: "GetChildTermsInTermSet",
            sspId: sspId,
            termSetId: termSetId,
            lcid: 1033,
            completefunc: GetData
        });
    }


    function GetChildTermsRecursive(sspId, termSetId, roottermId) {
        var terms = new Array();
        $().SPServices({
            operation: "GetChildTermsInTerm",
            sspId: sspId,
            termId: roottermId,
            termSetId: termSetId,
            lcid: 1033,
            completefunc: GetData
        });
       return myListTerms;

    }

 
    function GetData(xData, Status) {
        if (Status == "success") {
            terms = new Array();
            xmlData = xData;
            // Fix for different XML parsing in IE and Chrome
            termsContent = $.parseXML(xmlData.responseText).firstChild.textContent == undefined ?
            $.parseXML(xmlData.responseText).text :
            $.parseXML(xmlData.responseText).firstChild.textContent;
            termsXML = $.parseXML(termsContent);
            $termsXML = $(termsXML);
            childTerms = $termsXML.find("T");
            parentTermId = null;
            filterOutput = "<ul>";
            for (i = 0; i < childTerms.length; i++) {
                termName = $(childTerms[i]).find("TL");

                hasChildTermsXml = $(childTerms[i]).find("TM");
                // request if child terms are avaliable

                hasChildTerms = $(hasChildTermsXml).attr("a69");
                var tsTerm = new Object();
                // Requesting actual term id
                tsTerm.termId = $(childTerms[i]).attr("a9");
                // Requesting term name

                tsTerm.termName = termName.attr("a32");

                // Setting Parent Term ID

                parentTermId = $(hasChildTermsXml).attr("a25");
                filterOutput += "<li id='" + tsTerm.termId + "'>" + tsTerm.termName;
                // try to put termName in doug comma delimited array - this works!!
                myListTerms+= ',' + tsTerm.termName;
                 // for debugging puroposes
                // If child terms are avaliable query child terms
                if (hasChildTerms != undefined && hasChildTerms == "true") {
                    // Request child Terms
                    tsTerm.child = GetChildTermsRecursive(taxconfig.SspId, taxconfig.TermSetId, tsTerm.termId);
                    tsTerm.hasChildTerms = true;
                } else {
                    tsTerm.child = null;
                    tsTerm.hasChildTerms = false;
                }

                filterOutput += "</li>";
                terms[i] = tsTerm;
            }
            filterOutput += "</ul>";
            // If parent element is specified query for parent element
            if (parentTermId != undefined || parentTermId != null) {
                $("#" + parentTermId).append(filterOutput);
            } else {
                currentFilter = $("#filter").html();
                $("#filter").html(currentFilter + filterOutput);

            }
            //  sort comma delimited string
             mySortedArry = myListTerms.split(",");
             var myListTerms2 = String(mySortedArry);                                                    
              $("#txtSearch").autocomplete({ source: myListTerms2.split(",")

            });
       
            //return dictionaryTerms;

        }

    }


    var fieldCollection;
    var termField = null;
    var termSetTerms = Array();
    var taxconfig = TaxConfiguration;
 

    function OnLoadFailed(sender, args) {
        alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());

    }
 
    //------ end taxonomy code------------------------------//
    $(document).ready(function () {
                ExecuteOrDelayUntilScriptLoaded(getKeywords, "sp.js");
                clearFormData() ;    
    });


    function getKeywords() {
        var clientContext = SP.ClientContext.get_current();
        if (clientContext != undefined && clientContext != null) {
            var webSite = clientContext.get_site().get_rootWeb();
            // Select 'Action' Field
            fieldCollection = webSite.get_fields();
            this.termField = fieldCollection.getByInternalNameOrTitle("TSDictionary");
            // Load Context
            clientContext.load(this.fieldCollection);
            clientContext.load(this.termField);
            clientContext.executeQueryAsync(Function.createDelegate(this, this.OnLoadSuccess), Function.createDelegate(this, this.OnLoadFailed)
                );
        }

    }

    function OnLoadSuccess(myList) {
        var fieldInfo = '';
        var fieldschema = termField.get_schemaXml();
        taxconfig.Configuration = fieldschema;
        taxconfig.ParseConfiguration();
        try {
            var taxsspId = taxconfig.SspId;
            var termSetId = taxconfig.TermSetId;
            var dictionaryTerms = '';
            GetTermsRecursive(taxconfig.SspId, taxconfig.TermSetId, dictionaryTerms);
             getVals('A');
        }   catch (e) {
            alert(e);
        }
    }
       function onCreateSucceeded() {
        // just an empty function
    }
//-------------------------------------------------------------------------------------

function searchKeyPress(e)
    {
        // look for window.event in case event isn't passed in
        if (typeof e == 'undefined' && window.event) { e = window.event; }
        if (e.keyCode == 13)
        {

            var mySearchField = document.getElementById('txtSearch');
             myGlobalSearch  = mySearchField.value;
            document.getElementById('btnSearch').click();
            getVals(mySearchField.value);
        }
    }

  
function getTerm() {
   var mySearchField = document.getElementById('txtSearch');
   myGlobalSearch = mySearchField.value;
   getVals(mySearchField.value);

}


function  clearMe() {
   var myfield2 = document.getElementById('txtSearch');
   myfield2.autocomplete = "off";
   myfield2.value = "";
}

 

Dictionary.js

function onFilterQueryFailed(sender, args) { alert ("Unable to retrieve terms. Please contact your administrator.") }

function getVals(letter) {
       var fieldDisplayName = "Title";
        var clientContext = new SP.ClientContext.get_current();     
       var spWebobj = clientContext.get_web();
        var spListobj = spWebobj.get_lists().getByTitle('Dictionary');
        var spCamlQuery = new SP.CamlQuery();
        spCamlQuery.set_viewXml ('<View><Query><Where><BeginsWith><FieldRef Name=\'Title\'/>' +   '<Value Type=\'Text\'>' + letter + '</Value></BeginsWith></Where><OrderBy><FieldRef Name="Title" Ascending="True" /></OrderBy></Query></View> ' );      
       this.collListItem = spListobj.getItems(spCamlQuery);
        clientContext.load(collListItem);
        clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onFilterQueryFailed));               

      }   


function getAllVals() {
var fieldDisplayName = "Title";
        var clientContext = new SP.ClientContext.get_current();     
        var spWebobj = clientContext.get_web();
        var spListobj = spWebobj.get_lists().getByTitle('Dictionary');
        var spCamlQuery = new SP.CamlQuery();
        spCamlQuery.set_viewXml ('<View><Query><Where><IsNotNull><FieldRef Name="Title" /></IsNotNull></Where><OrderBy><FieldRef Name="Title" Ascending="True" /></OrderBy></Query></View> ');

        this.collListItem = spListobj.getItems(spCamlQuery);
        clientContext.load(collListItem);
        clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onFilterQueryFailed));

}

function onQuerySucceeded(sender, args) {
    var listItemInfo = '';
    var listItemEnumerator = collListItem.getEnumerator();
    document.getElementById("ShowMeTerm").innerHTML ='';
    while (listItemEnumerator.moveNext()) {
        var oListItem = listItemEnumerator.get_current();
        listItemInfo += '\nID: ' + oListItem.get_id() +
            '\nTitle: ' + oListItem.get_item('Title') +
            '\nBody: ' + oListItem.get_item('Definition');
          myterm =   oListItem.get_item('Title');
     document.getElementById("ShowMeTerm").innerHTML += oListItem.get_item('Title') + ":<br />" +  "<div style=color:black; font-weight:normal;>" + oListItem.get_item('Definition') +  "</div> <br /><br />" ;
       }


     if (listItemInfo.toString()== "") {
        document.getElementById("ShowMeTerm").innerHTML = "No matches found."
     }
}

Understanding SQL Join Statements

Overview

This article is to explain how JOIN statements are written using SQL in MicroSoft Studio Manager application. When comparing data with more than one table, you use a JOIN. An SQL JOIN clause combines records from two or more tables in a database. A JOIN clause combines fields (columns) from each table that shares a common value. An SQL programmer writes a JOIN statement to display the records for joining. The combined data can then be used a record set or stored in a temporary table.

There are four types of JOIN: INNER, OUTER, LEFT, and RIGHT. Lets work with using these statements using the two tables below:

Model table Car table

In the above tables the CarID column of the Car table (which can be designated as Car.CarID) is the primary key, while CarID of the Model table is a foreign key, or model.CarID. Notice that the BMW has no value assigned (NULL) and that Lexus does not have any models assigned to it. Using SSMS, The SQL statement below will compile and create the following tables in the database:



CREATE TABLE Car

(

CarID INT,

CarName VARCHAR(20)

);

CREATE TABLE model

(

modelName VARCHAR(20),

CarID INT

);

INSERT INTO car(CarID, CarName) VALUES(1,'Acura');

INSERT INTO car(CarID, CarName) VALUES(2, 'Honda');

INSERT INTO car(CarID, CarName) VALUES(3, 'Ford');

INSERT INTO car(CarID, CarName) VALUES(4, 'Lexus');

INSERT INTO model (modelName, CarID) VALUES('TL', 1)

INSERT INTO model (modelName, CarID) VALUES('Civic', 2)

INSERT INTO model (modelName, CarID) VALUES('Accord',2)

INSERT INTO model (modelName, CarID) VALUES('Escape',3)

INSERT INTO model (modelName, CarID) VALUES('Explorer',3)

INSERT INTO model (modelName, carID) VALUES('335i', NULL);

CROSS JOIN

CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table. Multiply the number of rows for each table (6 x 4) and that is the number of records (24) displayed in the result.


SELECT * FROM model CROSS JOIN car

Or

SELECT * FROM model, car

INNER JOIN

Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. In other words, The INNER JOIN keyword returns rows when there is at least one match in both tables.

SQL specifies two different syntactical ways to express joins: "explicit join notation" and "implicit join notation". The "explicit join notation" uses the JOIN keyword along with the ON keyword to specify the predicates for the join, as in the following example:


SELECT *
FROM model INNER JOIN car
ON model.CarID= car.CarID

Implicit join notation uses the WHERE clause as a filter predicate:


SELECT *
FROM model, car
WHERE model.CarID = car.CarID;

The result turns out a table like this:

Note that the resulting table has no record of 335i because the CarID has a null value and no reference to the car table. There is also no record of Lexus because there is no CarID associated with it in the model table.

OUTER JOIN

An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table’s rows are retained (left, right, or both).

(In this case left and right refer to the two sides of the JOIN keyword.)

LEFT OUTER JOIN

The LEFT JOIN keyword returns all rows from the left table (model_CarID), even if there are no matches in the right table (car_CarID).


SELECT *
FROM model LEFT OUTER JOIN car
ON model.carID = car.carID

RIGHT OUTER JOIN

The RIGHT JOIN keyword returns all the rows from the right table (car_carID), even if there are no matches in the left table (model_CarID)

SELECT *
FROM model RIGHT OUTER JOIN car
ON model.CarID = Car.CarID

FULL OUTER JOIN

FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching

SELECT *
FROM model FULL OUTER JOIN car
ON model.carID = car.carID

EQUI JOIN

SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality.

There are two types of EQUI JOINS:

1) SQL INNER JOIN

2) SQL OUTER JOIN

An SQL non EQUI JOIN is a JOIN condition which makes use of some comparison operator other than the equal sign like >, <, >=, <=.

What is the difference between Equi Join and Inner Join in SQL?

An EQUI JOIN is a join with a join condition that only contains an equality operator. An EQUI JOIN returns only the rows that have equivalent values for the specified columns. An inner join is a join with a join condition that may contain both equality or non-equality.

Using REST in Sharepoint 2013

Get the GUID value of a SharePoint List and item ID using Javascript

I coded some Javascript that will retrieve the GUID value of a SharePoint list and the first item ID of that list.

Once all the values have been determined, Javascript will open the Infopath form of that list using the

SharePoint Client Side Dialog object.



<script type="text/javascript">


 function OpenDialogOki() {
      
       var options = SP.UI.$create_DialogOptions();
       options.resizable = 1;
       options.scroll = 1;
       
       	 clientContext = new SP.ClientContext.get_current();					
		spWebobj = this.clientContext.get_web();
		spListobj = spWebobj.get_lists().getByTitle("MessagefromOki");
		var spCamlQuery = new SP.CamlQuery();
		var spViewXML = '<View><Query /></View>';
		spCamlQuery.set_viewXml(spViewXML);
		spListobjItems = spListobj.getItems(spCamlQuery);
	
			
		clientContext.load(spWebobj);
		clientContext.load(spListobjItems);  
        this.collList = spWebobj.get_lists();
	    clientContext.load(collList, 'Include(Title, Id)');
        
        // get the GUID info
        var listInfo = '';
		var listEnumerator = collList.getEnumerator();
			   while (listEnumerator.moveNext()) {
			      	var oList = listEnumerator.get_current();
			         
			        if ( oList.get_title() == "MessageFromOki") {
			             listInfo += 'Title: ' + oList.get_title() + ' ID: ' + oList.get_id().toString() + '\n';
			             var oki_guid = oList.get_id().toString();
			              }	
			           }
			    
         //window.OkiItemID - global variable of the list item ID        		
          options.url =  "/Lists/MessageFromOki/Item/displayifs.aspx?List=" + oki_guid + "&ID=" + window.OkiItemID;
          SP.UI.ModalDialog.showModalDialog(options);
   } 
   

 
 function OpenDialogSal() {
      
       var options = SP.UI.$create_DialogOptions();
       options.resizable = 1;
       options.scroll = 1;
       
       	clientContext = new SP.ClientContext.get_current();					
		spWebobj = this.clientContext.get_web();
		spListobj = spWebobj.get_lists().getByTitle("MessagefromSal");
		var spCamlQuery = new SP.CamlQuery();
		var spViewXML = '<View><Query /></View>';
		spCamlQuery.set_viewXml(spViewXML);
		spListobjItems = spListobj.getItems(spCamlQuery);
	
			
		clientContext.load(spWebobj);
		clientContext.load(spListobjItems);  
        this.collList = spWebobj.get_lists();
	    clientContext.load(collList, 'Include(Title, Id)');
        
        // get the GUID info
        var listInfo = '';
		var listEnumerator = collList.getEnumerator();
			   while (listEnumerator.moveNext()) {
			      	var oList = listEnumerator.get_current();
			         
			        if ( oList.get_title() == "MessageFromSal") {
			             listInfo += 'Title: ' + oList.get_title() + ' ID: ' + oList.get_id().toString() + '\n';
			             var sal_guid = oList.get_id().toString();
			              }	
			           }
	      options.url =  "/_layouts/listform.aspx?PageType=4&ListId=" + sal_guid + "&ID=" + window.SalItemID;
     
         SP.UI.ModalDialog.showModalDialog(options);
   } 
  




			    var tpj = jQuery;
			    tpj.noConflict();
                 
			    tpj(document).ready(function () {
 			
              if (typeof(_spBodyOnLoadWrapper) != 'undefined') {
                   _spBodyOnLoadWrapper();
              }
              
              
              
			  if (tpj.fn.cssOriginal != undefined)
			            tpj.fn.css = tpj.fn.cssOriginal;
			       		tpj('#services-example-1').services(
						{
						    width: 920,
						    height: 300,
						    slideAmount: 5,
						    slideSpacing: 30,
						    carousel: "off",
						    touchenabled: "on",
						    mouseWheel: "on",
						    hoverAlpha: "off", 		     // Turns Alpha Fade on/off by Hovering
						    slideshow: 0, 			    // 0 = No SlideShow, 1000 = 1 sec Slideshow (rotating automatically)
						    hovereffect: "on", 		    // All Hovereffect on/off
						    callBack: function () { } 	//Callback any Function after loaded
						  
						});
					 });
			    
			    
			   			    
			    
			    		      
		      ExecuteOrDelayUntilScriptLoaded(getOkiTitle, "sp.js");
					
                 function getOkiTitle() {
                   
			  	    clientContext = new SP.ClientContext.get_current();					
					spWebobj = this.clientContext.get_web();
					spListobj = spWebobj.get_lists().getByTitle("MessagefromOki");
					var spCamlQuery = new SP.CamlQuery();
					var spViewXML = '<View><Query /></View>';
					spCamlQuery.set_viewXml(spViewXML);
					spListobjItems = spListobj.getItems(spCamlQuery);
					clientContext.load(spWebobj);
					clientContext.load(spListobjItems);                     
                 						
					// need to retrieve GUID for List MessageFromSal, MessageFromOki
			    	 this.collList = spWebobj.get_lists();
			    	 clientContext.load(collList, 'Include(Title, Id)');
					 clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
		
					
				}
				
				
								
			 function onQuerySucceeded() {
                clientContext.executeQueryAsync(onSuccess, onFail);
               }
			
			 function onQueryFailed(sender, args) {
			    alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
			 }

	
			function GetSalTitle() {
					clientContext = new SP.ClientContext.get_current();
					spWebobj = this.clientContext.get_web();
					spListobj = spWebobj.get_lists().getByTitle("MessagefromSal");				
					var spCamlQuery = new SP.CamlQuery();
					var spViewXML = '<View><Query /></View>';
					spCamlQuery.set_viewXml(spViewXML);
					spListobjItems = spListobj.getItems(spCamlQuery);
					clientContext.load(spWebobj);
					clientContext.load(spListobjItems);
					clientContext.executeQueryAsync(onSuccess2, onFail);
				}
					
					
			function onSuccess2() {
					
					  	liCount = spListobjItems.get_count();
					  	//alert (liCount);
					    for (x = 0; x < liCount; x++) {
					        var listItem = spListobjItems.itemAt(x);
					        var itemTitle  = listItem.get_item('Title');
					      	var SalItemID  = listItem.get_id();
					     	//window.SalItemID = SalItemID;  
							//display dynamic title on the page
					        document.getElementById("SaloTitle").innerHTML = itemTitle;		      					                     
					    }
					       window.SalItemID = SalItemID; 
					     	
                       }
	
					
				
				function onSuccess() {
					// get the GUID value of MessageFromOki list
					    liCount = spListobjItems.get_count();
					    for (x = 0; x < liCount; x++) {
					        var listItem = spListobjItems.itemAt(x);
					        var itemTitle  = listItem.get_item('Title');
					        var OkiItemID  = listItem.get_id();
					        //display dynamic title on the page
					    	document.getElementById("OkiTitle").innerHTML = itemTitle;
					        				          
					     }
					    // global variable of the list item to display
					     window.OkiItemID = OkiItemID;  
                         GetSalTitle()
                        }
	
					function onFail() {
					    alert('request failed ' + args.get_message() + '\n' + args.get_stackTrace());   
					}
					

				
			
			</script>




How to fix the security issue with content deployment wizard

Retrieving a List Title Using Javascript

Recently had a project where I needed display the title of a SharePoint list inside an html file.

This can be accomplished by working with SharePoint’s Client Object Model using some Javascript.

In this example I use JavaScript to retrieve the title from the SharePoint list called ‘MySharePointList.’

Start first by creating an empty div inside the body tag and give it an id value.

<div id="MyListTitle" > </div>

Next use the javascript below:

<script type="text/javascript"> 
ExecuteOrDelayUntilScriptLoaded(

function GetListTitle() {

clientContext = new SP.ClientContext.get_current();

spWebobj = this.clientContext.get_web();

spListobj&nbsp;= spWebobj.get_lists().getByTitle("MySharePointList");

var spCamlQuery = new SP.CamlQuery();

var spViewXML = '';

spCamlQuery.set_viewXml(spViewXML);

spListobjItems = spListobj.getItems(spCamlQuery);

clientContext.load(spWebobj);

clientContext.load(spListobjItems);

clientContext.executeQueryAsync(onSuccess, onFail);

}, "sp.js");

function onSuccess() {

liCount = spListobjItems.get_count();

for (x = 0; x &lt; liCount; x++) {

var listItem = spListobjItems.itemAt(x);

var itemTitle&nbsp; = listItem.get_item('Title');

document.getElementById("MyListTitle").innerHTML&nbsp;= itemTitle;

}

}

function onFail() {

alert('request failed ' + args.get_message() + '\n' + args.get_stackTrace());

}

</script>