I figured it was too complex for "just a menu"....but hey, I thought I'd
give it a try. :-)
Thanks for the replies all.
Actually, I dug some more (Thanks Thad) and found some stuff Tim Widowfield
posted April 24, 2004. I'm going to attempt to use some of that in an
escalation and some filters to build data and store it in a table. Then I'll
just drive the menu off of that data stored in the table. I'll let you know
if it works.
Thanks,
Tim
The subject of Tim Widowfield's post was:
"Field Label - Where is it kept ?"
Here's the content:
Date: Sat, 24 Apr 2004 03:43:37 -0400
From: Tim Widowfield <[EMAIL PROTECTED]>
Subject: Re: Field Label - Where is it kept ?
Phillip,
I keep getting asked this question. When people peruse the ARSLIST
archives, they find an old message thread where I posted some SQL scripts
that pull out field labels, but then they're frustrated when they find the
attachments are missing.
Well, I'm just going to copy the SQL statements right here in the body of
this message, and that should put the issue to rest.
First of all, let's start with the standard caveats. Don't do this kind of
thing unless you really have to. Remedy at any time can change the way it
stores information at the database level. The best way to get data stored
in ARS is via the API.
Now, as you probably know, the field label is a view-level attribute.
You'll find it in the propLong column of the field_dispprop table.
Suppose you have a character field on a form, and on all three views it has
a different field label. How would you retrieve them? To start with, you'd
run a query that looks something like this:
----------------------
select
propLong
from
field_dispprop
where
fieldId='536870913'
and
schemaId=(
select
schemaid
from
arschema
where
name='Test01')
----------------------
Here's the output from my test form, truncated for educational purposes:
propLong
-----------------------------------------------------------------...
20\14\40\1\170\40\0\20\4\4\Name\3\41\2\41400\4400\54337\7900\41\5...
20\14\40\1\170\40\0\20\4\3\Nom\3\41\2\41400\4400\54337\7900\4\\5\...
20\14\40\1\170\40\0\20\4\26\Put Your Name Here, Please\3\41\2\00\...
If we had to put into words where to find the label, we might say:
1. The field label value starts one character after the first backslash
following the string, '\20\4\'.
2. The field label value ends at the integer value that follows '\20\4\',
or one character before the next backslash.
I found it to be easier to find the next backslash than to chop out
characters between backslashes and covert them to integers.
***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')
----------------------
Is that ugly, or what? Here were my results:
Label_Value
-----------------------------------------------------------------...
Name
Nom
Put Your Name Here, Please
(3 row(s) affected)
***The Oracle Solution:
Oracle is a little easier, because you don't have to convert the CLOB into
some other data type. Instead, you use special functions for the LOB field
type. Here's the original SQL*Plus script I posted a long, long time ago.
----------------------
-- getlabel_long.sql
--
-- Author: Tim Widowfield, [EMAIL PROTECTED]
--
-- If the field label is stored in the proplong field of field_disprop
-- use this script to pull out the value.
--
-- Hide the replacement message
SET VER OFF
-- Do the query
SELECT
dbms_lob.SUBSTR(proplong,
(dbms_lob.INSTR(proplong, '\', (dbms_lob.INSTR(proplong, '20\4\')),
3) + 2),
(dbms_lob.INSTR(proplong, '\', (dbms_lob.INSTR(proplong, '20\4\')), 4)) -
(dbms_lob.INSTR(proplong, '\', (dbms_lob.INSTR(proplong, '20\4\')),
3) + 2))
FieldLabel
FROM
field_dispprop
WHERE schemaid='&schemaid' and vuiid='&vuiid' and fieldid='&fieldid'
/
----------------------
I get nostalgic when I see that old DNACo email address. (Sniffle.) See the
note about "if" the label is stored in propLong? In early versions of ARS,
the display properties were often found in propShort.
Nowadays I'm pretty sure they're *always* stored in propLong.
That's all, folks.
--
Tim Widowfield, UWIP
http://www.uwip.com
937-878-9045
--
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:[EMAIL PROTECTED] On Behalf Of Carey Matthew Black
Sent: Monday, October 02, 2006 11:28 PM
To: [email protected]
Subject: Re: Menu to retreive field label names...under certain conditions
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: [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
