destructuring in js

arrays

[x,...y] = [1,2,3,4,5,6]

[1, 2, 3, 4, 5, 6]

x

1

y

[2, 3, 4, 5, 6]

 objects

var x2 = {name:"herby",amount:23423,happy:true,getStatus:function(){return this.name + (this.happy?" is happy":" isnt happy" )  }}

/with renaming variable

var {name:newname, getStatus:getit} = x2;

console.log(newname); //herby

console.log(getStatus); // function etc

Calculated Column logic

lets say you have a date column date1
you can have logic as follows

if(columna = "hello",Today(),"")

this should really fail because "" is not a date
it doesn't fail, but access for example will come up with #error

 lets say I want to keep the null



if(columna = "hello",Today(),date1)

this fails because of circular logic

if(columna = "hello",Today(),null)
if(columna = "hello",Today(),blank)

these are also not correct syntax

if(columna = "hello",Today())

that works but updates the column with a 0 which SharePoint shows incredibly as a 0

calculated columns in sharepoint

calculated columns (misnomer? they are real columns) update upon insert and update of the row. now , if you update the formula, sharepoint will show the new result but the underlying data will still be based on the old formula (you can see this in access linked table for example). he data will only really change when you do an update on the row

template literals

for template literals use the back tick "`"

console.log(`this is the sum of 8 X 45 :${8 * 45}`)
var x = {name:"herbert"}
console.log(`this is his name:${x.name }`)

select all on multi line textbox

     private void hody_KeyDown(object sender, KeyEventArgs e)

        {

          

             TextBox txtBox = sender as TextBox;

             if (txtBox != null && txtBox.Multiline == true && e.Control == true && e.KeyCode == Keys.A)

                 txtBox.SelectAll();

        }

ctrl-c on treeview

   private void TV_KeyDown(object sender, KeyEventArgs e)

        {

            if (e.KeyData == (Keys.Control | Keys.C))

            {

                if (TV.SelectedNode != null)

                {

                    Clipboard.SetText(TV.SelectedNode.Text);

                }

                e.SuppressKeyPress = true;

            }

        }

Example of csom looping recursively through termstore

  private void addNodes(TreeNode treeNode,Term term,string source = "Root")

        {

            if (term.Terms.Count > 0)

            {

                foreach (Term t in term.Terms)

                {

                    TreeNode tn = new TreeNode(string.Format("{0}  - {1}", t.Name, t.Id.ToString()));

                    treeNode.Nodes.Add(tn);

                    status.Text = string.Format("{0}-{1}", source, t.Name);

                    Application.DoEvents();

                    context.Load(t.Terms);

                    context.ExecuteQuery();

                    if (t.Terms.Count > 0)

                    {

                        addNodes(tn, t, string.Format("{0}-{1}", source, t.Name));

                    }

                }

            }

        }

 //calling code here

   foreach (Term t in ts.Terms)

            {

                TreeNode tn = new TreeNode(string.Format("{0}  - {1}", t.Name,t.Id.ToString()));

                status.Text = t.Name;

                Application.DoEvents();

                    //tn.Nodes.Add(string.Format("{0}",t.Id.ToString()));

                if (Recursive.Checked && (item.Text.Length == 0 || t.Name.Equals(item.Text, StringComparison.OrdinalIgnoreCase )))

                {

                    context.Load(t.Terms);

                    context.ExecuteQuery();

                    if (t.Terms.Count > 0)

                    {

                        addNodes(tn, t, t.Name);

                    }

                }

               treeNode.Nodes.Add (tn);

            }

          

            TV.Nodes.Add(treeNode);

logging into sharepoint with csom

   #region SECURITY

           

            string userName = this.User.Text;//

            string userPassword = this.pwd.Text;//            if (!string.IsNullOrWhiteSpace(userName) && !string.IsNullOrWhiteSpace(userPassword))

            {

               SecureString securePassword = new SecureString();

                    for (int i = 0; i < userPassword.Length; i++)

                    {

                        securePassword.AppendChar(userPassword[i]);

                    }

                if (!this.windows.Checked)

                {

                    SharePointOnlineCredentials creds = new SharePointOnlineCredentials(userName, securePassword);

                    context.Credentials = creds;

                }

                else

                {

                    NetworkCredential creds = new NetworkCredential(userName, securePassword);

                    context.Credentials = creds;

                }

            }

            #endregion

Calculated columns in sharepoint opened in access

If you have a calculated column thus

=IF([Status] ="Completed", TODAY(),"")

and the column is of type date

this is no issue for share point , the column will be empty if Status <> Completed

BUT access will read this as #error (but I have seen sometimes it reading it as 12/30/1899 (min date) and I do not know when or how this happens)

if you try iserror function it will not return yes, rather #error

any other function (len etc) will do that also - return #error

if you explicitly calculate like this 

=IF([Status] ="Completed", TODAY(),0)


access will work fine

but then again share point will have 12/30/1899 

Classes

class SimpleDate {

    constructor(year, month, day) {

     

        this.dt = new Date(year, month, day);

        this._year = year;

        this._month = month;

        this._day = day;

    }

    addDays(nDays) {

        dt += nDays;

    }

    getDay() {

        return this._day;

    }

}

class ComplicatedDate extends SimpleDate {

    constructor(year, month, day,Hour, Minute) {

        debugger;

    super(year, month, day);

    this.dt.setHours(this.dt.getHours()+Hour);

    this.dt.setMinutes(this.dt.getMinutes()+Minute);

    }

}

c = new     ComplicatedDate(1977,12,19,2,45);

Simple Generator

function* generateId(){
var i  = 0 ;
while(true){yield i++  }
}


const gen = generateId();
console.log(gen.next().value)
 0
console.log(gen.next().value)
 1
console.log(gen.next().value)
 2
console.log(gen.next().value)
 3

Promises example in JS and JSOM

 function getListItemByID(listName, itemID) {
return new Promise(function(resolve, reject) {
//debugger;
context = SP.ClientContext.get_current();
        web = context.get_web();
        oList = web.get_lists().getByTitle(listName);

        var currentItem = oList.getItemById(itemID);

        context.load(currentItem);
        context.executeQueryAsync(
            function () {
                resolve(currentItem);
            },
            function (sender, args) {
                reject(args.get_message());
            }
        );
});
};


getListItemByID("Milestones",843)
.then(row=>getListItemByID("Process List",row.get_item("Process_x0020_ID")))
.then(row=>console.log(row.get_item("Process_x0020_Name")))
.catch(error=>console.error(error))

Sets in JS (and spread operator)

Constructor

s= new Set()

Set {}

Methods

s.add(45)

s.add(34)

Set {45, 34}

s.clear()

Set {}

Using the spread operator to convert a set to an array

let t = new Set([...s].filter(x=>x ==34))

t

Set {34}

using spread to concatenate sets

x = [s,t]

[Set, Set]//unspread

x=[...s,...t]

[45, 34, 34]//spread

Intersect

new Set([...s].filter(x=>t.has(x)))

Set {34}

difference

new Set([...s].filter(x=>!t.has(x)))

Set {45}

Mapping JS

Declare

const testMap = new Map()

set map

testMap.set(1,{name:"boris",cash:345});

testMap.set(2,{name:"tony",cash:45});

testMap.set(3,{name:"peter",cash:5});

Map {1 => Object {name: "boris", cash: 345}, 2 => Object {name: "tony", cash: 45}, 3 => Object {name: "peter", cash: 5}}

properties

testMap.size

3

testMap.has(2)

true

testMap.has(56)

false

methods

testMap.get(3).name

"peter"

for loop

//first item in array is key and second is value

for(let item of testMap){ console.log( item[1].cash)}//value

345

45

5

for(let item of testMap.keys()){ console.log( item)}

1

2

3

Array methods

Using splice to add items in any position

const arr = ['a','b','c','d']

x = arr.splice(2,0,'3')

x will be empty - []

arr will be ["a", "b", "3", "c", "d"]

Add to the beginning of array

arr.unshift(-13)

[-13, "a", "b", "3", "c", "d"]

remove 1^st item

arr.shift()

["a", "b", "3", "c", "d"]

For Each

var temp =””

arr.forEach(function (item) {

    if (isNaN(item)) {

        temp += item;

    }

});

temp = "abcd"

mapping

const test =[{name:"herby",cash:23},{name:"boris",cash:456} ]

const cash = test.map(x=>x.cash)

cash = [23, 456]

Every and Some

const test2 =[{name:"herby",cash:23,eyecolor:"brown"},{name:"boris",cash:456} ]

test2.some(x=>x.eyecolor)

true

test2.every(x=>x.eyecolor)

false

Find

returns 1^st item

const z = test2.find(x=>{return x.eyecolor === "brown"})

z    {name:"herby",cash:23,eyecolor:"brown"}

const w = test2.find(x=>{return x.cash  > 0})

w   {name:"herby",cash:23,eyecolor:"brown"}

to get multiple items as an array use:

Filter

const t = test2.filter(x=>{return x.cash  > 0})

sort

 ascending

test2.sort((x,z)=>x.cash -z.cash)

descending

test2.sort((x,z)=>z.cash -x.cash)

Aggregating

console.log(test2.reduce((a,c)=>a+=c.cash,0))

479

why is 32 bit x86

here

New Parameter Functionality in JS

1. New Parameter Functionality
   
   Rest Params
   
   function testParams( i , ...j){
   var total = 0;
   j.forEach( function(value, index) { total += value; });
   return Math.pow(total,i);
   }
   testParams(2,1,2,3,4)
   100
   
   Rest Params with Arrow Functions
   function testParams( i , ...j){
   var total = 0;
   j.forEach( value => total += value);
   return Math.pow(total,i);
   
   }
   testParams(2,1,2,3,4)
   100
   
   default Params
   
   function testDefault(i, power =2){
   return Math.pow(i,power );
   }
   
   testDefault(10)
   100
   testDefault(10,3)
   1000

access list boxes - stop selection

use Locked property


if you use the enabled property then the scrollbars wont work

const in js

1. new const feature
   
   in the past there was no real constants in js, everything could be overwritten (even sometimes keywords)
   in ECMAScript6 this has been alleviated
   
   const in JS means the object can be assigned once
   if you try this
   
   const i = 45
   i= 0
   console.log(I)
   
   
   this will fail
   SyntaxError: Identifier 'i' has already been declared
   
   with objects member can be assigned afterwards
   
   
   const obj2 = {}
   obj2.a ="gg"
   
   
   
   but subsequent
   obj2 = {}
   will fail
   
   even with const, the only way to have a readonly member of an object is
   
   var testobj = {};
   Object.defineProperty( testobj , "pseudoconst",
   {
   value: 3,
   
   writable: false,
   
   enumerable: true,
   
   configurable: true
   });
   
   testobj.pseudoconst
   3
   testobj.pseudoconst = 5//this wont raise an error but wont change the value either
   5
   testobj.pseudoconst
   3
   
   

let in js

1. as Douglas Crockford has said, the worst feature of JavaScript is global variables.
   This has been alleviated greatly by block scoped variables
   
   consider the following
   
   
   var test ="herman"
   
   {
   test ="boris";
   console.log(test)
   }
   console.log(test)
   
   or
   
   
   var test ="herman"
   
   {
   var test ="boris";
   console.log(test)
   }
   console.log(test)
   
   the result will be
   
   
   boris
   boris
   
   However, with the new let keyword
   
   var test ="herman"
   
   {
   let test ="boris";
   console.log(test)
   }
   console.log(test)
   
   the results are now
   boris
   herman

Factless Fact Table

here

Access and SharePoint Interactions

GetLookupDisplayValues

When you create with VBA an attached SharePoint list in access, you are given the option of getting lookup display values (if you attach manually the value will be False).

expression.TransferSharePointList(TransferType, SiteAddress, ListID, ViewID, TableName, GetLookupDisplayValues)

GetLookupDisplayValues

Optional

Variant

Specifies whether to transfer display values for Lookup fields instead of the ID used to perform the lookup.

This setting will determine if SharePoint Created by and Modified by columns are lookups which will use UserInfo lookup table or plain text values (e.g. herby ).

Strangely enough, this will not help for user defined columns (such as escalated to)

Likewise, if querying “modified by”, the query window is acting like a lookup by being in drop down

And raises the following error

Number of Rows Limit

This will happen when GetLookupDisplayValues = true

If this is the case - set  cache settings  

Number of lookups limit

SharePoint has a limit of 12 lookup columns.

This bubbles up to Access

Non-Solutions to deal with this in Access

·        Query less columns through a query object

·        Set GetLookupDisplayValues to true

·        changing cache options

Solutions to deal with this in Access

·        Create a view

For example, in the “herby” list I have created a view called herby1”.

With this you gain not including Microsoft lookup columns like “Taxonomy Catch All Column”

When you connect to SharePoint, use view id (this image is from my test platform)

 

So you can now connect to the list

UDF in access-sql

must have a type "as String etc.". otherwise you will get internal database engine error

Lookup columns in sharepoint to access

if allow multiple values the ado.field type will be 109(complex text) if not then it's a regular 10 (text)


many bugs

house analogy

build one house that fulfills 100 people's design requests - that's software

jsom set datetime to null

use null ;)

sharepoint worklow history

to see how long the process took add modified to the view

vba doesnt short circuit

here

"in" in access

in ssql server this

select * from

(select 1 as p union select 2 as p union select 3 as p ) as main

where p in (1,1)

will give you  1 row

in access - you will get 2 rows

sharepoint lookup threshold hit in access

set no caching in current database settings

how to get more than 100 items in a rest call in SP workflow

pathetically, sharepoint workflow bombed when i tried to get the __next parameter

with

System.ApplicationException: HTTP 500  {"Transfer-Encoding":["chunked"],"X-SharePointHealthScore



so i changed the call to say gt ID - initially ID= 0 and when i looped through the first 100 i set the ID to the new id's - then I ran the call again - gt ID so it got the next group

ACCESS vba not running

make sure content enabled

limitiations with SP designer rest calls

the calls will return only 100 records

sharepoint and word

share point stores attributes of the document set in the actual word document so if you upload again unless the attributes are overwritten they will retain the old attributes

if your changes to the header on the first page dont apply to the other pages...

SharePoint Designer

is evil. If you change a variable data type in the local var windows - it wont really change the used variable type.
You have to rewrite with a new name

simple jsom to get an item

Utils.getListItemByID = function (listName, itemID ){
var dfd =  $.Deferred();
context = SP.ClientContext.get_current();
web = context.get_web();
oList = web.get_lists().getByTitle(listName);

var currentItem = oList.getItemById(itemID);

context.load(currentItem);
context.executeQueryAsync(
function(){
dfd.resolve(currentItem);
},
function(sender,args){
dfd.reject(args.get_message());
}
);
return dfd.promise();
}


Utils.getListItemByID('blah', 36).then(function(item){ console.log(item.get_item('blahName')) },function(sender, args){ console.log(args.get_message())});

CAML and SharePoint folders

make sure the query is recursive

e.g.
'</In></Where><QueryOptions><ViewAttributes Scope=\'Recursive\' /> </QueryOptions></Query></View>';


later I found this to work better

<View Scope="RecursiveAll">

nice pattern for jsom async calls

pass the call backs as parameters to the function



function getSiteField(fieldName,success,failure)
{
     var ctx = SP.ClientContext.get_current();
     var rootWeb = ctx.get_site().get_rootWeb();
     var field = rootWeb.get_availableFields().getByInternalNameOrTitle(fieldName);
     ctx.load(field);
     ctx.executeQueryAsync(
         function(){
            success(field)
         },
         failure);
}
SP.SOD.executeFunc('SP.js', 'SP.ClientContext', function() {

   //get Title column and print its properties
   getSiteField('Title',
    function(field){
       //print field properties
       console.log(field.get_title());
       console.log(field.get_internalName());
       console.log(field.get_typeAsString());
       console.log(field.get_description());
    },
    function(sender,args){
       console.log(args.get_message());
    });

});

got it here

don't use includes

use indexOf instead (ie etc)

source in sharepoint forms url

use decodeURIComponent to convert ampersands to url encoding

popups in sharepoint

the form needs to be created from webpart

no shown title for webpart

add a value of spaces

Uncaught ReferenceError

good chance the js file is malformed

taxonomy

i was getting cant do toString on null erros when accessing taxonomy.

I thought the problem was that there wasn't a default taxanomy - but the real reason was I forget to add a reference to taxonomy.js

powershell permissions to execute

I had to add this to commnd line

PS > powershell -ExecutionPolicy ByPass -File blah.ps1


adding to file permissions did not help

why we need createdelegate in JSOM

here

add jquery to console script

var jq = document.createElement('script');
jq.src = "https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js";
document.getElementsByTagName('head')[0].appendChild(jq);

wpf center a popup

PowerOff PO = new PowerOff();

            PO.Owner = Application.Current.MainWindow;            PO.WindowStartupLocation = WindowStartupLocation.CenterOwner;

            PO.Show();