Solved

Extracting Data from Appended Multi-Line Text Field


I have a multi-line text box that is appending changes.  The SharePoint list does not display any information in this field, but instead displays ‘View Entries’.  I need to build a workflow that will pull out all the data in this field (userstamp, datestamp, comment), and load into another text field so we can easily see all comments and export it to excel.

icon

Best answer by emha 15 June 2018, 06:44

View original

14 replies

wouldn't it be easier to use regular multiline text field instead of appendable one?

you'd have direct access to whole track...

for appendable multiline text field you will have to get whole history track by a call to Lists.asmx web service method GetVersionCollection and implement a logic that extracts respective data

Lists.GetVersionCollection method (WebSvcLists) 

Hi Marian,

Unfortunately, no, a regular multiline text field will not work.  We are using this field to track communications between different users.  We need to be able to track the date/time and user that submitted the comments.  Essentially, we are using it like a messenger (Skype Business Messenger).  We need to be able to export the entire conversation into excel for analysis purposes, hence the need to use the append changes function on the multiline text box.

For the solution you provided, I will need further indepth instructions for how to complete that.  Please include screen shots and documentation of each step.

Thank you,

basically, it's not anything strange

let's have an appendable text field named "appendrtf" with the content like follows

as mentioned above, you have to make a webservice call to get field's version history track.

you'll get returned quite huge XML with all the versions/changes of a requested field and actual content for each change

next you'll need process above returned XML and extract data that are interesting for you.

eg. to get dates and comments (content) Query XML action configuration might look like follows.

results have to be stored into collection variables.

note: so that it's visually clear what going on I just copy&pasted XML returned by above action. in runtime you will need to store result into a variable and then use the variable as an input.

Note: markups seen in content output are caused by the appendrtf field being 'reach text' enabled. if you use plaintext field you will get simple plain next.

finally just process content of collections according your needs.

‌  ‌append changes‌

Good Morning Marian,

Thank you for these instructions, as this is very helpful.  I will need to work with my IT Department to setup a generic Username & Password, as it is not best practice to use your own Username & Password with automated procedures.  Other than that, I am making headway (using my own credentials for the time being). 

I am confused on how to get from step 1 to step 2 above.  Your screen shot of the Query XML does not show what I need to enter into the XML input box.  What do I need to load into there to pull in the XML returned from the Call Web Service step?

Thank you again very much for your help.

as mentioned above, you have to store webservice result into a variable

and then use the variable as an input for Query XML

ok, great, thank you!!!  I am pulling out the data correctly!!

I do have a follow-up question, and if I need to create a new post, please let me know.

Once I have this data pulled out, how can I remove the extra xml tags as in the screen shot below:

I would like to take this data, strip out the non-needed text, concatenate the outputs back together and store in another field.  I know to concatenate the fields back together I need to use Build String, but I'm not sure what to use when stripping out the unneeded text.

Thank you!!

this I would see as a different topic, please start new question.

Thank you again, Marian for all your help!  I will post a new question based on my last post.

Hi Marian,

I am trying to run my workflow based on the above setup.  Nothing is being returned.  I inserted an email Notification to show me the raw XML that is being returned from the Call Web Service.  Nothing is displaying in the email.  When I click on the 'Run Now' in the workflow designer, it shows data being returned.  Why, when I publish and run the workflow, is there nothing being returned?

Body of email contains the variable in which the above is to be stored.  Nothing is being returned/stored in the variable.

What is causing the disconnect?  Why is it working when I manually 'Test' the call web service while in designer mode, but when I run it regularly after publishing, is it not pulling anything back?

Thank you!

make sure you send notification in plain text format.

if you send it as a reach text XML elements are interpreted as a HTML markups

Hi Marian,

Thank you, that was extremely helpful!!!  Additionally, in my Query XML action, I had to update the outputs to use the following:

//defaultNS:Versions/defaultNS:Version/@Modified

Which just removed the first defaultNS:GetVersionCollectionResult.

Once I remove that first part, and changed the email to be Plain Text, data started being returned and visible in the emails.

Thank you!!

Which just removed the first defaultNS:GetVersionCollectionResult.

if you haven't had mistyped anything it should with it.

but  it doesn't matter if it's not in there in this case.

Yes, I'm not sure why it was not working.  I made sure everything was entered correctly.  When I ran and extracted the raw xml, it did not look like the raw xml in your image within this post.  After comparing the 2 sets of XML, I removed the defaultNS:GetVersionCollectionResult, and it immediately started working and loading the text into the different outputs.

Badge +5

@cwentzel - can you provide detailed steps and screen prints you used to concatenate the fields back together using build string? Thank you very much.

Reply