Need help substringing part of a field

[sarend]

I have a field that grows each time a record is updated. It contains the Date, Time, employee, and a description. I am pulling this field everytime it is updated, however, I only need the most recent record and I don't want all the other information.

Here is an example of the field (ACTION):

04/03/07 08:20:28 US/Central (empname):
Assigned ownership to BA. Closing ticket.
04/03/07 08:19:20 US/Central (empname):
Plain Paper outq has been cleared. Added knowledge to IM.
04/02/07 13:57:34 US/Central (empname):
This has been completed.

I only want this information:
04/03/07 08:20:28 US/Central (empname):
Assigned ownership to BA. Closing ticket.
How can I substring from the beginning of the field to the next Date/Time entry??

Thanks for you help

 

[lbass]

Try:

stringvar x := split({table.action},"):")[1]+"):"+
split({table.action},"):")[2];
left(x,instrrev(x,"."));

-LB

 

[sarend]

Thanks! I just tried it and it works for some records but not all. here is example:

Here is the ACTION field.
04/03/07 16:26:14 US/Central (empname):
Updated
04/03/07 16:18:08 US/Central (empname):
Added to Windows Server Maintenance calendar.

As stated above, I display the record everytime it was updated given a time period. So for April 3rd, 2007, I should have one row that that has the first update and then another for the second update. Using the code you gave me, this is my output:

Update Time: 4/3/2007 4:26:14PM
Update Action:

Update Time: 4/3/2007 4:18:08PM
Update Action: 04/03/07 16:18:08 US/Central (empname):
Added to Windows Server Maintenance calendar.

 

[lbass]

First, if you try my formula on your original example, you will see that it works for what you initially described, with your initial example.

Your examples should always show the possible variations in the field. It now appears that there is not always a period as the last character per date entry.

Your original example only showed that you wanted the most recent entry. You now seem to be saying you want to disregard the times and show all entries for a particular date?

Note that had you used my actual formula on your second example, it would have returned nothing (my result when I tested it).

-LB

 

[Turkbear]

Hi,
Furthermore, any solution may only encourage you not to strike the database model's designer about the head and shoulders for giving you this structure
( If you are the DBA in question, maybe change it in future versions..)
Rant Follows:

Using 1 large data field like that means that any attempt at returning some subset of data involves inefficient parsing of a long string and looping through its parts until all needed data has been retreived..If the ACTIONs where in a separate table (linked by some key field in the Main table) with a new record for each update, your task would be trivial..




Using some variant of lbass's solution, once you provide the info requested, will probably solve this problem until you can get the model changed.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[sarend]

Thank you lbass and turkbear! Our team is going to tell the users that unless they all key in the decription using the same format, there is no way for us to report on this due to it being a free-form text field. Thanks again for you help.

 

[dgillz]

Sarend,

NO!!!!!!

That is not what Turkbear and Lbass suggested. They are suggesting that these different data elements all be given a separate field in the DB structure. These fields can then be made mandatory, with pre-defined data limitations, so this way you are not reliant on what the user does.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry