Charlie, I ended up using a Direct SQL action in a modify filter to populate the LName_Soundex and Name_Soundex fields. Name_Soundex does a soundex(LastName+FirstName). This comes in handy searching through the Smiths.
Maybe when I get some time I’ll investigate why this problem appeared solely on the basis of moving to a new DB and ARServer version.. Thank you, --- John J. Reiser Remedy Developer/Administrator Senior Software Development Analyst Lockheed Martin - MS2 The star that burns twice as bright burns half as long. Pay close attention and be illuminated by its brilliance. - paraphrased by me From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Charlie Lotridge Sent: Monday, August 04, 2014 12:47 PM To: arslist@ARSLIST.ORG Subject: Re: EXTERNAL: Re: using SQL soundex function to find similar names ** John, As you've probably guessed the N in the N'<whatever>' is normal for a unicode server - it's telling the DB to consider the string a unicode string. That really is a very strange problem. You could still try the step 2 I described to try to directly issue just "SELECT 'S363'" instead of the SOUNDEX to make sure a "normal" string is making it into the field to which would then seem to imply it's somehow something about the particular about the string as returned from SOUNDEX. Probably a stupid question, but have you run a filter log merged with your sql log to make sure that some filter isn't nulling the field before it gets written to the DB? -charlie On Mon, Aug 4, 2014 at 8:59 AM, Reiser, John J <john.j.rei...@lmco.com<mailto:john.j.rei...@lmco.com>> wrote: ** Charlie, I did as you suggested. The workflow showed up as select soundex(N’STEHRT’) I never saw the “N” before. I see it in other logs now too. So I ran that statement and it returns S363 but in the log file it sets the Lname_soundex field to “” The update statement later in the log shows C672201002=N'' So I put Select ‘S363’ into my filter and ran a modify that would fire the filter. It placed S363 into the Lname_soundex field just as it should. I’ll give the Update statement a try. Thank you, --- John J. Reiser Remedy Developer/Administrator Senior Software Development Analyst Lockheed Martin - MS2 The star that burns twice as bright burns half as long. Pay close attention and be illuminated by its brilliance. - paraphrased by me From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] On Behalf Of Charlie Lotridge Sent: Thursday, July 31, 2014 11:37 AM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: EXTERNAL: Re: using SQL soundex function to find similar names ** John, This is a pretty strange problem you're having, and I can suggest the following steps to *try* to figure out what's going on: 1) Run a SQL log and capture the actual SQL being issued to generate the soundex value, then run it in a query window in the MS SQL Management Studio. Make sure it's doing what you expect. 2) Take the actual string returned by the SQL above and put it directly into the Set Fields' "SELECT" statement. E.g. "SELECT 'G123'". Then make sure that the value "G123" is being returned to the Lname_Soundex field. But if all this fails to yield anything useful, you can probably just work around the problem by updating the Lname_Soundex field directly using a Direct SQL action in a filter: UPDATE <The Form's View Name> SET Lname_Soundex = SOUNDEX(User_Last_Name) Of course, this is problematic during a CREATE operation, so don't bother trying during a Submit triggered filter. But a work-around to THIS is to have a Submit triggered filter do a Push Fields to the same record to trigger the filter that runs that Direct SQL (which probably just should run with the qualification 'User Last Name' != $NULL$ AND 'Lname_Soundex' = $NULL$). Of course be sure the filter doing this Push Fields does not have the tick-bang (`!) suffix - you don't want the Push Fields happening before the record is actually created in the DB. If the problem is isolated to just this Lname_Soundex situation then this should be a sufficient workaround: you don't ever *really* need to visualize the soundex data at the app level anyway (do you?). And you've seemed to indicate that this is true (it's isolated). If it's not isolated, then, obviously, you really need to figure out what's going on as this is probably causing data corruption. If any of this is not clear let me know. -charlie On Thu, Jul 31, 2014 at 8:02 AM, Reiser, John J <john.j.rei...@lmco.com<mailto:john.j.rei...@lmco.com>> wrote: Fred, Wouldn't that cause problems with all set fields actions where I'm getting data from other forms or is it because the function is modifying and returning a value? I'll look around to see what I can find. Thank you, --- John J. Reiser Remedy Developer/Administrator Senior Software Development Analyst Lockheed Martin - MS2 The star that burns twice as bright burns half as long. Pay close attention and be illuminated by its brilliance. - paraphrased by me -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] On Behalf Of Grooms, Frederick W Sent: Thursday, July 31, 2014 10:12 AM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: EXTERNAL: Re: using SQL soundex function to find similar names Remember . The thick client (Windows User Tool) is not truly Unicode compliant It sounds like you may have some sort of mismatch in the character sets (between SQL, the server, and the client) Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] On Behalf Of Reiser, John J Sent: Thursday, July 31, 2014 8:50 AM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: using SQL soundex function to find similar names ** Hello Listers, ARS 8.1.00 MS SQL 2008 MS OS 2008 R2 While I'm working on my DSO issues and the report publisher I also came across a problem in some workflow. I've been calling the soundex() function for years in my Customer Form to make it easier to look for names that may be spelled incorrectly. You know Smith instead of Smyth and such. Since we've moved to the new system the set fields action is returning non-displayable characters in the thick client and what looks to me like oriental characters in Mid Tier. Is this a Unicode setting or does ARS 8.1 not handle the returned varchar properly? I just run a Set Fields filter using SQL as the data source with a query of SELECT SOUNDEX('$User Last Name$') Then I set my field Lname_Soundex with $1$ Can this be corrected or is there a better way to search and manage similar names? Thank you, --- John J. Reiser Remedy Developer/Administrator Senior Software Development Analyst Lockheed Martin - MS2 The star that burns twice as bright burns half as long. Pay close attention and be illuminated by its brilliance. - paraphrased by me _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org<http://www.arslist.org> "Where the Answers Are, and have been for 20 years" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org<http://www.arslist.org> "Where the Answers Are, and have been for 20 years" _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
