When we build our new Change Management and Service Desk apps we went with the table option. We had the same requirement to allow people to pull a single record with Work Log into a spreadsheet via web reports, Crystal, etc. We don't differentiate between Work Log entries and Audit records except with a Record Type flag so they are all in the same form. We were able to do this with a little SQL magic in a SQL view --> View form --> Join to SD/CM. Our SD and CM forms have their own Work Log form so we have a SQL view for CM and one for SD.
Basically our SQL view (MS SQL Server) selects a handful of columns from the CM or SD SQL view (the one created by Remedy) and then has a sub-select on the work log form SQL view (created by Remedy) that uses "STUFF" and "XML Path" to put all of the work log entries into a single record's column. We sprinkle in some replaces and a epoch to date/time function (that I originally got of the List) to make it look like a diary field entry. The only difference in looks from a diary field is the timestamp is in 24 hr format instead of 12 hr (we just would need to update our function to adjust it). [image: Inline image 1] Jason On Wed, Mar 15, 2017 at 1:13 PM, LJ LongWing <[email protected]> wrote: > ** > John, > BMC themselves went away from Diary fields for the most part (certainly in > the work log process) many years ago....the down side, as you pointed out > is storing the diary in individual records causes issues with > reporting....this is easily worked around in a sufficiently advanced > reporting tool allowing you to create sub-reports reporting all of the > diary entries from a second table....the fact that they can't be pulled in > the same entry is the only down side that I know of...with plenty of up > sides. > > On Wed, Mar 15, 2017 at 3:06 PM, Reiser, John J <[email protected]> > wrote: > >> ** >> Hello Listers, >> >> Is there a downside to replacing diary fields with a different method of >> capturing work activity? >> Since diary fields don’t show up in Mid Tier Web reporting I was looking >> into a way to replace the diary field for reporting. >> >> We actually have reports that get created in csv format for Excel that >> use the Diary. >> Occasionally we have a diary so large it breaks the cell character limit >> in Excel. >> >> I was contemplating using a table field to display records from a work >> activity form and then using either a join or some other temporary means to >> gather the activity into a report. >> >> Can I use an unlimited Display Only field to dump the diary during the >> report process? >> Is there a better mouse trap for collecting large amounts of data and >> reporting it? >> >> As always, thanks in advance. >> >> Thank you, >> --- >> John J. Reiser >> Building 760-J202 >> Remedy AR System Developer >> Senior Software Development Analyst >> Lockheed Martin - RMS Moorestown Region >> The star that burns twice as bright burns half as long. >> Pay close attention and be illuminated by its brilliance. - paraphrased >> by me >> >> >> >> _ARSlist: "Where the Answers Are" and have been for 20 years_ > > > _ARSlist: "Where the Answers Are" and have been for 20 years_ > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
