FW: Moving records to the top based on a condition
Rashmi, Here is one way it can be done, however it might not be useful due to performance. I present this to spark ideas... Overview: Using SQL, create a SQL View that includes a calculated column. This column would contain the sort order based on whatever criteria you require. Then create an ARS View form on top of this SQL View. Your table field would reference this calculated column to sort the records on screen. Here is the SQL View: CREATE VIEW dbo.uvw_MySQLViewForARS AS SELECT Summary, Last_Modified_Date, Priority, State, dbo.udf_SortOrder AS SortOrder FROM dbo.SHR_ConsolidatedList Here is the function: CREATE FUNCTION dbo.udf_SortOrder ( @Last_Modified_Date INT, @Priority INT, @State INT ) RETURNS VARCHAR(20) BEGIN DECLARE @SortOrder VARCHAR(20) SET @SortOrder = CONVERT(VARCHAR,@Last_Modified_Date) + '|' + CONVERT(VARCHAR,@Priority) + '|' + CONVERT(VARCHAR,@State) IF @State = 0 AND @Priority = 1 AND (SELECT * FROM dbo.uvw_CurrentRemedyTime) (@Last_Modified_Date + (45 * 60)) RETURN '0|' + @SortOrder RETURN '1|' + @SortOrder END Add whatever other parameters to the function you may need for sorting. For criteria that matches your condition prefix a number. In this example there is only one condition, so I am using only a 0 and a 1 to provide the proper sorting. If you had 5 conditions then you would use numbers 0-5 (or 1-6, or 100 - 105, etc.). Here is the SQL View that the function needs to get the current date/time in Remedy time. *With SQL Server you can't use a non-deterministic value (such as GETDATE()) directly, so you have to use a View to return the value. CREATE VIEW [dbo].[uvw_CurrentRemedyTime] AS SELECT DATEDIFF(s, '01/01/1970', GETDATE()) CurrentRemedyTime HTH Stephen Remedy Skilled Professional From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Rashmi Amruth Sent: Monday, March 24, 2008 1:09 AM To: arslist@ARSLIST.ORG Subject: Re: Moving records to the top based on a condition ** Hi Joe, The table records are being retrieved from a view form. But this view form already has sort criteria set for existing records. Now, I need to sort only a subset of these records for the criteria which I have defined. Thanks, Rashmi ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Moving records to the top based on a condition
...use this Select instead SELECT Summary, Last_Modified_Date, Priority, State, dbo.udf_SortOrder(Last_Modified_Date, Priority, State) AS SortOrder As someone once wrote: If cups_of_coffee 2 Then refill From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Heider, Stephen Sent: Monday, March 24, 2008 9:13 AM To: arslist@ARSLIST.ORG Subject: FW: Moving records to the top based on a condition Rashmi, Here is one way it can be done, however it might not be useful due to performance. I present this to spark ideas... Overview: Using SQL, create a SQL View that includes a calculated column. This column would contain the sort order based on whatever criteria you require. Then create an ARS View form on top of this SQL View. Your table field would reference this calculated column to sort the records on screen. Here is the SQL View: CREATE VIEW dbo.uvw_MySQLViewForARS AS SELECT Summary, Last_Modified_Date, Priority, State, dbo.udf_SortOrder AS SortOrder FROM dbo.SHR_ConsolidatedList Here is the function: CREATE FUNCTION dbo.udf_SortOrder ( @Last_Modified_Date INT, @Priority INT, @State INT ) RETURNS VARCHAR(20) BEGIN DECLARE @SortOrder VARCHAR(20) SET @SortOrder = CONVERT(VARCHAR,@Last_Modified_Date) + '|' + CONVERT(VARCHAR,@Priority) + '|' + CONVERT(VARCHAR,@State) IF @State = 0 AND @Priority = 1 AND (SELECT * FROM dbo.uvw_CurrentRemedyTime) (@Last_Modified_Date + (45 * 60)) RETURN '0|' + @SortOrder RETURN '1|' + @SortOrder END Add whatever other parameters to the function you may need for sorting. For criteria that matches your condition prefix a number. In this example there is only one condition, so I am using only a 0 and a 1 to provide the proper sorting. If you had 5 conditions then you would use numbers 0-5 (or 1-6, or 100 - 105, etc.). Here is the SQL View that the function needs to get the current date/time in Remedy time. *With SQL Server you can't use a non-deterministic value (such as GETDATE()) directly, so you have to use a View to return the value. CREATE VIEW [dbo].[uvw_CurrentRemedyTime] AS SELECT DATEDIFF(s, '01/01/1970', GETDATE()) CurrentRemedyTime HTH Stephen Remedy Skilled Professional From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Rashmi Amruth Sent: Monday, March 24, 2008 1:09 AM To: arslist@ARSLIST.ORG Subject: Re: Moving records to the top based on a condition ** Hi Joe, The table records are being retrieved from a view form. But this view form already has sort criteria set for existing records. Now, I need to sort only a subset of these records for the criteria which I have defined. Thanks, Rashmi __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Moving records to the top based on a condition
Hi Joe, The table records are being retrieved from a view form. But this view form already has sort criteria set for existing records. Now, I need to sort only a subset of these records for the criteria which I have defined. Thanks, Rashmi On 3/23/08, Joe D'Souza [EMAIL PROTECTED] wrote: ** Rashmi, Create a database view of what you want, and create a view form for that view.. that view form will have the records sorted as you wish if you have taken care to order it by whatever condition you wish Joe -Original Message- *From:* Action Request System discussion list(ARSList) [mailto: [EMAIL PROTECTED] Behalf Of *Rashmi Amruth *Sent:* Saturday, March 22, 2008 7:45 AM *To:* arslist@ARSLIST.ORG *Subject:* Moving records to the top based on a condition ** Hi All, A remedy table field (say, Remtab) contains some records which has columns (Last Modified Date, Priority, State). I need to move certain records to the top based on a condition. The condition is, say - State is Active and Priority is 1 and Current date and time is more than 45 minutes after Last Modified Date. Any ideas on how this can be done? Regards, Rashmi __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Moving records to the top based on a condition
One suggestion is to use a self-join on the target form to determine the results sort order differently than how the original form has it specified. So REMTAB points to FORM A whose results list is ordered by Date, and Request ID. Create a SELF-JOIN of FORM A with a results list set ordered by Status/Priority and Last Modified Date and point REMTAB to that? This would get you part-way to the solution. Regards Dave Morgan Tiberone Technologies Ltd -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Rashmi Amruth Sent: 22 March 2008 11:45 To: arslist@ARSLIST.ORG Subject: Moving records to the top based on a condition --=_Part_4106_1533299.1206186276245 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi All, A remedy table field (say, Remtab) contains some records which has columns (Last Modified Date, Priority, State). I need to move certain records to the top based on a condition. The condition is, say - State is Active and Priority is 1 and Current date and time is more than 45 minutes after Last Modified Date. Any ideas on how this can be done? Regards, Rashmi ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are --=_Part_4106_1533299.1206186276245 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline ** divHi All,/div divnbsp;/div divA remedy table field (say, Remtab)nbsp;contains some records which has columns (Last Modified Date, Priority, State). I need to move certainnbsp;recordsnbsp;to the top based on a condition. /div divThe condition is, saynbsp;- span style=FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-fareast-font-family: #39;Times New Roman#39;; mso-bidi-font-family: #39;Times New Roman#39;; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA; mso-bidi-font-weight: boldState/spanspan style=FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-fareast-font-family: #39;Times New Roman#39;; mso-bidi-font-family: #39;Times New Roman#39;; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA; mso-bidi-font-weight: bold is Active and Priority is 1 and Current date and time is more than 45 minutes after Last Modified Date/span./div divnbsp;/div divAny ideas on how this can be done?/div divnbsp;/div divRegards,/div divRashmi/div divnbsp;/div __Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are html___ --=_Part_4106_1533299.1206186276245-- ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Moving records to the top based on a condition
Rashmi, Create a database view of what you want, and create a view form for that view.. that view form will have the records sorted as you wish if you have taken care to order it by whatever condition you wish Joe -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Rashmi Amruth Sent: Saturday, March 22, 2008 7:45 AM To: arslist@ARSLIST.ORG Subject: Moving records to the top based on a condition ** Hi All, A remedy table field (say, Remtab) contains some records which has columns (Last Modified Date, Priority, State). I need to move certain records to the top based on a condition. The condition is, say - State is Active and Priority is 1 and Current date and time is more than 45 minutes after Last Modified Date. Any ideas on how this can be done? Regards, Rashmi No virus found in this outgoing message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.21.8/1338 - Release Date: 3/21/2008 5:52 PM ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
