How to correct source list of Data Lookup

This article describes how to use the sbDataLookups browser console script to update Data Lookup columns that point at a SharePoint list from any site.

The script lists all such Data Lookup columns on a site and lets you redirect them to another source site, list, or display column. It works on SharePoint Online and SharePoint on-premises (2019 and Subscription Edition).

NOTE: On SharePoint Online, you can make the same change without a script using the Solution Studio deployment maintenance dialog. See Data Lookup introduction for details. On-premises deployments do not have this option in the dialog — use this script there.

Table of Contents

• Overview
• Prerequisites
• Step-by-Step Guide
• Reading the Results
• Notes and Limitations
• The Script

Overview

For each SharePoint list from any site Data Lookup column, the script updates the following values in the column's configuration and keeps everything else unchanged:

Prerequisites

• Permissions: Manage Lists or Full Control on the site where the solution is deployed, and read access to the source sites.
• Browser: Microsoft Edge, Google Chrome or Mozilla Firefox. Internet Explorer 11 is not supported.
• Same URL: The source site URL must start the same way as the site you run the script on, for example https://portal.contoso.com/. On SharePoint Online this covers every site in the tenant (tenant.sharepoint.com), but not OneDrive (tenant-my.sharepoint.com). On-premises, it means a path-based site collection in the same web application, opened through the same URL.
• Preview first: The script writes nothing unless you add apply: true. Always run a preview and check the table before you apply.

Step-by-Step Guide

await sbDataLookups.run()

await sbDataLookups.run({ siteUrl: '/sites/crm' })

await sbDataLookups.run({ siteUrl: '/sites/crm', apply: true })

await sbDataLookups.run({ list: 'Orders', field: 'Customer', siteUrl: '/sites/crm',
                          listUrl: 'Lists/Customers', displayField: 'Title' })

await sbDataLookups.run({ list: 'Orders', field: 'Customer', siteUrl: '/sites/crm',
                          listUrl: 'Lists/Customers', displayField: 'Title', apply: true })

Back to top

Reading the Results

The script prints one table row per Data Lookup column. The SiteUrl, LookupListUrl, LookupListId and DisplayField columns show the current value, or old -> new when the value will change.

Notes and Limitations

The Script

Copy everything in the block below and paste it into the browser console (Step 1).

/*
 * skybow Data Lookup - report / move source site and list (SharePoint Online / 2019 / Subscription Edition)
 *
 * Reports "SharePoint list from any site" data lookups, and points them at another source site (and optionally another
 * list / display column) by rewriting
 *   DataConnection.SiteUrl        server-relative URL of the source web      "/sites/crm"
 *   DataConnection.LookupListUrl  web-relative URL of the source list         "Lists/Customers"
 *   DataConnection.LookupListId   id of the source list                       "6f3c...-..."
 *   DataConnection.DisplayField   internal name of the display column        "Title"
 * inside the field's ClientSideComponentProperties. Everything else in the field is kept as is.
 * The source list id is always re-read from SharePoint, so the three values stay consistent.
 *
 * Works on SharePoint Online and on-premises. On SharePoint Online the same change can also be made
 * without a script, in the Solution Studio deployment maintenance dialog:
 *   https://my.skybow.com/hc/en-us/articles/4581700792082-Data-Lookup-introduction#h_01FXW9X2DKR4X90ANFKNB1HPWF
 *
 * HOW TO RUN
 *   Open any page of the site where the solution is deployed (the site whose lists hold the data
 *   lookup columns), as a user with Manage Lists / Full Control there. Press F12, paste this whole
 *   file into the Console, Enter. Then call sbDataLookups.run(...) - see the usage printed below.
 *   Edge / Chrome / Firefox (not IE11). Nothing is written unless you pass apply: true.
 */
(function () {
    if (typeof _spPageContextInfo === "undefined") {
        throw new Error("Open a page of the SharePoint site first (_spPageContextInfo is missing).");
    }
    var WEB = _spPageContextInfo.webAbsoluteUrl.replace(/\/$/, "");
    var ORIGIN = location.origin;
    var eqi = function (a, b) { return String(a || "").toLowerCase() === String(b || "").toLowerCase(); };
    var lit = function (s) { return encodeURIComponent("'" + String(s).replace(/'/g, "''") + "'"); };
    var absUrl = function (u) {
        u = String(u).trim();
        return (/^https?:\/\//i.test(u) ? u : ORIGIN + "/" + u.replace(/^\/+/, "")).replace(/\/+$/, "");
    };
    async function api(url, opts) {
        opts = opts || {};
        var res;
        try {
            res = await fetch(url, {
                method: opts.method || "GET",
                credentials: "same-origin",
                headers: Object.assign({ Accept: "application/json;odata=verbose" }, opts.headers),
                body: opts.body
            });
        } catch (e) {
            throw new Error("cannot reach " + url + " (" + e.message + "; a site on another host cannot be called from this page)");
        }
        if (!res.ok) {
            var msg = res.status + " " + res.statusText;
            try { msg = (await res.json()).error.message.value; } catch (e) { /* keep status */ }
            throw new Error(msg);
        }
        return res.status === 204 ? null : (await res.json()).d;
    }
    var digestValue = null;
    async function digest() {
        if (!digestValue) {
            digestValue = (await api(WEB + "/_api/contextinfo", { method: "POST" })).GetContextWebInformation.FormDigestValue;
        }
        return digestValue;
    }
    async function getDataLookups(filter) {
        var lists = (await api(WEB + "/_api/web/lists?$select=Id,Title&$top=5000")).results;
        var found = [];
        for (var i = 0; i < lists.length; i++) {
            var list = lists[i];
            if (filter.list && !eqi(list.Title, filter.list) && !eqi(list.Id, filter.list)) continue;
            var fields = (await api(WEB + "/_api/web/lists(guid'" + list.Id + "')/fields" +
                "?$select=Id,InternalName,Title,Hidden,ClientSideComponentProperties")).results;
            for (var j = 0; j < fields.length; j++) {
                var f = fields[j];
                var raw = f.ClientSideComponentProperties;
                if (f.Hidden || !raw || !/"SkybowDataConnector"\s*:\s*true/i.test(raw)) continue;
                if (filter.field && !eqi(f.InternalName, filter.field) && !eqi(f.Title, filter.field)) continue;
                var props;
                try { props = JSON.parse(raw); } catch (e) {
                    console.warn("Skipped " + list.Title + " / " + f.InternalName + ": properties are not valid JSON");
                    continue;
                }
                var dc = props.DataConnection;
                if (!dc || !eqi(dc.DataSourceType, "SharePointList")) continue;
                found.push({ list: list, field: f, dc: dc });
            }
        }
        return found;
    }
    async function resolveSource(siteUrl, listRef, displayField) {
        var webAbs = absUrl(siteUrl);
        var base = webAbs + "/_api/web";
        var web;
        try { web = await api(base + "?$select=ServerRelativeUrl"); }
        catch (e) { throw new Error("source site '" + siteUrl + "' not found: " + e.message); }
        var webRel = web.ServerRelativeUrl;
        var path, alias = null;
        if (listRef.title) {
            path = "lists/getByTitle(@l)";
            alias = listRef.title;
        } else if (listRef.url) {
            var u = String(listRef.url).trim();
            if (/^https?:\/\//i.test(u)) u = decodeURI(new URL(u).pathname);
            alias = u.charAt(0) === "/" ? u : webRel.replace(/\/$/, "") + "/" + u;
            path = "GetList(@l)";
        } else if (listRef.id) {
            path = "lists(guid'" + String(listRef.id).replace(/[{}]/g, "") + "')";
        } else {
            throw new Error("no source list: pass listUrl or listTitle");
        }
        var qs = function (extra) {
            return "?" + [alias !== null ? "@l=" + lit(alias) : null].concat(extra).filter(Boolean).join("&");
        };
        var list;
        try { list = await api(base + "/" + path + qs(["$select=Id,RootFolder/ServerRelativeUrl", "$expand=RootFolder"])); }
        catch (e) { throw new Error("source list '" + (alias || listRef.id) + "' not found in " + webRel + ": " + e.message); }
        var rootRel = list.RootFolder.ServerRelativeUrl;
        var listWebRel = (webRel === "/" ? rootRel : rootRel.substring(webRel.length)).replace(/^\/+/, "");
        var df = displayField;
        if (df) {
            try {
                df = (await api(base + "/" + path + "/fields/getByInternalNameOrTitle(@f)" +
                    qs(["@f=" + lit(df), "$select=InternalName"]))).InternalName;
            } catch (e) { throw new Error("display column '" + df + "' not found in source list " + rootRel); }
        }
        return { SiteUrl: webRel, LookupListUrl: listWebRel, LookupListId: list.Id, DisplayField: df };
    }
    async function save(item, next) {
        var props = JSON.parse(item.field.ClientSideComponentProperties);
        Object.assign(props.DataConnection, next);
        await api(WEB + "/_api/web/lists(guid'" + item.list.Id + "')/fields(guid'" + item.field.Id + "')", {
            method: "POST",
            headers: {
                "Content-Type": "application/json;odata=verbose",
                "X-RequestDigest": await digest(),
                "X-HTTP-Method": "MERGE",
                "IF-MATCH": "*"
            },
            body: JSON.stringify({
                __metadata: { type: item.field.__metadata.type },
                ClientSideComponentProperties: JSON.stringify(props)
            })
        });
    }
    async function run(options) {
        var o = options || {};
        if (!o.siteUrl && (o.apply || o.listUrl || o.listTitle || o.displayField)) {
            throw new Error("siteUrl is required to change data lookups, e.g. sbDataLookups.run({ siteUrl: '/sites/crm' })");
        }
        digestValue = null;
        var items = await getDataLookups(o);
        if (!items.length) {
            console.warn("No 'SharePoint list from any site' data lookups found in " + WEB +
                (o.list || o.field ? " for list '" + (o.list || "*") + "', field '" + (o.field || "*") + "'" : ""));
            return [];
        }
        if ((o.listUrl || o.listTitle || o.displayField) && items.length > 1) {
            throw new Error(items.length + " data lookups match; listUrl / listTitle / displayField need exactly one - " +
                "add list and field. Matches: " + items.map(function (x) { return x.list.Title + " / " + x.field.InternalName; }).join(", "));
        }
        var rows = [];
        for (var i = 0; i < items.length; i++) {
            var it = items[i], cur = it.dc;
            var row = { List: it.list.Title, Field: it.field.InternalName, Status: "" };
            var next = null;
            try {
                var listRef = o.listTitle ? { title: o.listTitle }
                    : o.listUrl ? { url: o.listUrl }
                    : cur.LookupListUrl ? { url: cur.LookupListUrl }
                    : { id: cur.LookupListId };
                next = await resolveSource(o.siteUrl || cur.SiteUrl || WEB, listRef, o.displayField || cur.DisplayField);
                var changed = ["SiteUrl", "LookupListUrl", "LookupListId", "DisplayField"]
                    .filter(function (k) { return !eqi(cur[k], next[k]); });
                if (!changed.length) {
                    row.Status = "OK";
                } else if (o.apply) {
                    await save(it, next);
                    row.Status = "UPDATED";
                } else {
                    row.Status = "WILL CHANGE";
                }
            } catch (e) {
                row.Status = "ERROR: " + e.message;
                next = null;
            }
            ["SiteUrl", "LookupListUrl", "LookupListId", "DisplayField"].forEach(function (k) {
                row[k] = next && !eqi(cur[k], next[k]) ? (cur[k] || "(empty)") + "  ->  " + next[k] : (cur[k] || "");
            });
            rows.push(row);
        }
        console.table(rows);
        if (o.siteUrl && !o.apply && rows.some(function (r) { return r.Status === "WILL CHANGE"; })) {
            console.log("Nothing written. Run again with apply: true to save the 'WILL CHANGE' rows.");
        }
        return rows;
    }
    window.sbDataLookups = { run: run };
    console.log([
        "sbDataLookups loaded for " + WEB,
        "",
        "  // 1. Report: every data lookup, where it points, and whether that resolves",
        "  await sbDataLookups.run()",
        "",
        "  // 2. Point all data lookups at another site (each keeps its list URL)",
        "  await sbDataLookups.run({ siteUrl: '/sites/crm' })                // preview",
        "  await sbDataLookups.run({ siteUrl: '/sites/crm', apply: true })   // write",
        "",
        "  // 3. One column to another site / list / display column",
        "  await sbDataLookups.run({ list: 'Orders', field: 'Customer', siteUrl: '/sites/crm',",
        "                            listUrl: 'Lists/Customers', displayField: 'Title' })              // preview",
        "  await sbDataLookups.run({ list: 'Orders', field: 'Customer', siteUrl: '/sites/crm',",
        "                            listUrl: 'Lists/Customers', displayField: 'Title', apply: true }) // write"
    ].join("\n"));
})();

Back to top