How to create a lookup column at site scope using site scripts ?

[stevebeauge]

What type of issue is this?

Question

What SharePoint development model, framework, SDK or API is this about?

Site designs & site scripts

Target SharePoint environment

SharePoint Online

What browser(s) / client(s) have you tested

• 💥 Internet Explorer
• 💥 Microsoft Edge
• 💥 Google Chrome
• 💥 FireFox
• 💥 Safari
• mobile (iOS/iPadOS)
• mobile (Android)
• not applicable
• other (enter in the "Additional environment details" area below)

Additional environment details

• browser version
• SPFx version
• Node.js version
• etc

Issue description

I need to create a list and a site column of type lookup that targets the list.

According to the documentation, site scripts can create list columns using addSPLookupFieldXml sub action, but there's no site level equivalent.

I tried to use createSiteColumnXml to specify everything manually, but I guess the List attribute is the blocking part.

According the documentation of the Field element, List attribute can be either:

• the list ID : not possible because the list's ID cannot be controlled and will be different across sites
• the list URL: we can infer the list url, but it's not working. The documentation state that it's working only if the list is from the same feature (which is more relevant to the good old days of provisioning web templates with elements.xml and other xml based files)

For example, this minimal site script reproduce the issue:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/site-design-script-actions.schema.json",
  "actions": [
    {
      "verb": "createSPList",
      "listName": "MyList",
      "templateType": 100
    },
    {
      "verb": "createSiteColumnXml",
      "schemaXml": "<Field Type=\"Lookup\" DisplayName=\"MyLookup\" List=\"Lists/MyList\"  ShowField=\"Title\" Group=\"DIAG\" ID=\"{bdcdf262-a7b7-455a-8d3e-d21e4f9add63}\" StaticName=\"MyLookup\" Name=\"MyLookup\" />",
      "pushChanges": true
    }
  ]
}

The site design does not error, but in the field properties, we have no "target"

FYI, the actual schema xml of the column in the site is:

<Field
  Type="Lookup"
  DisplayName="MyLookup"
  List="Lists/MyList"
  ShowField="Title"
  Group="DIAG"
  ID="{bdcdf262-a7b7-455a-8d3e-d21e4f9add63}"
  StaticName="MyLookup"
  Name="MyLookup"
  SourceID="{dea7ebb7-cd95-41aa-a125-a00da36fdd56}"
  Version="1"
/>

I tried various versions of the schema, playing with Overwrite="TRUE" DisplaceOnUpgrade="TRUE" parameters as I did on old onprem features, but nothing works.

How to solve this ?

PS: I'm aware that triggering a flow can overcome the limitation, but it introduces a lot of complexity, since the invoke flow action is asynchronous, and make subsequent site script actions hard to orchestrate.

PS2: The site script could have been awesome if it evolved. It never changes since its initial release AFAIK and even if some undocumented actions are presents in the schema, these actions are not working. What are the plan regarding this sitescripts?

Thank you for reporting this issue. We will be triaging your incoming issue as soon as possible.

[expiscornovus]

Hi @stevebeauge,

Edit: apologies, I did not read this thread properly. You are looking for a site column, not a list column. In that case ignore the below. 😅

I was running into a similar issue and came across this thread.

Not sure if it helps, since this is a year ago. But for me it worked when I used the addSPLookupFieldXml in a subactions array instead.

Below is an example.

{
     "$schema": "schema.json",
         "actions": [
            {
                "verb": "createSPList",
                "listName": "Tasks",
                "templateType": 100,
                "subactions": [
                    {
                        "verb": "setDescription",
                        "description": "Example Tasks List"
                    }
                ]
            },
            {
                "verb": "createSPList",
                "listName": "Deliverables",
                "templateType": 100,
                "subactions": [
                    {
                        "verb": "addSPLookupFieldXml",
                        "schemaXml": "<Field Type=\"Lookup\" DisplayName=\"Deliverable Task\" Required=\"FALSE\" EnforceUniqueValues=\"FALSE\" ShowField=\"Title\" UnlimitedLengthInDocumentLibrary=\"FALSE\" RelationshipDeleteBehavior=\"None\" ID=\"{101f1f89-d667-4c7f-9ebc-76cb5831d0a2}\" StaticName=\"DeliverableTask\" Name=\"DeliverableTask\" />",
                        "targetListName": "Tasks"
                    }
                ]
            }
],
"bindata": { },
"version": 1
}

[stevebeauge]

Don't worry 😂

I came to the conclusion that site design is a half finished feature. Lot of things are not properly handled or inexistant 😕