Here's something I posted to the list back in 2004. It might help in this
situation...
The MS SQL Server Solution:
Maybe there's an easier way to do it, but here's what I came up with. I
use a lot of charindex and substring calls to chop out the field label.
Notice that the propLong field has to be converted to a varchar data
type so I can perform normal string operations. Here it is:
----------------------
select
substring(
substring(convert(varchar(255), propLong),
charindex('\20\4\', (convert(varchar(255), propLong))) + 6,
datalength(convert(varchar(255), proplong))),
charindex('\', substring(convert(varchar(255), propLong),
charindex('\20\4\', (convert(varchar(255), propLong))) + 6,
datalength(convert(varchar(255), proplong)))) + 1,
charindex('\',
substring(
substring(convert(varchar(255), propLong),
charindex('\20\4\', (convert(varchar(255), propLong))) + 6,
datalength(convert(varchar(255), proplong))),
charindex('\', substring(convert(varchar(255), propLong),
charindex('\20\4\', (convert(varchar(255), propLong))) + 6,
datalength(convert(varchar(255), proplong)))) + 1, 255)) - 1 )
Label_Value
from
field_dispprop
where
fieldId='536870913'
and
schemaId=(
select
schemaid
from
arschema
where
name='Test01')
----------------------
Assume that the database name of the field is 'Test01' and its field ID is
536870913.
Tim Widowfield
[EMAIL PROTECTED]
v: 937-878-9045
f: 937-878-9055
m: 937-369-7012
http://www.widowfield.com
----- Original Message ----
From: Joe D'Souza <[EMAIL PROTECTED]>
To: [email protected]
Sent: Monday, October 2, 2006 9:43:06 PM
Subject: Re: [ARSLIST] Menu to retreive field label names...under certain
conditions
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
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
