Pick out values in memo field 1

[daveinuk]

Hi All. I have one here that I think is a real diffucult one but im sure someone will be able to help. Below is a memo field I have that is a list of names contained in one field:-

CN=Gillian S Corner/OU=GBPTD/O=ABB;CN=Graham Uglow/OU=GBPTD/O=ABB;CN=Keith Corner/OU=GBPTD/O=ABB;CN=Mandy Fox/OU=GBPTD/O=ABB;CN=Rob Elsey/OU=GBPTD/O=ABB;CN=Shane T Paul/OU=GBPTD/O=ABB

Is there a way of extracting the names from the middle of this and splitting each one so it appears on its own line, for example I want it to end up looking something like this:-

Gillian S Corner
Graham Uglow
Keith Corner

etc.......

can anyone help?

Many thanks.

David.

 

[daveinuk]

Sorry I should have added im using CR10 and connecting to a lotus domino server. Thanks! David.

 

[lbass]

Use formulas like this:

//{@firstname}:
if ubound(split({table.memo},"=") >= 1 then
left(split({table.memo,"=")[1],instr(split({table.memo,"=")[1],"/")-1)

//{@secondname}:
if ubound(split({table.memo},"=") >= 2 then
left(split({table.memo,"=")[2],instr(split({table.memo,"=")[2],"/")-1)

-LB

 

[lbass]

PS. You would place these in separate detail sections_a,_b, etc.

-LB

 

[daveinuk]

Hi, thanks for the really quick reply! its not working for me. my table and field name is called
{Form__Group_Distribution_List_Access_Group_Access_List_Group.Members}

It keeps coming up with error after the first then saying 'a ")" is missing'

Any ideas?

Many thanks.

David.

 

[lbass]

Sorry, I did that too quickly. Try the following:

//{@firstname}:
if ubound(split({table.memo},";")) >= 1 then
extractstring(split({table.memo},";")[1],"=","/")

//{@secondname}:
if ubound(split({table.memo},";")) >= 2 then
extractstring(split({table.memo},";")[2],"=","/")

-LB

 

[daveinuk]

Hi Thanks for the reply again.

The first one is working superb, however the second one which I have placed in 'details B' is not working, the formula does not throw up an error it just does not print anything on the page. Any ideas?....

and am i correct in thinking that if I want 90 names then I would need 90 details areas?

Many thanks.

 

[lbass]

The formula above should work, as I tested it. If there is a blank it might be because there is no second section to the memo field.

If the list of names is for display purposes only, then you can use:

numbervar i;
numbervar j := ubound(split({table.memo},";"));
stringvar display;

for i := 1 to j do(
if ubound(split({table.memo},";")) >= i then
display := display + extractstring(split({table.memo},";"),"=","/")+chr(13));
display

Place this formula in the detail section and then right click on it->format field->common->check "Can Grow". This will return all values in the memo field separated by a return, but this list will appear all in one detail row. If you need them in separate rows, then you need separate formulas.

If you go the separate formula route, you should format each detail section to "suppress blank section" for those memos where there are fewer names.

-LB

 

[daveinuk]

Hi yet again!

Right what is happening is I have grouped the report by the list names....and what happens is at each grouping it adds one more name from the first list. any ideas?

 

[lbass]

Forgot to reset the variables--sorry. Change the formula to:

numbervar i := 0;
numbervar j := ubound(split({table.memo},";"));
stringvar display := "";

for i := 1 to j do(
if ubound(split({table.memo},";")) >= i then
display := display + extractstring(split({table.memo},";"),"=","/")+chr(13));
display

-LB

 

[daveinuk]

Hi. sorry to be a real pain but its now only giving me the first name in each list.... any ideas?

Many thanks.

David.

 

[lbass]

I think you forgot to format the formula to "can grow". The chr(13) is entering returns, so you have to format it this way to see the subsequent list members.

-LB

 

[daveinuk]

Hi lb, hope that you are ok and had a good christmas. I still have issues with this formula. I have checked and I do have 'can grow' ticked. At the bottom of my report it is telling me that only 4 records are coming back (which is the amount of groups I have told the report to look at) so it appears it is only returning the first value in each group. Any more ideas?

Thanks

David.

 

[lbass]

Please explain what you mean by:

I have grouped the report by the list names

What is the formula/field you are grouping by?

Also, please copy and paste the formula you are using to create the list of names into the thread.

-LB

 

[daveinuk]

Hi
I have created a grouping by each of the distribution list names, and I have told the report to look at 4 list names by using record selection and creating the formula:

{Form__Group_Distribution_List_Access_Group_Access_List_Group.ListName} in ["#Staffs PFI", "#GBPTD_Sleaford", "#GBPTD_Derby", "#GBPTD_Rugby"]



then in the details section i have created the formula:

numbervar i := 0;
numbervar j := ubound(split({Form__Group_Distribution_List_Access_Group_Access_List_Group.Members},";"));
stringvar display := "";

for i := 1 to j do(
if ubound(split({Form__Group_Distribution_List_Access_Group_Access_List_Group.Members},";")) >= i then
display := display + extractstring(split({Form__Group_Distribution_List_Access_Group_Access_List_Group.Members},";"),"=","/")+chr(13));
display


Any ideas?

Thanks.

 

[lbass]

Your formula tests out here. However, I only tested against the field you showed in your first post. Is there variation in the way that the field uses the dividing characters? Maybe show the several contents of the field (those not working above).

I don't know how the grouping relates to the records. Are you sure there should be multiple details per listname? If each row contains six names or so, couldn't that be the entire group?

-LB

 

[daveinuk]

right we are getting there now! I was using the wrong field so i have now corrected that and i am getting a list of names with a return which is brilliant, however it is only returning the first 20 names from each list even though there are more names in the list. I have checked that can grow is selected and can see no obvious faults.... any more ideas?

thanks

David.

 

[lbass]

Are you saying that there are more than 20 names in one detail field?

If you make the last line of the formula:

;
j

...what value appears? This would tell you the number of names in the record. Next, also try adding a last line of:

;
len(display)

...does a consistent value appear? What is it?

-LB

 

[daveinuk]

Hi.

Right when I add the

;
j

I have values of 28, 39, 6 and 18 for my four groups. When I add the next line of

;
len(display)

I have values of 404, 577, 76 and 264.

So that would suggest to me that in my groups i have 28, 39, 6 and 18 names in my groups.

Do we have a problem then if there are more than 20?...if that as far as the field 'can grow'?

Many, many thanks for all your help!

David.

 

[lbass]

In the first of your examples, only 20 names are displaying?

Please paste each of these instances into the thread so we can troubleshoot. I'll have to get back to you later today.

-LB