Delegation - How to overcome the 500/2000 Row Limit with Collections

 At some stage, most of us will encounter the need to overcome the row limits for non-delegable queries. This is necessary to provide data aggregation for reports and charts, allow better searching of data with more specific search criteria, and many other reasons.

The best reference guide for this is Mr Dang's post here:

https://powerusers.microsoft.com/t5/General-Discussion/500-item-limit-in-CDM-entity-search-filter-need-to-switch-to-asp/m-p/22980#M9872

In this excellent post, Mr Dang describes how to use a ForAll loop to collect records into a local collection in batches of 500. You can find a full description of how to implement this technique in Mr Dang's post, but here's a brief extract. Well done Mr Dang!

UpdateContext({firstrecord: First(datasource)});
UpdateContext({lastrecord: First(Sort(datasource,RecordId,Descending))});
UpdateContext({maxiter: RoundUp((lastrecord.RecordId-firstrecord.RecordId)/500,0)});

ClearCollect(iter,
	AddColumns(AddColumns(Filter(HundredChart,Number<=maxiter),"min",(Number-1)*500),"max",Number*500)
);

Clear(datasource_temp);
ForAll(iter,
	Collect(datasource_temp,
		Filter(datasource,RecordId>=firstrecord.RecordId+min && RecordId<firstrecord.RecordId+max)
	)
)

You can find other useful details of this topic in the posts here:

https://powerusers.microsoft.com/t5/General-Discussion/Overcoming-the-500-record-limit-locally/td-p/18379

https://powerusers.microsoft.com/t5/General-Discussion/Solution-Overcome-Delegation-for-Aggregate-Functions/m-p/71966

                                             Or  Go Head With Another Article 

This solution makes it possible for you to avoid making a column that calculates which block of 500 a record belongs to (I previously used a column called n). I made my formulas based on a CDS entity and performed calculations on the default RecordId field which is a Big Integer. I previous was using PrimaryId, since it was small numbers starting at 1, but the problem is that it is handled as text. RecordId is a value at least.

 

 

UpdateContext({firstrecord: First(datasource)});
UpdateContext({lastrecord: First(Sort(datasource,RecordId,Descending))});
UpdateContext({maxiter: RoundUp((lastrecord.RecordId-firstrecord.RecordId)/500,0)});

ClearCollect(iter,
	AddColumns(AddColumns(Filter(HundredChart,Number<=maxiter),"min",(Number-1)*500),"max",Number*500)
);

Clear(datasource_temp);
ForAll(iter,
	Collect(datasource_temp,
		Filter(datasource,RecordId>=firstrecord.RecordId+min && RecordId<firstrecord.RecordId+max)
	)
)

 

There are three parts:

 

1. Determine the first record (firstrecord), the last record (lastrecord). Subtracting their RecordId value and dividing it by 500 determines how many times you would need to perform iterations (maxiter). The firstrecord's RecordId will be used as a reference for pulling in records later.

 

UpdateContext({firstrecord: First(datasource)});
UpdateContext({lastrecord: First(Sort(datasource,RecordId,Descending))});
UpdateContext({maxiter: RoundUp((lastrecord.RecordId-firstrecord.RecordId)/500,0)});

 

2. Make a static table of whole numbers [1, 2, 3, ... 100 or whatever you want]. Filter it to use as the argument in ForAll later. It will give instructions to ForAll on how many times to "loop." So if in step 1, you determined that your number of iterations (maxiter) was 3, then the formula would Filter all whole numbers less than and equal to 3.

 

Note: my formula below is messy. I am using an existing table I have that only has a column with whole numbers. I had to add columns for the minimum 500 and upper 500 using AddColumns(). You may opt to make those columns in your table so it does not need to be calculated every time. I figure it's a small calculation so it's not a big deal.

 

ClearCollect(iter,
	AddColumns(AddColumns(Filter(HundredChart,Number<=maxiter),"min",(Number-1)*500),"max",Number*500)
);

 

3. The last part is where the formula pulls in records. First it clears the temporary collection (datasource_temp) that is used for holding the records. ForAll will pull in 500 records at a time for each whole number you Filtered in step 2. So if you have 3 whole numbers in the iter Collection (maxiter=3), then ForAll will pull in:

• all records with RecordId>=firstrecord.RecordId+0 and RecordId<firstrecord.RecordId+500
• all records with RecordId>=firstrecord.RecordId+500 and RecordId<firstrecord.RecordId+1000
• all records with RecordId>=firstrecord.RecordId+1000 and RecordId<firstrecord.RecordId+1500
• then it will stop because there are no other whole numbers in the "iter" Collection.

 

Clear(datasource_temp);
ForAll(iter,
	Collect(datasource_temp,
		Filter(datasource,RecordId>=firstrecord.RecordId+min && RecordId<firstrecord.RecordId+max)
	)
)

 

Power Apps Patch Function Examples For Every SharePoint Column Type

 

Power Apps Patch function examples by SharePoint column type:

• PATCH A SharePoint Single Line Text Column
• PATCH A SharePoint Multiple Line Text Column
• PATCH A SharePoint Number Column
• PATCH A SharePoint Yes/No Column
• PATCH A SharePoint Person Column
• PATCH A SharePoint Person Column With Multiple Values
• PATCH A SharePoint Date Column
• PATCH A SharePoint Date & Time Column
• PATCH A SharePoint Choices Column
• PATCH A SharePoint Choices Column With Multiple Values
• PATCH A SharePoint Lookup Column
• PATCH A SharePoint Hyperlink Column
• PATCH A SharePoint Currency Column
• PATCH A SharePoint Task Outcome Column
• PATCH A Managed Metadata Column (coming soon)

This column type cannot use PATCH however there is a workaround:

• PATCH A SharePoint Image Column

There is no way to PATCH these column types:

• Calculated Column
• External Data Column
• Location Column

2,000 Free Power Apps Icons

PowerApps Collections Details with example

 Basic Examples

• What Is A Collection?
• Create A Collection (manual)
• Create A Collection (from a datasource)
• Create A Single-Column Collection
• Insert A New Row Into A Collection
• Insert Multiple New Rows Into A Collection
• Change The Values In A Row

Selecting Collections Examples

• First Rows Of A Collection
• Last Rows Of A Collection
• Middle Rows Of A Collection
• Get The First Row With A Matching Value (Lookup)
• Get A Value From A Record (LookUp & Positional)
• Get A Column From A Collection
• Filter A Collection
• Filter A Collection With AND Condition
• Filter A Collection With OR Condition
• Ungroup A Table Stored Within A Cell
• Get A Value From A Table Within A Cell
• Get Collection Column Names

Summarizing Collections Examples

• Calculate the SUM, COUNT, AVERAGE, MAX, MIN
• Group By And SUM Values
• Group By And COUNT Values
• Group By And AVERAGE Values
• Group By And MAXIMUM Values
• Group By And MINIMUM Values
• Get The Rows With Top Values
• Get The Rows With Bottom Values
• RANK Rows from Highest To Lowest With Ties
• COUNT The Unique Values In A Column
• Calculate The SUMPRODUCT Of Two Columns
• Add A Column Showing Percentage Of Total

Transforming Collections Examples

• SORT a Collection By One Or More Columns
• JOIN One Column From Another Collection
• JOIN All Columns From Another Collection
• SPLIT A Text Column By Delimiter
• MERGE Two Columns Into A Single Column
• REPLACE A Whole Row In A Collection
• REPLACE All Values In A Column
• REPLACE Cells In A Collection With Blank Values
• ROUND The Values In A Column
• Randomly SHUFFLE The Order Of Rows
• Fill Blanks In A Column With Last Non-Blank Value
• Add A ROW NUMBER Column To A Collection
• RENAME A Column From A Collection
• REMOVE A Row
• REMOVE All Rows
• REMOVE One Or More Columns
• REMOVE All Columns Except
• UNIQUE Values From A Collection Column
• UNIQUE Values From A Collection Column With Blank At The Top
• UNIQUE Records From A Collection
• Matching Values Within 2 Collections
• Change The Date Format In A Collection
• Stack Values From Multiple Columns In A Collection

Exporting Collections Examples

• Export Collection To A Datasource
• Export Collection To A JSON
• Export Collection To A CSV
• Export Collection To HTML Table

Dropdown Selection Change Event in React Js

import * as React from 'react'; 

import styles from './Reactspfx.module.scss'; 

import { IReactspfxProps } from './IReactspfxProps'; 

import { escape } from '@microsoft/sp-lodash-subset';

 export interface IReactGetItemsState

{ selectValue:string }

export default class Reactspfx extends React.Component 

{ 

public constructor(props: IReactspfxProps) { 

super(props); 

this.state = { 

selectValue:"Radish" 

};

}

handleChange = (event) => { this.setState({selectValue:event.target.value}); alert(event.target.value); 

};

public render(): React.ReactElement { alert(this.state.selectValue); return (

  <select value={this.state.selectValue}  onChange={this.handleChange}>
    <option value="Orange">Orange</option>
      <option value="Radish">Radish</option>
      <option value="Cherry">Cherry</option>
    </select>
);

}

SHAREPOINT FRAMEWORK - REACT JS alert on Page Load

Here is my code for getting alert on page load.

componentDidMount() {   
  alert("Hi");
}

public render(): React.ReactElement<IReactCrudProps> {   
    return ( 
        <select id="myDDl">

        </select>
    );
} 

To Get File And Folder From Document Library Using JSOM From To Way

 

1- Without Caml Querry

2- With Caml Query

 

 

var LibraryNameForUpdate='NGODocument';

1-  Without Caml Querry

 

function FileWithContentTypeNotUsedCAMLQuery(folderName, selectedLink)

{

var context = new SP.ClientContext.get_current();

var web = context.get_web();

var list = web.get_lists().getByTitle(LibraryNameForUpdate);

var query = SP.CamlQuery.createAllItemsQuery();

query.set_folderServerRelativeUrl(folderName);

allItems = list.getItems(query);

context.load(allItems, 'Include(Title,ContentType,File,Author,Editor,Modified)'); // If You want Specific column is Loaded Then write This

//context.load(allItems, 'Include(ID,Title,Description0,File,Author,Editor,Modified,_ModerationStatus,Created)');

//context.load(allItems);  If You want All  column is Loaded Then write This

 

context.executeQueryAsync(Function.createDelegate(this, this.successGetViewAllFileApprovedReject),   Function.createDelegate(this, this.failedsuccessGetViewAllFileApprovedReject));

 

}

 

function successGetViewAllFileApprovedReject()

{

 

  var fileUrls = "";

  var ListEnumerator = this.allItems.getEnumerator();

   var count = 0;

   while(ListEnumerator.moveNext())

   {

    var currentItem = ListEnumerator.get_current();

    var _contentType = currentItem.get_contentType();

   

     if(_contentType.get_name() != "Folder")

       {

         debugger;

      // count = count + 1;

        var File = currentItem.get_file();

        var fieldUserValueCreatedBy = currentItem.get_item("Author");

        var fieldUserValueModifiedBy = currentItem.get_item("Editor");

        var currentdate = new Date();

        var modifiednumberofDaysago = showDays(currentdate.toISOString(), currentItem.get_item('Modified'));

        if(File != null)

          {

         debugger;

          fileUrls += File.get_serverRelativeUrl() + '\n';

         

          }

       }

   }

//alert(fileUrls);

  //alert(count);

}

function failedsuccessGetViewAllFileApprovedReject(sender, args) {

  alert("failed. Message:" + args.get_message());

}

 

 

 

1-  With Caml Querry (It best Way To Use )

 

 

function FileWithCAMLQuery(folderName, selectedLink)

{

   debugger;

    var context = new SP.ClientContext.get_current();

                var web = context.get_web();

                var list = web.get_lists().getByTitle(LibraryNameForUpdate);

                var query = new SP.CamlQuery();

                query.set_folderServerRelativeUrl(folderName);

                var q = '<View Scope=\'Recursive\'><Where><Eq><FieldRef Name=\'FSObjType\'/><Value Type=\'Number\'>0</Value></Eq></Where></View>';

                query.set_viewXml(q);

                allItems = list.getItems(query);

                //this.listInfoArray=context.load(allItems);If You want All  column is Loaded Then write This

                context.load(allItems, 'Include(ID,Title,Description0,File,Author,Editor,Modified,_ModerationStatus,Created)'); // If You want Specific column is Loaded Then write This

 

                context.executeQueryAsync(Function.createDelegate(this, this.successFileWithCAMLQuery), Function.createDelegate(this, this.failedsuccessFileWithCAMLQuery));

 

}

 

function successFileWithCAMLQuery()

{

debugger;

  var fileUrls = "";

  var ListEnumerator = this.allItems.getEnumerator();

   var count = 0;

   while(ListEnumerator.moveNext())

   {

       var currentItem = ListEnumerator.get_current();

      //  debugger;

         count = count + 1;

         var File = currentItem.get_file();

         var fieldUserValueCreatedBy = currentItem.get_item("Author");

         var fieldUserValueModifiedBy = currentItem.get_item("Editor");

         var currentdate = new Date();

         var modifiednumberofDaysago = showDays(currentdate.toISOString(), currentItem.get_item('Modified'));

         var dateCreated = new Date(currentItem.get_item('Created'));

         console.log(dateCreated.getDate() + "-" + monthNames[dateCreated.getMonth()] + "-" + dateCreated.getFullYear());

         console.log(fieldUserValueCreatedBy.get_lookupValue());

         console.log(fieldUserValueModifiedBy .get_lookupValue());

         console.log(currentItem.get_item('Title'));

         console.log(currentItem.get_item('Description0'));

         console.log(currentItem.get_item('ID'));

         console.log(currentItem.get_item('_ModerationStatus'));

      

   }

    //alert(modifiednumberofDaysago);

     alert(count);

}

function failedsuccessFileWithCAMLQuery(sender, args) {

  alert("failed. Message:" + args.get_message());

}

 

 

****************************All Field Get By this Internal Name*********************************

LIST OF INTERNAL NAMES FOR SHAREPOINT FIELDS

In order to reference a column or field using the the SharePoint object model, you often need to know its internal name. For example, when creating a CAML query, you can specify the field on which to search by providing its internal name. As opposed to the display name, which can be changed in the UI, the internal name is immutable. While the default display name and the internal name are often identical or similar, they can also be very different. The difference isn’t always consistent. For example, spaces in the display name can be removed, such as IsCheckedoutToLocal, or replaced with the hexadecimal equivalent, such as HTML_x0020_File_x0020_Type. Furthermore, the display name can be the same for more than one fields, so the internal name is the only way to distinguish between them.

You can quickly determine the internal name of a field using the UI:

1. Open the List Settings page
2. Under the Columns section, select a column to view the Edit Column page
3. The URL of this page includes the internal name in the query string. For example, the URL for the Created By field includes the following query string List=%7BF641CEF1%2DCDE2%2D49E1%2D9800%2D861A408EF890%7D&Field=Author. The value for the Field parameter, Author, is the internal name for Created By.

However, this approach isn’t always convenient. If the column you want doesn’t already belong to the list, you’d have to first add it.

For reference purpose, below is the list of the display name and corresponding internal name for the default fields of a Document Library.

TITLE	INTERNAL NAME
Approval Status	_ModerationStatus
Approver Comments	_ModerationComments
Check In Comment	_CheckinComment
Checked Out To	CheckoutUser
Checked Out To	CheckedOutTitle
Checked Out To	LinkCheckedOutTitle
Content Type	ContentType
Content Type ID	ContentTypeId
Copy Source	_CopySource
Created	Created
Created	Created_x0020_Date
Created By	Author
Document Created By	Created_x0020_By
Document Modified By	Modified_x0020_By
Edit	Edit
Edit Menu Table End	_EditMenuTableEnd
Edit Menu Table Start	_EditMenuTableStart
Effective Permissions Mask	PermMask
Encoded Absolute URL	EncodedAbsUrl
File Size	File_x0020_Size
File Size	FileSizeDisplay
File Type	File_x0020_Type
GUID	GUID
Has Copy Destinations	_HasCopyDestinations
Html File Link	xd_ProgID
HTML File Type	HTML_x0020_File_x0020_Type
ID	ID
ID of the User who has the item Checked Out	CheckedOutUserId
Instance ID	InstanceID
Is Checked out to local	IsCheckedoutToLocal
Is Current Version	_IsCurrentVersion
Is Signed	xd_Signature
Item Type	FSObjType
Level	_Level
Merge	Combine
Modified	Modified
Modified	Last_x0020_Modified
Modified By	Editor
Name	FileLeafRef
Name	LinkFilenameNoMenu
Name	LinkFilename
Name	BaseName
Order	Order
owshiddenversion	owshiddenversion
Path	FileDirRef
ProgId	ProgId
Property Bag	MetaInfo
Relink	RepairDocument
ScopeId	ScopeId
Select	SelectTitle
Select	SelectFilename
Server Relative URL	ServerUrl
Shared File Index	_SharedFileIndex
Source Name (Converted Document)	ParentLeafName
Source Url	_SourceUrl
Source Version (Converted Document)	ParentVersionString
Template Link	TemplateUrl
Title	Title
Type	DocIcon
UI Version	_UIVersion
Unique Id	UniqueId
URL Path	FileRef
Version	_UIVersionString
Virus Status	VirusStatus
Workflow Instance ID	WorkflowInstanceID
Workflow Version	WorkflowVersion

In my case, I’m interested in the Name field. I wouldn’t have guessed that it would be mapped to FileLeafRef.

USEFUL REFERENCE FOR SHAREPOINT INTERNAL FIELDS NAME

There are few common and important sharepoint internal fields name which will help to access data in your Web application.

Document Library Fields:

Display Name             Internal Name
Name                                  FileLeafRef
Name                                  LinkFilenameNoMenu
Name                                  LinkFilename
Name                                  BaseName
URL Path                          FileRef
Path                                    FileDirRef
File Size                             File_x0020_Size
File Type                           File_x0020_Type
Source Url                       _SourceUrl
Type                                  DocIcon

Custom List Fields:

Display Name                Internal Name
Name                                     FileLeafRef
Title                                        Title
Order                                     Order
HTML File Type                 HTML_x0020_File_x0020_Type
URL Path                              FileRef
Created                                 Created_x0020_Date
File Size                                 File_x0020_Size
File Type                               File_x0020_Type
File Name                              BaseName
Created By                            Author
Modified By                          Editor

References:

1-http://blog.softartisans.com/2009/12/08/list-of-internal-names-for-sharepoint-fields/

2-https://dipansaha.wordpress.com/2012/07/23/useful-reference-for-sharepoint-internal-fields-name/