Forum Discussion

Mark Teviotdale's avatar
Mark Teviotdale
Copper Contributor
Mar 15, 2017

Accessing list items (with history) in SharePoint online

Hi Team,

 

I have a SharePoint list that is a small sales pipeline. I would like to either export the list items with version metadata so I can hook it up to Power BI and analyse it. There is a drop down that controls the item through the sales pipeline so exporting the history would give me metrics on how many items made it all the way through the sales pipeline. Is this possible to do? I have tried exporting via excel or connecting directly to the list in Power BI but can't view the version controls change records.

 

Another approach would be to setup workflow that every time a list item was created/edited it dropped the current item into a duplicate historical list. Thoughts on this approach would be apricated if above is not possible. 

 

Thanks

Mark

  • JaredMatfess's avatar
    JaredMatfess
    Iron Contributor

    Hi Mark,

     

    I might not be understanding all the requirements but maybe rather than creating all those duplicate records perhaps you could do a few of the following:

     

    1) Maybe have an extra column that would track last status before close? That way you know how far it got in the pipeline before it was closed?

     

    2) If for some reason you wanted that data outside of the pipeline list, why not have a workflow create an item in that history list for key events versus on every *touch* (edit).

     

    3) For those type of business process solutions I find myself leveraging calculated columns for certain activities which help out reporting. Not sure if this would be applicable in your scenario? Maybe tracking time between open and close - leverage "Created" for initial and then set the close column value when that action happens. Then perhaps a calculated column that does the math to figure out the difference between created & the custom closed column.

     

    Maybe one of these will give a little inspiration? Happy SharePointing!

     

    -Jared

    • Mark Teviotdale's avatar
      Mark Teviotdale
      Copper Contributor

      Hi Jared 

       

      Thanks very much for your reply and your suggestions, I'm pretty lose regarding requirements on this one! I think your idea about adding another column in the list would work well with regrads to storing the previous pipeline status. I'm assuming I need to setup a workflow for the value to be copied ? 

       

      Yes I have been looking into using the created and modified columns to look at how long items have been in the pipeline. But the data is noisey on the created value due to migration from on-prem to SharePoint online so I will have to apply some filerting.

       

      Thanks for you help,

      Cheers

      Mark

      • JaredMatfess's avatar
        JaredMatfess
        Iron Contributor

        Hi Mark,

         

        Yes that's exactly right - you'd have a workflow that runs when the Item is edited and it can check the dropdown value. It would then write a value to that new custom column you'll create.

         

        Sorry to hear that you're dealing with some bad data. Most of the tools today should've been able to maintained the dates, but it sounds like that ship has sailed.

         

        Definitely add to this thread if you hit anything else while building.

         

        -Jared

Resources