Highlighted
Honored Contributor.
Honored Contributor.
683 views

Update closed records with a new field

We need to be able to track records by the County so I created a new field in location, incidents and probsummary tables and setup the link files to add the county when the location is filled as tickets are created.

My problem is that I have hundreds of closed records that I need to fill the county field. Any suggestions would be appreciated.

0 Likes
14 Replies
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Update closed records with a new field

You could do a couple of things:

1.  Populate them on the backend database.

2.  Create a javascript, do a Mass Update or a Scheduled Task that searches for the closed records, does the necessary lookup in the location table to grab the country, update the field and then save the record.

0 Likes
Highlighted
Honored Contributor.
Honored Contributor.

Re: Update closed records with a new field

I have been trying to do a mass update with no luck. Here is what I tried:

In the formatctrl record, in the Subroutines panel, added

 

Update: null(newCounty in $file)

Before: true

Application: fill.fc

Parameter Names | Parameter Values

record | $file

text | newCounty

string1 | location

 

 

Then, in a complex mass update, under the "Instructions for actions on EACH RECORD":

 $x=$x

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Update closed records with a new field

That probably doesn't work because you normally can't save a closed record.  Remove that code from the format control and just code in the "Instructions for each record" area.  You need to do an rinit the location table, then do a select for the location record you need, then update the field in the record with the value of the field in the location record and then do an rupdate on the record. 

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Update closed records with a new field

Best way use:

1) SQL in DB, for example MS SQL:

 

merge into  probsummary t1
using location t2
on (t1.location=t2.location)
when matched then update 
set t1.conty=t2.county
where t1.county="" and t1.status='Closed'; 

 

 

2) ScriptLibrary something like that:

a) using FILL

 

var bad = 0;
var good = 0;
var currentFile = new SCFile( "probsummary" );
var findRecord = currentFile.doSelect('county="" and status="Closed"');
if ( findRecord == RC_SUCCESS )
{
do
{
// initialize the variable link and find the master link on the table probsummary
var link = lib.ScAPI_RAD.se_get_record("probsummary", "name", "link", true);

// simulated pressing the button to fill in the LOCATION - launch link
lib.ScAPI_RAD.us_link(record ,"location",link,"fill",true);

if (currentFile.doUpdate()!= RC_SUCCESS)
{
bad=bad+1;
}
else
{
good=good+1;
}
}
while ( currentFile.getNext() == RC_SUCCESS );
}
print("Updated: "+ good);
print("Can't update: "+ bad);

b) using copy from location (like in MS SQL)

 

0 Likes
Highlighted
Honored Contributor.. Honored Contributor..
Honored Contributor..

Re: Update closed records with a new field

This should typically work. Have you ensured the "location" link record has a line called "newCounty" and it has the field assignment correctly?

Additionally, are there any error messages during the mass update?

0 Likes
Highlighted
Honored Contributor.
Honored Contributor.

Re: Update closed records with a new field

which one should work? The complex update? I get no errors, the field just doesn't populate

 

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Update closed records with a new field

Have you tried the method I suggested ?

0 Likes
Highlighted
Honored Contributor.
Honored Contributor.

Re: Update closed records with a new field

I have not. The database piece would be a challenge for me as we keep our DB's locked down by other departments. As for the Script, where would I put that? I am not very familiar with scripting.

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Update closed records with a new field

Well tell you more details.

1) use command line (in SM) sl - this is start ScriptLibrary

2) Create new one - for example Drew_Test

3) Copy my code to your new script library

4) save and compile it

5) if no errors press RUN

**********

I'm agree that will better change my code to another sequence - "find all opened SD"-"find related IM"-"check if IM closed"-"close SD"

0 Likes
Highlighted
Honored Contributor.. Honored Contributor..
Honored Contributor..

Re: Update closed records with a new field

Yes, the complex update should work, unless the records aren't getting touched. IF you aren't getting any messages, that means the update isn't actually done. Can you "mass update" a single record with "$x=1" and run a debug trace from the console? That should tell us at least if it is trying to update, or just ignore everything.

0 Likes
Highlighted
Honored Contributor.
Honored Contributor.

Re: Update closed records with a new field

What package do I use?

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.