That is a great idea, but I have 16 attachments in my attachment pool and I really don't want to create 16 temporary fields because users can and do add more then one attachment at a time. The user wants me to check for key letters in a file name and send him an email only when new files or changed files get uploaded.
-----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Heider, Stephen Sent: Thursday, December 07, 2006 9:22 AM To: [email protected] Subject: Re: Fwd: Question: SQL Have you tried: Action 1 (if run from a filter): Set Fields zTmpField1 = UPPER($File1$) Action 1 (if run from an active link): Set Fields zTmpField1 = UPPER($File1$) WHERE RequestID = $RequestID$ Action 2: Set Fields SQL SELECT CHARINDEX('SOW', $zTmpField1$) and store to zTmpInt1 After it runs the zTmpInt1 field will contain the position within the filename if 'SOW' is found. If not found, then zTmpInt1 will be 0. Stephen -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Tyrone Dee Sent: Thursday, December 07, 2006 9:11 AM To: [email protected] Subject: Re: Fwd: Question: SQL Hey Jim - thanks for your help - much appreciated ! I have an attachment pool - with 10 attachments and right now I am only trying it on one of the attachments to see if my SQL statement is correct. I only have on filter that fires on modify - executing at 500. Set Fields - Read Value from SQL and I set the SQL Result Column 1 to a integer field. -----Original Message----- From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED] Sent: Thursday, December 07, 2006 9:07 AM To: Tyrone Dee; [email protected] Subject: RE: Fwd: Question: SQL What event is the filter attached to? What happens if you add a 2nd attachment to a particular request? Does it return 1 if you do? -----Original Message----- From: Tyrone Dee [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 06, 2006 3:32 PM To: Ashton, Jim (JUS); [email protected] Subject: RE: Fwd: Question: SQL Filter is firing at 500 - should I change it to 999 ? Will that make a difference? -----Original Message----- From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 06, 2006 3:27 PM To: Tyrone Dee; [email protected] Subject: RE: Fwd: Question: SQL Sounds like your filter is running before your attachment is saved. Does it return 1 if you add a second attachment (which would actually be recognition of the existence of the first attachment)? -----Original Message----- From: Tyrone Dee [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 06, 2006 2:29 PM To: Ashton, Jim (JUS); [email protected] Subject: RE: Fwd: Question: SQL Yes -----Original Message----- From: Ashton, Jim (JUS) [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 06, 2006 2:20 PM To: Tyrone Dee; [email protected] Subject: RE: Fwd: Question: SQL Does the statement work properly if you run it with a known sample value for $Request ID$ in Query Analyzer? -----Original Message----- From: Tyrone Dee [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 06, 2006 1:37 PM To: [email protected] Subject: RE: Fwd: Question: SQL Jim - thanks for your email. I decided to go back a bit and try just looking for SOW in the filename - here is the SQL Statement: SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID = '$Request ID$') AND ((PATINDEX('%SOW%',UPPER(Scope_of_Work)) > 0) ) It is returning 0 when I add the attachment and 1 when I delete the attachment. Suggestions? Ty -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Jim Ashton Sent: Wednesday, December 06, 2006 10:02 AM To: [email protected] Subject: Re: Fwd: Question: SQL I think you may need one more set of brackets. It appears that the query always returns 1 because Request_ID = '$Request ID$' is always true and at least one of the OR conditions is true somewhere in the IPVPN_IPACT_Overview table. Put another set of brackets around everything following the AND (as shown below) which will result in both that particular request and at least one of the OR conditions needing to be true to return a value. SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID = '$Request ID$') AND ( ((PATINDEX('%SOW%', UPPER(Scope_of_Work)) > 0) OR (PATINDEX('%IPDP%', UPPER(Scope_of_Work)) > 0) OR (PATINDEX('%MACD%', UPPER(Scope_of_Work)) > 0)) ) Cheers, Jim. ________________________________________________________________________ ____ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
