Extract data from memo field when position and length are variable?

[cfsutter]

I've read through quite a lot on this forum regarding parsing memo fields and have been about 99% successful in accomplishing what I need to do, but I have run into a road block. I feel like the answer is probably obvious, but I am just not seeing it. I am working with a Remedy Help Desk system and am trying to pull one line out of the audit log. The data in the audit log is variable so what I want is not always in the same place and it's not always the same length. When the data is in the log it does always start with the same field name followed by the variable dept name.

This is what I am currently using to parse out the Assigned Group from the audit log, it works great unless the Assigned Group is the last entry in the audit log. I understand why the issue occurs, I'm just stumped on how to fix it.

whileprintingrecords;
numbervar v_start;
numbervar v_end;

v_start := instr({HPD_HelpDesk_AuditLogSystem.Log},"Assigned Group:");
v_end := instr(mid({HPD_HelpDesk_AuditLogSystem.Log}, v_start + 7),chr(10));

if {HPD_HelpDesk_AuditLogSystem.Log} like ["*Assigned Group:*"] then mid({HPD_HelpDesk_AuditLogSystem.Log},v_start,(6 + v_end)) else ""


Here's a sample of the data. The first two samples work perfectly with the formula as it is, the last one does not and is the problem I'm trying to solve:

Status: Assigned (1)
Product Categorization Tier 1: Hardware
Product Categorization Tier 2: Printer
Product Categorization Tier 3: Network
Product Name: Printer - EH
Manufacturer: Hewlett Packard
Last Name: Lay
First Name: Chris
Categorization Tier 1: Failure
Categorization Tier 2: Printing
Urgency: 2-High (2000)
Impact: 3-Moderate/Limited (3000)
Priority: High (1)
Priority Weight: 18
Reported Date: 9/1/2013 4:51:01 AM
Assigned Support Company: ABC Company
Assigned Group: Control Center
Assigned Support Organization: ABC
Owner Support Company: ABC Company
Owner Group: Control Center
Owner Support Organization: ABC
Owner:
Responded Date: 9/1/2013 4:51:01 AM
Status History:
Product Model/Version:
Categorization Tier 3:
Resolution:
Assignee:
Last Resolved Date:
Generic Categorization Tier 1:
Generic Categorization Tier 2:
Generic Categorization Tier 3:
Vendor Contact:
Incident Association Type:
Original Incident Number:
Reported to Vendor:
Patch Last Build ID:
Infrastructure Chg Initiated:
Category:
Reproduceable Flag:
Resolution Category:
Resolution Method:
Resolution Category Tier 2:
Resolution Category Tier 3:
Closure Product Category Tier1:
Closure Product Category Tier2:
Closure Product Category Tier3:
Closure Product Name:
Closure Product Model/Version:
Closure Manufacturer:
Closure_Source:
Satisfaction Rating:
Estimated Resolution Date:
Required Resolution DateTime:
Direct Contact Last Name:
Direct Contact First Name:
Group_Manager_Site_Group:



Assigned Support Organization: Palo Verdes
Assigned Group: PV IT Desktop
Assignee: Ricky Robin
Status: Assigned (1)



Assigned Support Organization: EB
Assigned Group: Desktop - AMC


I'd appreciate any help that you can give on how to move past this last hurdle with the data parse.

Thanks!

 

[fisheromacse]

maybe change your end position formula to something like this:

//{@EndPosition}
IF
instr(mid({HPD_HelpDesk_AuditLogSystem.Log}, v_start),chr(10)) = TRUE
then
v_end := instr(mid({HPD_HelpDesk_AuditLogSystem.Log}, v_start),chr(10))
else
v_end := len({HPD_HelpDesk_AuditLogSystem.Log});



It is late in my workday and the coffee has worn off, so my apologies if i have typos, misunderstood the question or am just plain wrong in my solution.

 

[cfsutter]

That almost works. It did work for the logs where the assigned group is the last entry, but now it is adding the subsequent 6 characters from the next line to the entries that fall in the middle of the log.

So now the mid log entries look like this, rather than just pulling the first line:

Assigned Group: PCS ABC
Assign

 

[cfsutter]

Fisheromacse

I figured out where the additional characters were coming from and with your help got the formula to work.

Here's the final formula:

whileprintingrecords;
numbervar v_start;
numbervar v_end;

v_start := instr({HPD_HelpDesk_AuditLogSystem.Log},"Assigned Group:");
v_end := IF instr(mid({HPD_HelpDesk_AuditLogSystem.Log}, v_start),chr(10)) > 0
then v_end := instr(mid({HPD_HelpDesk_AuditLogSystem.Log}, v_start),chr(10))
else v_end := len({HPD_HelpDesk_AuditLogSystem.Log});

if {HPD_HelpDesk_AuditLogSystem.Log} like ["*Assigned Group:*"] then mid({HPD_HelpDesk_AuditLogSystem.Log},v_start,( v_end)) else ""

Thank you so much for your help! I really appreciate it.