Be careful with the solutions provided because login names that are substrings of other login names will result in broken member lists.
For example, consider the following member value: AGRAMS;AGRAMS2 Now, if you replage AGRAMS with '', you get the following: ;2 A safer approach is to add the delimiter to the beginning and end of the string using a set fields: ex., ;AGRAMS;AGRAMS2; Then replace the leading and trailing delimiter and value with the delimiter. i.e., replace ";AGRAMS;" with ";" The result following: ex., ;AGRAMS2; You can then proceed to trim the leading and trailing delimiters. Axton Grams Thad Esser wrote: > ** > 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 > Please respond to > arslist@ARSLIST.ORG > > > > To > arslist@ARSLIST.ORG > cc > > Subject > Re: Break out Member List from AP:Role form (Approval Server)? > > > > > > > > > ** > 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:* [EMAIL PROTECTED] > 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 > > > Please respond to > arslist@ARSLIST.ORG > > > > To > arslist@ARSLIST.ORG > cc > > Subject > Re: Break out Member List from AP:Role form (Approval Server)? > > > > > > > > > > > > > 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.*** > > __20060125_______________________This posting was submitted with HTML in > it___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
