?
Solved

XPath predicate / sql join-esque statement

Posted on 2009-05-14
9
Medium Priority
?
813 Views
Last Modified: 2013-11-18
Hi,
I have two SharePoint lists.  One is a list of Purchase Requests, the other is a list of workflow tasks.  A workflow task references a Purchase Request.

Occasionally a workflow will fail to start correctly and the Purchase Request gets stuck as Pending despite having no active tasks.

Using SharePoint Designer I'd like to produce a table that displays Purchase Requests currently pending in the first column and the number of active workflow tasks corresponding to that invoice in the second column.

So the left column is
@PR_x0020_Reference
from the set
/dsQueryResponse/Purchase_Requests/Rows/Row[normalize-space(@PR_x0020_Status) = 'Pending']

To count the number of tasks for a specific invoice, say ISD0000140, you use the following expression
count(../../../Workflow_Tasks/Rows/Row[@TaskStatus!= 'Completed' and contains(@WorkflowLink, 'ISD0000140')])

Essentially I want to replace 'ISD0000140' with @PR_x0020_Reference, but when I do then it always evaluates to true so it just returns the total number of active tasks.

Hope that makes sense, any pointers much appreciated.
0
Comment
Question by:Dhope
  • 6
  • 3
9 Comments
 
LVL 21

Expert Comment

by:66866
ID: 24397831
Are you using a DVWP?
0
 
LVL 5

Author Comment

by:Dhope
ID: 24397869
Yes, I can add it as a joined subview but ideally I'd like to filter on the result, and the main table wouldn't know the contents of the subview that way.
0
 
LVL 21

Assisted Solution

by:66866
66866 earned 2000 total points
ID: 24397897
Did you try adding a parameter and passing the invoice number to the parameter?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:Dhope
ID: 24402057
Would the parameter not exist for the page rather than just a row though?  (see 'code' below)

So in the first row the parameter would be PR00000010, second row would be PR00000015 etc.
Column 1     |     Column 2
---------------------------------------------------------
PR00000010   | count(tasks WHERE linked_item = PR00000010)
             |
PR00000015   | count(tasks WHERE linked_item = PR00000015)
             |
PR00000025   | count(tasks WHERE linked_item = PR00000025)

Open in new window

0
 
LVL 21

Assisted Solution

by:66866
66866 earned 2000 total points
ID: 24412635
You'll have to create an explicit "FieldRef" for the PR Reference column. Did you create that in the SPDatasource?
Here's a reference
http://mdasblog.wordpress.com/2009/04/21/displaying-columns-in-a-crosslist-dvwp/
0
 
LVL 5

Author Comment

by:Dhope
ID: 24431620
I'm not using Marc's crosslist dvwp.  The problem isn't displaying results, it's displaying results without creating a nested table.
In the attached picture see the faint box around each result in the remaining GBP column.  This is because it's a subview.  I can't sort on subviews though, since each subview is unrelated to the other.
0
 
LVL 5

Author Comment

by:Dhope
ID: 24431622
I'm not using Marc's crosslist dvwp.  The problem isn't displaying results, it's displaying results without creating a nested table.
In the attached picture see the faint box around each result in the remaining GBP column.  This is because it's a subview.  I can't sort on subviews though, since each subview is unrelated to the other.
0
 
LVL 5

Author Comment

by:Dhope
ID: 24431633
Sorry.  Can't seem to upload the pic, guess the firewall at work is being a bit overzealous today.
0
 
LVL 5

Accepted Solution

by:
Dhope earned 0 total points
ID: 24501815
Think I have it
http://www.experts-exchange.com/Web/Web_Languages/XML/Q_21294193.html

count(../../../Workflow_Tasks/Rows/Row[contains(@WorkflowLink, current()/@PR_x0020_Reference)])
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question