Thanks, Ben, for your helpful suggestions. Here is my solution for anyone interested in the details:
1. Use an sql query to retrieve the diary field (df) entry, substituting readable values for special characters. Specifically replace ascii 03 which marks the end of a particular diary entry, with a nonsense sequence you are sure you won't encounter in real life, eg "Q^+q", and ascii 04 which separates the date and user values from the rest of the text, with whatever demarcation you want, eg a comma. Put this value into a "df database" field. My query is: select replace(replace(c540001400 [the diary field], chr(03), 'Q^+q'), chr(04), ', ') from t762 where c1 = '$Entry ID$' 2. Use a Filter Guide sequence to -Move the integer portion (characters 0 - 9) of the "df database" field into a date-time field, and then move the date-time field into a "df translated" field. -Find the position of your first nonsense sequence. -Copy everything from position 10 of "df database" to the character just before your nonsense sequence, into "df translated." -Trim off the beginning of "df database" thru the end of the first nonsense sequence. -Find the next nonsense sequence. If there isn't one, quit. Else repeat the sequence. I once suggested that Remedy make the database values of diary fields available to workflow. Does anyone else think this is a good idea? Dwayne ********************************** From: Ben Chernys <[email protected]> Subject: Re: Including the database value of a diary field in an email In-Reply-To: <[email protected]> Content-Type: multipart/alternative; Here you are: /* Constants used for items in values returned from the server */ #define AR_DEFN_DIARY_SEP '\03' /* diary items separator */ #define AR_DEFN_DIARY_COMMA '\04' /* char between date/user/text */ /* within a diary item */ So, you'll get: \03nnnnnnnn\04user\04text text text Followed by the next entry \03nnnnnnnn\04user\04text text text Ben Chernys Senior Software Architect Software Tool House Inc. Canada / Deutschland / Germany Mobile: +49 171 380 2329 GMT + 1 + [ DST ] Email: <mailto:[email protected]> Ben.Chernys _AT_ softwaretoolhouse.com Web: <http://www.softwaretoolhouse.com> www.softwaretoolhouse.com Check out Software Tool House's free Diary Editor. Meta-Update, our premium ARS Data tool, lets you automate your imports, migrations, in no time at all, without programming, without staging forms, without merge workflow. <http://www.softwaretoolhouse.com/> http://www.softwaretoolhouse.com/ From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Martin, Dwayne - martinrd Sent: September-30-10 17:26 To: [email protected] Subject: Including the database value of a diary field in an email From: Martin, Dwayne - martinrd Sent: Thursday, September 30, 2010 11:26 AM To: '[email protected]' Subject: Including the database value of a diary field in an email Dear List, I want to send an email about a form entry that will query the database for the stored diary field value, and include it as a comprehensible text. Challenge 1: Finding the date integers and converting them to texts. Right now I can look for a character sequence that starts with "128," but as time goes by that number will increase and I'll have to update the workflow. Challenge 2: The User logon field has a square before and after it. Anybody know what character these squares are and how to remove them? Anybody done this before? Is there a simpler approach altogether? Dwayne Martin James Madison University (ARS 7.1 p3, Oracle 10.2 db) _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
