Extract data from memo field

[mart10]

I am using CR XI



I have data in a memo field

Group A:aaa,bbb,ccc;
Group B:xxx,yyy,zzz;

The data is always of format GroupX:,; ie starts with a : sperated by a , and ends with a ;

There maybe one or many pieces of data separated by a ,



I have a formula :

extractstring({table.field},"Group A:",";")


The above works at the Group level but what If I now want to go further and extract all positions in the data from one of the group rows

eg GroupB:123,289,uwu,628l,iii890dew;

I want to create several formula

eg GroupB1 would return data 123
GroupB2 would return data 289
etc

and say I wanted to extract the 2nd piece 289, how would I do that and if a 2nd piece of data did not exist would it return null?

 

[MCuthill]

mart10,

I have not used Array's before, but was able to piece together this solution from Help. Is is very rudimentary, but hopefully helps you get going.

{@ReturnElement1}

StringVar Array yourText;
StringVar result;

yourText:=Split({@text},",");

result:=yourText [1]

this will return the first item. In your example text, this would return "123".

To return the 2nd element ("289"), it would like this:
{@ReturnElement2}

StringVar Array yourText;
StringVar result;

yourText:=Split({@text},",");

result:=yourText [2]

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[mart10]

never used an array so bit puzzled

what is @text?

 

[MCuthill]

oops!!!

I'm sorry Mart10... {@Text} was the formula in my test report that contained "123,289,uwu,628l,iii890dew". Substitute your formula name that returns this portion of the string.

I have a formula :extractstring({table.field},"Group A:",";")

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[mart10]

Great, at first sight this seems to work a treat

 

[mart10]

Its not quite working when I setup formula for 'missing' data

So in example below I need to allow for 25 names in Group A, 15 Group B, 15 Group C and 5 Group D for instance,

Group Aaul Pindar, Gordon Hurst, Clare Waters, Simon Mayall,,,,,,,,,,,,,,,,,,,,,;
Group B:John Eary, Kristian Jewitt, Costas Demetriou,,,,,,,,,,,,;
Group C:,,,,,,,,,,,,,,;
Group D:,,,,;

I set up individual formula as you suggested eg Group A (5)

StringVar Array MandateText;
StringVar result;
MandateText:=Split({@GroupA},",");
result:=MandateText [5]


For Group A 1 to 4 it works great as there are names in the string but for the next one I get following msg:

' A subscript must be between 1 and the size of the array'

Bit baffled, if I put in an x in the number 5 position it still doesnt work, I have no idea what is going on and never used arrays before

 

[lbass]

StringVar Array MandateText := Split({@GroupA},",");
if ubound(MandateText) >= 5 then
MandateText[5]

Add the ubound() clause before the array element, testing the subscript number each time. It basically says: if there are at least 5 elements in the array, show me element 5.

-LB

 

[mart10]

wow, this has saved me - I luv u! Sos for posting elsewhere but was getting no response and my project needed the result like before I even started trying to think how to do this!!!