Extracting values after specific words 1

[deangels3]

Hi, we are using CR XI and our audit table holds old and new values that has been changed, deleted and added. We need to show which data has been changed/deleted/added under each heading. The data is stored as a long string and it looks something like this:

<Value label="OpeningDate"><OldValue /><NewValue>15/11/2010</NewValue><Value label="Cert Number"><OldValue>44369</OldValue><NewValue /><Value label="Country"><OldValue>UK</OldValue><NewValue />

The heading will always be after <Value label= and if there is no existing or new value, there will be a space in <OldValue /> or <NewValue />.

We would like to show the above example as:
OpeningDate
From: (Not set)
To: 15/11/2010
Cert Number
From: 44369
To: (Not set)
Country
From: UK
To: (Not set)

Any advise on how can we achieve this please ?

Thank you.

 

[lbass]

Try this formula to replace your field:

stringvar x := {table.field};
stringvar z := '';
numbervar i;
stringvar array a;
stringvar array b;
stringvar array c;

for i := 2 to ubound do(
redim preserve a;
redim preserve b;
redim preserve c;

a := '<b>'+left(y,instr(y,'">')-1)+'</b>'+'<br>';

b := if " " in extractstring(y,'<OldValue','</') then
"From: (Not Set)" +'<br>'else
"From: "+extractstring(y,'<OldValue>','</OldValue>')+'<br>';

c:= if " " in extractstring(y,'<NewValue','/>') then
"To: (Not Set)" +'<br>'else
"To: "+extractstring(y,'<NewValue>','</NewValue>')+'<br>';

z := z + a+b+c;
);
z

Replace {table.field} with your field and then format the formula by going to paragraph tab->text interpretation->HTML text.

-LB

 

[deangels3]

Hi lbass

Thank you so much for your reply. I replaced the {table.field} with my field, however the y in the 7th line is being highlighted and a message saying 'The ) is missing'.

Thank you.
LG

 

[IanWaterman]

Looks like a typo y should be X, just change first line

stringvar y := {table.field};

Ian

 

[deangels3]

Hi Ian

I tried that and the y in the 7th line is still being highlighted with a message: An array is required here.

Thank you.

LG

 

[lbass]

Sorry--when I copied and pasted and removed my sample field, I accidentally removed the line that declares the stringvar array y. You just need to add in the second line--no other changes are necessary.

stringvar x := {table.field};
stringvar array y := split(x,'Value label="');
stringvar z := '';
numbervar i;
stringvar array a;
stringvar array b;
stringvar array c;

for i := 2 to ubound do(
redim preserve a;
redim preserve b;
redim preserve c;

a := '<b>'+left(y,instr(y,'">')-1)+'</b>'+'<br>';

b := if " " in extractstring(y,'<OldValue','</') then
"From: (Not Set)" +'<br>'else
"From: "+extractstring(y,'<OldValue>','</OldValue>')+'<br>';

c:= if " " in extractstring(y,'<NewValue','/>') then
"To: (Not Set)" +'<br>'else
"To: "+extractstring(y,'<NewValue>','</NewValue>')+'<br>';

z := z + a+b+c;
);
z

-LB

 

[deangels3]

It worked! You're a super star LB ! Thank you so much

 

[deangels3]

Hi LB

Sorry to bother you again. What should I do if I have the below:

<value type="simple" label="Service Reg Hist ID">7486</value><value type="simple" label="Initial Contact Date">09/04/2018</value><value type="simple" label="Start Date">09/04/2018</value>

and I would like it to appear as:
Service Reg Hist ID
7486
Initial Contact Date
09/04/2018
Start Date
09/04/2018

I tried to use the solution which you have provided to me to show these but I am making a big mess of it

Thank you.

LG

 

[lbass]

This is a different field, correct? Are the components of this field always populated or do you need to take into account spaces representing missing data?

-LB

 

[deangels3]

Hi LB

Thank you for your speedy reply. if there is no data, then it will be:

<value type="simple" label="Application Type" /><value type="simple" label="Application Status" />

and the output will be just:
Application Type
Application Status

Thank you.

LG

 

[lbass]

In your latest examples, you are showing spaces in multiple places, not just to indicate missing data. Is this actually the case or did autocorrect insert spaces? Also there don't appear to be initial codes for <value> anywhere, just closing codes. Is this actually the case?

-LB

 

[lbass]

Try this:

stringvar p := {table.differentfield};
stringvar array q := split(p,'value type="simple" label="');
stringvar r := '';
numbervar k;

stringvar array d := '';
stringvar array e := '';
stringvar array f := '';

for k := 2 to ubound(q)
do(
redim preserve d[k];
redim preserve e[k];

d[k] := '<b>'+ (if not(" />" in q[k]) then
left(q[k],instr(q[k],'">')-1) +'</b>'+'<br>' else
left(q[k],instr(q[k],'" />')-1))+'</b>';

e[k] := if " " in extractstring(q[k],'"','</value>') then
" " +'<br>'else
extractstring(q[k],'>','</value>')+'<br>';
r := r + d[k]+e[k]
);
r;

PS. I think you set the arrays a, b, c to '' in the other formula, too.

-LB

 

[deangels3]

Hi LB

<value type="simple" label="Application Type" /><value type="simple" label="Application Status" />
If there is no value in the particular heading, ie Application Type or Application Status, it will just be a blank space after the heading followed by a /.

With your latest solution, I am getting a 'string length is less than 0 or not an integer' and left(q[k],instr(q[k],'" />')-1) is highlighted.

Thank you.

LG

 

[lbass]

I tested this with your sample data and it worked, so please provide the data that you were using when this formula failed. I can only assume that your data is consistent with the samples you provide.

-LB

 

[lbass]

Also, please copy your actual formula into this post.

-LB

 

[deangels3]

Hi LB

The raw data is as follows:

<value type="simple" label="Service Reg Hist ID">7207</value><value type="simple" label="ServiceID">f30dba55-f45c</value><value type="simple" label="End Date">27/03/2017</value><value type="simple" label="Registration Type">AS Directory</value><value type="simple label="Initial Contact Date">27/03/2017</value><value type="simple" label="Info Pack Date">27/03/2017</value><value type="simple" label="Amendment Reason" /><value type="simple" label="Area" /><value type="simple" label="Locality" /><value type="simple" label="Last Updated">27/03/2017</value><value type="simple" label="Updated By">LHARRIS</value><value type="simple" label="Application Type">-</value>

The output will be:
Service Reg Hist ID
7207
ServiceID
f30dba55-f45c
End Date
27/03/2017
Registration type
AS Directory
Initial Contact Date
27/03/2017
Info Pack Date
27/03/2017
Amendment Reason
Area
Locality
Last Updated
27/03/2017
Updated By
LHARRIS
Application Type
-

As for the formula, I was using the one which you have kindly provided on 01 May 2018 19:28:
stringvar p := {CS_AUDIT_LOG.VALUE};
stringvar array q := split(p,'value type="simple" label="');
stringvar r := '';
numbervar k;

stringvar array d := '';
stringvar array e := '';
stringvar array f := '';

for k := 2 to ubound(q)
do(
redim preserve d[k];
redim preserve e[k];

d[k] := '<b>'+ (if not(" />" in q[k]) then
left(q[k],instr(q[k],'">')-1) +'</b>'+'<br>' else
left(q[k],instr(q[k],'" />')-1))+'</b>';

e[k] := if " " in extractstring(q[k],'"','</value>') then
" " +'<br>'else
extractstring(q[k],'>','</value>')+'<br>';
r := r + d[k]+e[k]
);
r;

Thank you.

LG

 

[lbass]

Change this line:
e[k] := if " " in extractstring(q[k],'"','</value>') then

to this:
e[k] := if " />" in extractstring(q[k],'"','</value>') then

Both using the first version and the second still produced results, but the first omitted any actual value like 'AS Directory' that included a space. The error you are getting is probably because you have a null or empty value for {CS_AUDIT_LOG.Value} somewhere. Try changing the first line of the formula to:

stringvar p := if isnull({CS_AUDIT_LOG.VALUE}) or
trim(({CS_AUDIT_LOG.VALUE})='' then
'' else
({CS_AUDIT_LOG.VALUE});

I can't test whether this addresses the issue here since I don't have real data to work with, so please report back after making these two changes to the formula.

-LB

 

[deangels3]

You really are a super star LB! It has done the trick!

After I made the changes as you have suggested, I viewed the result and it was just 1 long string. After a long while of trying to figure out what was wrong, I realised that I have not change the Text Interpretation to HTML Text !



Have a good day.

LG