How can I create a formula to pull all data in between html tags in a html field 3

[jchewsmith]

I have a field in my database that is html code and contains the tags and the data from a comment field.

My field data looks like this:

<TaskHistory>
<Save When>
<OpenAssigned to>
<Comment> This is the info that I want to display in my report! </Comment>
&lt;br /&gt;

I need a formula to easily pull only the data: This is the info that I want to display in my report!

 

[pmax9999]

I did it like this:

WhilePrintingRecords;
Local NumberVar S := InStr({Table.Column}, '<Comment>') + 9;
Local NumberVar E := InStr({Table.Column}, '</Comment>');
Local NumberVar L := E - S;

TRIM(Mid({Table.Column}, S, L))

Hope this helps.

Cheers
Pete

 

[lbass]

Or you can create a formula like this:

extractstring({table.textfield},'<Comment>','</Comment>')

Either approach should work.

-LB

 

[jchewsmith]

Great Post and THANKS!

Now what if I have additional comments in the memo field that also start and end with the tags <Comment>,</Comment>, is there a way to pull all instances of fields between those two tags?

 

[lbass]

Can you show an example of the actual field content including all HTML coding? If there are line returns using <br> for each line, the following code might work:

numbervar i := 0;
numbervar j := ubound(split({table.text},'<br>'));
stringvar display := "";

for i := 1 to j do(
if ubound(split({table.text},'<br>')) >= i then
display := display + extractstring(split({table.text},'<br>'),"comment>","</comment>")+'<br>');
display

You would then format this formula to HTML text interpretation.

-LB

 

[jchewsmith]

Unfortunately no line returns.
I only want to display: Added SFC 162 and Missing SFC for REsident Alien. Please update GSE delivery with the appropriate SFC, and then let me know when the task is complete. Thanks - User2:
but I would be fine with displaying everything from the first comment to the 2nd comment.
Sample:

<TaskHistory>
<Save When="1/3/2019 1:23 PM PT" By="User">
<ResolvedClosedAssignedTo To="User2" />
<Comment>Added SFC 162</Comment>
<HTMLComment>Added SFC 162</HTMLComment>
</Save>
<Save When="1/3/2019 1:20 PM PT" By="User2">
<OpenedAssigned To="User" />
<Comment>User,

Missing SFC for Resident Alien. Please update GSE Delivery with the appropriate SFC, and then let me know when the task is complete.

Thanks – User2

</Comment>
<HTMLComment>User2,
&lt;br /&gt;
&lt;br /&gt;Missing SFC for Resident Alien. Please update GSE Delivery with the appropriate SFC, and then let me know when the task is complete.
&lt;br /&gt;
&lt;br /&gt;Thanks - User2
&lt;br /&gt;
&lt;br /&gt;</HTMLComment>
</Save>
</TaskHistory>

 

[lbass]

So you are showing returns in your example, but really it displays in one continuous block?

-LB

 

[jchewsmith]

correct.

 

[lbass]

Try this:

numbervar i := 0;
numbervar j := ubound(split({table.text},'<comment')); //notice no closing caret
stringvar display := "";

for i := 1 to j do(
if ubound(split({table.text},'<comment')) >= i then //notice no closing caret
display := display + extractstring(split({table.text},'<comment'),">","</comment>")+'<br>'); //notice no closing caret except on comment exit code
display

-LB

 

[jchewsmith]

LB
I copied your formula and just changed the {table.text} to my actual table.field and
I changed the formatting to HTML Text amd I am not getting any errors but I am not getting consistent output. Some records show everything I want with not line breaks (which is ok) but other records only show the first comment's data?

Thank you for all of your help, it is very close to what I want.

Jeannie

 

[lbass]

Not sure why you wouldn’t get line breaks.

Please copy samples of one instance that is working properly and one that isn’t into this thread.

-LB

 

[jchewsmith]

LB,
I misspoke, upon further inspection I am only getting the data after the 1st comment tags, it is just that some of my raw data has more than one set of lines inside the 1st comment. Here is an example of raw data versus my output:
Raw Data:
<TaskHistory>
<Save When="11/19/2018 1:29 PM PT" By="User">
<ResolvedClosedAssignedTo To="User2" />
<Comment>Added SPF and updated credit scores</Comment>
<HTMLComment>Added SPF and updated credit scores</HTMLComment>
</Save>
<Save When="11/19/2018 9:31 AM PT" By="User2">
<OpenedAssigned To="User1" />
<Comment>Social Security Number (SSN) for borrower John Doe has either been recently issued or has not been issued by the Social Security Administration.
</Comment>
<HTMLComment>Social Security Number (SSN) for borrower John Doe has either been recently issued or has not been issued by the Social Security Administration.
&lt;br /&gt;
&lt;br /&gt;</HTMLComment>
</Save>
</TaskHistory>

My output:
Added SPF and updated credit scores

 

[lbass]

You’d better show me the formula that you used. Also, your raw data apparently does NOT look like you show, if you say there are no line returns.

-LB

 

[jchewsmith]

Formula I used:

numbervar i := 0;
numbervar j := ubound(split({Command.TaskHistoryXml},'<comment')); //notice no closing caret
stringvar display := "";

for i := 1 to j do(
if ubound(split({Command.TaskHistoryXml},'<comment')) >= i then //notice no closing caret
display := display + extractstring(split({Command.TaskHistoryXml},'<comment'),">","</comment>")+'<br>'); //notice no closing caret except on comment exit code
display

Here is the data when I copy it directly from SQL, I was pulling it from a field in Crystal before, so I am not sure about the line breaks:

<TaskHistory>
<Save When="11/19/2018 1:29 PM PT" By="User">
<ResolvedClosedAssignedTo To="User2" />
<Comment>Added SPF and updated credit scores</Comment>
<HTMLComment>Added SPF and updated credit scores</HTMLComment>
</Save>
<Save When="11/19/2018 9:31 AM PT" By="User2">
<OpenedAssigned To="User" />
<Comment>Social Security Number (SSN) for borrower John Doe has either been recently issued or has not been issued by the Social Security Administration.</Comment>
<HTMLComment>Social Security Number (SSN) for borrower John Doe has either been recently issued or has not been issued by the Social Security Administration.
&lt;br /&gt;
&lt;br /&gt;The Credit Score provided (743) for borrower (John Doe) is more than 20 points different from the representative Credit Score in DU.</HTMLComment>
</Save>
</TaskHistory>

 

[lbass]

I'm sorry, but when I try to use your code above as a source formula to test my formula, it displays in unexpected ways, so I am unable to troubleshoot this. If you want to send a report saved with data, I can take a further look, but can't promise I'll be able to uncover the issue without understanding your datasource. Maybe someone else who has a better understanding of HTML code can see what's going on. For one thing, it is unexpected that <br> doesn't create a return. You could try <p>, but that doesn't solve the larger issue. When I tested my formula here, it picked up multiple comments with a text field I created.

-LB