Joining Lists using Calculated Columns

Question

I have two lists, a parent list and a child list, which I wish to join by ID. the end result is that I wish to pull information from the parent list across to the child list according to this common ID key.

Starting point: I do not have access to SharePoint Designer, workflows, nada, zip, so I am using calculated columns to bring the two together. Yes, this following is a desperate method.

Base Data List 1 Name: CCParent

List 1 Columns: CPParenttitle (single line text)

List 2 Name: CCChild

List 2 Columns: CCChildkey (single line text) CCChildTitle (single line text)

So, the logic is that where the CCChild(CCChildkey) = CCParent(ID) then copy the CCParent(CPParenttitle(ID)) to CCChild(CCChildTitle(CCChildkey))

So, to do this, I've added the following extra columns into CCChild. The first column is setTitlefunction. This is a Calculated Column, left as text.

="function setTitle(TR, ParentKey){"
&" var CTXSRC = new SP.ClientContext(""https://oursite/Lists"");"
&" var listsrc=CTXSRC.get_web().get_lists().getByTitle(""CCParent"");"
&"  var itemsrc=listsrc.getItemById(ParentKey);"
&"CTXSRC.load(itemsrc, ""CPParenttitle"");"
&"var srcitemTitle = itemsrc.get_item(""CPParentTitle"");"
&"  var CTX=new SP.ClientContext.get_current();"
&"  var list=CTX.get_web().get_lists().getById(SP.ListOperation.Selection.getSelectedList());"
&"  var ID=TR.id.split(String.fromCharCode(44))[1];"
&"  var item=list.getItemById( ID );"
&"  CTX.load(item);"
&"  item.set_item("
&CHAR(39)
&"CCChildTitle"
&CHAR(39)
&"  , srcitemTitle);"
&"  item.update();"
&"    CTX.executeQueryAsync("
&"     Function.createDelegate(this, function(){"
&"        AJAXRefreshView({currentCtx:ctx,csrAjaxRefresh:true},1);"
&"     }));"
&"}"  

Next I add in another calculated column, set, which is a calculated column, of type number.

="<button onclick=""{"
&  setTitlefunction
&" var TR=this.parentNode.parentNode.parentNode;"
&" setTaskID(TR,"
& CCChildkey
&");"
&"}"">"
&"Copy Title"
&"</button>"

What happens when the list is displayed and the button clicked? The whole row is highlighted, with a tick on the left hand side to indicated the row is selected, but that's it. The details aren't carried across.

I've also done the check to make sure that the variables in the column are the same as the internal variable names.

Can anyone spot the problem with the code? Are there any parts that I have missed?

Thanks!

=========================

UPDATE:

I have progressed with some test code that does execute, but not with the correct outcome.

The code below is supposed to copy the Title from Row 1 of a Parent List and copy this into the field ChildTitle in the current row of a Child List. When the code runs, blank values are put into the ChildTitle, and not the Title from the other List. Can anyone spot the error?

="<img src=""https://..blank.gif"" style=""cursor:pointer"""
&" onclick=""{event.stopPropagation();"
&"var clientContext=new SP.ClientContext.get_current();"
&"var list=clientContext.get_web().get_lists().getByTitle('ChildList');"
&"var TR=this.parentNode.parentNode.parentNode;"
&"  var ID=TR.id.split(',')[1];"
&"clientContext.load(item);"
&"var ccxsrc=new SP.ClientContext.get_current();"
&"var listsrc=ccxsrc.get_web().get_lists().getByTitle('ParentList');"
&"var itemsrc=listsrc.getItemById(1);"
&"ccxsrc.load(itemsrc);"
&"ccxsrc.executeQueryAsync();"
&"var ParentTitle = itemsrc['Title'];"
&"item.set_item('ChildTitle',ParentTitle);"
&"item.update();"
&"this.style.opacity=0;"
&"clientContext.executeQueryAsync("
&"(function(){"
&" this.style.opacity=0.5;"
&" this.style.backgroundColor="&CHAR(39)&"lightcoral"&CHAR(39)&";"
&" false || AJAXRefreshView({currentCtx:ctx,csrAjaxRefresh:true},1);"&"}).bind(TR));"&"}"">"

Answer 1

Note:

in June 2017, Microsoft disabled the use of JavaScript in a Calculated Column

That means given answers may not apply for newer SharePoint versions

For long explanation and work arounds see:
June 13th 2017 Microsoft blocked handling HTML markup in SharePoint calculated fields - how to get the same functionality back

---

Original answer:

.. when the list is displayed and the button is clicked the whole row is highlighted, with a tick on the left hand side to indicated the row is selected, but that's it. [the function is not executed].

That happens because the SharePoint row has an onclick event by itself which selects the row so you can perform actions in the Ribbon.
When you click your button; the event bubbles/propagates up the DOM and first triggers all other events on parent/ancestor DOM elements. The default SharePoint row event is executed which prevents/cancels other events.

You can deselect the SharePoint row-select behaviour in the View settings:
(Tabular View setting is collapsed by default in the EditView page)

or

you add one line to your button function instructing it to not bubble up the click event:

="<button onclick=""{"

&" event.stopPropagation();"

&  setTitlefunction
...

If you are getting deeper into this ICC (Inline Javascript in Calculated Columns) stuff
take some time to understand the examples I referred to you earlier,
I purposly kept them 'simple' and added lots of explanations:

http://www.viewmaster365.com/#/Create/ActionDelete
Is exactly your requirement of executing an action on a click

http://www.viewmaster365.com/#/Create/SearchFilter
Is interesting as well, as it adds an eventlistner to another DOM element (the default List search-box)

Also understand the difference of event.stopPropagation() versus event.preventDefault()

The less characters you type, the less mistakes you can make

As explained in other SO postings, the code I post come from my private-use-only Calculated Column editor, which can't handle single-quotes well because I store them in JSON format.

In your code replace:

&"  var ID=TR.id.split(String.fromCharCode(44))[1];"

with

&"  var ID=TR.id.split(',')[1];"

and

&"  item.set_item("
&CHAR(39)
&"CCChildTitle"
&CHAR(39)
&"  , srcitemTitle);"

with

&"  item.set_item( 'CCChildTitle' , srcitemTitle);"

Makes it better to understand/read

Answer 2

The simplest solution is to create a lookup column in your child list that points to the parent list. Any additional fields you'd like to automatically include, you can tick the box in the lookup column configuration. Then when users create child list items, they select the parent, and all the appropriate parent data is included in the child list item.