Re: Populating Table field with sql query result
Hi Ravi; I've not had a reason to do this yet, so I can't offer specifics (happily, there's been lots of good and related information over the last couple of days--and much more earlier, I'm sure). You'll need a view or vendor form set up so that ARS can see the table from the other database--after that, you can probably run queries against the view/vendor form in a similar way. View forms generally require a link specified in ar.cfg with the connection string of the remote DB, and vendor forms require an ARDBC plug-in to use. If you can't get direct access to the remote DB, maybe you can get the other DBA to ship you the data in batches to import (eeew)? Sorry I couldn't be more help, but if you give these grand ARS listers more detail like remote DB type, the access you're likely to have to it, ARS specs...well, all that, I bet they've more clues :) ~james -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Ravi Sent: Thursday, May 22, 2008 8:43 PM To: arslist@ARSLIST.ORG Subject: Re: Populating Table field with sql query result James: I have been trying to do something similar. One question. If the table on which I want to run the sql query is on a different database, what do I have to do within Remedy so it can connect to a table in that database. Thanks Ravi Russell, James C wrote: ** Hello Victor; I don't know that this is what you're looking for, but I run a series of sql queries in a single escalation to populate a 'current state' kinda form--I'm using one query per field; might not be the most effective way in the world, but it works. This has MS SQL date conversions in--oracle is different and a bit more cryptic (IMO--I've got those around somewhere if you need 'em): Here's a Set Fields example that works just fine: Read Value for Field From: SQL SQL Command: SELECT COUNT(1) FROM Your_Form_Name WHERE (Create_Date = DATEDIFF(s, '19700101', DATEADD(mm,-30,getdate( AND (rb_TicketType = 5) If No Requests Match: Set fields to NULL If Multiple Records Match: Use first matching Fields: Name: cntTiks Value: $1$ ...there's no reason in the world that you couldn't make a set fields with an sql statement like: SELECT thing1, thing2, thing3, ... ,thingN FROM Your_Form WHERE Field_name = 'char_value' OR Field_Name2 84600 (or whatever--could do joins and unions against multiple forms if necessary) ..and then the Names/values in the 'Fields' section of the set fields is like: Name: Field1 Value: $1$ Name: Field2 Value: $2$ Name: Field3 Value: $3$ : : Name: FieldN Value: $N$ When you're choosing a value for the field, the drop-down list will give you an option to choose a value x from the 'SQL Result Column,' where x is a number between 1 and N (the number of fields you're pulling in your select statement). Erm. For whatever it's worth. HTH ~james -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Victor Sent: Sunday, May 18, 2008 12:47 PM To: arslist@ARSLIST.ORG Subject: Populating Table field with sql query result Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Populating Table field with sql query result
On Sunday 18 of May 2008 22:39:03 Grooms, Frederick W wrote: Have your SQL Query populate a table in the database and have a View form pointing to that table. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Victor Sent: Sunday, May 18, 2008 12:47 PM To: arslist@ARSLIST.ORG Subject: Populating Table field with sql query result Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are Thank you Fred - but I'm afraid this approach is unacceptable. The idea is to introduce a (sort of) summary button on system class form. On clicking this button a summary page describing this particular CI together its dependencies,components and components of its dependencies (in a single Table form) will be displayed. Remedy ARS 6.3 Oracle 9i database MS IIS Windows 2000 Any suggestions please? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Populating Table field with sql query result
Victor, It is not the same as an ARS table field... but What if you write workflow that walks the data and builds a dynamic HTML page that is displayed in a View field? (Which by the way is basically what the CI viewer does, only it uses a Data Visualization Field which has a few more bells and whistles than a plain old View field(iframe).) Do you think this approach could work for the desired UI? -- Carey Matthew Black Remedy Skilled Professional (RSP) ARS = Action Request System(Remedy) Love, then teach Solution = People + Process + Tools Fast, Accurate, Cheap Pick two. On Thu, May 22, 2008 at 5:44 AM, Victor [EMAIL PROTECTED] wrote: On Sunday 18 of May 2008 22:39:03 Grooms, Frederick W wrote: Have your SQL Query populate a table in the database and have a View form pointing to that table. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Victor Sent: Sunday, May 18, 2008 12:47 PM To: arslist@ARSLIST.ORG Subject: Populating Table field with sql query result Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are Thank you Fred - but I'm afraid this approach is unacceptable. The idea is to introduce a (sort of) summary button on system class form. On clicking this button a summary page describing this particular CI together its dependencies,components and components of its dependencies (in a single Table form) will be displayed. Remedy ARS 6.3 Oracle 9i database MS IIS Windows 2000 Any suggestions please? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Populating Table field with sql query result
** Hello Victor; I don't know that this is what you're looking for, but I run a series of sql queries in a single escalation to populate a 'current state' kinda form--I'm using one query per field; might not be the most effective way in the world, but it works. This has MS SQL date conversions in--oracle is different and a bit more cryptic (IMO--I've got those around somewhere if you need 'em): Here's a Set Fields example that works just fine: Read Value for Field From: SQL SQL Command: SELECT COUNT(1) FROM Your_Form_Name WHERE (Create_Date = DATEDIFF(s, '19700101', DATEADD(mm,-30,getdate( AND (rb_TicketType = 5) If No Requests Match: Set fields to NULL If Multiple Records Match: Use first matching Fields: Name: cntTiks Value: $1$ ...there's no reason in the world that you couldn't make a set fields with an sql statement like: SELECT thing1, thing2, thing3, ... ,thingN FROM Your_Form WHERE Field_name = 'char_value' OR Field_Name2 84600 (or whatever--could do joins and unions against multiple forms if necessary) ..and then the Names/values in the 'Fields' section of the set fields is like: Name: Field1 Value: $1$ Name: Field2 Value: $2$ Name: Field3 Value: $3$ : : Name: FieldN Value: $N$ When you're choosing a value for the field, the drop-down list will give you an option to choose a value x from the 'SQL Result Column,' where x is a number between 1 and N (the number of fields you're pulling in your select statement). Erm. For whatever it's worth. HTH ~james -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Victor Sent: Sunday, May 18, 2008 12:47 PM To: arslist@ARSLIST.ORG Subject: Populating Table field with sql query result Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Populating Table field with sql query result
James: I have been trying to do something similar. One question. If the table on which I want to run the sql query is on a different database, what do I have to do within Remedy so it can connect to a table in that database. Thanks Ravi Russell, James C wrote: ** Hello Victor; I don't know that this is what you're looking for, but I run a series of sql queries in a single escalation to populate a 'current state' kinda form--I'm using one query per field; might not be the most effective way in the world, but it works. This has MS SQL date conversions in--oracle is different and a bit more cryptic (IMO--I've got those around somewhere if you need 'em): Here's a Set Fields example that works just fine: Read Value for Field From: SQL SQL Command: SELECT COUNT(1) FROM Your_Form_Name WHERE (Create_Date = DATEDIFF(s, '19700101', DATEADD(mm,-30,getdate( AND (rb_TicketType = 5) If No Requests Match: Set fields to NULL If Multiple Records Match: Use first matching Fields: Name: cntTiks Value: $1$ ...there's no reason in the world that you couldn't make a set fields with an sql statement like: SELECT thing1, thing2, thing3, ... ,thingN FROM Your_Form WHERE Field_name = 'char_value' OR Field_Name2 84600 (or whatever--could do joins and unions against multiple forms if necessary) ..and then the Names/values in the 'Fields' section of the set fields is like: Name: Field1 Value: $1$ Name: Field2 Value: $2$ Name: Field3 Value: $3$ : : Name: FieldN Value: $N$ When you're choosing a value for the field, the drop-down list will give you an option to choose a value x from the 'SQL Result Column,' where x is a number between 1 and N (the number of fields you're pulling in your select statement). Erm. For whatever it's worth. HTH ~james -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Victor Sent: Sunday, May 18, 2008 12:47 PM To: arslist@ARSLIST.ORG Subject: Populating Table field with sql query result Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Populating Table field with sql query result
*Hi Victor, *On search in doc, I found related doc for you. To pull information from external tables, you can use the Set Fields action with the Read Value for Field From set to SQL. This allows you to send an SQL |SELECT| command to the database and assign the return values to AR System fields. Observe the following general rules for using SQL commands: * You need not use every value that is returned from the SQL command, but you must use at least one. * You can use the same value in more than one field. * You can issue only one SQL command per action. You cannot enter two commands separated by a semicolon and have both commands run. To run a set of commands, create separate actions, or create a stored procedure and run that. (Stored procedures do not return values.) * Turn on AR System server SQL logging to debug the SQL syntax if it returns unexpected values or results. A good debugging strategy is to start an SQL interpreter (for example, isql for Sybase, SQL*Plus for Oracle^® , Command Center for DB2, or Query Analyzer for SQL Server) and to enter the same SQL command directly into the database to verify its validity. * Because there is no error checking on the SQL statement, run the SQL statement directly against the database (as a test) before you enter it into the SQL Command field. You can then copy and paste the tested SQL command directly into the SQL Command field. * If the SQL operation fails, an AR System error message and the underlying database error message appear * For more information about Set Fields action with SQL, see the /Workflow Objects/ guide. Hope this helps... Regards, *Sandeep Vyom Labs Pvt. Ltd. An ISO 2 certified company. Consulting | Outsourcing | Training || BMC Remedy BSM | ITIL Web : www.vyomlabs.com * Victor wrote: Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
Re: Populating Table field with sql query result
Have your SQL Query populate a table in the database and have a View form pointing to that table. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Victor Sent: Sunday, May 18, 2008 12:47 PM To: arslist@ARSLIST.ORG Subject: Populating Table field with sql query result Hello Listers, It is possible to populate a table field with the result from an sql query? Any idea how this could be done? Victor ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: Where the Answers Are
