Thrilling title, eh? So, previously I’ve talked about merged lists in the Dataview web part. This time, I was after something slightly different – rather than merging two lists, I wanted to join them.
Joining is pretty easy, actually, but the process is a little bizarre. I ended up using the post ‘Performing Joins with SharePoint Lists‘ by Sahil Malik. The thing that confused me was that when I was I expected to provide the ‘keys’ of my lists when I created the new Datasource – but actually, you define it when you insert your ‘joined subview’.
Anyway, the scenario was that there are customer purchases for ‘Credits’ to do things. Operatives then do things for them, and these actions subtract time off those pools of credits. The need was for a way to:
- Record purchases of pools of credits. Customers may purchase multiple times, but under different agreements (or invoices, or whatever)
- Record actions and the number of credits that they cost
- Remaining total.
I solved this with a Dataview web part, and two lists. First off, we’ve got a list of customer Credit purchases:
This is the list for recording the pools of credits bought by customers. Note that Agreement Ref is the ‘key’ to this list, not customer name, as a customer may purchase multiple times.
Then we’ve got a (simple) list for recording actions, and how many credits it cost:
Notice that the Agreement Ref column is a lookup column against Agreement Reference in the ‘Credits’ list.
So, an operative performs some action, and records it’s usage:
…and then the Dataview web part shows our agreements, totals and remaining time:
Okay, so that kind of skipped a step – how the hell did I get the dataview web part to show that last screen? Well, I inserted a Joined subview as Sahil Malik described, and then modified the XSL for the subview.
- I removed the titles and stuff for the subview
- I changed the XSL template for the subview body. Instead of calling a template for each row, I created two XSL variables – for the number of credits in the pool, and to calculate the number used (highlighted in yellow)
- I then added another XSL variable to calculate the number remaining!
- And finally, I emit the values as two table cells:
So what’s that scary XSL line for ‘used’ doing? Well, it’s summing the ‘Used’ values for rows where the value of ‘Agreement Ref’ equals the ‘Title’ of the parent row. Works pretty well.You could do other stuff there – like counting the number of calls, etc..
How is this better than a grouped view, with a ‘Sum’ total for the used column? Well, we needed to have a way of seeing the remaining credits – this means knowing 1) the total credits bought in the first place, and 2) units remaining. A list view with a group and ‘Sum’ total wouldn’t show us these – and without that context, the totals are a bit meaningless.
Hopefully normal blogging service will be resumed shortly – been busy as lately!