Thanks Randeep, I only saw three fields in the DB associated with my new Cost field: CostC (currency type such as USD), CostV (the numeric value) and CostD (epoch time). That must be because I was lazy and was looking at the view instead of the table itself. Once I looked directly at the table I see that there’s a fourth suffix “USD” but for the record that I had modified via SQL that fourth column was indeed null.
I re-ran the SQL statement but this time against the table and it updated all four columns (C, V, D and USD) however the WUT still shows the field empty. Thanks for directing me to the Database Reference document, I will definitely remember to check there in the future but it didn’t completely resolve my issue yet. I had set the value of the USD column the same as the V column based on the data for a record I had modified via the WUT first. I must still be missing something, below is the update statement that I used. UPDATE T1545 SET C536870961V = C260600001, C536870961C = 'USD', C536870961D = C6, C536870961USD = C260600001 WHERE C1 = '000000000044210' Thanks in advance, Rick _________________________ Rick Westbrock Remedy Administrator | IT Department 24 Hour Fitness USA, Inc. From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Randeep Atwal Sent: Monday, May 19, 2014 3:36 PM To: [email protected] Subject: Re: Copying a decimal field into a currency field ** Check out the database Ref document for 7.0 I recall that for currency fields, there are four columns that need updating. Value, Code, Date of conversion and value for the supported currencies. They all use the same field id with special suffixes, so it will be tricky with sql but ou could set it up with one currency quite easily I would think. Sent from my BlackBerry 10 smartphone on the Rogers network. From: Rick Westbrock Sent: Monday, May 19, 2014 6:10 PM To: [email protected]<mailto:[email protected]> Reply To: [email protected]<mailto:[email protected]> Subject: Copying a decimal field into a currency field ** Hi folks, I am working with a legacy custom application on my ARS 7.0.1 system (Oracle 10 back-end) where the initial developer created a Price field as a decimal field. My problem is that now we are integrating this form with the CMDB (ITSM 7.0.03) and I need to get that data into a currency field. My initial thought was to create a new currency field named Cost and do a simple SQL update statement to get the data copied rather than rely on an escalation. I thought that this statement would work and it does update the destination fields to the expected values, however when I look at the record in the WUT the new field is empty. Any idea why this might be or how to get around it via SQL? I will use an escalation if I have to but I don’t like having to change the Modified Date on historical records. If there’s a way to preserve the Modified Date with some smoke & mirrors I would appreciate that as well. My understanding is that there is come CMDB “black box” functionality that handles currency fields and I can import currency data into AST forms via a CSV file and that process works fine so apparently the import tool runs the data through that black box on the way into the AST forms. -Rick _________________________ Rick Westbrock Remedy Administrator | IT Department 24 Hour Fitness USA, Inc. _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
