First, I changed Jason's subject line -- was OT: Extracting digits from a character field -- because the topic is really different and I wanted to make sure that folks can see the discussion for what it is really about.
With this said, I will share what is best practice and provides the best solution. This includes issues of perfomance and consistency of functionality in the mix of clients and api programs and importing data and all other interaction with the system. One additional point, what I am describing here has ALWAYS been the best practice. There are some folks who thought that something different was better (yes, even some folks on the Remedy/BMC application development teams but that was corrected years ago). But, the same answer has always been the right answer. Filters -- 100% of your business logic should be implemented in filters. EVERY rule, EVERY restriction, EVERY lookup that is for validation or enforcement. EVERYTHING should be done in filters. It is the only way to gaurantee that the logic is done no matter how someone access the system. Whether through an API program or the client or in any way. These are your business rules. You want to protect your data and to have complete and consistent operations. The only way to gaurantee it is in filters. Also, it is the best performing solution. You cannot control the client the customer is coming in on. You cannot control the network speed/latency. You have full and complete control on the server. You can scale a server up -- you cannot scale up unknown clients. You can add server groups and split load and do all kinds of things with plugins for extra processing or whatever on the server. You cannot on the client. Active link -- are for screen fiddling and customer fillin assistance. You can do some checking and some business logic checking because you want to give more immediate feedback or give some feedback before the next stage of the process. BUT, that logic should be 100% replicated in the server to ensure that the business logic is done. In general, you want to minimize active links if possible. - performance -- fewer things on the wire, fewer things running interactivly for the client, fewer things happening - end user experience -- if it is not important for the customer to get the action, DON'T DO IT. Too often there is "gratuitous screen fiddling" going on with active links that does stuff on the client side that is really not useful to the user of the system. Sometimes it is simply unnecessary. Sometimes it is to work around where a better design would be useful. Yes, you need active links. Sometimes you need a lot of them. But, their purpose is for screen interaction and assisting the end user to interact with the system. Not for business logic. Escalations -- see filters above. These are server side business logic and the same reasoning and topics for filters apply to escalations. To go one step further, you sometimes want to use filters to speed up active link interaction.... What do I mean here? Well, say you needed to get 5 pieces of data for the customer and they were on different records whenever they selected an option. Using active links, that is 5 set fields operations that means 5 round-trips to the server (actually 10 round trips before 7.1, but 5 in 7.1). Using the service call from an active link and having filters run "on service", you can put the logic of the 5 set fields in one or more filters and then have one active link action that performs the service call to the server to get all five values you need at one time. The improvement? Well, 5 (or 10) client server interactions has become 1. If you had a latency on your line of say 200 ms. You have just made the operation almost 1 second faster (or almost 2 seconds if things are pre 7.1). The client gets a faster response and better interaction. Yes, more work on the server, but much better result. And, the server is limited by DB interaction speed not by memory processing and in the example above, the db interaction is unchanged so no change to the limiting factor of the server (and that is true for all workflow or interaction -- the db interaction is the same whether an active link or filter). So, a long response, but hopefully it provides a non-ambiguous position and reasoning for that position and why that recommended best practice is what is described above. Doug Mueller ________________________________ From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Jason Miller Sent: Friday, April 16, 2010 12:29 AM To: arslist@ARSLIST.ORG Subject: OT: Extracting digits from a character field ** This might start a whole new debate (and kind of what I am looking to do)... I remember learning that train of though (use an AL to run on the client and use their resources when you can and built filters only when you have to) but I have started to hear the opposite over the last few years... Let the server do the work. I think part of it is that server are so fast now days and also with server groups you can have multiple load balanced AR server to share the load. With the move to all web clients, ultimately a server will end up doing the work whether it is an MT server or an AR server. We are people's thoughts on this? Jason On Thu, Apr 15, 2010 at 3:39 PM, cpgold <cpg...@gmail.com<mailto:cpg...@gmail.com>> wrote: ** Does it have to be a filter, an active link is much better suited for this type of processing than to let the server handle it. On Wed, Apr 14, 2010 at 1:58 PM, Joe D'Souza <jdso...@shyle.net<mailto:jdso...@shyle.net>> wrote: I do not have access to an Oracle instance at the moment to try it, but I'm quite sure that update tablename set columnname = REPLACE('Does work', 'Does ', NULL); commit; would update the column to 'work'. Which is ideally what you would want it to.. With MS-SQL it sets the column to NULL. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>]On Behalf Of Jim Fox Sent: Wednesday, April 14, 2010 2:03 PM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Extracting digits from a character field You mean works incorrectly. LOL. F Sent from my Verizon Wireless BlackBerry -----Original Message----- From: Joe D'Souza <jdso...@shyle.net<mailto:jdso...@shyle.net>> Date: Wed, 14 Apr 2010 13:25:31 To: <arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>> Subject: Re: Extracting digits from a character field You are right, I just tried and it sets the whole column to NULL instead of just replacing the specific characters in expression 2 to NULL.. It works correctly in Oracle to the best of my knowledge.. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>]On Behalf Of Jim Fox Sent: Wednesday, April 14, 2010 1:00 PM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Extracting digits from a character field Replaciing a character with Null is an oxymoron in MS-SQL. You would replace a character with a zero-length string. Null and "" are not the same. Fluxman Sent from my Verizon Wireless BlackBerry -----Original Message----- From: Joe D'Souza <jdso...@shyle.net<mailto:jdso...@shyle.net>> Date: Wed, 14 Apr 2010 11:42:21 To: <arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>> Subject: Re: Extracting digits from a character field Jim, Just curious.. What are the limitations that you encountered replacing a character or a string with NULL on MS-SQL.. And what version of MS-SQL?? Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>]On Behalf Of Jim Fox Sent: Wednesday, April 14, 2010 11:12 AM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Re: Extracting digits from a character field On MS-SQL, trying to replace one character with Null is not a good idea. Fluxman ------Original Message------ From: Joe D'Souza Sender: Action Request System discussion list(ARSList) To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> ReplyTo: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Sent: Apr 14, 2010 11:07 Subject: Re: Extracting digits from a character field If you are in an Oracle database, use the function TRANSLATE to replace all Alpha characters with either NULL or space or whatever else you wish to.. This will leave the string with only special characters and numerical characters.. I am not sure if TRANSLATE works on MS-SQL but you could give it a shot if MS-SQL is your underlying database. Joe -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>]On Behalf Of Atul Vohra Sent: Wednesday, April 14, 2010 9:42 AM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Extracting digits from a character field I have a free form character field and need to extract digits from that field - may be in a filter? Am on v7.1, oracle. Any one has some function they used (like in sql or may be combination of strstr??) Looks painful to me. Help please Atul _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org<http://www.arslist.org/> attend wwrug10 www.wwrug.com<http://www.wwrug.com/> ARSlist: "Where the Answers Are" _attend WWRUG10 www.wwrug.com<http://www.wwrug.com> ARSlist: "Where the Answers Are"_ _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"