Re: SQL feed into Remedy 7.6 sending special characters
Oracle 10g (and higher) has the CONVERT function which will convert between character sets You can use a Simple Set-Fields action (from SQL) SELECT CONVERT('$field$','{to character set}','{from character set}') FROM DUAL Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Ben Chernys Sent: Friday, November 11, 2011 3:35 PM To: arslist@ARSLIST.ORG Subject: Re: SQL feed into Remedy 7.6 sending special characters ** In that case, I would offer a special search field and a hidden field to contain the name without the special characters. This would be an addition to the CTM:People form. Again, I would use an SQL function to do the xlate as the Remedy functions would be slow. You can only do it through a translation table. You cannot do it through arithmetic. Check out the ASCII character set for the codes themselves. Cheers Ben -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of john.athe...@schneider-electric.com Sent: November-11-11 22:20 To: arslist@ARSLIST.ORG Subject: Re: SQL feed into Remedy 7.6 sending special characters ** When a user calls into the Helpdesk and tells then their name is Noël Smith the support person in another part of the world types in Noel Smith and does a search. Since Noel Smith does not equal Noël Smith they get the message no record found. I think I'm going to push back that support needs to search on the user company ID. Thanks _ John Atherly | APC by Schneider Electric | Information, Process & Organization (IPO) | Remedy Administrator / Developer Phone: +305-266-5005 ext. 237 | Email: john.athe...@apcc.com | Site: www.apc.com/ | Address: 703 Waterford Way, Suit 850, Miami, FL 33126 USA *** Please consider the environment before printing this e-mail -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Ben Chernys Sent: Friday, November 11, 2011 4:07 PM To: arslist@ARSLIST.ORG Subject: Re: SQL feed into Remedy 7.6 sending special characters ** Why would you want to? Do you not want the names correct? I am quite sure they would not be in fields that cannot support them such as email addresses of user ids. Also, and perhaps only with umlauts in German, removal of the umlaut implies an addition of an ‘e’. So for example, Juergen and Jürgen are equivalent in German. The same is not true for French accents. And there are umlauts in French as well. Noël for example. Note that there are no umlauts on ‘E’ in German and only on ‘E’ in French. I do not know the correct rules for removing accents from Norwegian or Finnish. So removal of these special characters (or rather the translation of these characters) would depend on the language that those characters were used in (perhaps in the case of a person, the site or country of that person). Finally, should you really want to do this, you can certainly approach it in a number of ways. One would be simply build an SQL view effecting the translations. In this case (I am presuming you will be using a view form) you would base the form on your view rather than the source table. Then, the characters would be replaced before they got to Remedy. With Meta-Update you can cause these characters to be changed with a simple substitute on reading the SQL before they get to Remedy. You can also base the substitution on the other data in the SQL such as language or location. Umlauts and other accents and characters are handled in the extended ASCII character set no problem so there is really no need to change these characters even if you are not running a UTF database. Cheers Ben Chernys Senior Software Architect Software Tool House Inc. Canada / Deutschland / Germany Mobile: +49 171 380 2329 GMT + 1 + [ DST ] Email: Ben.Chernys _AT_ softwaretoolhouse.com Web: www.softwaretoolhouse.com Check out Software Tool House's free Diary Editor. Meta-Update, our premium ARS Data tool, lets you automate your imports, migrations, in no time at all, without programming, without staging forms, without merge workflow. http://www.softwaretoolhouse.com/ -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of john.athe...@schneider-electric.com Sent: November-11-11 21:31 To: arslist@ARSLIST.ORG Subject: SQL feed into Remedy 7.6 sending special characters ** I have a data base that holds employee information that is using SQL to push the data into Remedy to either create new users or update the users current record in the CTM:People form. Since we are a global company some of the names are coming in
Re: SQL feed into Remedy 7.6 sending special characters
In that case, I would offer a special search field and a hidden field to contain the name without the special characters. This would be an addition to the CTM:People form. Again, I would use an SQL function to do the xlate as the Remedy functions would be slow. You can only do it through a translation table. You cannot do it through arithmetic. Check out the ASCII character set for the codes themselves. Cheers Ben From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of john.athe...@schneider-electric.com Sent: November-11-11 22:20 To: arslist@ARSLIST.ORG Subject: Re: SQL feed into Remedy 7.6 sending special characters ** When a user calls into the Helpdesk and tells then their name is Noël Smith the support person in another part of the world types in Noel Smith and does a search. Since Noel Smith does not equal Noël Smith they get the message no record found. I think I'm going to push back that support needs to search on the user company ID. Thanks _ John Atherly | APC by Schneider Electric | Information, Process & Organization (IPO) | Remedy Administrator / Developer Phone: +305-266-5005 ext. 237 | Email: <mailto:%20john.athe...@apcc.com> john.athe...@apcc.com | Site: <http://www.apc.com/> www.apc.com/ | Address: 703 Waterford Way, Suit 850, Miami, FL 33126 USA *** Please consider the environment before printing this e-mail Ben Chernys Sent by: "Action Request System discussion list(ARSList)" 11/11/2011 04:07 PM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Re: SQL feed into Remedy 7.6 sending special characters ** Why would you want to? Do you not want the names correct? I am quite sure they would not be in fields that cannot support them such as email addresses of user ids. Also, and perhaps only with umlauts in German, removal of the umlaut implies an addition of an ‘e’. So for example, Juergen and Jürgen are equivalent in German. The same is not true for French accents. And there are umlauts in French as well. Noël for example. Note that there are no umlauts on ‘E’ in German and only on ‘E’ in French. I do not know the correct rules for removing accents from Norwegian or Finnish. So removal of these special characters (or rather the translation of these characters) would depend on the language that those characters were used in (perhaps in the case of a person, the site or country of that person). Finally, should you really want to do this, you can certainly approach it in a number of ways. One would be simply build an SQL view effecting the translations. In this case (I am presuming you will be using a view form) you would base the form on your view rather than the source table. Then, the characters would be replaced before they got to Remedy. With Meta-Update you can cause these characters to be changed with a simple substitute on reading the SQL before they get to Remedy. You can also base the substitution on the other data in the SQL such as language or location. Umlauts and other accents and characters are handled in the extended ASCII character set no problem so there is really no need to change these characters even if you are not running a UTF database. Cheers Ben Chernys Senior Software Architect Software Tool House Inc. Canada / Deutschland / Germany Mobile: +49 171 380 2329GMT + 1 + [ DST ] Email:<mailto:ben.cher...@softwaretoolhouse.com> Ben.Chernys _AT_ softwaretoolhouse.com Web: <http://www.softwaretoolhouse.com/> www.softwaretoolhouse.com Check out Software Tool House's free Diary Editor. Meta-Update, our premium ARS Data tool, lets you automate your imports, migrations, in no time at all, without programming, without staging forms, without merge workflow. <http://www.softwaretoolhouse.com/> http://www.softwaretoolhouse.com/ From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of john.athe...@schneider-electric.com Sent: November-11-11 21:31 To: arslist@ARSLIST.ORG Subject: SQL feed into Remedy 7.6 sending special characters ** I have a data base that holds employee information that is using SQL to push the data into Remedy to either create new users or update the users current record in the CTM:People form. Since we are a global company some of the names are coming in with special characters in them. Such a the two dots over the "a". My question can I have remedy strip these out or should I have SQL strip these out. If I have remedy what would be the best way to do this? _ John Atherly | APC by S
Re: SQL feed into Remedy 7.6 sending special characters
When a user calls into the Helpdesk and tells then their name is Noël Smith the support person in another part of the world types in Noel Smith and does a search. Since Noel Smith does not equal Noël Smith they get the message no record found. I think I'm going to push back that support needs to search on the user company ID. Thanks _ John Atherly | APC by Schneider Electric | Information, Process & Organization (IPO) | Remedy Administrator / Developer Phone: +305-266-5005 ext. 237 | Email: john.athe...@apcc.com | Site: www.apc.com/ | Address: 703 Waterford Way, Suit 850, Miami, FL 33126 USA *** Please consider the environment before printing this e-mail Ben Chernys Sent by: "Action Request System discussion list(ARSList)" 11/11/2011 04:07 PM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Re: SQL feed into Remedy 7.6 sending special characters ** Why would you want to? Do you not want the names correct? I am quite sure they would not be in fields that cannot support them such as email addresses of user ids. Also, and perhaps only with umlauts in German, removal of the umlaut implies an addition of an ‘e’. So for example, Juergen and Jürgen are equivalent in German. The same is not true for French accents. And there are umlauts in French as well. Noël for example. Note that there are no umlauts on ‘E’ in German and only on ‘E’ in French. I do not know the correct rules for removing accents from Norwegian or Finnish. So removal of these special characters (or rather the translation of these characters) would depend on the language that those characters were used in (perhaps in the case of a person, the site or country of that person). Finally, should you really want to do this, you can certainly approach it in a number of ways. One would be simply build an SQL view effecting the translations. In this case (I am presuming you will be using a view form) you would base the form on your view rather than the source table. Then, the characters would be replaced before they got to Remedy. With Meta-Update you can cause these characters to be changed with a simple substitute on reading the SQL before they get to Remedy. You can also base the substitution on the other data in the SQL such as language or location. Umlauts and other accents and characters are handled in the extended ASCII character set no problem so there is really no need to change these characters even if you are not running a UTF database. Cheers Ben Chernys Senior Software Architect Software Tool House Inc. Canada / Deutschland / Germany Mobile: +49 171 380 2329GMT + 1 + [ DST ] Email: Ben.Chernys _AT_ softwaretoolhouse.com Web: www.softwaretoolhouse.com Check out Software Tool House's free Diary Editor. Meta-Update, our premium ARS Data tool, lets you automate your imports, migrations, in no time at all, without programming, without staging forms, without merge workflow. http://www.softwaretoolhouse.com/ From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of john.athe...@schneider-electric.com Sent: November-11-11 21:31 To: arslist@ARSLIST.ORG Subject: SQL feed into Remedy 7.6 sending special characters ** I have a data base that holds employee information that is using SQL to push the data into Remedy to either create new users or update the users current record in the CTM:People form. Since we are a global company some of the names are coming in with special characters in them. Such a the two dots over the "a". My question can I have remedy strip these out or should I have SQL strip these out. If I have remedy what would be the best way to do this? _ John Atherly | APC by Schneider Electric | Information, Process & Organization (IPO) | Remedy Administrator / Developer Phone: +305-266-5005 ext. 237 | Email: john.athe...@apcc.com | Site: www.apc.com/ | Address: 703 Waterford Way, Suit 850, Miami, FL 33126 USA *** Please consider the environment before printing this e-mail _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ __ This email has been scanned by the MessageLabs Email Security System. __ _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
Re: SQL feed into Remedy 7.6 sending special characters
Why would you want to? Do you not want the names correct? I am quite sure they would not be in fields that cannot support them such as email addresses of user ids. Also, and perhaps only with umlauts in German, removal of the umlaut implies an addition of an e. So for example, Juergen and Jürgen are equivalent in German. The same is not true for French accents. And there are umlauts in French as well. Noël for example. Note that there are no umlauts on E in German and only on E in French. I do not know the correct rules for removing accents from Norwegian or Finnish. So removal of these special characters (or rather the translation of these characters) would depend on the language that those characters were used in (perhaps in the case of a person, the site or country of that person). Finally, should you really want to do this, you can certainly approach it in a number of ways. One would be simply build an SQL view effecting the translations. In this case (I am presuming you will be using a view form) you would base the form on your view rather than the source table. Then, the characters would be replaced before they got to Remedy. With Meta-Update you can cause these characters to be changed with a simple substitute on reading the SQL before they get to Remedy. You can also base the substitution on the other data in the SQL such as language or location. Umlauts and other accents and characters are handled in the extended ASCII character set no problem so there is really no need to change these characters even if you are not running a UTF database. Cheers Ben Chernys Senior Software Architect Software Tool House Inc. Canada / Deutschland / Germany Mobile: +49 171 380 2329GMT + 1 + [ DST ] Email:<mailto:ben.cher...@softwaretoolhouse.com> Ben.Chernys _AT_ softwaretoolhouse.com Web: <http://www.softwaretoolhouse.com> www.softwaretoolhouse.com Check out Software Tool House's free Diary Editor. Meta-Update, our premium ARS Data tool, lets you automate your imports, migrations, in no time at all, without programming, without staging forms, without merge workflow. <http://www.softwaretoolhouse.com/> http://www.softwaretoolhouse.com/ From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of john.athe...@schneider-electric.com Sent: November-11-11 21:31 To: arslist@ARSLIST.ORG Subject: SQL feed into Remedy 7.6 sending special characters ** I have a data base that holds employee information that is using SQL to push the data into Remedy to either create new users or update the users current record in the CTM:People form. Since we are a global company some of the names are coming in with special characters in them. Such a the two dots over the "a". My question can I have remedy strip these out or should I have SQL strip these out. If I have remedy what would be the best way to do this? _ John Atherly | APC by Schneider Electric | Information, Process & Organization (IPO) | Remedy Administrator / Developer Phone: +305-266-5005 ext. 237 | Email: <mailto:%20john.athe...@apcc.com> john.athe...@apcc.com | Site: <http://www.apc.com/> www.apc.com/ | Address: 703 Waterford Way, Suit 850, Miami, FL 33126 USA *** Please consider the environment before printing this e-mail _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
SQL feed into Remedy 7.6 sending special characters
I have a data base that holds employee information that is using SQL to push the data into Remedy to either create new users or update the users current record in the CTM:People form. Since we are a global company some of the names are coming in with special characters in them. Such a the two dots over the "a". My question can I have remedy strip these out or should I have SQL strip these out. If I have remedy what would be the best way to do this? _ John Atherly | APC by Schneider Electric | Information, Process & Organization (IPO) | Remedy Administrator / Developer Phone: +305-266-5005 ext. 237 | Email: john.athe...@apcc.com | Site: www.apc.com/ | Address: 703 Waterford Way, Suit 850, Miami, FL 33126 USA *** Please consider the environment before printing this e-mail ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"