I'm not a DBA, but can get around in SQL/Oracle fairly well. Can you clarify what you mean by: "...using pipes - | - instead of percent signs!"
I'm aware of double pipes that get used for concatenation, but I'm curious how you use them "instead of" percent signs?
Thanks,
Thad
"Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away." - Antoine de Saint-Exupéry
| "Rebecca Hammond"
<[EMAIL PROTECTED]>
Sent by: "Action Request System discussion list(ARSList)" <arslist@ARSLIST.ORG> 09/28/2006 03:28 PM
|
|
**
This is great – although we’re in Prod. Code lockdown, so I can’t do this now ;-) However, I’m going to definitely save it and try to implement as an enhancement! Thank you!
I did figure it out, in SQL*Plus, at least to find the affected records – if anyone’s interested, I can send you the query syntax (darn Oracle, and using pipes - | - instead of percent signs!)
From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Thad Esser
Sent: Thursday, September 28, 2006 1:44 PM
To: arslist@ARSLIST.ORG
Subject: Re: Break out Member List from AP:Role form (Approval Server)?
**
Rebecca,
It is what it would like in a set fields of a filter. For my setup, I have a filter guide on the SHR:People form that when triggered, goes through all the forms and deletes the users or updates records associated with them as appropriate. In the case of AP:Role, I push a value to a "Command field" (display only char field) on the AP:Role form, where a filter detects that and does the set fields below. Specifically:
--- The "Delete Person" filter guide searches the AP:Role form for roles where the Login Name is the only value in the member list, and then displays an error if it finds any,
--- Having passed the above, the next filter pushes to the AP:Role form:
Push If:
( 'Member List' LIKE (( "%" + $Login Name$) + "%" )) AND ( 'Status' = "Active")
If none, take no action. If any, modify all.
Command = "REMOVE-USER-FROM-MEMBER-LIST " + $Login Name$
Change History = (($Full Name$ + " (") + $Login Name$) + ") is being removed from Remedy and has been removed from the list of approvers for this role."
Then over on the AP:Role form, I have the following filter on Modify:
Run If:
'Command' LIKE "REMOVE-USER-FROM-MEMBER-LIST %"
--- Action 1, Set Fields:
Command = ( ";" + REPLACE($Command$, "REMOVE-USER-FROM-MEMBER-LIST ", "")) + ";"
Member List = ( ";" + $Member List$) + ";"
(parses the login name out of the command field and adds semi-colons so we can avoid partial matches, e.g. jdoening matching when trying to remove jdoe)
--- Action 2, Set Fields:
Member List = REPLACE($Member List$, $Command$, ";")
(pulls out the login name, replacing it with a semi-colon so that login names are still separated properly)
--- Action 3, Set Fields:
Member List = SUBSTR($Member List$, 1, LENGTH($Member List$) - 2)
(cuts off the leading and trailing semi-colons that we added in action 1 to make our search easier)
I hope that clarifies things for you.
Thad
"Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away." - Antoine de Saint-Exupéry
| "Rebecca Hammond"
<[EMAIL PROTECTED]> Sent by: "Action Request System discussion list(ARSList)" <arslist@ARSLIST.ORG> 09/28/2006 06:44 AM
|
|
I may not have had enough coffee this morning...but this, below, is what it
would look like in a piece of Remedy workflow, or a SQL command at the DB
level (or both)?
On Wed, 27 Sep 2006 10:36:40 -0700, Thad Esser <[EMAIL PROTECTED]> wrote:
>Try:
>
>REPLACE($Member List$, $Login Name$, "")
>followed by
>REPLACE($Member List$, ";;", ";")
>
>(you could combine them into one if you wanted)
>
>Thad
>"Perfection is achieved, not when there is nothing more to add, but when
>there is nothing left to take away." - Antoine de Saint-Exupéry
>
>
>
>"Rebecca Hammond" <[EMAIL PROTECTED]>
>Sent by: "Action Request System discussion list(ARSList)"
><arslist@ARSLIST.ORG>
>09/27/2006 10:28 AM
>Please respond to
>arslist@ARSLIST.ORG
>
>
>To
>arslist@ARSLIST.ORG
>cc
>
>Subject
>Break out Member List from AP:Role form (Approval Server)?
>
>
>
>
>
>
>Hi all!
>
>I'm going through the process of trying to remove a whole bunch (hundreds)
>
>of People/User records - these are people who no longer work in my
>organization.
>
>I know if I use the App. Config. Console, this will blow the users out of
>places like SHR:Assignment. However, I've found that there can be
>problems
>if a user is deleted but their login name is still listed as part of a
>Member List in the AP:Role form. In there, the login is stored with other
>
>members of the role, separated by a ;
>
>Does anyone have any tricks for editing the AP:Role records when a User
>account is deleted?
>
>Thanks!
>
>___________________________________________________________________________
____
>UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
>
>
>
>***IMPORTANT NOTICE: This communication, including any attachment,
contains information that may be confidential or privileged, and is
intended solely for the entity or individual to whom it is addressed. If
you are not the intended recipient, you should delete this message and are
hereby notified that any disclosure, copying, or distribution of this
message is strictly prohibited. Nothing in this email, including any
attachment, is intended to be a legally binding signature.***
>
>___________________________________________________________________________
____
>UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
>
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
***IMPORTANT NOTICE: This communication,
including any attachment, contains information that may be confidential
or privileged, and is intended solely for the entity or individual to whom
it is addressed. If you are not the intended recipient, you should delete
this message and are hereby notified that any disclosure, copying, or distribution
of this message is strictly prohibited. Nothing in this email, including
any attachment, is intended to be a legally binding signature.***
__20060125_______________________This
posting was submitted with HTML in it___
__20060125_______________________This posting was submitted with HTML in it___
***IMPORTANT NOTICE: This communication, including any attachment, contains information that may be confidential or privileged, and is intended solely for the entity or individual to whom it is addressed. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message is strictly prohibited. Nothing in this email, including any attachment, is intended to be a legally binding signature.***
