Thanks Fred, I tried that but it still returns 2012-09-03 which is the correct date but not the correct syntax. I thought that maybe by renaming the output (which was something I left in from the sample code I used as an example) the $1$ in the set fields was not getting a value back so I removed the AS clause and tried again. Still the same results so maybe the direct SQL doesn't work on a display-only field. I will proceed with the string manipulations, the basic one for the fields that don't need to retain the timestamp is quite easy, I just need to trap for null values since that would change the value to a single hyphen:
RIGHT($zTmpDiscoveredDate$, 5) + "-" + LEFT($zTmpDiscoveredDate$, 4) Thanks, Rick ___________________________ Rick Westbrock QMX Support Services -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W Sent: Monday, May 13, 2013 10:44 AM To: arslist@ARSLIST.ORG Subject: Re: Converting date formats in set fields action I think you need to put single quotes around the field for the SQL SELECT CONVERT(VARCHAR(10), '$zTmpDiscoveredDate$', 101) AS DiscDate That is why you are getting 2000 ( 2012 - 9 - 3 ) Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Rick Westbrock Sent: Monday, May 13, 2013 11:21 AM To: arslist@ARSLIST.ORG Subject: Re: Converting date formats in set fields action ** I did try that on Friday but the field was being set to 2000 to my temp date/time field again (see log below) so I may not have the syntax quite correct. I’m using this SQL query in the set fields action into the temp character field with the idea that I am only converting the first ten characters which hold the date (MS-SQL Server 2008): SELECT CONVERT(VARCHAR(10), $zTmpDiscoveredDate$, 101) AS DiscDate <SQL > <TID: 0000003068> <RPC ID: 0000258818> <Queue: List > <Client-RPC: 390620 > <USER: rwestbrock > <Overlay-Group: 1 > /* Fri May 10 2013 09:41:59.3250 */SELECT CONVERT(VARCHAR(10), 2012-09-03, 101) AS DiscDate <SQL > <TID: 0000003068> <RPC ID: 0000258818> <Queue: List > <Client-RPC: 390620 > <USER: rwestbrock > <Overlay-Group: 1 > /* Fri May 10 2013 09:41:59.3250 */OK <ACTL> zTmpWhenDiscoveredDate (936880920) = 2000 Today I should be able to insert some message actions to pause the workflow so that I can hopefully figure out if that run process Application-Copy-Field-Value is where the data change happens. -Rick Rick Westbrock QMX Support Services -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Vaibhav Singhal Sent: Friday, May 10, 2013 18:59 PM To: arslist@ARSLIST.ORG Subject: Re: Converting date formats in set fields action ** How about calling a direct SQL and parse the value? Sent from my iPhone On 11/05/2013, at 8:38 AM, "Grooms, Frederick W" <frederick.w.gro...@xo.com> wrote: ** Your date format is ALMOST the standard XML date format (The actual XML format has a T between the date and time instead of a space) I parse some XML thru Perl (using the Simple::XML package. It will create an array you can walk easily for the 0 to many types of data. (Perl is free, so it may still be an option) You could also do the following Setup some forms corresponding to the XML structures and make your own web service you simply pass the flat file into. From your forms you can push data where it needs to go. Multiple child records from a main record are no problem to do. Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Rick Westbrock Sent: Friday, May 10, 2013 4:36 PM To: arslist@ARSLIST.ORG Subject: Re: Converting date formats in set fields action ** That’s a good idea Paul. Currently we are doing some manipulation outside of Remedy before importing the files with the Data Import tool. We don’t have Perl available however, I’m not sure what other scripting tools I might be able to leverage but I’ll look into it. The toughest part of this is that in the XML file each incoming ticket can have zero to many work info entries; any work info entries go in a different regular form (just to make for a more complex process). -Rick Rick Westbrock QMX Support Services -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Paul Blasquez Sent: Friday, May 10, 2013 14:24 PM To: arslist@ARSLIST.ORG Subject: Re: Converting date formats in set fields action ** Rick, Since you're stuck with a flatfile anyway, would it be an option for you to process that file into a Remedy-friendly format first using a scripting language such as perl? I've found it's much easier to deal with heavy text validation/manipulation by sending it outside of Remedy then reading it back. -Paul -----Original Message----- On Fri, May 10, 2013 at 1:21 PM, Rick Westbrock wrote: ** Thanks for the replies. Mark, I was hoping that I was missing something obvious and wouldn’t have to do so much string manipulation but I may have to go that route. This is actually related to a previous issue I posted, my data feed is a daily XML file exported from the other system so the file is basically one giant string. For various reasons I don’t have the option of web services at this time, I’m stuck with the XML flat file once per day. I realized while composing a longer reply that there are two other steps in the process that could be causing this problem. The complete workflow is that I walk a table to pick up the field name and field ID on the display-only form where the data element needs to end up. There’s a set fields that parses it out of the long XML string into a temp character field first, then a run process Application-Copy-Field-Value to copy it to the destination date/time field. This is the first place that I hadn’t considered might be causing the problem because it only returns a 0/1 result for success/failure so I can’t see what it is setting into the date/time field without putting a message in there to pause the workflow so I can see the transitory data. There is also a subsequent push fields action that could be causing the problem. Below is a set of log lines showing all the steps: walk the table to get the destination field name & ID and copy the data element from the XML to the temp field (action 0), then action 1 uses the run process to copy to the date/time field, then action 6 in a different active link pushes to a regular form. The problem could be introduced in action 1 or action 6. (I am walking a table instead of hard-coding because there are nearly 40 data fields to parse and I’ve already had one additional field added to the spec so it’s easier to add a row to the supporting form instead of modifying workflow when a field is requested to be added.) <ACTL> 0: Set Fields <ACTL> Current Field ID (936870941) = 536871196 <ACTL> Current Field Name (936870940) = DiscoveredDate <ACTL> Current Field Value (936870936) = 2011-12-06 00:00:00.0 <ACTL> 1: Set Fields <ACTL> Process: Application-Copy-Field-Value 536871196 936870936 <ACTL> Integer Field (936870937) = 0 <ACTL> 6: Push Fields <ACTL> To Schema RegularForm on Server @ <ACTL> (536871196) = Wednesday, December 31, 1969 16:33:31 PM ___________________________ Rick Westbrock QMX Support Services -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Rick Cook Sent: Friday, May 10, 2013 12:26 PM To: arslist@ARSLIST.ORG Subject: Re: Converting date formats in set fields action ** Why would you need to parse or munge the data? The date/time data is stored as a string, and the client settings determine how it is displayed. So why would you need to do anything more than copy the field to a temp date/time field on the destination system, which would display it in the new format? Rick On May 10, 2013 10:35 AM, "Brittain, Mark" <mbritt...@navisite.com> wrote: Hi Rick, For a Friday this might work. Since the format is always the same maybe you can probably do a series of substring set fields in a filter to put in a temp date (character) field and then move to your final date field. incomingdate is also a character field. Set field tempdate SUBSTR($incomingdate$,0,3) result YYYY Set field tempdate SUBSTR($incomingdate$,8,9) + "/" + $tempfield$ result DD/YYYY Set field tempdate SUBSTR($incomingdate$,5,6) + "/" + $tempfield$ Result MM/DD/YYY Set field finaldatefield $tempdate$ Good Luck, Mark -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Rick Westbrock Sent: Friday, May 10, 2013 1:03 PM To: arslist@ARSLIST.ORG Subject: Converting date formats in set fields action I don't know if the long week has caught up with me and this is a heavy question for a Friday. I am just not able to figure out how to convert a date that I am getting from another system in YYYY-MM-DD format to the MM-DD-YYYY format that my server uses. For testing purposes I'm just pasting the output from the other system into a display-only form and then doing a set fields for the date portion of that string into my date/time field (DiscoveredDate). If my symptoms below make sense to anyone I would gladly take any advice on how to convert the date format. The date is actually coming in as YYYY-MM-DD HH:MM:SS with milliseconds but the time portion is all zeroes. I am using the LEFT function to set the value into a temp character field (zTmpDiscDate) taking only the first 10 characters (i.e. the date portion) which works fine. When I do a set fields (current screen) to take that value into my date/time field DiscoveredDate it sets it to a default of Wednesday, December 31, 1969 16:33:20 PM presumably because the incoming date format doesn't match. I tried using a set fields with a SQL statement as shown below but it just sets my temp character field zTmpDiscDate to 2000 for some reason: SELECT CONVERT(VARCHAR(10), $zTmpDiscDate$, 101) AS DiscDate The SQL log shows that it is parsing the date field but even with AL, Database and API logging turned on in the WUT this is all I get back which is not helping me figure out where the failure lies. The server is on Windows 2008 and the database is MS-SQL Server 2008. The ultimate goal of this is to process incoming data on the server without human intervention at all, I'm just using the WUT as a test platform to tweak my code. <SQL > <TID: 0000000336> <RPC ID: 0000258953> <Queue: List > <Client-RPC: 390620 > <USER: rwestbrock > <Overlay-Group: 1 > /* Fri May 10 2013 09:45:51.6090 */SELECT CONVERT(VARCHAR(10), 2012-09-03, 101) AS DiscDate<SQL > <TID: 0000000336> <RPC ID: 0000258953> <Queue: List > <Client-RPC: 390620 > <USER: rwestbrock > <Overlay-Group: 1 > /* Fri May 10 2013 09:45:51.6090 */OK<API > <TID: 0000000336> <RPC ID: 0000258953> <Queue: List > <Client-RPC: 390620 > <USER: rwestbrock > <Overlay-Group: 1 > /* Fri May 10 2013 09:45:51.6090 */-GSQL OK<CLAT> /* Fri May 10 2013 09:45:51.5820 */-API call <ACTL> zTmpDiscDate (936880920) = 2000 <ACTL> 8: Set Fields <ACTL> DiscoveredDate (536871196) = Wednesday, December 31, 1969 16:33:20 PM _______________________________________________________________________________ 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"
