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 <[email protected]> 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: > [email protected]] On Behalf Of Grooms, Frederick W > Sent: Thursday, July 31, 2014 10:12 AM > To: [email protected] > 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: > [email protected]] On Behalf Of Reiser, John J > Sent: Thursday, July 31, 2014 8:50 AM > To: [email protected] > 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 "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" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
