16.02.2013 Updated to v1.3 with these changes:
- Added more options to process the values pulled back from the query. In addition to the previous substring, you can now have prefix and suffix and additional date formatting options. Hover over the help icon to learn more. Please note that you must reconfigure any existing “substring setup” after upgrading to v1.3.
- Added support for hyperlink columns.
- Added count to the “Action menu”.
- Fixed bug with “hide field” when using a text string enclosed in curly braces in the “In URL” configuration.
25.01.2013 Updated to v1.26 Added option to split the string by a specified separator, returning a selected array index.
30.11.2012 I have updated to v1.23 to fix a compatibility issue with Office 365 for SP2013.
18.11.2012 Updated to v1.22 and added some video tutorials:
Please note that this requires v1.22 of vLookup_sp2010.js
08.11.2012 v1.2 fixes some bugs and adds some new features.
- Removed an extra “/NewForm.aspx” from the url generated in the “addChild” function.
- When creating a child in a subsite or a parent site, the setting of values from the “parent item” failed due to a wrong baseUrl parameter in the URL.
- As suggested by “advacomp”, added an option to use a text string to set a field in the child list. See instructions on the help icon in the “In URL” section of the setup.
- Added an option in the GUI to hide the form label in DispForm and EditForm.
16.10.2012 v1.1 fixes a bug with wrong list ID appearing when you edit the configuration from DispForm or EditForm.
I have previously posted a solution for SharePoint 2007, and by request I have rewritten the solution for SharePoint 2010. Please note that this solution will NOT work for any SharePoint version prior to SharePoint 2010.
This solution, as the previous one, supports these “methods”:
- Show all records: Returns a list view of all the records.
- Sum: Sums the values for all the records.
- Average: Sums the values for all the records, and devides the sum on the number of non-empty records.
In the 2007 version, the configuration was setup in a calculated column trough the list settings > edit field.
In this solution, the configuration is stored in a dedicated list – shared by all vLookup columns in the site. This eliminates the fiddly work of hand editing the configuration.
When you first setup this solution in a site, the configuration list is created automatically. This list is not to be hand edited, and you might optionally hide it from browsers trough SharePoint Designer. You must however ensure all users have read access to this list.
You can use this solution in plain list views, grouped views and in DispForm / EditForm (see note bout field type below).
- Download the code for the file “vLookup_sp2010.js” from here.
- Download the file “spjs-utility.js” from here. Ensure you get the latest version (by date of the folder).
- If you prefer a local copy of jQuery, get it here.
- Upload this files to a library in your SharePoint site collection, or to a folder in the root site – created using SharePoint Designer.
- Add a column to your list / library where the FieldInternalName starts with “vLookup”. This is what triggers the solution. Note that it’s the FieldInternalName and not the Display name that must match.
You can use both a calculated column or a single line of text column as “placeholder” for the “vLookup items”. If you use a calculated column, use a formula like this:
The contents of the field will be overwritten anyway.
Add a HTML Form Web Part to the bottom of your list view, DispForm or EditForm, and add this code trough the “Source editor”:
You use the exact same code for list views and for list forms.
All the configuration option are explained by hovering over the question mark.
When you add items to the child list, and want to prefill values in your NewForm, you use the In URL section of the configuration to set the “from” and “to” fields.
You must add the same code to a HTML form web part below the form web part in NewForm as you have used in DispForm and list views. This code will pull in the values from the “parent list” and fill in the “child list”.
If you are “connecting” the two lists by a lookup column, ensure you prefill the lookup column like in the example in the top of this article.
This solution supports MUI and you can set labels and column headers in multiple languages. Use “default” as “LCID” to have a default value and/or set up the correct LCID (matching your installed language packs) as alternate values.