Posts Tagged ‘Lookup fields’

Conditional Formatting Based On A lookup Field

June 1st, 2011 Abed Khooli No comments

I will not explain conditional formatting here as you can easily find plenty of literature on how to do that. You can format a row or a column (or show/hide content) based on a certain rule or condition. Conditions can be simple or complicated and can comprise comparison and Boolean operators. There is an issue when you try to based your condition on a lookup field (field referenced from another list).
Let’s say we have a list and one of its columns (status) is a lookup to a different list. We want to highlight the status cells (column) when the status is either empty or ” Rejected”. You may say well, click inside one of the status cells in a listview (xsltlistview), select conditional formatting, format column (from the ribbon in SPD), and in the criteria dialog, you set status is null or status equal Rejected. You select your format (reddish background in this example) and test it. You will notice that empty cells were picked but not those with value equal to Rejected. What;s the problem?
Well, internally, the lookup column is not stored as we think it is. In fact, I created this example using a service as a data source so you can see what I am talking about (this is why it has those funny column headers) – the status is stored in the format: “number;#value” and this is why the equal operator will not work. This is not too bad, actually. We can use contains instead and it’ll work just fine. The number is the ID of the item corresponding to the selected value (they happen to be in order in this example just by accident!). Also, you won’t make a new list to store a few status values – this is just an illustration. Finally, make sure, when debugging your conditional formatting, to use easy to spot changes like backgrounds or fonts. Some styles like borders may be overridden by the SharePoint styles!
There are other cases where a similar format is used: multiple selections, user fields and managed metadata, so beware.

Categories: SharePoint Designer 2010, SharePoint Server 2010, Web Parts Tags: ,

Importing Two Linked Tables Into SharePoint 2010

May 8th, 2011 Abed Khooli No comments

SharePoint 2010 has a list template called Import Spreadsheet which is very handy if you have data sitting in Excel or exported from Access tables into Excel sheets (assuming you need to work with lists and not Excel or Access services). The procedure is straightforward: you give the list a name and description (optional) and then browse to your Excel file and hit the Import button. You can select a data range as table range, cell range or named range (the import dialog may be hidden if Excel is open, but you will see its icon blink). Once your list data is imported, you can adjust column names (and some types – depending on how SharePoint interpreted it. It is probably better if you use short column names without spaces or strange characters in the Excel header and then change display names in SharePoint).
Now, what if you have two lists and they are related with one common column and you want to display one or more columns in the second list based on a common column with the first list? In SharePoint, you could create a lookup column to achieve this. An initial thought would be to import your two lists and change the common column in the second one to lookup. Unfortunately, this is not allowed (some column types can be changed to a limited list of other types).
One work around is to import the first list as described above and create the second list with a lookup pointing to the first list. Then, put this (empty) list in DataSheet View and copy data cells from the second Excel sheet (preserving column order and format). Your lookup column is now pre-populated. Now, add any other fields from the first list (edit the lookup field in the second list) and you are done. For new entries, you have to pick the lookup item from the drop down manually, of course, but this will save you from adjusting existing data.

Categories: Lists, SharePoint Server 2010 Tags: Import from excel, , Relational data