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.

  • 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.

  • 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

  • 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. 

  • 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)

     

  • 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)

     

  • 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)

     

  • 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?

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

     

  • Have you tried the method I suggested ?

  • 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.