Category Archives: vLookup

vLookup for SharePoint 2010-13 v2.100 BETA

I have published a BETA version of vLookup Frontend with these changes:

  • Fixed a bug where number columns could not use the “css” set in vLookup setup.
  • Changed how the list name is picked up from the URL as this would fail in some cases – as described here.
  • Added support for settings SPFieldLookupMulti fields when creating children – as requested here.
  • Changed the $ to spjs.$ as I did in the latest BETA of DFFS due to an error in SharePoint when using rich text fields and “Insert > Link > From SharePoint” as the file “assetpicker.js” will “kill” jQuery by overriding the global $ variable.

Get the latest BETA version here (ensure you pick the latest one).

This is released as a BETA due to the changes in how jQuery is referred. Please post any findings or questions in the forum.

Best regards,
Alexander

vLookup for SharePoint 2013 v1.5

Change log
September 3, 2014
Changes in v1.730:
Minor update for compatibility with DFFS v4 “direct to backend script”.

June 27, 2014
Changes in v1.723:
Added check for a function named “vlookupChildViewOrEditCallback” to detect open or edit of a child (in dialog only). Add a function with this name, and it will be called when the dialog closes.

April 29, 2014
Changes in v1.721:

  • Fixed a bug from the previous version when using [URL:Key] in the CAML: NOT supplying the URL parameter generated an error.
  • Changed how the debug also alerts the values set in NewForm of the child item when using ?vLookupDebug=1 in the url

April 27, 2014
Changes in v1.720:

  • Fixed first column width if header row is hidden.
  • png icons in SP2013 and fallback to white icon for file types not recognized.
  • Added icons to show the view state of the groups (when using grouping).
  • Added option to use a URL querystring variable in the CAML. Hover over the help icon for the “Build Query” or the “Write your own CAML-query” to learn how to use it.

April 21, 2014
Changes in v1.710:
If you leave all display names in the viewFields section empty, the header row will be hidden.

April 13, 2014
Changes in v1.700:

Please note that there are a number of changes in this version and there will most likely be a few new bugs. You should test it before adding it to a production environment. Let me know if you find a bug, and I’ll fix it as soon as I can manage.
  • Fixed a compatibility issue with “fixQuirkyLookup” in the function “init_vLookupFields”.
  • Added option for selecting whether or not to open “new child” in a dialog.
  • Added “Show all option” when using “Rowlimit”.
  • Added support for grouping the items.
  • When using “Attachments” as viewField you will now get the paperclip in stead of “Yes” or “No” to indicate that an item has attachments.
Please note that you must index the columns you are querying to prevent errors when you reach a total of 5000 items in the list.

April 13. 2014
Changes in v1.651:
Fixed an issue with IE 11 for SP 2013: L_Menu_BaseUrl is undefined.

February 2. 2014
Changes in v1.65:

  • Changed <View Scope=’Recursive’> to <View Scope=’RecursiveAll’> to fix a bug with discussions (that are in fact folders).
  • Added option to refresh all vLookup columns when one has changed.
  • Added option to open links in the same window, and not in a new dialog.
  • Minified the code.

January 04. 2014
Changes in v1.644:

  • Added L_Menu_BaseUrl and L_Menu_LCID to the script for IE 11 compatibility in SP 2013.
  • Wrapped the table cell values in a div for better handling of “ViewField Special configurations CSS”.
  • The “ViewField Special configurations CSS” will also be applied to empty cells.
  • Fixed missing refresh in list view when a vLookup item is added or edited.

December 06. 2013
Changes in v1.640:

  • A new Action: “Comma separated list” is added. See instructions on the help icon for the “Action” field.
  • Custom style now works for all column types and not only text and note columns.
  • Date format can now be specified in the “In URL” section. See the help icon for details.
  • A few small bugfixes.

November 12. 2013
Changes in v1.631:
Child table max-height: changed overflow:scroll > overflow:auto to hide the scrollbar if it is not needed.

November 3. 2013
Changes in v1.63:

  • Added option to pass an URL parameter to the child list NewForm when adding children. Hover over the help icon for instructions.
  • Added option to set the max-height on the child table to have vertical scrollbars.
  • Changed how the page id is calculated when storing and retrieving the configuration. In some cases it came out wrong.

October 11. 2013
Changes in v1.62:
Added the number formatting support for “Action” sum, average and count.

October 11. 2013
Changes in v1.6:
Updated the number formatting support and added separate setting for “thousandsSeparator”. You can now use one or more of the following settings: “decimals”:2,”thousandsSeparator”:”.”,”decimalSeparator”:”,”}

October 06. 2013
Changes in v1.59:

  • Fixed set multichoice column from URL.
  • Added automatic refresh of the vLookup table when editing a child.

September 16. 2013
Changes in v1.56: Fixed a bug regarding showing documents located in sub folders in a document library.

September 10. 2013
Changes in v1.55:

  • Fixed a bug regarding multi choice people pickers.
  • Changed the available “events” you can use.

Interaction / events (put code in the CEWP)

// If defined, this function is called when clicking on "add new child". This function must return true to allow for children, or false to disallow.
function vlookupAddChildPreCheck(fin){
	if(fin==="vLookup_Your_FieldInternalName"){
		// check something and return false if conditions are not met
	}
	return true;
}

// If defined, this function is called for each of the fields set in the child from the "In URL" setting.
function vLookupPresetFromUrlDone(fin){
	if(fin==="FieldInternalName_you_are_setting"){
		// do something
	}
}

// If defined, this function is called when a vLookup column is rendered complete.
function vLookupIsLoadedCallback(fin){
	if(fin==="vLookup_Your_FieldInternalName"){
		// do something
	}
}

// If defined, this function is called when a child is added and you close the dialog with "OK".
function vlookupChildAddedCallback(){
	// Do something
}

// If defined, this function is called when you click "add new child", but close the dialog without saving.
function vlookupChildNotAddedCallback(){
	// Do something
}

August 28. 2013
Changes in v1.53:

  • Fixed bug in multiselect viewfields (checkboxes, multilookup and multiuser)
  • Added rowlimit setting for the query.
  • Changed some ids and variable names in the code.

This solution lets you create relationships between two or more lists based on for example a lookup column connection. You can list all children in the parent element (the children has a lookup column targeting the parent). This is not possible out of the box in SharePoint.

You can also have any kind of dynamic query based on values in the current form. You can for example pull in information from a list based on a selection in a choice column. Please note that this last example will not pull in this information on the fly when changing the selection, but when the form is loaded in DispForm, EditForm or in a list view.

This solution also lets you create new children from the parent (both DispForm, EditForm and in list views), and lets you auto-populate the “connection” to the parent element to ensure a connection.

Image from the GUI
IMG
I have previously posted two articles in this series:
vLookup for SharePoint 2010
vLookup now supports SharePoint 2013

This one is done mainly for compatibility with the Dynamic Forms for SharePoint solution, but I have redone the code and also fixes a few bugs and adds some new features:

  • The creation of the configuration list is now triggered when you first enter the setup. This is done to prevent some rare bugs where the list is not detected onload, thus asking to create it despite it already exists.
  • The heading for number columns are now aligned right (as the values)
  • Fixed bug where exiting the setup did not load the form with its item-ID.
  • Added custom css for the viewFields. Hover over the help icon to the right of the “ViewFields” section for details.
  • Added option to enter a license code to remove the PayPal-logo in the top of the GUI
Used with the Dynamic Forms for SharePoint solution

Refer this solution in the CEWP where you set up the DFFS solution (DispForm and EditForm) like this:

<link type="text/css" href="/Scripts/DFFS/default.css" rel="stylesheet">
<script type="text/javascript" src="/Scripts/jquery-1.10.2.min.js"></script>
<script type="text/javascript" src="/Scripts/spjs-utility/spjs-utility.js"></script>
<script type="text/javascript" src="/Scripts/vLookup/vLookup.js"></script>
<script type="text/javascript" src="/Scripts/DFFS/dffs.js"></script>
Used as a standalone solution in forms or list views

To use this as a standalone solution, you must do the configuration in the CEWP code like this:

<script type="text/javascript" src="/Scripts/jquery-1.10.2.min.js"></script>
<script type="text/javascript" src="/Scripts/spjs-utility/spjs-utility.js"></script>
<script type="text/javascript" src="/Scripts/vLookup/vLookup.js"></script>
</script>
First setup

Please note that the setup must be MANUALLY triggered the first time. Set up the scripts, create the vLookup column and then – in DispForm – add this to the URL to create the list:
/Lists/mylist/DispForm.aspx?ID=[an existing ID in your list]&vLookupSetup=1

Refer the previous articles in this series for further details on the setup process.

Download code

Get the code for the file “vLookup.js” here.

How to remove the PayPal-logo / Unlicensed version from the GUI?

In this version I have added support for providing a license code for removing the PayPal-logo and displaying a “Registered to” text in stead. To obtain a license code, go to this page, scroll down to “Dynamic Forms for SharePoint / vLookup for SP201X” and follow the instructions.

As this is JavaScript, there is of course no problem to bypass this license code and manually remove the logo, but please remember that I put in a lot of hours developing these solutions, and I appreciate a few beers now and then.

Please post any bugs or comments below.
Alexander

vLookup now supports SharePoint 2013

Change log
June 19. 2013
Changes in v1.47

  • Added {currentSite} as option in “List base URL” to dynamically use the current sites base url. Use this if you plan to save the site as a template.

May 15. 2013
Changes in v1.45

  • Numbers are now aligned right.

May 14. 2013
Changes in v1.44

  • Added formatter for percentage in the viewfields: {“percentage”:true,”suffix”:”%”}
  • Applied “toLocaleString” to numbers that are not formatted as “percentage”.

May 13. 2013
Changes in v1.43

  • Fixed bug related to Dynamic Forms for SharePoint and reloading form when creating a child element.
  • Fixed a bug regarding prefix and suffix for currency and number columns. Suppresses the prefix / suffix if the field is empty.

Added event hooks:

To have something done before a child element is creates, add a function with this name: “vlookupAddChildPreCheck”
This function must return true to procede with the add child action.

To have something happen after a child had successfully been added, add a funcion with this name: “vlookupChildAddedCallback”
This function must return true to procede with the updating of the “vLookup child table” in the form.

To have somethng happen after the vLoolup solution has finished loading the “Add new item button”, add a function with this name: “vLookupIsLoadedCallback”

I have upgraded the vLookup solution to support SharePoint 2013. I have not dissected SP2013 fully, and therefore you might find some bugs regarding the SP2013 support. Please post any bugs or suggestions below, and I’ll address them as soon as I can manage.

Read this article for setup instructions and download instructions

Besides adding SP2013 support, his version also features some new features:

  • Added option to write your own CAML-query freehand
  • Added dateFormat option to specify the returnvalue for a date and tinme column using a string like this: yyyy-MM-dd HH:mm:ss

IMG

Post any comments below,
Alexander

vLookup for SharePoint 2010

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.
Bugs fixed:

  • 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.

Features added:

  • 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.

This new solution is much easier to set up as it has a GUI:
IMG

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).

When this solution is setup in a list view, it looks like this:
IMG

DispForm and EditForm:
IMG

IMG
Adding new “Tasks” in EditForm does not refresh the form, only the “vLookup items” – thus you does not lose unsaved data in your EditForm.

How to set up the solution
  1. Download the code for the file “vLookup_sp2010.js” from here.
  2. Download the file “spjs-utility.js” from here. Ensure you get the latest version (by date of the folder).
  3. If you prefer a local copy of jQuery, get it here.
  4. Upload this files to a library in your SharePoint site collection, or to a folder in the root site – created using SharePoint Designer.
  5. 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.

    To be able to have the solution work in EditForm, you must use a single line of text column as “vLookup field”. Note that you cannot use this field to store anything as the contents will be overwritten by the vLookup solution.
  6. Add a HTML Form Web Part to the bottom of your list view, DispForm or EditForm, and add this code trough the “Source editor”:

    <script type="text/javascript" src="/test/vLookupv2/Script/jquery-1.7.2.min.js"></script>
    <script type="text/javascript" src="/test/vLookupv2/Script/spjs-utility.js"></script>
    <script type="text/javascript" src="/test/vLookupv2/Script/vLookup/vLookup_sp2010.js"></script>

    IMG

    You use the exact same code for list views and for list forms.

Creating the configuration list

The first time you set up the solution, you will get this prompt:
IMG

Hit “OK” to create the list:
IMG

You will now get this prompt (provided your field is in the view):
IMG

You will now be presented with the configuration GUI:
IMG

This GUI is automatically presented while the configuration has not been created, or it contains errors that are detected by this solution. To reenter the configuration at a later time, add “vLookupSetup=1” to the URL like this:

// List view
.../Lists/MyList/AllItems.aspx?vLookupSetup=1
// DispForm
.../Lists/MyList/DispForm.aspx?ID=12&vLookupSetup=1

All the configuration option are explained by hovering over the question mark.

Creating new items in the “child list”

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.

MUI support

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.

Do you use this solution?
I spend a lot of time developing these free solutions. If you use it, please hit the PayPal button to send me a few bucks as motivation for releasing new solutions.

vLookup type rollup for SharePoint

02.03.2011 Updated the code for the file “vLookupForSharePoint.js” to v1.7.0

  • Add an “orderBy” option to sort the returned items by a FieldInternalName – See parameter descriptions below.
  • The dependencies upon interaction.js and stringBuffer.js has been eliminated.

10.06.2010 Updated the code for the file “vLookupForSharePoint.js” to fix some bugs.
This update includes:

  • Support for multiple webparts in one page
  • Bugfix: get listName in EditForm mode
  • Added option “DispFormRelURL” under “inQueryString” to provide current items relative DispForm URL
  • Handling multichoice values by replacing “;#” with “<br />”
  • Fixed bug with “newItemLink” if “linkBack” is not true
  • Added id tag for the “newItemLinkContainer” – to make hiding it possible.
  • Fixed bug when item has no “children” (newItemLink and inQueryString)
  • Fixed bug when matching on a calculated column

06.05.2010 Updated the code for the file “vLookupForSharePoint.js”.

This update includes:

  • Some small bugfixes
  • Added support for getting items connected by a multiLookup
  • Added createNewItem option by setting the parameter “newItemLink:true”
  • Added inQueryString option to include values from DispForm when creating new items using the “createNewItem” option
  • Added option to display vLookup connected items in EditForm
  • Localized for Norwegian, Swedish and English

01.04.2010 Small update to the file “vLookupForSharePoint.js”. I have modified the argument “hoverToView” to have separate settings for DispForm and for ListView. You must change the argument “hoverToView” to “hoverToViewDispForm” and “hoverToViewListView” in your calculated columns.


I have long thought of making a generic solution for pulling information from another list, kind of like you would do in Excel with the function vLookup. Here is my attempt to create just this. I’m fairly confident this solution will fill an empty space in many SharePointers hearts…

This solution features

  • Easy, generic interface to pull information from any list in current site or cross site
  • Concatenation of values from multiple items
  • Sum, average or count values from multiple items
  • Link back to filtered view of all matched items in “sum”, “avg” or “count” -mode, or to individual items in “concat” -mode
  • Direct link to documents
  • “Reverse lookup” on lookup columns – the “mother” item now has link back to the “children”.
  • And more…

The way this solution works is by using a calculated column to build a string with all the parameters like this:

This code matches the ID of the current item against a lookup column (the “child-list” has a lookup column named “ParentBug” targeting the “Mother-list” – what field the lookup is connected to is irrelevant as it is the ID we are looking for). This approach is a bit different than it will be for other columns as the ID is not accessible to a normal calculated column.

="listName:vLookupBugTrackerTasks|find:ID|findInCol:ParentBug|action:concat|linkBack:true|viewFields:Title#Title;TaskDescription#Task description;Deadline#Deadline"

This example matches the text in the “Title” column in one list against the “Title” column in another list.

="listName:vLookupBugTrackerTasks|find:"&Title&"|findInCol:Title|action:concat|linkBack:true|viewFields:Title#Title"

These are the available arguments:

  • listName: Name or GUID of the target list.
  • listBaseUrl: The base URL of the site the target list is located in. Defaults to current site is the argument is omitted.
  • find: The string to search for in the column specified as “findInCol” below. To use the current items ID (in case of a lookup connection), specify like this: “find:ID”.
  • findInCol: The FieldInternalName of the column to query against.
  • viewFields: “Array” of the fields of which to return a value for. Format: FieldInternalName1#DisplayName1. Separate multiple fields with semicolon.
  • action: “sum”, “avg”, “count” or “concat”.
  • sumOrAvgPrefix: A prefix in “sum” or “avg” -mode, like “$” for dollar.
  • sumOrAvgPostfix: As above, but postfix.
  • linkBack: true=Link back to item(s), false=no linkback. Default value: false
  • linkBackView: If in “sum”, “avg” or “count” -mode, set the target view name for the filtered result on “linkBack”. Defaults to “AllItems.aspx” if omitted.
  • hoverToViewListView: (In “concat” -mode) true=Displays a placeholder text that the user must hover over with the mouse to view the items, false=View items directly in list view. Default value: false
  • hoverToViewDispForm: (In “concat” -mode) true=Displays a placeholder text that the user must hover over with the mouse to view the items, false=View items directly in DispForm. Default value: false
  • hoverToViewEditForm: (In “concat” -mode) true=Displays a placeholder text that the user must hover over with the mouse to view the items, false=View items directly in DispForm. Default value: false
  • newItemLink: (In “concat” -mode) true=Displays a “Create new item” link. Default value: false
  • inQueryString: (In “concat” -mode) semicolon separated array of FieldInternalNames to include the value from in the queryString that is passed to the NewForm. This only applies if the above parameter is set to true. Default value: “”. You need another script in the target list to pull the parameters from the query string and write them to a field in NewForm
  • orderBy: New! A FieldInternalName to sort the returned items by. Set the parameter “orderBy” in the calculated column like this: orderBy:Title. To have the result ordered descending, append a hash behind the FieldInternalName like this: orderBy:Title#

These are the base arguments that are required:
‘listName’,’find’,’findInCol’,’action’,’viewFields’

Note: If your formula errors out, it most likely has to do with the string being to long. To overcome this problem, just concatenate the string like this:
…first part of the string her”&”and the rest here…


Example images:

List view with tasks pulled from tasklist
IMG

Hover over each item for “link back menu”. Click on a “link back” will take you to the item
IMG

hoverToViewListView:true – before hover:
IMG

hoverToViewListView:true – after hover:
IMG

“sum”, “avg” or “count” looks like this:
IMG

A click on a “link back” on items in “sum”,”avg” or “count” -mode will take you to a filtered list of all matched items.
IMG
The target list view is set in the parameter “linkBackView”.

DispForm with “newItemLink:true”:
IMG

The same item in EditForm:
IMG

When setting the parameter inQueryString like this “inQueryString:Title;ID”, the value from these fields are included in the querystring of the URL (click to enlarge the image):
IMG
You need another script in the target list to pull the parameters from the query string and write them to a field in NewForm. Look here for an example.

When the code is added to the listView and to DispForm/EditForm, you create a new “enhanced lookup column” by creating a new field of type “Calculated (calculation based on other columns)” with a FieldInternalName starting with “vLookup” (you can rename the column afterwards, it’s only the FieldInternalName that is important). All fields with a FieldInternalName starting with this text will be included. No need to specify the columns to include! (this is not true for EditForm though, see separate instructions). Note: the ID column must be in the view (but can be hidden in the script).

Here is the code

As always we start like this:
Create a document library to hold your scripts (or a folder on the root created in SharePoint Designer). In this example i have made a document library with a relative URL of “/test/English/Javascript” (a sub site named “test” with a sub site named “English” with a document library named “Javascript”).

The jQuery-library is referred from Google, but if you prefer a local copy, it is found here. The pictures and the sourcecode refers to jquery-1.5.1.min. If you download another version, be sure to update the script reference in the sourcecode.

The sourcecode for the file “vLookupForSharePoint.js” is found below.

Read here how to add a CEWP to the DispForm or EditForm.

Add this code in a CEWP below the list form in DispForm:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.1/jquery.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/vLookupForSharePoint.js"></script>
<script type="text/javascript">
  init_vLookupForSharePointDispForm();
</script>

Add this code in a CEWP below the list form in EditForm:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.1/jquery.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/vLookupForSharePoint.js"></script>
<script type="text/javascript">
  // This is an array of objects. Add more fields by adding a new object to the array
  // The "insertHere" value can be "top", "bottom" or a FieldInternalName to append the returned data to
  init_vLookupForSharePointEditForm([{'FieldInternalName':'vLookupTasks','FieldDisplayName':'Tasks','insertHere':'BugDescription'}]);
</script>

The reason the EditForm code is different is that the calculated column is not available unless we query for it using a CAML query. The “insertHere” parameter is used to place the “new field”.

Add this code in a CEWP below the list view:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.5.1/jquery.min.js"></script>
<script type="text/javascript" src="/test/English/Javascript/vLookupForSharePoint.js"></script>
<script type="text/javascript">
// The argument "true" sets the ID column to hidden
  init_vLookupForSharePointListView(true);
</script>

The code for the file “vLookupForSharePoint.js”:
download code

Upload the file to the scriptlibrary as described above.

If you find any bugs, please let me know!

Regards
Alexander