Skip to Content
0
Aug 08, 2023 at 04:13 PM

I need to parse date out of multi-line notes field

59 Views

Hello all,

I am using CR 2016.

The SQL table I am working with contains service data relating to the repair of consumer electronics.

I have a notes field {logmemos.stat_his} that contains a list of date/time stamped events separated by carriage returns. The number of events in each record is not consistent. I am looking to parse the date out of a specific line in this note field. Here is an example of the data that is found in this field:

7/20/2023 (7:01 AM) - SERVICE BENCH -> FORCED DISPATCH
7/20/2023 (7:01 AM) - SERVICE BENCH -> IN PROCESS ASSIGNED TO BOX
8/4/2023 (10:19 AM) - NB -> IN PROCESS ASSIGNED TO DEP
8/4/2023 (10:53 AM) - DTR -> IN PROCESS ASSIGNED TO ALM
8/4/2023 (1:45 PM) - ARF -> PARTS REMOVED FROM WORK ORDER

8/4/2023 (1:45 PM) - ARF -> PARTS WERE RETURNED TO INVENTORY BY ARF

I am specifically looking for the line where the the claim is assigned to the technician {log.tech}.

This particular record is assigned to technician ALM, so I am looking to parse the date in the fourth line down.

I have been able to split the field around the carriage returns into an array, then I can select the fourth element of the array with:

stringvar array a;a := split({logmemos.Stat_His},chr(10));a[4];

but this only works if the assignment line is the fourth event for the claim, and the function fails if the record has fewer than four lines in it..

What I need to do is select the first element of the array that contains the string:

("IN PROCESS ASSIGNED TO " & {log.tech})

I have not been able to find a way to search for a string within elements of an array.

Once I can separate that one element, I should be able to parse the date out from there.

Best regards,

David