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: [email protected] 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: [email protected] 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 ____________________________________________________________________________ ___ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/458 - Release Date: 9/27/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/458 - Release Date: 9/27/2006 _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
