Since it is a view of an external DB do the following. Create a local view of the external DB. Inside the local view add Rownum as a column in the query from the external database (use LPAD(rownum,15,'0') to create a 15 character "0" padded columm).
Have your Remedy View form use the local view as it's source and the Rownum column as the unique key. Personally I never create a Remedy view form across a dbLink. I always make a local db view. This way when I move a form from Dev to Test to Production the form does not change even if the dbLink has to change between environments. Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 4:28 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Unfortunately, neither of these solutions creates a column that is a suitable key field. From the documentation: The database table must have a column (field) that enforces non-null and unique values. This column will act as the Request ID. If the administrator chooses a column that is non-unique or allows nulls, data corruption will probably occur. The Request ID field must be an integer field or a character field that is no less than 6 and no greater than 15 characters. Otherwise, the Key field list will be empty, and you will not be able to create the view form. My DBA and I are still trying to create a column that meets this requirement. -Paul -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Friday, September 07, 2007 12:14 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Add the Column ROWNUM to the view Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Nall, Roger Sent: Friday, September 07, 2007 2:11 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? If it truly a view then you should be able to add a column to the view that will act as Request ID. Make sure that you increment the column by 1 for each record. HTH, Roger A. Nall Manager, OSSNMS Remedy T-Mobile USA Desk: 813-348-2556 Cell: 973-652-6723 FAX: 813-348-2565 sf49fanv AIM IM RogerNall Yahoo IM -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 3:08 PM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? Ah yes, view forms. (You can tell I'm new at this.) Unfortunately, while creating the view, none of my database fields qualify as an index (tripping up on the 6-15 char window). I'm trying to work around this in oracle (the table is actually a view of an external DB so I have no control of that varchar limit), any other methods of implementing are appreciated. Thanks! -Paul -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Opela, Gary L Contr OC-ALC/ITMA Sent: Friday, September 07, 2007 11:43 AM To: arslist@ARSLIST.ORG Subject: Re: AR 6.3 How to Populate a Table with a SQL Query? You cannot populate a table via direct SQL (unfortunately). You can, however, create a remedy view and point it to a database SQL view of what you need, then point the remedy table to the view you created. Now, to pass a dynamic variable in the table qualification field, check out EXTERNAL() function in the AR Admin basic book. Thanks, Gary Opela, Jr Sr. Remedy Developer Leader Communications, Inc. 405 736 3211 -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Paul Blasquez Sent: Friday, September 07, 2007 12:16 PM To: arslist@ARSLIST.ORG Subject: AR 6.3 How to Populate a Table with a SQL Query? Hello, I have a 'Customer Data' page on my main form in which there is one read/write character field named 'Company Name+', and several read-only character fields. I have set up a menu that reads from an Oracle View SQL query our database of customers, attached it to the 'Customer Data' field. and set up a couple active links so that when a search term is entered into Customer Data, or a Customer is chosen from the menu, the read-only fields are filled in with the appropriate SQL result columns. Now, my issue is that I would also like to have a table on the page that I can populate with the customer's contact list. In order to do this, I must make another SQL query, using the 'customer_id' from the original 'company_name' query to key which rows to pull out. Any ideas on workflow that could pass the customer_id variable to a new SQL query, and then populate a table with several rows of data? I can't seem to work out a solution using menus/forms/active links. Thank you! _______________________ Paul Blasquez [EMAIL PROTECTED] _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
