Now I sort with Attachment Count, which is a primary sort. Wondering if I can have another sort on the name? For example I have 5 items with attachment count of 10, then sort these 5 items with name in ascending order.
Thanks, it works beautifully.
Is it possible to have a secondary sort?
Thanks, Will.
Notice another issue that the jqgrid column width not responsive to the window resizing. The columns look best at 80% Chrome window size. If I zoom to 100%, the last two columns disappeared and there is no horizontal scroll bar to scroll to the right.
I did some search, some people suggested to use this function. It doesn’t work, actually even at 80%, last two columns are truncated.
1 2 3 4 5 |
$(<span class="hljs-built_in">window</span>).on(<span class="hljs-string">"resize"</span>, <span class="hljs-function"><span class="hljs-keyword">function</span> () </span>{ <span class="hljs-keyword">var</span> $grid = $(<span class="hljs-string">"#list"</span>), newWidth = $grid.closest(<span class="hljs-string">".ui-jqgrid"</span>).parent().width(); $grid.jqGrid(<span class="hljs-string">"setGridWidth"</span>, newWidth, <span class="hljs-literal">true</span>); }); |
Thanks a lot, Will.
Use addRowData method to add 2nd array, can I add a few hundred rows?
2 arrays I limit the total search result to 500 rows.
I have two arrays to display in jqgrid, one is folders and the other is attachments. I concatenate them into one array.
Is it possible to add the folder array to the grid first, then add attachment array? now they are mixed together.
I got it fixed now: A function was called to do another filtering of data array. That is why it works for the api call that returns all data, then this function filters the data by search term. Another api that failed returns already filtered data so calling this function will remove the data from jqgrid.
The only issue now is I have to do F5 to have a hard refresh to start a new search.
The code failed to populate jqgrid:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 |
function searchLibrary(txtSearch) { //$.jgrid.gridUnload("#gridID"); $("#requestGrid").trigger("reloadGrid", [{ page: 1, current: true }]); var requestArr = []; // document set array var attachmentArr = []; //attachment array var requestmetadata = []; //requestmeta data array var folderLength = 0; var apiUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/search/query?querytext='" + txtSearch + "*'&sourceId='d90c1997-7b33-42be-8fb3-d1dc2543ffa7'&rowlimit=100&selectproperties='Filename, Path, ContentTypeId'"; $.ajax({ url: apiUrl, type: "GET", async: false, headers: { "accept": "application/json;odata=verbose" }, success: function (data) { var results; if (data.d) { results = data.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results; alert('length: ' + results.length); for (i = 0; i < results.length; i++) { var item = results<em class="d4pbbc-italic"></em>; var itemCell = item.Cells; var itemResults = itemCell.results; //10.3.2020 check content id, if document set, run another api call to get meta data var ContentTypeId = getValueByKey("ContentTypeId", itemResults); var Path = getValueByKey("Path", itemResults); var Filename = getValueByKey("Filename", itemResults); var pathArray = Path.split('/'); var period = pathArray[pathArray.length - 1]; // alert('Filename: ' + Filename); if (ContentTypeId.indexOf('0x0120D5200') !== -1) { /* If result has docset content type, push the url to the docset array */ var requestmetadata = getrequestValues(Filename); var folderLength = fileSizing(Filename); if (requestmetadata[5] != null && requestmetadata[5] != "" && requestmetadata[5] != "undefined") { SubmitterName = getSubmitterName(requestmetadata[5]); } else { SubmitterName = ""; } var ItemCount = requestmetadata[4]; if (ItemCount > 0) { //only add to requestArr when there is attachment requestArr.push({ RequestName: Filename, Name: Filename, ExaminationName: requestmetadata[0], Submitter: SubmitterName, PublishedDate: requestmetadata[1], MRAName: requestmetadata[2], RecommendationName: requestmetadata[3], ItemCount: ItemCount }); } } else if (ContentTypeId.indexOf('0x0120D5200') == -1 && period.indexOf('.') !== -1 && period.indexOf('.aspx') == -1) { /* If result is not a docset, contains a period, but not .aspx, push the url to the doc array */ attachmentArr.push({ Name: Filename, ContentTypeId: ContentTypeId, Path: Path, RequestName: Filename, ExaminationName: "", Submitter: "", PublishedDate: "", MRAName: "", RecommendationName: "", ItemCount: 0, FolderLength: 0 }); } } } }, //end of success error: function (data) { console.log(data); } }); function getValueByKey(key, results) { var postItem = $.grep(results, function (e) { if (e.Key === key) return e; })[0].Value; return postItem; } //10.03.2020 Join requestArr and attachmentArr to a new array // var allresults = requestArr.concat(attachmentArr); $('#requestGrid').jqGrid({ // data: allresults, data: requestArr, datatype: "local", colNames: ['Request Name', 'Docs Name', 'Examination Name', 'Submitter', 'Published Date', 'MRA Name', 'Recommendation Name', 'Item Count'], colModel: [ { name: 'Name', index: 'Name', width: 80, formatter: viewLink }, { name: 'RequestName', index: 'RequestName', width: 80, sortable: true, hidden: true }, { name: 'ExaminationName', index: 'ExaminationName', width: 100, sortable: true, editable: true }, { name: 'Submitter', index: 'Submitter', width: 80, sortable: true, editable: true }, { name: 'PublishedDate', index: 'PublishedDate', width: 60, sorttype: 'date', editable: true, editrules: { date: true }, formatter: 'date', datefmt: 'd/m/Y' }, { name: 'MRAName', index: 'MRAName', width: 80, sortable: true, editable: true }, { name: 'RecommendationName', index: 'RecommendationName', width: 100, sortable: true, editable: true }, { name: 'ItemCount', index: 'ItemCount', width: 30, sortable: true, editable: true } ], localReader: { repeatitems: true }, rowNum: 50, rowList: [50, 100, 150, 200, 250], autoload: true, rownumbers: true, sortname: "Name", sortorder: "asc", refresh: true, gridview: true, viewrecords: true, multiselect: true, rowTotal: 250, pager: '#gridPager', loadonce: false, height: 1000, ignoreCase: true, altRows: true, shrinkToFit: true, autowidth: true, resizable: false, groupColumnShow: false, hidegrid: false, multiboxonly: true, caption: "Secure Library Search Results", searchoptions: { clearSearch: true, attr: { size: 18, maxlength: 18, style: "width:130px;margin-top:1px;" } }, onSelectRow: function (a, b, c) { if (this.p.selarrrow.length === currids.length) { $('#cb_' + $.jgrid.jqID(this.p.id), this.grid.hDiv)[this.p.useProp ? 'prop' : 'attr']("checked", true); } }, gridComplete: function () { currids = $(this).jqGrid('getDataIDs'); // $('#count').html($('#gridID').getGridParam('records')); var ids = $(this).jqGrid('getDataIDs'); if (ids) { var sortName = $(this).jqGrid('getGridParam', 'sortname'); var sortOrder = $(this).jqGrid('getGridParam', 'sortorder'); for (var i = 0; i < ids.length; i++) { $(this).jqGrid('setCell', ids<em class="d4pbbc-italic"></em>, sortName, '', '', { style: (sortOrder === 'asc' ? 'background:aqua;' : 'background:yellow;') }); } } } }); |
The code jqgrid successfully populated:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 |
function searchLibrary(txtSearch) { //$.jgrid.gridUnload("#gridID"); $("#requestGrid").trigger("reloadGrid", [{ page: 1, current: true }]); var requestArr = []; var apiUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/GetFolderByServerRelativeUrl('documentrequest')/Folders?$expand=ListItemAllFields,AuthorID/id&$select=Name,ItemCount,ListItemAllFields/AuthorId,ListItemAllFields/ExaminiationName,ListItemAllFields/PublishedDate1,ListItemAllFields/MRAName,ListItemAllFields/RecommendationName&$filter=Name ne 'Forms' and ItemCount gt 0"; $.ajax({ url: apiUrl, type: "GET", async: false, headers: { "accept": "application/json;odata=verbose" }, success: function (data) { $.each(data.d.results, function (index, value) { requestArr.push({ RequestName: value.Name, Name: value.Name, ExaminationName: value.ListItemAllFields.ExaminiationName, Submitter: value.ListItemAllFields.AuthorID, PublishedDate: value.ListItemAllFields.PublishedDate1, MRAName: value.ListItemAllFields.MRAName, RecommendationName: value.ListItemAllFields.RecommendationName, ItemCount: value.ItemCount }); }); }, error: function (data) { console.log(data); } }); $('#requestGrid').jqGrid({ data: requestArr, datatype: "local", colNames: ['Request Name', 'Docs Name', 'Examination Name', 'Submitter', 'Published Date', 'MRA Name', 'Recommendation Name', 'Item Count'], colModel: [ { name: 'Name', index: 'Name', width: 80, formatter: viewLink }, { name: 'RequestName', index: 'RequestName', width: 80, sortable: true, hidden: true }, { name: 'ExaminationName', index: 'ExaminationName', width: 100, sortable: true, editable: true }, { name: 'Submitter', index: 'Submitter', width: 80, sortable: true, editable: true }, { name: 'PublishedDate', index: 'PublishedDate', width: 60, sorttype: 'date', editable: true, editrules: { date: true }, formatter: 'date', datefmt: 'd/m/Y' }, { name: 'MRAName', index: 'MRAName', width: 80, sortable: true, editable: true }, { name: 'RecommendationName', index: 'RecommendationName', width: 100, sortable: true, editable: true }, { name: 'ItemCount', index: 'ItemCount', width: 30, sortable: true, editable: true } ], localReader: { repeatitems: true }, rowNum: 50, rowList: [50, 100, 150, 200, 250], autoload: true, rownumbers: true, sortname: "Name", sortorder: "asc", refresh: true, gridview: true, viewrecords: true, multiselect: true, rowTotal: 250, pager: '#gridPager', loadonce: false, height: 1000, ignoreCase: true, altRows: true, shrinkToFit: true, autowidth: true, resizable: false, groupColumnShow: false, hidegrid: false, multiboxonly: true, caption: "Secure Library", searchoptions: { clearSearch: true, attr: { size: 18, maxlength: 18, style: "width:130px;margin-top:1px;" } }, onSelectRow: function (a, b, c) { if (this.p.selarrrow.length === currids.length) { $('#cb_' + $.jgrid.jqID(this.p.id), this.grid.hDiv)[this.p.useProp ? 'prop' : 'attr']("checked", true); } }, gridComplete: function () { currids = $(this).jqGrid('getDataIDs'); // $('#count').html($('#gridID').getGridParam('records')); var ids = $(this).jqGrid('getDataIDs'); if (ids) { var sortName = $(this).jqGrid('getGridParam', 'sortname'); var sortOrder = $(this).jqGrid('getGridParam', 'sortorder'); for (var i = 0; i < ids.length; i++) { $(this).jqGrid('setCell', ids<em class="d4pbbc-italic"></em>, sortName, '', '', { style: (sortOrder === 'asc' ? 'background:aqua;' : 'background:yellow;') }); } } } }); |
Thanks for following up! We have two versions of api code to populate the jqgrid. We are building search results with jqgrid then allow users to select to download.
The 1st approach is to use this api call to get back items
var apiUrl = _spPageContextInfo.webAbsoluteUrl + “/_api/web/GetFolderByServerRelativeUrl(‘documentrequest’)/Folders?$expand=ListItemAllFields,AuthorID/id&$select=Name,ItemCount,ListItemAllFields/AuthorId,ListItemAllFields/ExaminiationName,ListItemAllFields/PublishedDate1,ListItemAllFields/MRAName,ListItemAllFields/RecommendationName&$filter=Name ne ‘Forms’ and ItemCount gt 0”;
This version of code loads the result successfully to jqgrid. Please see the attached working jqgrid txt file and array for the jqgrid loaded successfully screenshot for the array items that are pushed to the array that feeds the jqgrid. Notice the items loaded successfully in jqgrid automatically have an id.
This approach doesn’t meet the requirement to return all the search results, all the fields and attachments.
The 2nd approach uses a SharePoint search api
var apiUrl = _spPageContextInfo.webAbsoluteUrl + “/_api/search/query?querytext='” + txtSearch + “*’&sourceId=’d90c1997-7b33-42be-8fb3-d1dc2543ffa7’&rowlimit=100&selectproperties=’Filename, Path, ContentTypeId'”;
this api doesn’t return meta data fields so we have to use the Filename to run another api call to get back the meta data, then push the meta data to the array that feeds the jqgrid. Jqgrid failed to load the data most of the time, even though the array is populated. I even trimmed down the properties to be the same as the 1st approach. still jqgrid doesn’t display data.
Please see attached jqgrid not loading code in txt file and array for the jqgrid failed to load screen shot.
RequestNameOWSTEXT, SubmittedByOWSUSER,ExaminiationNameOWSTEXT,PublishedDate1OWSDATE are managed properties that are queryable but no value returned from the search api call. search api call returns system columns, like contenttypeId, filename, path but not the custom column values.
Found out need to use results = data.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results to retrieve data from the search results
https://siteurl/_api/search/query?querytext=’cash flow*’&sourceId=’d90c19*****************a7’&rowlimit=100&selectproperties=’Filename, RequestNameOWSTEXT,SubmittedByOWSUSER,ExaminiationNameOWSTEXT,PublishedDate1OWSDATE’
Two issues:
1. RequestNameOWSTEXT,SubmittedByOWSUSER,ExaminiationNameOWSTEXT,PublishedDate1OWSDATE are searchable managed properties but results are null
2. document set filename are displayed in the JGGrid just fine but the attachments are not displayed in jqgrid. Filename returns both document sets and attachments.
requestArr.push({
Name: getValueByKey(“Filename”, itemResults),
RequestName: getValueByKey(“Filename”, itemResults),…..
})
Copyright 2014 TriRand LtdAll Rights ReservedRSS
Back to Top