All, Thanks for your suggestions. I looked into the logs and played with the SQL some more and got resolution. First thing I noticed once it started working is that when you trigger a filter on save and/or modify of a field and reference that field from the database, you get the value BEFORE the update and not after. So, I re-worked it and used the power of SQL against the field record and not the database entry and it worked. Below is the query I used in case any are interested.
update HPD_Help_Desk set SLM_EMAIL_TO = (rtrim(ltrim(substring('$Assigned Group$', Charindex('-', '$Assigned Group$') + 1, Len('$Assigned Group$')))) + 't...@client.com'), SLM_EMAIL_CC = (rtrim(ltrim(substring('$Assigned Group$', Charindex('-', '$Assigned Group$') + 1, Len('$Assigned Group$')))) + 't...@client.com') + '; ' + (rtrim(ltrim(substring('$Assigned Group$', Charindex('-', '$Assigned Group$') + 1, Len('$Assigned Group$')))) + 't...@client.com'), SLM_EMAIL_BCC = 'serviced...@gssamerica.com' where (Incident_Number = '$Incident Number$' and (upper('$Assigned Group$') like upper('%Tier 4%'))) Thanks, Scott Scott Fluegge CIO & Sr. VP of Operations GSS America Infotech Ltd., A SEI-CMMI Level 3 certified company Third Floor, Block B, Cyber Gateway, HITEC City, Madhapur, Hyderabad - 500 081. AP. India Phone : 91 40 44556600 ext: 312 Mobile: 91 9701222218 VoIP: (847) 307 7606 ext: 250 Email: scott.flue...@gssamerica.com Visit: http://www.gssamerica.com -----Original Message----- From: Peter Romain [mailto:p.romain.arsl...@parsolutions.co.uk] Sent: Wednesday, June 03, 2009 3:15 PM Subject: Re: AW: Remedy 7.0 issue with Direct SQL in a filter Scott, You may find extra quotes are being inserted into the SQL. Try building the complete SQL statement in a Remedy temp field and using just this field in the direct sql action (Direct SQL -> $temp field$). Cheers Peter > Scott, > > turn on SQL-Logging and you'll see the statement wich the arserver is > issueing against the database. I think this should point you in the > right direction. > > HTH > > Kind Regards Conny > > ________________________________ > > Von: Action Request System discussion list(ARSList) > [mailto:arsl...@arslist.org] Im Auftrag von Scott Fluegge > Gesendet: Mittwoch, 3. Juni 2009 09:53 > An: arslist@ARSLIST.ORG > Betreff: Remedy 7.0 issue with Direct SQL in a filter > Wichtigkeit: Hoch > > > ** > > Anyone able to help me on a potentially simple Remedy question that has > my team stumped? > > > > This is in regards to a Remedy customization we need to do in our > environment. It is with respect to custom filters invoking Direct SQL > (MS SQL 2005 database). The SQL works PERFECT from the enterprise > manager but doesn't execute the complex query in Remedy. I did 4 direct > SQL statements. 3 are complex and don't do anything. The simple 4th > test (all are table updates) does work. I can't for the life of me > figure out why the others fail. I am pasting the SQL here in case you > can look into it... I am desperate!!! We are writing to hidden objects > within the incident record which SLM will use for notification. > > > > Here is a sample that works: > > update HPD_Help_Desk set SLP_EMAIL_TO = 'email_address_here' where > Incident_Number = '$Incident_Number$' and Assigned_Group like '%TIER 2%' > > > > And this one does nothing... (No errors unless I invalidate the SQL so > seems a matching issue): > > update HPD_Help_Desk set SLM_EMAIL_TO = > (rtrim(ltrim(substring(Assigned_Group, Charindex('-', Assigned_Group) + > 1, Len(Assigned_Group)))) + 't...@client.com'), SLM_EMAIL_BCC = > 'serviced...@gssamerica.com' where Incident_Number = '$Incident_Number$' > and Assigned_Group like '%TIER 2%' > > > > Can you help me??? Just to re-iterate, the SQL works perfect from SQL > Enterprise Manager (just changing the $Incident_Number$ to a real > number) so I know it is not a simple SQL error... > > Thanks, > > > > Scott > > _Platinum Sponsor: rmisoluti...@verizon.net ARSlist: "Where the Answers > Are"_ > > ____________________________________________________________________________ ___ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" > ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"