Or... <note> I do not suggest using Direct SQL against ARS's db's at any time. It is generally a sign that you are doing something that is "not really supported" and likely better done with an External API program. </note>
A single ARS form can result in zero or more tables to hold it's data depending on the form "Type", the data elements that are defined on the form and the RDBMS your using as your data store. The details are detailed, but here are the basics.... If a Form holds data on the RDBMS (Type = "Regular") then it will have at a minimum a T and an H table. These tables are "glued" together with a schemaId. So a single form's tables would be T5 and H5, or T100 and H100. (The tables share the same number with a different letter prefix identify what kind of data is in that table.) "T" = Basic data (Char, date, int, real, Floating, etc...) "H" = "History data" This is the Status History data for a form. "B" = "Binary data" (attachment field, attached files in a compressed form) in some cases you will also see tables like: T<schemaId>C123456 ( I think... do not have the docs in front of me right now.) = RDBMS's that do not allow more than one "unlimited width column in a single table. So... You can go to the arschema table to get the schemaId value at run time. select schemaId from arschema where name = '$SCHEMA$' That should return the right schemaId number for the T table 100% of the time. ( And this approach would guard against a form rename and will work on any ARS server. ) Then Set field Action: field_where_schemaId_was_returned = "T" +$field_where_schemaId_was_returned$ Then a Set Field SQL action: Like select <bla...> from $field_where_schemaId_was_returned$ where <bla...> So this approach would continue to work for workflow moves from Development to Testing to Production and form rename events too. The only "down side" is that it takes an extra Direct SQL Action, a temp field char field and you do not have access to the Status History fields in the T table. (For those you have to go to the H table, or the view to get T and H data together. Or you can do a compound select "AKA Join" statement too.) -- Carey Matthew Black Remedy Skilled Professional (RSP) ARS = Action Request System(Remedy) Solution = People + Process + Tools Fast, Accurate, Cheap.... Pick two. Never ascribe to malice, that which can be explained by incompetence. On 8/27/06, Dave Saville <[EMAIL PROTECTED]> wrote:
On Sun, 27 Aug 2006 09:05:50 -0700, Mimi Lakew wrote: >Hi Lisr, > > I am trying to use Direct SQL action which I never used before. My understanding is, on the SQL statement the table name has to be defined. Like "select * from tablename" I am trying to search an ARS form, is there anyway to find what the table name is on the database for the form? The *real* table name will be Tnnn where nnn are digits. However Remedy sets up "views" and you can use the form name with the proviso that any non alphanumeric character in the name will be changed to an underscore. So a form called "My Form" would be My_Form. This also applies to field names. It is better to use these views as they are consistent across the same tables on different installations of ARS. "MY Table" might be T123 one box and T456 on another - a maintenance nightmare and a wonderful source of error :-) HTH -- Regards Dave Saville
_______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
