检索两个列表，对值进行排序和比较，然后显示所有结果 [英] Retrieve two lists, sort and compare values, then display all the results
问题描述
我在SharePoint 2013 Online中有两个列表.我需要获取用户输入的键(字符串)的匹配值，进行排序，并将两个列表都显示为一个.如果我能够使用SQL创建视图，就足够简单了.最好的解决方案似乎是只显示两个列表.
I have two lists in SharePoint 2013 Online. I need to get matching values for a user-entered key (string), sort, and display both lists as one. Easy enough if I were able to use SQL to create a view. Best solution seems to be to just display both lists.
我尝试使用SPD链接源，但是链接字段"选项从不显示，无预览SPD太糟糕了(MS在想什么?).工作流程不可行.列表项可以在数据表视图"(客户要求)中进行编辑.查找需要选择以显示相关字段.
I've tried using SPD Linked Sources, but the "linked field" option never displays and the no-preview SPD is awful (what were MS thinking?). Workflow isn't feasible. List items may be edited in 'datasheet view' (client requirement). Lookups require a selection to display the related field.
我可以同时获得两个列表并分别显示它们.
I can get both lists and display them separately.
我所拥有的:
List 1 List 2
fruit apple type rome fruit apple state washington
fruit pear type bartlett fruit pear state oregon
fruit grapes type red fruit orange state florida
我想要什么:
fruit apple type rome state washington
fruit grapes type red
fruit orange state florida
fruit pear type bartlett state oregon
我缺少两件事(可能还有更多)，可以用于排序的数组以及用于匹配两个列表中的水果的比较.真实列表可能有50-120个项目(每个)需要匹配.
I'm missing two things (maybe more) an array that I can use for sorting and a comparison to use for matching the fruit on both lists. The real lists may have 50-120 items (each) that need to be matched.
所有物品应退回.如果存在匹配项，则数据应位于同一行中.如果不是，应显示空白.
All items should be returned. If there's a match, the data should be in the same row. If not, blanks should display.
下面的代码通过html页面显示，其中每个表单元格的ID与下面的脚本中的单元格引用匹配.未排序，行不匹配.
The code below displays via an html page where the ID for each of the table cells matches the cell references in the script below. It's not sorted and the rows don't match.
$(function() {
$.ajax({
url: "sharepointlist/_api/web/lists/GetByTitle('List1')/items",
type: "GET",
headers: { "accept": "application/json;odata=verbose"
},
}).success(function (data) {
var title = '';
var type = '';
$.each(data.d.results,
function (key, value) {
title += "Title: " + value.Title + "<br/>";
type += "Type: " + value.Type + "<br/>";
});
$("#tdtitle").html(title);
$("#tdtype").html(status);
$.ajax({
url: "sharepointlist/_api/web/lists/GetByTitle('List2')/items",
type: "GET",
headers: { "accept": "application/json;odata=verbose"
},
}).success(function (data) {
var title2 = '';
var state = '';
$.each(data.d.results,
function (key, value) {
title2 += "Title2: " + value.Title + "<br/>";
city += "State: " + value.State + "<br/>";
});
$("#tdsecond").html(title2);
$("#tdstate").html(city);
推荐答案
似乎您正在尝试对从REST查询返回的列表项执行加入"操作.如果是这样，您可以考虑采用以下方法
It seems you are trying to perform the "join" operation on list items returned from REST queries. If so, you could consider the following approach
function getListItems(webUrl,listTitle,selectProperties){
return $.getJSON( webUrl + "/_api/web/lists/GetByTitle('" + listTitle + "')/items?$select=" + selectProperties.join(','))
.then(function(data){
return data.value.map(function(item){
return selectProperties.reduce(function(result, key) {
result[key] = item[key];
return result;
},{});
});
});
}
function joinListItems(left, right, key) {
if(left.length == 0 || right.length == 0)
return new Error("No data was found");
var columns = Object.keys(left[0]).concat(Object.keys(right[0]));
var createRow = function(left,right){
var row = {};
columns.forEach(function(key){
row[key] = null;
});
var values = left != null ? left : right;
for(var name in values) row[name] = values[name];
return row;
};
var updateRow = function(existingRow,values){
for(var name in values) existingRow[name] = values[name];
};
return left.concat(right).reduce(function(result, current, index){
if(index < left.length){
result.rows.push(createRow(current,null));
result.keys[current[key]] = index;
}
else {
var rowIdx = result.keys[current[key]];
if(typeof rowIdx !== 'undefined'){
updateRow(result.rows[rowIdx],current);
}
else {
result.rows.push(createRow(null,current));
}
}
return result;
},{rows: [], keys: {}}).rows;
}
$.when(
// Get List1
getListItems( _spPageContextInfo.webAbsoluteUrl, "List1",['Title','Type']),
// Get List2
getListItems( _spPageContextInfo.webAbsoluteUrl, "List2",['Title','State'])
)
.then(function(items1,items2){
var key='Title';
var result = joinListItems(items1,items2,key);
result = result.sort(function(a, b){
return a.Title.localeCompare(b.Title);
});
console.log(JSON.stringify(result,null,2));
//displayResults(result);
});
//print results (from comment section)
function displayResults(items){
var title = '';
var type = '';
$.each(items, function (index, item) {
title += "Title: " + item.Title + "<br/>";
type += "Type: " + item.Type + "<br/>";
});
}
You also might find this thread helpful that specifically discusses join operation.
结果
[
{
"Title": "fruit apple",
"Type": "type rome",
"State": "state washington"
},
{
"Title": "fruit grapes",
"Type": "type red",
"State": null
},
{
"Title": "fruit orange",
"State": "state florida",
"Type": null
},
{
"Title": "fruit pear",
"Type": "type bartlett",
"State": "state oregon"
}
]
更新排序功能
替换:
result = result.sort(function(a, b){
return a.Title.localeCompare(b.Title);
});
使用
result = result.sort(function(a, b){
if(!a.Title) a.Title = "";
if(!b.Title) b.Title = "";
return a.Title.localeCompare(b.Title);
});
这篇关于检索两个列表，对值进行排序和比较，然后显示所有结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
