arsperl-users

[Arsperl-users] specifying sort order ...

[Andrew H. <an...@hi...>]

Hello everyone:

I realize this is a little off topic, as it doesn't relate directly to 
arsperl, but I'm sure someone here knows the answer:

the short question:
	
	is there some way, inside an ARS QBE string, to specify the order in 
which results
	should be returned, similar to SQL's "order by" clause?

the long question:

I need to create a filter that will check for duplicate entries in a 
form. When a new record is created in the form, the filter should fire, 
and place the most recent duplicate entry's entryID in a field in the 
new record.

This should be no sweat, just select entries with identical field 
values, sort by date descending and "if multiple entries match: use 
first", no? My problem is that I can't figure how to get remedy to 
order by date descending. I tried setting this in the sort pane of the 
form's 'properties' dialog, to no avail. I had thought to just use a 
straight SQL query, but then I'll have to use big nasty oracle decode() 
blocks to match dropdown menu value, and I don't fancy having to find 
each and every one of those and modifiy it if I have to add another 
menu value.

It's a long story, but the company I'm working for won't pay for a 
supportweb account, and I can't seem to google ANY documentation on 
this.

I call upon the vast knowledge of the group in my pitiful struggle.

thank you all in advance,

-Andrew ;-)

###################################################
## Andrew N. Hicox
## Hicox Information Systems Development
## [voice]: 703-367-9085
## [email]: an...@hi...
## [web]: http://hicox.com
##
## Rome built daily.
###################################################

Re: [Arsperl-users] specifying sort order ...

[Carey M. B. <bla...@gm...>]

Andrew,

  Since this is not an ARSPerl question, but definitely an ARS
question then it is likely better suited for the ARSlist.
(www.arslist.org) In the future you might try there for such ARS
"topics". (I have cross posted my response.)


But here is my two cents on the question:

1) ARS only allows you to have one "order by" clause per form. (And it
is applied to all queries on the form.) It is the form's property for
"Sort order".

   This is a bit inflexible, but can be worked around with filter
looping or direct SQL approaches.


2) Instead of using RDBMS specific solutions for your SQL... use ARS
tools to do simpler translations to keep things a bit more portable.
(Just in case you ever need to switch from Oracle to DB2, or DB2 to
Informix, etc...)


Who to do that you ask... read on...

Filter looping:

With a Goto:
  Filter looping can repeatedly walk the table to get the "first
matching" record and you may be able to use the define "form sort
order" to allow you to "select the next bigger" (or smaller) record.
This is somewhat dependent on the sort order providing some
integer/character value that will consistently order the records and
prevent you from seeing the same record in the filter looping process.
The default sort order for the form is Ascending 'Entry ID'. (field 1)
So if you have no other sort settings on the form, use that order to
your advantage and keep the last matching 'Entry ID' value and in your
loop find the next record using "bla... bla .. bla... AND 'Entry ID' >
$tempEntryID$".

With a table field/Filter guide over a table field:
   However, if your on an ARS version that does server side table
fields then you might also perfer to set up a table field and walk it
instead of walking the form. A table field can also set a sort order
for the data. (But a SetField action can not.  A fairly silly
inconsistency that we all hope will we corrected in future ARS
versions.)


Direct SQL approaches:

   Selection fields are a bit of a pain when it come to using their
values in direct SQL. This is because ARS "sees" what the
number/mapped text values are in it's design. But there are a few ways
to help translate things to make them much easier to deal with in
future modifications.

) Selection field too temp integer field.
   This will get the numeric value for the selected value of the
selection field. The temp field could later be used in Direct SQL, Run
process, etc.. actions. This is nice when you want the number from a
selection field.

) Selection field to a temp Character field.
  This will get the string value for the selected value of the selection fi=
eld.

When changes are made to the source selection field in the cases
above... there is no additional work needed for these solutions.


) Selection field to Selection field.
    This is nice to translate "Label" values to their "actual values".
Then you can move the value to a character field to show the "actual
values" as text values.
   Sure this requires you to maintain two selection fields when
changes are made, but it is a better option, IMHO, than using RDBMS
value translation/decode options.


Also... Form names can/do change over time. So you might want to also
lookup the value from a config form so that data can be updated to
"fix" the SQL when you later change the form name and forget to
correct your SQL in the workflow. Changing data during the day is not
a big deal. Changing an ARS object outside of a change window... could
get someone in trouble. :)





Oh and at the risk of this sounding rude.... but I am only poking a
bit of fun about this.

"Rome built daily."  (Apparenly not in ARS... :)

--=20
Carey Matthew Black
Remedy Skilled Professional (RSP)
ARS =3D Action Request System(Remedy)

Solution =3D People + Process + Tools
Fast, Accurate, Cheap.... Pick two.
Never ascribe to malice, that which can be explained by incompetence.

On 6/28/05, Andrew Hicox <an...@hi...> wrote:
> Hello everyone:
>=20
> I realize this is a little off topic, as it doesn't relate directly to
> arsperl, but I'm sure someone here knows the answer:
>=20
> the short question:
>=20
>        is there some way, inside an ARS QBE string, to specify the order =
in
> which results
>        should be returned, similar to SQL's "order by" clause?
>=20
> the long question:
>=20
> I need to create a filter that will check for duplicate entries in a
> form. When a new record is created in the form, the filter should fire,
> and place the most recent duplicate entry's entryID in a field in the
> new record.
>=20
> This should be no sweat, just select entries with identical field
> values, sort by date descending and "if multiple entries match: use
> first", no? My problem is that I can't figure how to get remedy to
> order by date descending. I tried setting this in the sort pane of the
> form's 'properties' dialog, to no avail. I had thought to just use a
> straight SQL query, but then I'll have to use big nasty oracle decode()
> blocks to match dropdown menu value, and I don't fancy having to find
> each and every one of those and modifiy it if I have to add another
> menu value.
>=20
> It's a long story, but the company I'm working for won't pay for a
> supportweb account, and I can't seem to google ANY documentation on
> this.
>=20
> I call upon the vast knowledge of the group in my pitiful struggle.
>=20
> thank you all in advance,
>=20
> -Andrew ;-)
>=20
> ###################################################
> ## Andrew N. Hicox
> ## Hicox Information Systems Development
> ## [voice]: 703-367-9085
> ## [email]: an...@hi...
> ## [web]: http://hicox.com
> ##
> ## Rome built daily.
###################################################