Highbrowse

In Excel, it is often the case that you will have rows of data with similar characteristics, which need to be graphed but you do not want to create a graph for each row. Via a list box and the use of the OFFSET function, one graph can be used to illustrate each row of data in turn with the user choosing the row from the list.

Imagine you have sales data from a number of different brands. The data are monthly sales volumes measured in kilo-units for the year and monthly sales totals in a common currency. You need a graph which displays both data sets in the same graph, with two different Y axes for any one brand, of which there may be anything up to, say, one hundred.

Let’s say your volume data are on a worksheet called Volumes in rows 2 to 100 with column headings in row 1. Column A contains the name of the brand and columns B to M the volume figures. The same pattern occurs in the Value Sheet for sales by value. In each case we add four Quarter totals in columns N to Q.

In the Graph worksheet , we will create our graph and create two rows of data which will contain an offset so we can move to different rows, depending on the choice made in the list box, which refers to the column containing the brands. Before defining the offset formula, we need to insert a list-box which will reference the brands in column A of either the Volumes or Values sheet. This is the input range and the cell link can be any free cell such as J3, which will then be hidden behind the list-box.

The OFFSET function for the Volumes row is as follows:

=OFFSET(Volumes!A$2,-1+$J$3,0)

and for the Values row:

=OFFSET(Value!A$2,-1+$J$3,0)

The resulting graph and list box look like this:

It is useful to use the Append text functionality to multiline text fields in SharePoint, but it is difficult to optimize the display of these entries in a SharePoint view. Using a content editor web part, however, it is possible to create a text box which will display the contents of the multiline text field in a box when the user hovers over an appropriate link.

To use this technique, add a Content Editor Web Part to the page with your view, below the view itself and place the following code in the source:

<script type=”text/javascript” src=”/test/English/Javascript/jquery-1.3.2.min.js”></script>

<script type=”text/javascript” src=”/test/English/Javascript/Preview_DispForm_metadata.js”></script>

<script type=”text/javascript”>

hoverImg = ‘/_layouts/images/OPENDB.GIF’;

hoverImgDescription = ‘Hover mouse over this image to preview the metadata’;

arrOfFieldsToShow = [‘MultilinePlainText’];

prependHoverImageTo = ‘MultilinePlainText’; // If “append-field” insert FieldInternalName here

</script>

You will need to insert the correct path to the two JavaScript files which are required and replace MultilinePlainText with the name of your text field.

You will also need two resource JavaScript files, Preview_DispForm_metadata.js, written by Alexander at http://sharepointjavacript.wordpress.com who is the author of these techniques. The other is the jquery file.

The custom JavaScript is available from:

http://sharepointjavascript.wordpress.com/2009/11/17/preview-metadata-in-list-view-on-mouseover/

Active Directory groups can be used to manage access control within SharePoint but oftentimes, the required groups are not in existence and cannot easily be created. But there is a lot of relevant organizational information in Active Directory which could be used for the creation of access control within SharePoint. By combining the ability of Nintex workflows to query Active Directory using LDAP and the possibility of creating access control groups within SharePoint via web services, group creation and configuration can be automated to some degree.

In order to do this, you will need a SharePoint list which stores the parameters which will be used to lookup information from Active Directory.

In this example, the objective is to create country based access control groups, which are then made up of certain departments. As the nomenclature used in each country may vary, the list allows this information to be tailored in each case.

The overall shape of the workflow is as follows:

The first step is to delete the existing group and then recreate it, querying Active Directory in five parallel actions for the members of three core departments, in this case Finance, Legal and Tax, with the option of another department plus a non-department criterion if the information required does not fall into the standard departmental schema.

Each parallel action consists of the following steps:

The LDAP query assembles the members of the group, then a For Each loop takes each user in turn, adds the domain name to the user ID and then write to the group which has been created.

The workflow only needs to be run once to create each group. However, it is ultimately dependent on the consistency of information held in Active Directory.

Nintex offers the possibility of doing an LDAP query of directories such as Active Directory:

Any attributes (in the case of Nintex, termed “Property to retrieve”) which are present in Active Directory can be looked up and used within a workflow. Below is a table outlining common attributes.

Calculated columns within SharePoint come in useful and broadly follow the syntax which is used in Excel functions. But sometimes they exhibit strange behavior. Take the following case. I have a list with items which can be linked to one another and needed to create a view which would display all items which are linked together. This could be done using a formula in a calculated field, except that a hyperlink field cannot be included in a calculated column formula.

So I wrote a little workflow to copy the contents of my hyperlink field to another text field (called ParentLink) and then run my calculated column against this field using the following formula:

=IF(ISNUMBER(FIND(“http”,ParentLink)),(MID(ParentLink,FIND(“=”,ParentLink,1)+1,LEN(ParentLink))),ID)

ParentLink is my text field and so the formula looks to see if it contains a hyperlink, and if yes then it will extract the ID number at the end of the URL. If the key text “http” is not found then it enters the ID of the item. The formula seems to work, correctly displaying the ID numbers of parent items and any child items that are linked to them. However, if an item is edited and closed then, if there is no link, the ID number is not retained but is set to 0. This happens whether the format of the calculated column is text or number.

Currently, my workaround is to populate the ParentLink field either with a hyperlink if it exists, or with the ID of the item if it does not. Then, rather than using the ID field, I just use the ParentLink field as follows:

=IF(ISNUMBER(FIND(“h”,ParentLink)),(MID(ParentLink,FIND(“=”,ParentLink,1)+1,LEN(ParentLink))),ParentLink)

This works, but bizarre behaviour, nonetheless.