locked
Count list items conditionally based on another list RRS feed

  • Question

  • Hi,

    I have two lists Projects and ProjectTasks. The ProjectTasks has a coloumn called Overdue, calculated based on the difference of (Today - DueDate).  If the difference > 3, set Overdue to "Yes". I need a column in Project list called [Overdue Count], counting Overdue = "Yes" in ProjectTasks list for each project item. How can I do it?

    I have searched the Web related to this topic, such as Data View, but it didn't help me. I am using SP 2007. Any hint is very much appreciated!

    Ella


    EW
    Tuesday, October 11, 2011 8:38 PM

Answers

  • Hi,

    I’ve tried bellow to check the count of “Yes” of every project. You can use the columns relevant in your project to make a test. You could directly count the “Yes” field in list view.

    1.       You may have a column named “projectID” in ProjectTasks list, and it’s related to the actual ID of project in Projects list.

    2.       Modify a list view of ProjectTasks list and find the Filter, choose “show items only when the following is true”, set the condition as ”Overdue” is equal to “Yes”.

    3.       Find the Group By, choose to group by projectID, then click OK.

    4.       The count of “Yes” of every project’ task is just behind the projectID in ProjectTasks list.

     

    If you want to join the Projects list and ProjectTasks list together, you could use a data view web part.

    1.       Create a linked data source: http://blah.winsmarts.com/2007-10-Performing_joins_between_SharePoint_lists.aspx.

    2.       Use the linked data source to create a Data view web part.

    3.       Select ID field and additional fields you need from Projects list, insert it as “multiple item form”.

    4.       Select projectID and additional fields you need from ProjectTasks list, insert it as “joined subview”. Select ID of Projects list is equal to projectID of ProjectTasks list.

    5.       Find the related <table>…</table> tag of the subview. Add following line before the last “</table>” label:    

             <tr><td>count: <xsl:value-of select= "count(/dsQueryResponse/projectTasks/Rows/Row[@Overdue=’Yes’ and @projectID=$dvt_ParentRow/@ID])" /></td></tr>.

    6.       Save the change and preview the page to see the result.

     

    If anything is unclear, don’t hesitate to get in touch.

    Thanks,
    Emir

    • Proposed as answer by Shubham Goyal Thursday, October 13, 2011 7:22 PM
    • Marked as answer by EW868 Thursday, October 13, 2011 7:50 PM
    Thursday, October 13, 2011 8:41 AM

All replies

  • Since the 'overdue' column is calculated based on dates, you cannot use an eventreceiver on the ProjectTask list to update the Project list when an item changes (as there is no event to attach to).

    One option would be to create a timer job (SPJobdefinition: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.administration.spjobdefinition(office.12).aspx Look in the 'Community Content' section at the bottom of the page for examples) that runs every day. The job could enumerate all the items in the ProjectList and get a count of the Overdue ProjectTasks.


    Sven De Bont - MCAD/MCTS http://blog.sdbonline.com
    Wednesday, October 12, 2011 8:28 AM
  • Hi,

    I’ve tried bellow to check the count of “Yes” of every project. You can use the columns relevant in your project to make a test. You could directly count the “Yes” field in list view.

    1.       You may have a column named “projectID” in ProjectTasks list, and it’s related to the actual ID of project in Projects list.

    2.       Modify a list view of ProjectTasks list and find the Filter, choose “show items only when the following is true”, set the condition as ”Overdue” is equal to “Yes”.

    3.       Find the Group By, choose to group by projectID, then click OK.

    4.       The count of “Yes” of every project’ task is just behind the projectID in ProjectTasks list.

     

    If you want to join the Projects list and ProjectTasks list together, you could use a data view web part.

    1.       Create a linked data source: http://blah.winsmarts.com/2007-10-Performing_joins_between_SharePoint_lists.aspx.

    2.       Use the linked data source to create a Data view web part.

    3.       Select ID field and additional fields you need from Projects list, insert it as “multiple item form”.

    4.       Select projectID and additional fields you need from ProjectTasks list, insert it as “joined subview”. Select ID of Projects list is equal to projectID of ProjectTasks list.

    5.       Find the related <table>…</table> tag of the subview. Add following line before the last “</table>” label:    

             <tr><td>count: <xsl:value-of select= "count(/dsQueryResponse/projectTasks/Rows/Row[@Overdue=’Yes’ and @projectID=$dvt_ParentRow/@ID])" /></td></tr>.

    6.       Save the change and preview the page to see the result.

     

    If anything is unclear, don’t hesitate to get in touch.

    Thanks,
    Emir

    • Proposed as answer by Shubham Goyal Thursday, October 13, 2011 7:22 PM
    • Marked as answer by EW868 Thursday, October 13, 2011 7:50 PM
    Thursday, October 13, 2011 8:41 AM
  • Thanks a lot for your reply, Sven De Bont!

    The timer job is a bit out of my experience. Will try to find other ways.


    EW
    Thursday, October 13, 2011 7:12 PM
  • Hi, Emir,

    I tried your second suggestion, using linked data source; and with your count suggestion, it works very well.

    I really appreciate your suggestions!

    Ella


    EW
    • Edited by EW868 Thursday, October 13, 2011 7:50 PM
    Thursday, October 13, 2011 7:20 PM