Data retrieval

[australia3]

In thread767-1097133 lbass helped me out with a formula which I was very grateful for.This one is a very similar one- can anyone help?

This is the text: -

05/04/2005 13:20 by Sarene Avanesians -- "Sev 5" (Initial Severity);05/04/2005 14:59 by Joanne Brown -- "Open" changed to "Resolved";22/04/2005 13:28 by Joanne Brown -- "Resolved" changed to "Confirmed";31/05/2005 16:50 by Sarene Avanesians -- "Confirmed

I want to retrieve the name of the person that preceeds ' -- "Open" changed to "Resolved"' and the date it was done in 2 different formulas, so for this example, the data I'm looking for is: -

05/04/2005
Joanne Brown

Any advice would be gratefully received!

 

[lbass]

Try:

//{@name}:
local stringvar array x := split({table.string},";");
local numbervar i;
stringvar name := "";

for i := 1 to ubound(x) do(
if instr(x,'"Open" changed to "Resolved"') > 0 then
name := mid(x, 21, instr(x," --")-21);
name

//{@date}:
local stringvar array x := split({table.string},";");
local numbervar i;
stringvar datex := "";

for i := 1 to ubound(x) do(
if instr(x,'"Open" changed to "Resolved"') > 0 then
datex := left(x, 10);
datex

-LB

 

[australia3]

The name one returns "True".Any ideas what I'm doing wrong? Do I have to change anything else in the formula apart from table.string?

 

[synapsevampire]

Post the formula you used, LB's looks OK.

If the text does not contain the specified text, then it should return a blank.

-k

 

[lbass]

Make sure your last line reads "name" (see above)

-LB

 

[australia3]

local stringvar array x := split({untitled.StatusHistory},";");
local numbervar i;
stringvar name := "";

for i := 1 to ubound(x) do(
if instr(x,'"Open" changed to "Resolved"') > 0 then
name := mid(x, 21, instr(x," --")-21);
name)

It wouldn't let me save without a bracket at the end.

 

[lbass]

Sorry. The paren belongs before the semicolon:

local stringvar array x := split({untitled.StatusHistory},";");
local numbervar i;
stringvar name := "";

for i := 1 to ubound(x) do(
if instr(x,'"Open" changed to "Resolved"') > 0 then
name := mid(x, 21, instr(x," --")-21));
name

Add the extra paren to the other formula, too.

-LB

 

[australia3]

lbass you are a legend. Thanks for your help. Works like a dream except for 3 small things.I know I'm being cheeky but I really appreciate your help:-

1. There appears to be a character limit when reporting from a text field in a notes database. Is there anyway to extend it?

2. If the text '"Open" changed to "Resolved"' appears twice, is there anyway to ensure it takes the latest one? (i.e. the one last in the field)

3. If I wanted to the same thing based on the text 'changed to "Resolved"'is that just changing the number from 21 to something else?

 

[lbass]

1-What CR version are you using? Did you get an error message? What was it?

2-The last one will automatically appear as the formula result, since the variable is set to pick up the name whenever it meets the criteria. If the last string component meets the criteria, the name in it will be the last one set to the variable.

3-No, the numbers are detecting where the name is, unrelated to the words in the criterion, so just remove the "Open" from within the quotes. The first clause will detect whether the string component contains 'changed to "Resolved"', while the second clause picks out the location of the name in the same component.

-LB

 

[australia3]

Currently using CR X1. I think it's a Notes ODBC thing. No error message, just no text after 254! Thanks for 2 and 3.

 

[lbass]

If you browse the field, does it show a string length of 254? I'm not sure, but if the field has a longer string length, you could try using a SQL expression to return segments of the string, as in:

substr(table.`text`,1,254)

Or,

{fn substring(table.`text`,1, 254)}

Then for the second substring, change the values to:

{fn substring(table.`text`,255, 254)}

The functions that work depend upon your datasource.

-LB

 

[ilses]

You can set the maximum number of characters to be returned for text fields and for rich text fields in the odbc connection: 254 is default - max for text is 15,360.
(Control Panel, Administrative Tools, ODBC, select DSN, click Configure). This then applies to all text fields.
I think you may incur a preformance hit.

Ilse