Find instance multiple times within a memo field 1

[Jillp]

We have a memo field that contains a variety of information.

For instance; Job Released No-->Yes, Date01: 01/01/01 --> 02/01/01, Req. Date: 03/01/01 --> 04/01/01, Req. Date: 04/01/01 --> 05/01/01

We need to find all instances of "Req. Date". How can I do a loop or next?

So far I have found the length of the field and the instr count where the first instance is located but I cannot figure how to look for the next instance?

Any tips would be greatly appreciated.

Jill

 

[lbass]

Set the formula up like this:

stringvar array x := split({table.memo},"Req. Date: ");
stringvar result := "";
numbervar i;
numbervar j := ubound(x);
for i := 2 to j do(
result := result + left(x,21)+", "
);
left(result,len(result)-2)

This assumes you want a display like this:
03/01/01 --> 04/01/01, 04/01/01 --> 05/01/01

-LB

 

[Jillp]

Thanks for responding LB,

I am not going to pretend I understand your formula. I do lots with crystal and formulas but very little with variables.

I receive the following message when I set up my formula.
"String Length is less than 0 or is not an integer."

Some of my memo fields have only 1 instance of the Req. By date change, others may have 3, and this example had 2. Could this be the problem?

Thanks again for your help,

Jill

 

[lbass]

The problem would be if there was no instance of Req Date and the array consists of one element--the entire memo field. So try changing the last line of the formula to:

if ubound(x) = 1 then
"No Req Date" else
left(result,len(result)-2);

Note also that if the memo field itself can be null, the formula would be null, too.

-LB

 

[Jillp]

I'll give it a try. I have already eliminated any memo field that does not have a "Reqd. By:" comment with the selection formula; and only records with comments are on the report so.....

I'll give this a try.

thanks for your help,

Jill

 

[lbass]

Hmm. Your sample did not include "Reqd. By:" Or is the comment a separate field? You would only get the error you got it the "Req. Date:" was not present in the memo. The other thing you should check is whether it is entered inconsistently, e.g., whether the spelling, case, punctuation or spaces vary from instance to instance.

-LB

 

[Jillp]

Yes -- I did pick up on the Req'd by problem and fixed that. The data is added automatically by the database so the words are consistent.

Once I got the Req'd by thing straightened out I began seeing the results I was hoping for. Now I need to find a way to extract some of those dates.

Thank you so much for your help, I never would have gotten through this without your help.

thanks again,

Jill

 

[lbass]

So is this resolved, or do you need more assistance?

-LB

 

[Jillp]

I think my approach is wrong, but cannot come up with another.

I need to look at all these 'reqd by:' changes and analyze both the start date and the end date, if there is more than one instance of the 'reqd by:' change then I only need to analyze the last change.

Any thoughts?

Thanks,

Jill

 

[lbass]

Can you please show a sample of a string that holds multiple Req'd by's and then show what you want to see as your formula result? I don't know what you mean by "analyze" or whether you need to see the start and end dates separately or what.

-LB

 

[Jillp]

I need look at the original Req. By Date.

In the example below it would be the first date on the 15:37:02 line, or 01/31/08.

If the date change moves the record into a different month then we need to select this record.

In the example on the first 'reqd by:' change we would not select the record but on the second line 15:37:18 the date is moved out to 02/15/09. After the third change, however, the date is pulled back to January, so, in the end, I would not select this record.

KThor 15:36:48 JobReleased: yes -> no
KThor 15:37:02 Req. By: 01/31/08 -> 01/30/09
KThor 15:37:18 Req. By: 01/30/09 -> 02/15/09
KThor 15:37:19 JobReleased: no -> yes

jlaplant 11:28:17 JobReleased: yes -> no

jlaplant 11:28:28 JobReleased: no -> yes

KThor 14:49:09 Req. By: 02/15/09 -> 01/30/09

Thanks,

Jill

 

[lbass]

Now I am thoroughly confused. Each "line" you mention above is from a different detail record, correct? Are you comparing these records within some group? What is it? When you refer to "selecting" this record, do you mean the entire group? Maybe it would be easier to follow if you explained the purpose of the report.

-LB

 

[Jillp]

We have a job backlog report that prints once per month for the next 6 weeks. During the month our planner will move the due date of the job several times. What we need to capture is any job that was on the report this month but has been moved outside of the 6 week time-frame. In other words, we need a list of jobs that have fallen off the backlog.

What I have available to gather the information is a 'change log'. The change log not only captures due date changes, but many other changes as well. Your formula helped me to narrow down all dates associated with the "Reqd By:" comment, but the dates are still text and are not searchable.

I would like to look at the starting "reqd by:" date and the ending "reqd by:" date. If the starting date was in my backlog and the ending date moved it outside of the 6 week range then I need to know.

thanks,

Jill

 

[scottostanek]

stringvar array x := split({table.memo},"Req. By: ");
stringvar dtstart := "";
stringvar dtend := "";

numbervar i;
numbervar j := ubound(x);
for i := 2 to j do( //2 because we don't care what came b4
if dtstart ="" then dtstart := left(x,8);
dtend := right(left(x,21),8)
);
//dtstart only fills first time encountered
//dtend fills every time but only last one kept at end
//dtend:left gives "02/15/09 -> 01/30/09"
//dtend:right gives "01/30/09"

Then Compare dtstart to your backlog range and dtend to 6 weeks plus. Comments added for a little clarity.

This uses LB's method modified slightly based on the thread.

Scotto the Unwise

 

[lbass]

Please clarify whether these dates are being evaluated only within one record (in which case Scotto's suggestion should work) or whether they occur across a set of records for a job group (in which case it needs to be modified).

-LB

 

[Jillp]

In answer to your question LB, yes the dates may be in more than one group, since the group is first by job, second by date.

The result I received from Scotto's suggestion was 'true' which really threw me off, since I was expecting a date. At this point I realized that I was in over my head. I have contacted an outside firm who is coming in to help me out. I appreciate the information you both provided. It has proved helpful to my consultant.

Thank you once again.

Best Regards,

Jill Pleau