Here is an example I used to update record 1 in a form called "+TemplateFrom" updating a new field called "Date/Time Field" using the Remedy generated SQL View via a Filter. I was not able to get $TIMESTAMP$ to resolve using ARUtilities so I had to build it using Remedy workflow.
UPDATE A_TemplateForm SET Date_Time_Field = DATEDIFF(second, '1970-01-01 00:00:00', '$TIMESTAMP$')+25200 WHERE Request_ID = '000000000000001' Adding 25200 is to account for my GMT offset. There are more elegant ways to account for your offset. I just kept it simple for this proof of concept. Jason On Mon, May 7, 2012 at 12:29 PM, Nancy Tietz <nti...@umich.edu> wrote: > ** > > Wow – I just found out I can use the ‘SQL’ thing in ARUtilities to > actually SEE my Data!!! > > I’m so excited!!! > > > > Okay I’ll try to hold myself back on the ‘!’s and smileys… > > > > The C950000019 field is a new field that I added to hold the ‘opened’ > date-time for the incident. It is a ‘date/time’ type of field. > > > > I’m on SQL Server – so the ‘sp_help’ is great to know, since I’m an Oracle > person that got plopped into this BMC Remedy environment. (not very long > ago as you all can tell…) > > > > So here is my Update statement that I hope would work… however I am > unclear how to format the Timestamp now. > > So would I do an > > Integer($TIMESTAMP$) > > Or I’ll look it up somewhere… > > > > On Load of the window: > > UPDATE HPD_Help_Desk SET UM_Opened_By = $USER$, UM_Opened_Date = > $TIMESTAMP$ WHERE Incident_Number = $Incident Number$ AND > Incident_Number != $NULL$ AND UM_Opened_By = $NULL$; > > > > On Close of the window: > > UPDATE HPD_Help_Desk SET UM_Opened_By = $NULL$, UM_Opened_Date = $NULL$ > WHERE Incident_Number = $Incident Number$ AND $Incident Number$ != $NULL$ > AND UM_Opened_By = $USER$; > > > > Thanks everyone!! > > > > *From:* Action Request System discussion list(ARSList) [mailto: > arslist@ARSLIST.ORG] *On Behalf Of *Jason Miller > *Sent:* Monday, May 07, 2012 3:19 PM > > *To:* arslist@ARSLIST.ORG > *Subject:* Re: to commit or not to commit... that is the question > > > > ** This is a dev system right? It appears you are getting a handle on a > few different techniques and concepts here. I bet there are probably a few > of us wincing while reading this thread. When working with Direct SQL your > gun is always loaded. Be careful where you point that thing and make sure > you always hit your target. > > > > That said we are absolutely here to help and you have caught our interest > :) > > > > One thing that stood out to me in the the UPDATE is "C950000019 = > $TIMESTAMP$." I am assuming C950000019 is a Remedy Date/Time field. > Setting that to $TIMESTAMP$ in Direct SQL is going to give you errors > because you'll be trying to put a character string into an Integer field. > If C950000019 is a Character field you are good to go (but Date/Time kind > of loses it's value when stored as text). To store $TIMESTAMP$ in an > Remedy Date/Time field you will need to convert it to UNIX Epoch > time (integer) before the UPDATE. > > > > Jason > > > > On Mon, May 7, 2012 at 11:58 AM, Joe Martin D'Souza <jdso...@shyle.net> > wrote: > > Just to add to Jason, replace spaces & special characters with _. > > You should see all your columns in your DB table or view if you describe > (describe or desc) it.. > > eg: > Desc HPD_Help_Desk > Desc Tnnn (replace nnn with the schemaid as appropriate) > > This works on Oracle.. > > If you are using MS SQL or Sybase, describe will not work.. On MS-SQL & > sybase use: > sp_help HPD_Help_Desk > > Joe > > > > -----Original Message----- > From: Jlbess > Sent: Monday, May 07, 2012 2:46 PM Newsgroups: > public.remedy.arsystem.general > To: arslist@ARSLIST.ORG > Subject: Re: to commit or not to commit... that is the question > > If you're going to use C id's instead of your field names, you need to > replace HPD_HELP_DESK with the T table. > The views like HPD_HELP_DESK don't have the C names, they have the > database name you gave your field. (replace spaces with underscore) > > You also need to put single quotes around '$USER$' and '$Incident Number$' > > > Jason > > On May 7, 2012, at 1:48 PM, smiley <nti...@umich.edu> wrote: > > Hi again - > > Here is my (hopefully) correct Direct Sql (which is an Action within my > Active Link): > > UPDATE HPD_Help_Desk SET C950000018 = $USER$, C950000019 = $TIMESTAMP$ > WHERE C1000000161 = $Incident Number$ > > The 'C' numbers are my Field ID's. I'm not sure if I'm supposed to > indicate > the form HPD:Help Desk with the view HPD_Help_Desk. > > A SQL using the same column names is not working at all in ARUtilities > > SQL, so ... I'm not sure how to test it. > Any ideas how to test this?? > > I'm used to Oracle dbs, not MS SQL Server. > I really appreciate it!!! > Thank you for your help!!! > > > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > > > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
