We recently had to tackle this same challenge on SQL Server. Reporting nicely to an Excel friendly format was a big requirement and I was set on using table field w/ Work Log form because of all of the benefits (filtering, row level access (RLA), unlimited number of attachments, etc). Unfortunately there is not support for Diary Fields in Web Reports <https://communities.bmc.com/message/424978#424978> so a diary field wasn't going to work for us.
I thought about creating workflow to duplicate all of the work log data and audit (we do not separate the two) to a hidden character field in a diary field format and create a filter that would prevent tampering. I couldn't bring myself to duplicate all of that that data just because somebody might want to report off of that record someday. The other trick is row level access. Some work log entries can be marked as non-public and RLA is used to grant access to entries. . We did find a solution and I'll post the details (or at least a link to them) in the next few days. In short we ended up with a SQL view that converts the multiple work log records to XML and uses STUFF and returns it as a single record. We created a View form against that view and a Join form to bring it together with the parent request. The only gotcha is people need to report against a "reporting with work log" form if they want the work log (also contains audit info). I much rather this and have the flexibility of individual records and not duplicate data (which wasn't going to work in our case anyways because of RLA). Jason On Tue, Sep 30, 2014 at 6:13 AM, Reiser, John J <[email protected]> wrote: > ** > > Charlie and Rick, > > Thanks for the feedback. > > I have used this in a similar fashion as the Incident Work Info section on > a home-grown system here. > > We are actually going in that direction now because some of the Work > information log data need to be sent to the customer for status. I didn’t > think our engineers would want to use one diary field for work logging and > another for customer status notices. > > We also frequently pass tickets between functional groups and will need to > allow for tracking billable time. > > So it is going to be our method forward for most applications. > > > > Theo, > > As for reporting, I’m using MS SQL Server 2008 and have not had time to > work on MS SQL Server Reporting tool or BIRT to make some canned reports. > I’ve tried joins and the multiple parent record fields are an issue with > people who use .csv exports to excel. They’re also the ones who complain > about too much data in a diary field getting truncated in excel. So I’m > hoping I can come up with a way to filter unnecessary records from the > Work Information table. > > > > Misi, > > You always seem to have tool on the shelf for everyone’s needs. > > Thanks, I’ll take a look at it when they ask me to convert the archived > diary field data to individual records. > > > > You both mentioned sub-reports. Is this something external to the BMC > Remedy report engine? > > Like BIRT or MS Reporting. We don’t have a Crystal Reports web service. > > > > Thank you, > > --- > John J. Reiser > Remedy Developer/Administrator > > Senior Software Development Analyst > Lockheed Martin - MS2 > The star that burns twice as bright burns half as long. > Pay close attention and be illuminated by its brilliance. - paraphrased by > me > > > > *From:* Action Request System discussion list(ARSList) [mailto: > [email protected]] *On Behalf Of *Charlie Lotridge > *Sent:* Monday, September 29, 2014 7:18 PM > *To:* [email protected] > *Subject:* EXTERNAL: Re: Diary field usage > > > > ** > > I can't speak to "best practices", but IMHO it really depends two things: > the expected size of the content and how you plan to use it. > > > > With regard to the expected content, if you're planning to document > something with a relatively limited life-cycle and not a lot of content, > then I think diary fields are an excellent choice: they're very easy to > create and maintain and don't require setting up the child form, workflow, > and UI needed to support it that the "PK/FK" paradigm requires. > > > > But if you're planning to document something with an indefinite > life-cycle, where the content may grow continuously, then diary fields > become a bad choice. Remember that these are stored in your DB as a blob > (text, ntext, etc), so the entire content needs to be read and written on > each access or update (respectively). What's worse is that this entire > content needs to be transferred over the wire multiple times (from DB to > ARS to mid-tier to client and back again). This obviously gets cumbersome > when the content is very large. > > > > With regard to planned usage, if you're planning on setting up complex > searching (or reporting) on the content, diary fields are difficult to > use. On the other hand, if you plan to use the content only for review > (e.g. users will only need to read through them to see what happened and > when), then diary fields are perfect for the job. > > > > Hope this helps. > > > > -charlie > > > > On Mon, Sep 29, 2014 at 3:35 PM, Rick Cook <[email protected]> wrote: > > ** > > Yes, they are not the best practice any more. Best practice is to > accumulate records in a back-end form and use a PK/FK relationship to tie > them to a master record (i.e. Work Logs to an Incident. Reporting can be > challenging, but a join at the reporting level should make things easier. > > > Rick Cook > > > > On Mon, Sep 29, 2014 at 3:30 PM, Reiser, John J <[email protected]> > wrote: > > ** > > Hello Listers, > > I have quick question. > > What is the general consensus on the use of Diary fields? > > Are they going out of style? > > I have tried a “work log” table type of design to replace the diary > function on a form. > > This method works ok except when it comes time to run reports. > > Do you dump the table field data to a large character field? > > Or is there a slick way to incorporate the child records into the report > on the fly? > > > > Thank you, > > --- > John J. Reiser > Remedy Developer/Administrator > > Senior Software Development Analyst > Lockheed Martin - MS2 > 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_ > > > > _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"
