SharePoint 2013: Error while creating lookup column

Question

We are using SharePoint 2013.

We have two lists in our site: one custom list and one calendar list.

We are trying to add lookup column in Calendar list and referencing the custom list.

Adding lookup works fine. But when we tried to Enforce relationship behavior to Cascade delete, it throws an error.

Error we are getting:

This lookup field cannot enforce a relationship behavior because this list contains incompatible fields.

The Goal is to delete all the calendar items which has lookup set from custom list when the corresponding item from custom list is deleted.

Answer 1

This is a Known behavior of Calendar list (Event Content Type) in SharePoint.

Work around:

I followed below steps to achieve the cascade delete on Event Content type:

1. I created one custom list instead of Calendar list.
2. Added lookup column from Parent/Provider custom list and set Enforce relationship behavior to Cascade delete.
3. Added Event Content type in lookup consumer list (Newly created custom list).
4. Added newly created lookup column in Event Content type (Add from list columns).
5. Verified deleting items from Parent/Provider list and it automatically deletes event items from consumer list.

Note:

Set Enforce relationship behavior to Cascade delete on lookup column before adding Event content type to consumer list.

Answer 2

In the doc page for Lookup fields, under Creating a List Relationship, there is a box describing reasons that enforcing relationship behavior may fail:

In addition, setting a relationship behavior fails if:

• The lookup field allows multiple values.

  Before setting the RelationshipDeleteBehavior property, be sure that the AllowMultipleValues property returns false.

• Existing list items contain invalid values for the lookup field.

  This can occur if the lookup field's RelationshipDeleteBehavior property was previously set to None and someone deleted the item on the target list that the lookup field points to. The lookup field is thus orphaned; it contains an invalid value. A relationship behavior cannot be enforced if the list contains orphaned lookup fields.

• The lookup field points to a list in another website.

  Check the value of the lookup field's LookupWebId property.

• The number of items in the list exceeds the maximum set for large lists.

  Compare the value that is returned by the list's ItemCount property with the value that is returned by the web application's MaxItemsPerThrottledOperation property.

Are any of these conditions true in your case?

As a workaround, I would suggest using a SharePoint Designer workflow or a JSOM script to find and delete the related items in the Calendar list, before finally deleting the item in the Custom list.

For this to work, you would also need to hijack the "Delete" button in the Custom list form, or create a custom Delete button that triggers the workflow.

Alternately, you could create an SPD 2010 workflow and use the WF start form as a "staging area" in which to run the Script.

The script would have three parts: first, get the lookup value you want to search for from the current item. Second, search for items in the Calendar that have that value in the Lookup column. Finally, do a POST call on each item to delete it, making sure to include X-RequestDigest in the headers:

function deleteItem(listTitle, itemId) {
    return jQuery.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getByTitle('" + listTitle + "')/getItemById(" + itemId + "),
        method: 'POST',
        contentType: 'application/json;odata=verbose',
        headers: {
            'X-RequestDigest': jQuery('#__REQUESTDIGEST').val(),
            'X-HTTP-Method': 'DELETE',
            'Accept': 'application/json;odata=verbose'
        }
    });
}