Tim, Uh... and you have to parse the right part of a "display property" out of one of two different columns in that field_dispprop table. (If memory serves.)
I started to try to reply the other day.. but it is still a bit incomplete... but I think it still might spark a few ideas out there as is... so here it is... incomplete and all: " An interesting request. How about this as an idea... Create a view form on top of a RDMB view that you create. The RDBMS view would look at field, field_prop (or is it field_props or field_properties ?), arschema and fields_by_vui (the table that I will describe next) to get the following set of data columns where your special tag is the leading part of the field.helpText column. aschema.name, vuiId, Vui_name, field.fieldId, fieldLabel_for_vui field.helpText The "fields_by_vui form" is no small task, but very possible too. The field Label for the given views. And think you will be best served by doing that part via either an escalation and a chunk of filters or an ARS API program to populate a form that maps. aschema.name, field.fieldId, field_prop.vuiId, Vui_name, (not ID for easier use in the menu) fieldLabel_for_vui You can look to the special $PROCESS$ command "Application-Map-Ids-To-Names-L <form> <VUI> <string>" for help if you want to make your API/Filter work a whole lot easier. The only "hard part" that I see is knowing when you re-cache the "fields_by_vui form" data. that could be done manually if your system is fairly stable, or you could automate it with the Server Event form and a filter or two. Then a Search menu should be able to use the $ SCHEMA $ keyword and the $VUI$ keyword to return the right fields for the user. You might even discover that the performance of some of those joins is so poor that you just want to build out "fields_by_vui form" a bit more and just join it with field to get the Helptext. Or just dump the "prefix" idea in the helptext and use "fields_by_vui form" as the "control" for what field to process and just check the modified data of your "fields_by_vui form" record vs the field table to grab updated help text nightly. Lots of ways to skin this cat, but non that I see as "just a menu". ( I think your requirements are a bit to ... specific ... for what ARS Objects are designed for. :) I doubt that is what you wanted to hear, but maybe it will help anyway. " -- 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 10/2/06, Joe D'Souza <[EMAIL PROTECTED]> wrote:
That's because you are querying the field table where the fieldname column stores the database name of the field. What you should be doing is run a query on the field_dispprop table to get the field name for the view by seeking the vuid from the vui table.. so basically your sql statement will be querying the field_dispprop table using the results of what you get from selecting the vuid from the vui table, using the results of what you get by selecting the field id from the field table... so its a nested select statemet just like you have written but a couple of levels more deep.. Cheers Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] Behalf Of Timothy Powell Sent: Monday, October 02, 2006 5:00 PM To: arslist@ARSLIST.ORG Subject: Re: Menu to retreive field label names...under certain conditions I didn't see anything on this...since that's so rare...I'm assuming it is because it was posted Friday afternoon....everybody took off early. :-) Reposting. And here's my sql statement on my menu: select a.fieldname, a.fieldid from field a where helptext like 'Field Level Help%' and a.schemaid = (select schemaid from arschema where name = '$SCHEMA$') and exists (select b.fieldid from field_dispprop b where a.schemaid = b.schemaid and a.fieldid = b.fieldid and b.vuiid = (select c.vuiid from vui c where c.schemaid = a.schemaid and c.vuiname = '$VUI$')) Like I stated, that pulls the right fields, but displays the database name of the fields. If you see where I can do it different and get the label names, I'd appreciate it. Thanks. Tim -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Timothy Powell Sent: Friday, September 29, 2006 2:34 PM To: arslist@ARSLIST.ORG Subject: Menu to retreive field label names...under certain conditions Hi list. I've researched the past list entries with negative results. I want to provide sight impaired users with a field that contains a list of all fields on a form that have a defined Help Text. To do this, I obviously need some sort of menu to give those users the ability to select from. I know that you can build a Data Dictionary menu that displays the field labels on a form, and I can select the type, such as if I want character fields, or tables, etc. The problem there is that the menu displays all fields of the selected type, regardless if their hidden, visible, etc. I only want to select those that have help text. We have built a SQL menu that looks up the field where we have defined specific leading Help Text. For example, on any given form, all fields that I want to display in the menu have a Help Text leading value of "Help Text For". The SQL menu does a lookup on the fields table and selects the schemaid that matches the schema I am on, evaluates the fields related to that schemaid that have this leading help text and then presents me a list of those fields where the Help Text is prefixed in that manner. Problem partially solved. However, the trouble with that is, the menu pulls back the database name of the field, NOT the label name. As we know, database names are sometimes very cryptic, especially in the ITSM forms. Does anybody know of a way to build a menu that will: 1) At a minimum, only show me the fields where the Help Text has my prefix, but present me the label names instead of the database names? 2) Optimally, not only show me the fields where the Help Text has my prefix, and present me the label names instead of the database name but also limit that to fields that are specific to the VUI I am currently on? ARS 6.3 Patch 17 ITSM 5.5 SQL Server 2000 Microsoft Server 2003 Thanks in advance, Tim Powell
_______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
