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 On Fri, May 10, 2013 at 1:21 PM, Rick Westbrock <rwestbr...@qmxs.com> 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**** > > Support to SPAWAR – IT Service Management Project, Code 54520**** > > QMX Support Services**** > > Office (619) 524-2303**** > > ** ** > > *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 > > > ___________________________ > Rick Westbrock > QMX Support Services > > > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the > Answers Are, and have been for 20 years" > > This e-mail is the property of NaviSite, Inc. It is intended only for the > person or entity to which it is addressed and may contain information that > is privileged, confidential, or otherwise protected from disclosure. > Distribution or copying of this e-mail, or the information contained > herein, to anyone other than the intended recipient is prohibited. > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at 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_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
