DATA EXTRACT

[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 , and I have to create a template for users showing a varrying number of commas at each group level

I have a formula :

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

QUESTION - How can I supress all the commas after the last name only?

 

[briangriffin]

left(extractstring({table.field},"Group A:",";"),len(extractstring({table.field},"Group A:",";") - 1)

 

[lbass]

But there IS no final comma. Extractstring should return:

aaa,bbb,ccc

-LB

 

[briangriffin]

I was wondering why there would be one at the end, but didn't test...

 

[mart10]

I should have said there is a final comma and then a final semi colon. This is because the user has to populate a standard piece of text in a memo field (to help them fill it out correctly it was thought this the only and best way to do this)

eg

GroupA:xxxx,yyyy,tttt uuuu,,,,,,,,,,,;
GroupB:rrr,,,,,,,;
etc

So I only want to extract for instance xxxx,yyyy,tttt uuuu

 

[lbass]

Can we go back to the beginning here? In ONE detail row are you saying we would see ONE memo field with multiple entries starting with "Group"? How are the commas added in the first place? Is there some formula? Or is this a template with commas prepopulating it?

-LB

 

[mart10]

Sorry LB it is as my last post tried to say a template of commasa prepoulating it ready for the user to place test inbetween the commas. So the memo field actually contains this before a user prepopulates:

Group A:,,,,,,,,,,,,,,,,,,,,,,,,;
Group B:,,,,,,,,,,,,,,;
Group C:,,,,,,,,,,,,,,;
Group D:,,,,;

As per a previous ? I use an array to extract each name into a field but as this post says I also want to extract one line but exclude all commas after the last name entered on each group. Is this OK? I did try briangriffin's formula but it didnt like the syntax

 

[lbass]

stringvar y := extractstring({table.memo},"Group A:",";");
stringvar z := "";
numbervar i;
numbervar j := len;
for i := 1 to j do(
if y = "," and
y[i+1] = "," then
z := z else
z := z + y
);
left(z,len(z)-1)

-LB

 

[mart10]

Thankyou LB you are a 5*, this works a treat although not sure how it does it!

 

[mart10]

The only outstanding issue really is that the fomula fails if the template is not added when originally saving the static data.

The failure is 'string length is less than 0 or not an integer' on this part of the formula

left(z,len(z)-1)


Sometimes they may forget to add the template in, for later polulation as they obtain the data. Is there anything we can do in the formula to cater for this?

 

[lbass]

Replace the last line with:

if len(z)>1 then
left(z,len(z)-1)

-LB