Oh, the downside are: - If you have multiple table fields you need to join in the report (like work log and related records) things start getting pretty messy. - Users need to select the View form instead of the Regular form when building a report if they are going to want the Work Log too. It takes a little bit of training.
Jason On Wed, Mar 15, 2017 at 2:50 PM, Jason Miller <[email protected]> wrote: > 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"
