How To Save Workflow History To Document Properties?


Badge +5

Hi Gang

We are using SharePoint and Nintex versions 2013.  I have a workflow that allows a document to be reviewed and approved by a number of employees.  When the document is fully approved I would like to save the workflow (approval) history somehow to the document properties.  I have no way to change the setting that removes the regular workflow history after 60 days.  So I'm stuck with trying to save it off for audit purposes.  For us we need to have this info for a number of years.  Can someone help me figure out how to save the workflow history so that we'll always have it for audit purposes for a document.

Best Regards,

Andy


17 replies

Badge +7

Hi,

you could query the Workflow History List to get all history entries and then write them to a sharepoint column. The trick is, that the History-List is a hidden list and therefor not visible in the "Query List" action.

But if you use the CAML editor you could "hand-craft" a CAML-statement to retrieve all associated history list entries.

206462_pastedImage_1.png

Of course you need to insert the name of the History-List you associated with the current workflow. Then you just have to query the internal fieldname "WorkflowInstance" for the value of the current workflow instance. (Attention: you need to have two "{" at the beginning and two "}" at the end!)

I retrieved the Description (that's the actual message logged to the history log) into a collection.

I then joined all collection-entries into a string, separated by a "newline".

206463_pastedImage_3.png

You can now write this into a sharepoint column, which could be mapped to a document or just keep it in the column.

Badge +5

Ok Henning, I think I have this working!

I've now created the following CAML code.  My last question to this is how would I get the User ID, Date Occurred, Outcome, and Description all together on their own line for each approval showing in a single item field (for the approved document in this case)?

Something like

Josh Francisco - 8/15/2017 7:47 PM - Approved - (Josh Fransisco) This is my approval comment.

Amy Zeller - 8/15/2017 5:22 PM - Approved - (Amy Zeller) This looks great!

Sarah Collison - 8/15/2017 2:13 PM - Approved - (Sarah Collison) Ok.

<Query>
  <Lists>
    <List Title="NintexWorkflowHistory" />
  </Lists>
  <ViewFields>
    <FieldRef Name="User ID" />
    <FieldRef Name="Date Occurred" />
    <FieldRef Name="Outcome" />
    <FieldRef Name="Description" />
  </ViewFields>
  <Where>
    <And>
      <Eq>
<FieldRef Name="WorkflowInstance" />
<Value Type="Text">{Common:WorkflowInstanceID}</Value>
</Eq>
      <Eq>
<FieldRef Name="Outcome" />
<Value Type="Text">Approved</Value>
</Eq>
    </And>
  </Where>
</Query>

Badge +7

Awesome! You will have to change "User ID" to just "User" and "Date Occured" to "Occured" as the CAML will need to reference the interanl field names, not the display names (hint: you can test the query in the "query list" action by pressing the "run now" button!)

Well, as shown in my original reply I store the result in a collection. You'll need to have four collections, one for each field.

Instead of joining the elements of the collection you need to iterate over one of the collections, remember the index of the current iteration and retrieve the corresponding values from the other collections. To retrieve the values from the collection you'll need some "temporary" variables - I like to call them "varCurrent[Fieldname]". So this is a set of variables you might need:

206674_pastedImage_1.png

You will want to wire the collections in the query list action:

206682_pastedImage_6.png

To iterate over your results you need the for-each action:

206679_pastedImage_3.png

Next you have to retrieve the description, date and outcome using the index like this:

206680_pastedImage_4.png

Finally you can build-up a string out of these values:

206681_pastedImage_5.png

Badge +5

Hi Henning

I'm close but I'm not getting any data in my variables (e.g. varCurrentDescription) from the collections (e.g. varColDescriptions). 

I set up the variables as you've recommended.

For testing, I run the Query List (in the "Run Now" using an existing WorkflowInstanceID) it works and I get data as below. 

When I run the real workflow I put "{Common:WorkflowInstanceID}" into the value for WorkflowInstance.

The next action as you've suggested creates the For Each loop using the UserID collection. 

The first action in the For Each Loop pulls out the description from it's collection.

For testing I put this Set Field action next.  Unfortunately when I run the workflow nothing is put into the Test field.  So that leads me to think there is nothing in the collection.

Any ideas on your side would be greatly appreciated!!


Best Regards,
Andy

Badge +7

Is just the Description empty or are all collections empty?

Badge +5

I'm not sure how to check to see if the collections are empty.  I assume that they are empty because I'm not getting anything in the end varHistory variable that should include everything.  I wish I could check to see if the CAML query is returning anything.

Badge +7

OK, make sure that you put {Common:WorkflowInstanceID} in double "{" and "}" like this {{Common:WorkflowInstanceID}}. The inner "{" are being replaced and the outer are there - because it's supposed to be a guid happy.png

Badge +5

Hmmm... I changed it to the following with the 2 curly brackets and I get the same result.  Seemingly no errors but no data as well.  It all looks like it should work.

<FieldRef Name="WorkflowInstance" />
<Value Type="Text">{{Common:WorkflowInstanceID}}</Value>

Badge +7

Can you enable verbose Logging für the workflow? You can enable this in the workflow settings. And you will also need to enable this in the central administration in the global settings of nintex workflow.

After enabling this, you can inspect the values in the workflow history by clicking on the workflow actions.

Badge +5

I'm pretty sure I don't have access to the central settings and I don't think it's turned on.  Is there any way that I can log variable values to the workflow history?

Badge +5

Hi Henning

Ok, here's some telling information.

In the For Each loop I added an action to change the value of (a test field I created) Test2 to be varIndex.  Then at least I can see if the loop is processing and what varIndex was when the workflow finished.

Here's the action.

So.. when I view the field properties after the workflow has completed Test2 is blank.  I would think that it means the For Each loop is not even starting. 

Your thoughts?

Best Regards,

Andy

Badge +5

Henning, this is big.  Ok, on a hunch I tried this.  I've been thinking that the {{Common:WorkflowInstanceID}} is not working and the CAML is not returning anything.  So I changed this;

<FieldRef Name="WorkflowInstance" />
<Value Type="Text">{{Common:WorkflowInstanceID}}</Value> 

to this;

<FieldRef Name="WorkflowInstance" />
<Value Type="Text">{50681acf-532c-4cf2-aa05-1222364521d7}</Value>

The {50681acf-532c-4cf2-aa05-1222364521d7} is the Workflow Context, Workflow Instance ID of another approved document in the library. The result is I get data in my fields.  It's data from another document but it proves that the code works.  You can see in the picture below that for the Travel Approval document, the Approval Details field and the Test2 field have data (that are getting data from the Chargeback document).



So... how do I get this working with the Workflow Instance ID of the current document?  Weird, huh?

Best Regards,

Andy

Badge +7

That sounds great!

Just a question: the workflow, that is retrieving the history - is this workflow also creating workflow-history entries or are we talking about two different workflows? I was under the impression, that you would have just one worklow, where these actions are the lasts steps in the workflow.

If we're talking just one workflow - you might have to make sure that all workflow-history entries are actually written to the list. You can achieve this, by adding a "commit pending changes" action to the workflow. I would insert this actions just before you start querying the history-list.

Badge +7

that's a little unfortunate. But as a workaround you could write values to the history for debugging purpose. You just will have to remember to remove those actions prior to going production with your workflow.

Badge +5

Yes, it's just one workflow and these actions to put the workflow approval history into a field is added onto the end of the workflow. 

Ok so I added the Commit Pending Changes action to the workflow just before the query history action... and... I get the same result with no data.  sad.png

The weird thing is I have a Set Field Value action just before the query history action and the field is populated with the correct workflow instance ID.  Yet, the next action it seems that there is nothing in the Workflow Instance ID or I'm doing the syntax wrong in the query.

Badge +5

Henning!!  Wait, wait, wait.  I put the Commit Pending Changes in the wrong spot.  I moved it to the right spot and it all works now!!!   OMG, what a nightmare that was!

Thank you ever so much for your patience and extensive help on this issue.


Best Regards,

Andy

Badge +7

No problem! Maybe you want to mark the reply (replies) as helpful/correct to help others as well.

Reply