In a Direct SQL no.. A Direct SQL sees a date field as an integer field. So all you need to do is calculate what that integer is, and set that integer to the field in your Direct SQL..
Cheers Joe From: Nancy Tietz Sent: Thursday, May 10, 2012 4:28 PM Newsgroups: public.remedy.arsystem.general To: [email protected] Subject: Re: Direct SQL in Active Link problems ** Hi again – Is there something I have to do to get the Integer value INTO the Date field??? Here is the ONLY way I could put the DATEDIFF into the Integer field… just before doing the UPDATe in the Direct SQL. DATEDIFF("second", "01/01/1970 00:00:00", $SERVERTIMESTAMP$) + 14400 Here is my UPDATE command in the Direct SQL: (which worked fine in ARUtilities > SQL) update HPD_Help_Desk SET UM_Opened_By = '$USER$', UM_Opened_Date = UM_Opened_Date_Integer WHERE Incident_Number = '$Incident Number$'; I didn’t get any date. L Thanks! From: Nancy Tietz [mailto:[email protected]] Sent: Thursday, May 10, 2012 2:42 PM To: [email protected] Subject: RE: Direct SQL in Active Link problems Hi Jason – thanks for your help – I did try the other form method first as it seemed to be the better option, however I never got it to work. What happened was that the other form would not be ‘activated’ On Display or On Window Loaded or On Window Open for the HPD:Help Desk form. It did in one trial only when I had HPD:Help Desk as a secondary form in the ‘Associated Forms’ in the top part of the Active Link. My users do not want the Last Modified By / Date to be updated when this ‘Opened By’ field is populated – and I Must somehow SAVE the field because other users need to see it. That was back when we were talking about the “@@:Application-Query-Delete-Entry” stuff. Sigh. Nothing is easy here evidently. The SAVE error message pops up when the HPD:Help Desk is an associated form. So that is a big no-no. L I was sure hoping that the Direct SQL would just get in there and do the job and get out. But again I’m having technical difficulty. I’m still working on the date-integer field, and I hope this will work without getting the Save error notice when they try to close without doing anything. Thanks again so much for your help!!! From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Jason Miller Sent: Thursday, May 10, 2012 1:59 PM To: [email protected] Subject: Re: Direct SQL in Active Link problems ** Hi Nancy, It has been fun to help you to work out these issues. I have a question regarding the design though. Is the idea other people are not allowed to make changes to the record when the first person to open it has it "locked?" Or will the other people just see that somebody else got to the record first (the field itself or a warning pop up)? I think somebody touched on this the other day and the reason I ask is workflow that fires on Un-Display/Window Closed is not guaranteed to fire. It does fire in most cases however if the browser crashes or if the person presses F5 (I haven't tested this F5 theory) your Active Link to "release" the record will not be triggered and now the record will inaccurately indicate that somebody has it open. If the design is to actually keep other people from updating then this could become a huge dissatisfier for your users. Have you thought about a clean up process for records that inaccurately show they are in use? I have found with Un-Display/Window Closed workflow I typically need plan B to for maintenance when Plan A has been circumvented. These new fields are on the Help Desk form correct? Have you thought about using a second custom form where you can build the majority of your process and then integrate this form with HPD:Help Desk? This is usually considered a best practice because 1) it is minimally invasive to the Out of the Box form, 2) can make upgrades much easier and 3) it protects the integrity of the data/business rules of the Out of the Box form (especially in this case where you want to use direct SQL). I think if your design was tweaked to use a second form you wouldn't even need to use Direct SQL to avoid triggering Filters on HPD:Help Desk and updating the last modified user/timestamp. The custom form's last modified user/timestamp would be updated which could be beneficial for cleanup. Using another form would also allow you to create an Escalation that cleans up records that have been locked for X amount of time. Fox example is it reasonable that a person would have an Incident locked for more than 30 minutes? You could have an escalation that deletes the lock record if it is older than 30 minutes. Or you could send them a remind they have the Incident locked. Because there should be minimal lock records in this form at any one time the Escalation has minimal impact on system performance compared to running it on your HPD:Help Desk form. Jose wrote an article that talks about many of these concepts here: http://theremedyforit.com/2012/01/scheduling-actions-in-bmc-remedy-action-request-system/ HTH, Jason On Thu, May 10, 2012 at 9:00 AM, Nancy Tietz <[email protected]> wrote: ** Hi Axton – Oh that’s right – somebody said to use ‘$SERVERTIMESTAMP$’… From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Axton Sent: Thursday, May 10, 2012 11:50 AM To: [email protected] Subject: Re: Direct SQL in Active Link problems ** I would not use TIMESTAMP in direct sql in active links. You will get different times from different users, depending on what timezone the client machine is configured for. Axton Grams On Thu, May 10, 2012 at 10:37 AM, smiley <[email protected]> wrote: Hi all - This Direct SQL seems like it should work, but I am getting an error message (in Mid-Tier, 7.6.03). UPDATE HPD_Help_Desk SET UM_Opened_By = '$USER$', UM_Opened_Date = DATEDIFF(second,'01/01/1970 00:00:00','$TIMESTAMP$') +14400 WHERE Incident_Number = '$Incident Number$' ; The above Direct SQL should translate to this: (which actually works as is in ARUtilities' SQL); UPDATE HPD_Help_Desk SET UM_Opened_By = 'myuserid', UM_Opened_Date = DATEDIFF(second,'01/01/1970 00:00:00','05/09/2012 03:00:00 PM') +14400 WHERE Incident_Number = 'INC000000031826' ; So what is Direct SQL doing to this sQL??? Is it the $Incident Number$ or $USER$ or $TIMESTAMP$ ??? I actually tried it withOUT the $TIMESTAMP$... and I had some NULL / IS NULL etc in there and had to take that out as I couldn't get the nulls to work in the WHERE statement. I thought it was working --- although that was in the BMC Remedy Client, not Mid-Tier. It is easier for me to test in the Client because I don't have to Flush Cached every time I tinker with the AL's. So close.... On the Client I was testing and found that the fields were being updated every Other time! However I am looking at ARUtilites in a SELECT statement after every step to see what happened... and maybe IT is only working every other time. Good grief! Any other ideas out there? Or maybe sympathy?? I know the Client works differently than the Mid-Tier but this is very tiring. Thanks for listening!! _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
