Filtering a Multiline Text field in crystal reports 1

[saran26]

Hi All,

We have a multiline text field with various information such as date,action,username etc.

Is it possible to filter based on the date , and display only the values which matches the date provided ??

Example -

Multiline text in the database contains,

2/11/2008 modify saran
2/21/2008 delete sam
2/11/2008 add subha

If I specify 2/11/2008 as filter to the query, it should display only two rows from the multiline text field.

2/11/2008 add subha
2/11/2008 modify saran

Please help me.

Thanks
Saran

 

[lbass]

Try this:

stringvar array x := split({@text},chr(13));
numbervar i;
numbervar j := ubound(x);
stringvar z;

for i := 1 to j do(
if split(x," ")[1] = {?stringdate} then
z := z + x+chr(13)
);
z

Format the formula to "can grow."

-LB

 

[saran26]

Hi LB,

Thanks. I tired but I am getting few errors which I am not able to understand it, so I am posting it again.

I have two fields. One is the audit_trail_text and the other field is history_expired_timestamp, When the report is run the value of history_expired_timestamp is captured and based on that value , audit_trail_text should be filtered.


audit_trail_text contains text like this:

====START====
Time : 2008-05-27 14:31:02 -05:00
Schema Rev : 39
User Name : Santosh Kale
User Login : skale
User Groups : Dev CMTeam
Action : Modify
State : Open
==Fields==
Note_Entry (0:116)
This requirement is flagged as Suspect due to Change in Associated Business Requirement Id: CQTS900000412 & Tag:BUS2

====END====

if history_expired_timestamp value = 5/21/2008 , the report should not display any thing , but if the history_expired_timestamp = 5/27/2008 then it should display the above record.

Can you please help me out if it is possible.? I tried your formula in the can grow field of audit_trail_text but it is giving me lot of errors.

Please help me out.

Thanks
Saran

 

[lbass]

It didn't work because your data is nothing like the sample you showed in your original post. I cannot tell what you are showing in your second post. Is from Start to End one record or multiple records? If this is one record, is the date always in the first line?

HOW is the value from the history_expired_timestamp captured? Is it entered as a parameter value?

-LB

 

[saran26]

First for sorry for confusing you! I am a newbie to crystal reports. Basically I am clearquest administrator now I am forced todo some crystal reporting, since reports in ClearQuest are built using Crystal reports.
OK coming to the question again.

1)
The history_action_time stamp is a query field , I
can include that as a report field if it is necessary.

2) Start and End is a portion of a record with date always in the first line.

Every portion will have start and end with the date at the first line.

My requirement is to list only those which matched to the date provided by the history_action_time stamp.

Let me know if you need more details.

Thanks
Saran

 

[lbass]

There is no such thing as a "portion" of a record. A record is a technical term meaning one row in the report. If you add the special field "recordnumber" to the report, it would increment once per record. Add recordnumber and the text field to the detail section to see what belongs to one record. Please show a sample that is for one (entire) record only.

-LB

 

[saran26]

Hi LB,

It is a memo field.

Here is the values it contains. How can query the following field for Time: 2008-05-30 08:54:26 -05:00 - that is what I want to achieve it.

Thanks
Saran

====START====
Time : 2008-05-30 08:54:26 -05:00
Schema Rev : 41
User Name : Santosh Kale
User Login : skale
User Groups : Dev CMTeam
Action : Modify
State : Coded
==Fields==
Description (80:101)
Old : The Security Definition Request message must define a valid combination of legs.
New : The Security Definition Request message must define a valid combination of legs.

====END====

====START====
Time : 2008-05-15 17:27:55 -05:00
Schema Rev : 27
User Name : CQ Script
User Login : Script
User Groups : Admin
Action : Modify
State : Coded
==Fields==
RequirementProject (0:6)
Old :
New : Falcon
RequirementVersion (0:3)
Old :
New : 3.0

====END====

====START====
Time : 2008-04-26 10:51:50 -05:00
Schema Rev : 16
User Name : CQ Script
User Login : Script
User Groups : Admin
Action : Coded
State : Coded
==Fields==
State (4:5)
Old : Open
New : Coded

====END====

====START====
Time : 2008-04-26 09:27:41 -05:00
Schema Rev : 15
User Name : CQ Script
User Login : Script
User Groups : Admin
Action : Modify
State : Open
==Fields==
EnhancementRequests (0:13)
Old :
New : CQTS900000288

====END====

====START====
Time : 2008-04-26 07:51:59 -05:00
Schema Rev : 14
User Name : CQ Script
User Login : Script
User Groups : Admin
Action : Modify
State : Open
==Fields==
ChildRequirements (0:13)
Old :
New : CQTS900000244

====END====

====START====
Time : 2008-04-26 07:49:10 -05:00
Schema Rev : 14
User Name : CQ Script
User Login : Script
User Groups : Admin
Action : Modify
State : Open
==Fields==

====END====

====START====
Time : 2008-04-26 07:44:59 -05:00
Schema Rev : 14
User Name : CQ Script
User Login : Script
User Groups : Admin
Action : Submit
State : Open
==Fields==
Author2 (8)
bwitteki
Counter_Tag (15)
FUNC2.1.1.99.10
CQProject (14)
ETS_FALCON 3.0
Description (80)
The Security Definition Request message must define a valid combination of legs.
Name1 (50)
The Security Definition Request message must defin
ParentRequirement (13)
CQTS900000051
ratl_mastership (7)
<local>
ReqModFlag (8)
33558006
State (4)
Open
Tag (12)
FUNC2.1.1.10
Tag_Name (18)
- FUNC2.1.1.10
VersionName (3)
3.0

====END====

 

[lbass]

So you are showing 5 records here? If so, then you should be able to create a date time parameter {?datetime} and then use a selection formula like this:

datetime(left(trim(split({table.text},":")[2]),19)) = {?datetime}

I don't know what the extension "-5:00" represents, but this formula ignores that.

If you really are showing one record here (as requested), the solution would be different.

-LB

 

[saran26]

Hi LB,

Thanks, The above is a single record. Can you give me the solution for that too.

I really appreciate your help.

Thanks
Saran

 

[lbass]

Try this:

whileprintingrecords;
stringvar array memoseg := split({@memo},"START");
numbervar i;
numbervar j := ubound(memoseg);
stringvar array timex;
stringvar y;

for i := 2 to j do(
if trim(mid(memoseg, instr(memoseg,"Time")+20,10)) = totext({?date},"yyyy-MM-dd")
then
y := y + "====START===="+chr(13) + mid(memoseg,5)+chr(13) else
y := y
);
y

Substitute your field name for {@memo}.

-LB

 

[saran26]

Hi LB,

Thanks again.I am using the following in a formula field called Text and displayed the @Text in the report.

But nothing is getting displayed.

Note I replaced @memo with a report field ClearQuest.audit_trail_text and ?date with a report field ClearQuest.history_action_timestamp - Please let me know why

whileprintingrecords;
stringvar array memoseg := split({ClearQuest.audit_trail_text},"START");
numbervar i;
numbervar j := ubound(memoseg);
stringvar array timex;
stringvar y;

for i := 2 to j do(
if trim(mid(memoseg, instr(memoseg,"Time")+20,10)) = totext({ClearQuest.history_action_timestamp},"yyyy-MM-dd")
then
y := y + "====START===="+chr(13) + mid(memoseg,5)+chr(13) else
y := y
);
y

 

[lbass]

You will have to do some testing. At the end of the formula, instead of "y", use:

trim(mid(memoseg, instr(memoseg,"Time")+20,10))

Replace the "i" with the number of the segment (start->end) containing the matching date for a particular sample, except add one. So if the match is in the fourth start-end segment, use a 5. Then report back with the result.

-LB

 

[saran26]

Hi LB,

I replaced i with 2 and obtained the following:

whileprintingrecords;
stringvar array memoseg := split({ClearQuest.audit_trail_text},"START");
numbervar i;
numbervar j := ubound(memoseg);
stringvar array timex;
stringvar y;

for i := 2 to j do(
if trim(mid(memoseg, instr(memoseg,"Time")+20,10)) = totext({ClearQuest.history_action_timestamp},"yyyy-MM-dd")
then
y := y + "====START===="+chr(13) + mid(memoseg,5)+chr(13) else
y := y
);
trim(mid(memoseg[2], instr(memoseg[2],"Time")+20,10))



******************************************************

8 05 30 08 5/30/2008 8:54:26AM FuntionalRequirement CQTS900000052

8 05 30 13
5/30/2008 1:16:08PM FunctionalRequirement CQTS900000053

8 05 30 13
5/30/2008 1:17:11PM FunctionalRequirement CQTS900000053

8 06 03 08
5/30/2008 9:05:39AM FunctionalRequirement CQTS900000058

8 05 30 09
5/30/2008 9:01:17AM FunctionalRequirement CQTS900000063

8 05 30 08
5/30/2008 8:50:12AM FunctionalRequirement CQTS900000065

8 05 30 13
5/30/2008 1:20:40PM FunctionalRequirement CQTS900000077

8 05 30 13

 

[saran26]

Sorry note the output is

8-05-30 08

 

[lbass]

Okay, it looks like you need to change the formula to:

whileprintingrecords;
stringvar array memoseg := split({ClearQuest.audit_trail_text},"START");
numbervar i;
numbervar j := ubound(memoseg);
stringvar array timex;
stringvar y [red]:= ""[/red];

for i := 2 to j do(
if trim(mid(memoseg, instr(memoseg,"Time")+[red]17[/red],10)) = totext({ClearQuest.history_action_timestamp},"yyyy-MM-dd")
then
y := y + "====START===="+chr(13) + mid(memoseg,5)+chr(13) else
y := y
);
y

-LB

 

[saran26]

Hi LB, Thanks for your help again.

The following line only displayed

====START====

any more changes ?

Thanks
Saran

 

[saran26]

Hi LB,

When I pass value 2 to i, to the following I am getting

trim(mid(memoseg[2], instr(memoseg[2],"Time")+17,10))

I am getting the correct value 2008-05-30 and also for
totext({ClearQuest.history_action_timestamp},"yyyy-MM-dd") as 2008-05-30 , but instead of printing the entire value for the match it prints ====START==== , do you know why?

Please help me out. Thanks

Saran

 

[lbass]

Did you format the formula to "can grow"?

-LB

 

[saran26]

Hi LB,

Thanks It worked well with one issue. For some date's the first page of the report is always empty . How to format the report page , so that it displays 5 or 6 records per page.

Thanks again.

Saran

 

[saran26]

Hi LB,

One more question,

If the date is with in a range , what changes I have to make on the formula ?

Please let me know.

Thanks

Saran