Select Data from Work Log

[Butlertl]

I have a work log/memo field that begins with

4/9/2009 3:46:26 PM terry.butler
Reportable changed to: No

I need to select all records that include the name xxxxxx.xxxxxx in the work log. I would provide about 30 names that should match for record selection......

What is the best way to accomplish this? Thank you for all suggestions

 

[lbass]

Create a parameter for the name {?Name}. Then create a formula {@memoname} like this:

stringvar array x;
stringvar array y;
if instr({table.memo}, chr(13) <> 0 then (
x := split({table.memo},chr(13));
y := split(x[1]," ")
);
y[ubound];

This assumes the name is always the last element in the first line. If the name can be missing, then it would be helpful to know if the name always contains a period between first and last names.

For a record selection formula, use:

not isnull({table.memo)) and
{@memoname} = {?Name}

-LB

 

[Butlertl]

The name is always the last element in the first line and the name cannot be missing....and the name always has a period between the first and last name.

I am getting an error on the formula with THEN.

 

[lbass]

Sorry--a missing paren:

stringvar array x;
stringvar array y;
if instr({table.memo}, chr(13)[red])[/red] <> 0 then (
x := split({table.memo},chr(13));
y := split(x[1]," ")
);
y[ubound];

-LB

 

[Butlertl]

Do I need the ELSE?

Using version 10

 

[Butlertl]

Also.....in the parameter....can I insert the 30 names and it will run the selection with intervention?

 

[lbass]

Not necessarily, but you could try this:

stringvar array x;
stringvar array y;
if instr({table.memo}, chr(13)) <> 0 then (
x := split({table.memo},chr(13));
y := split(x[1]," ");
) else
y := split({table.memo}," ");
y[ubound];

Didn't test this, but I think it would allow for cases where there is only one line in the memo.

-LB

 

[Butlertl]

The formula saves now without error.

Last question, I hope, I created the parameter (?Name)
Value type: String
Discreate value
Allow multiple values

then clicked on Default values and added the names as they should be in the work log. Is this correct?

And I really want to thank you for your advice and help.

 

[lbass]

As long as you entered them in the format firstname.lastname all in lower case, and assuming the name is always lower case in the memo field.

-LB

 

[Butlertl]

Got it....tested two names and the reprot isn't finding them....will do a little research

 

[Butlertl]

New error

y[ubound];

is producing "A subscript must be between 1 and the size of the array"

 

[lbass]

Did you add:

not isnull({table.memo))

...to the record selection formula? If so, then maybe you also need to change the record selection to:

not isnull({table.memo}) and
trim({table.memo}) <> "" and//etc.

Otherwise I think the formula should work. Please copy your actual formula into the post.

-LB

 

[Butlertl]

THis is my select statement

{OAO_HDSP_Support.Create Date} in {?Date Range} and
not isnull({OAO_HDSP_Support.Summary}) and
{@memoname} = {?Name} and
not ({OAO_HDSP_Support.Status} in ["Cancelled"])

Terry

 

[Butlertl]

This is my memoname formula

stringvar array x;
stringvar array y;
if instr({OAO_HDSP_Support.Summary}, chr(10)) <> 0 then (
x := split({OAO_HDSP_Support.Summary},chr(10));
y := split(x[1]," ")
)else
y := split({OAO_HDSP_Support.Summary}," ");
y[ubound];

 

[lbass]

Please do a check by changing your formula to:

OAO_HDSP_Support.Create Date} in {?Date Range} and
not isnull({OAO_HDSP_Support.Summary}) and
not ({OAO_HDSP_Support.Status} in ["Cancelled"]) and
instr({OAO_HDSP_Support.Summary}," ") = 0

This will test whether the summary ever contains no space.

-LB

 

[Butlertl]

I get data with the test.....could the issue be now the list I created with the NAME parameter field?

 

[lbass]

What does the data look like that is returned? Does it contain the desired name field? Please show some samples. This means that splitting the array based on spaces doesn't work in some cases.

-LB

 

[Butlertl]

One problem...I was using the wrong field....this is my memoname formula:

stringvar array x;
stringvar array y;
if instr({OAO_HDSP_Support.Work Log}, chr(13)) <> 0 then (
x := split({OAO_HDSP_Support.Work Log},chr(13));
y := split(x[1]," ");
) else
y := split({OAO_HDSP_Support.Work Log}," ");
y[ubound];

My select state ment is:

{OAO_HDSP_Support.Create Date} in {?Date Range} and
not isnull({OAO_HDSP_Support.Work Log}) and
instr({OAO_HDSP_Support.Work Log}," ") = 0 and
not ({OAO_HDSP_Support.Status} in ["Cancelled"])

When the report runs.....this line is highlighted
y := split({OAO_HDSP_Support.Work Log}," ");

and an error stating "An Array's dimension must be an integer between 1 and 1000

 

[lbass]

Comment out the variable formula or remove it from the report and then see what is displayed on the report for the memo field when you use the above selection formula. Then provide samples of what is returned.

-LB

 

[Butlertl]

Removed memoname from the reprot and will leave the select statement unchanged