Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

How to copy files from SD to History or the reverse

SOLVED
Go to solution
Highlighted
Guy Salomon
Regular Collector

How to copy files from SD to History or the reverse

Is there a way of copying fields from SD client form to history or the reverse. In detail I would like to copy SD TIME SPENT to HISTORY SPENT TIME and SD INFORMATION TO HISTORY INFORMATION (or SUBJECT .. but I would prefer INFORMATION for the added space).

I have read through through the FORUMS and a couple have said you can do it thorough a DB rule, but I can't seem to figure out how as all the copy to's seem to only work for the SD Client form.

If I can get that to work then I plan to learn enough about sd_event to extract individual time spent per person per call.
Thanks in advance,

Guy
12 REPLIES
Guy Salomon
Regular Collector

Re: How to copy files from SD to History or the reverse

Sorry I meant FIELDS NOT FILES
George M. Meneg
Honored Contributor

Re: How to copy files from SD to History or the reverse

Hello,

You can't copy fields from/to history lines without using either a web-api application or sd_event/sd_import like utilities.

However, if enable auditing of the fields "SD TIME SPENT" and "INFORMATION" then each change on these fields will be recorded on history lines.
menes fhtagn
Guy Salomon
Regular Collector

Re: How to copy files from SD to History or the reverse

So if I understand correctly I should write a script using sd_event with the proper flags to capture the information and then set up a DB rule to populate the fields from SD to History or the reverse.

Also I had already set TIME SPENT (not SD TIME SPENT....doesn't exist) from AUDIT RULES>SERVICE CALLS. I can get the information field to update from SD to History but the time is not copied.

Could this be because I am using SD 4.5 SP10?

Also, when I try to do Analized Data I can only get the earliest 1000 records. If I try changing the range (say date) on the view, I don't get any results. I remember Ruth Porter said something in one of her entries in Forum that a SQL script could be used. But I can't locate the Forum Thread.

A thread: HISTORY TRACKIHG ON INFORMATION FIELD, said that it is possible to modify the History form so more of the information could be captured? By adding a NEW FIELD.... is there a field by that name. I don't see it. Or I just can't figure out how to add it.

As an alternative I was thinking of skipping the whole history thing and access the TIME SPENT directly through SQL 2000. I have looked through the DATA DICTIONARY list and can find TOTAL TIME but not TIME SPENT. And the followup question would be does SQL store every incident of TIME SPENT or just use that field as a temp field and 0's it out.

Sorry about all the question but tracking individual time spent on a ticket has been a big hot button around my office and I have been banging away at this on and off for sometime now.

Thanks again for your help,
Points are waiting :-)

g
Ruth Porter
Honored Contributor

Re: How to copy files from SD to History or the reverse

Hi Guy,

I am not sure if anything that follows actaully addresses your issue but here are some considerations on the subject of time spent that I have used over the years.

1. The time spent in history can only really be used if you have a culture in your orgnaisation that means staff will go in and enter the details - becuase you cannot make it mandatory. Also you need a reporting tool such as Crystal to extract the information.

2. An alternative approach is to use 2 custom number fields. One minutes spent and the other the total. Make the first mandatory using a UI rule on save and have a DB rule which adds it into the total and then clears it.

Hope something here helps, Ruth
http://www.teamultra.net
Guy Salomon
Regular Collector

Re: How to copy files from SD to History or the reverse

Hi Ruth and thanks for the reply.

At the moment the client form already has a TIME SPENT field (which is mandantory) and a TOTAL TIME SPENT field which is updated via rules. I have also made INFOMATION mandantory and on saving the record the text in that field is moved to the Diary field with a Consultants am and a time stamp as to when the entry was made.

Everyone handling a SD call enters detailed information etc.

As the product matured, management wants to be able to track time for any individual that works a service desk call on a call by call basis. So if three consultants work a given SD call we would not only see the total time spent by all three consultants but break out the amount of time each one contributed.

In my research suggestions have included... Westbury (to expensive), history, sd_event and possibly Quering SQL 2000 directly. Although this Forum and the SD manuals have provided information it is piece meal and would require a better skill set then I may have. I am sure I can do it if I had a cookbook or more detailed guidance.

The Good the bad and the Ugly....things I have discovered:

History: Using it to update Analyized data is do-able. I added History to the SD form. I have enable Information and Time spent for Audit. However, only get INFORMATION to move to HISTORY/SUBJECT but not TIME SPENT to HISTORY/SPENT TIME. Ideally (besides time)I would like to move SD/INFORMATION to HISTORY/INFORMATION.

I read that you can't do that on the default form for History but have to add a NEW FIELD??? Meaning I have to create a custom field???

If so I don't know how for HISTORY... done it plenty of times on other forms sd_event: I understand it might be possible to create a script using this to pull information from the SD Client form and push it to analyized data. And it would require a DB
rule to do it. Am I on the right path.. that you would use Command Exec Action?

Again, I have little experience with this function. Do I put the script in Parameters or do I create a call to the script.

SQL quering: We are able to locate and pull total times in SQL TOTAL TIME. Does SD/SQL store the incremental values of TIME SPENT from the SD Client Form?

Is so I have not been able to locate it in the DATA DICTIONARY listings. If I had this information we could use MS Report services Crystal or export the SQL Query to Access or whatever.


Sorry this is such a ramble but as you can see I have been digging by no paydirt so far?

Thanks again for your Help!!!

g
Ruth Porter
Honored Contributor

Re: How to copy files from SD to History or the reverse

Hi guy,

Perhaps the simplest way would just be to have another custom field where you capture the current user any time they enter a value in your custom spent time field.

If this does not give you what you want, sd_event is the best bet: it is documented in Chapter 5 of the data excahnage guide and basically you need to set up an import mappting for service call history line (use the Service Call as the unique ID). Experiment with issuing the command by hand in a DOS box until you are happy you know how to use it; then put it in a DB rule.

Regards, Ruth
http://www.teamultra.net
Guy Salomon
Regular Collector

Re: How to copy files from SD to History or the reverse

Thanks Ruth, funny I came up with a similar idea as you first suggestion just as I sent the response. I guess I could concatenate the call id, name and time like I do with the Diary for each update. A custom text field should do it. Now the issue would be sub-totals for each consultant. Text wouldn't allow you to do that it would have to be exported to Excel? Thoughts?

I have been through the Data Exchange in this regard, several times and barely can make heads or tails of it.

g

Guy Salomon
Regular Collector

Re: How to copy files from SD to History or the reverse

two last things... you said re: sd_event.... DB rule that would be a Command exec or something else?

Also I set up the Audit to trigger TIME SPENT TO SPENT TIME and it doesn't update HISTORY... I am running 4.5 SP10... could that be why?
Ruth Porter
Honored Contributor

Re: How to copy files from SD to History or the reverse

Hi Guy,

Re your point on excel - yes or a reporting tool such as Crystal.

"I have been through the Data Exchange in this regard, several times and barely can make heads or tails of it."

You are not the only one but I attach some details which may help.

Try the command by hand and then when you are happy, set up a DB rule to run it; say on the server - but you will need to set up an agent on the server and ensure it is running.

Regards, Ruth

PS pints would be nice!

http://www.teamultra.net
Ruth Porter
Honored Contributor
Solution

Re: How to copy files from SD to History or the reverse

Hi Guy,
while pints would indeed be nice I meant points!
Regards, Ruth
http://www.teamultra.net
Guy Salomon
Regular Collector

Re: How to copy files from SD to History or the reverse

George I also assigne you some points. Thanks again for your help. G
Guy Salomon
Regular Collector

Re: How to copy files from SD to History or the reverse

Hi Ruth... for some reason my posting to you didn't transfer. I plan to try the excel/access export first. If not I guess I gotta dig into (bbbbrrrrrr) sd_event. I have assigned you points in lieu of Guiness :-) Thanks again, G
//Add this to "OnDomLoad" event