Sharepoint, Meet Database. Database, Sharepoint.

...And, Sharepoint? We'd like to ask you to be a bit more like Database.

I needed to associate multiple records from one list with a single item in another list. And boy, is that harder than you think!

Say, for example, that you have a list of projects. You want to be able to enter notes on each project as the weeks go by. If multiple users are working on a single project, then each should be able to add as many notes as they want. Versioning wouldn't be an option in this case, since the users want to be able to quickly and easily view all notes for a project at once.

So, that means you gotta do two lists: ProjectList and NotesList. Each Note will have some kind of ID tying it to a Project. Simple enough - databases do this all the time, right?

Well, the challenge is in NewForm.aspx!

We had started working on a custom New Item Form page for the Projects list, assuming that we could add a simple button that, when clicked, would let a user enter a new note. Specifically, we figured that the button would redirect the user to the New Item page for the Notes list, add an ID, and life would be good.

The problem? You don't HAVE an ID. The ID is only created when the form is submitted.

I started surfing for options. I tried a couple JQuery solutions, but I honestly couldn't get them to work right. The Redirect-with-ID option was very promising, but I could only get it to perform the redirect on 'submit' - and that doesn't help much when I want my users to click a special button for this.
Tried combining Jquery with a custom submit button, but that didn't work either. Only the built-in button seemed to work. I'm sure I was doing something dumb, but I was frustrated and moved on.
I considered writing up an event handler, but the pain of writing it (code isn't my strongest suit) combined with the pain of deploying it convinced me to discard that option as well.
Found a few examples with SPAPI, but wasn't successful in implementing them.

Finally, inspired by the SharePoint Hillbilly's post on passing parameters, I took a look at it from the SharePoint perspective.
Let's see - I know I can use those custom submit buttons to get a user onto a page. And once I actually get there, there WILL be an ID, 'cause the form was just submitted. I can't pass it via the URL parameter since it's just not ready when the user clicks the button....But what if I can pull the ID back as soon as they hit the page?

I tried the following:
--Re-added that custom submit button to my New Project form, and pointed the redirect paramete rto my New Note form
--Added a stripped-down display item form to the New Note page. This form just shows one item: the ID of the most recently modified Project for the current user. To make life easier, I identified the cell that contains the Project ID as "ProjectID", so I could nab the value in my javascript.
--Added javascript to pull that ID and write it to the form, so that the Note is associated with the right Project ID (code below)
--Added more javascript to append that ID onto another custom submit button - this one points the user at the Project list's EditItem form

Now a user can click to enter a new project, type stuff in, click to enter a note, go right back to their project page, and finish typing whatever they want. Magic!


---THE CODE----
*Custom button on New Project form:
<input type="button" value="Add a Note" name="btnSave"onclick="{ddwrt:GenFireServerEvent('__commit;__redirect={http://server/site/pages/newnote.aspx}')}; " />

*Javascript to add the ID from the New Note page onto my redirect URL:
<script type="text/javascript">
function GoToPage(url)
{
var TheCell = document.getElementById("ProjectID")
var TheID = TheCell.innerHTML;
location.href = url + "?ID=" + TheID;
return false;
}
</script>

*Button code that will send user from New Note page to Project Edit Form with correct ID appended:
<input type="button" value="Click me!" onClick="GoToPage('http://server/site/Lists/Projects/Item/editform.aspx');"></input>


*Javascript to pull the value from my ProjectID field and write it to the Notes form:
<script type="text/javascript">

_spBodyOnLoadFunctionNames.push("fillDefaultIDValues");

function fillDefaultIDValues() {
var temp = document.getElementById("ProjectID")
var RealId = temp.innerHTML;
setLookupFromFieldName("Project ID", RealId);
alert(temp);
}

function setLookupFromFieldName(fieldName, value) {
if (value == undefined) return;
var theSelect = getTagFromIdentifierAndTitle("select","Lookup",fieldName);

// if theSelect is null, it means that the target list has more than
// 20 items, and the Lookup is being rendered with an input element

if (theSelect == null) {
var theInput = getTagFromIdentifierAndTitle("input","",fieldName);
ShowDropdown(theInput.id); //this function is provided by SharePoint
var opt=document.getElementById(theInput.opt);
setSelectedOption(opt, value);
OptLoseFocus(opt); //this function is provided by SharePoint
} else {
setSelectedOption(theSelect, value);
}
}

function setSelectedOption(select, value) {
var opts = select.options;
var l = opts.length;
if (select == null) return;
for (var i=0; i < l; i++) {
if (opts[i].value == value) {
select.selectedIndex = i;
return true;
}
}
return false;
}

function getTagFromIdentifierAndTitle(tagName, identifier, title) {
var len = identifier.length;
var tags = document.getElementsByTagName(tagName);
for (var i=0; i < tags.length; i++) {
var tempString = tags[i].id;
if (tags[i].title == title && (identifier == "" || tempString.indexOf(identifier) == tempString.length - len)) {
return tags[i];
}
}
return null;

}

</script>