Get the last state of an Object with query definition or REST

Hello ARAS community,

once again o would have your advice.

to be clear, this is what we have in the table of an object : 

id                                  REF_NUMBER_         MODIFIED_                           ON STATE       IS_CURRENT        MAJOR_REV
2154BB99E5F046        B                                 2019-11-06 09:43:54.560       Working           1                            A
2A7B8BD203A54C       C                                 2019-11-06 09:44:03.450       Working           1                             A
5E2A1CD82CD14A       A                                 2019-11-06 09:45:41.210       Saved              1                             A
B3CE5360E24449B      A                                  2019-11-06 09:43:48.837      Working           0                             A

I need as a result to get the line 1, 2 and 4. this correspond to get all the object at the state Working and only the most recent one.

My problem is if i use as filter (state = 'working') AND (is_current = 1) i only get line 1 and 2. the 3 cannot be segregated.

Is it possible ?

Thank you :)

  • Hi Alexandrecimp,

    Just to make sure I understand, in this table the IS_CURRENT column is a 1 when it's the most recent version and a 0 if it's old? If that's the circumstance, and you want the objects which have the state Working and are current, then it seems like rows 1 and 2 are the correct output, since row 4 isn't current.

    You can effectively do OR searches, but since line 3 has IS_CURRENT of 1, it would turn up in that search. In this data set, just searching for (state='working') would return only rows 1, 2, and 4, but I imagine that in a larger data set there might be rows which are being worked on but aren't current. 

    Let me know if that helps! If it doesn't, please give a little more detail on what defines the most recent rows. If there isn't a clear answer in the data you have, you might be able to add a property that would have the information you need.

    Skyler C.

  • Hello,

    I tried something like that but i am afraid this is still not enough as i have to get all the last version of my object for one state :

    Innovator _innovator = this.getInnovator();

    //extract log
    var _log_item = _innovator.applyMethod("_Get_Session_Variable", "<_variable>Log_Enabled</_variable>");
    var _log_enabled = false;
    if (_log_item.getProperty("_variable_value", "") == "True") _log_enabled = true;

    //print log
    if (_log_enabled) CCO.Utilities.WriteDebug("_" + DateTime.Now.ToString("yyyy-MM-dd"), "run_qry method begin");

    System.Diagnostics.Debugger.Launch();

    string State = this.getProperty("state","");


    Item gti = this.newItem("object", "get");
    ***.setProperty("generation", "*");
    ***.setPropertyCondition("generation", "like");
    ***.setProperty("state", State);
    // ***.setAttribute("orderBy","generation DESC");
    ***.setAttribute("queryType","Latest");

    ***= ***.apply();

    System.Diagnostics.Debugger.Break();


    return ***;

    Now i will try to put some SQL in my method^^.

  • For the community this one works fine :  

    Innovator _innovator = this.getInnovator();

    //extract log

    var g_log_item = _innovator.applyMethod("_Get_Session_Variable", "<gtsi_variable>_Log_Enabled</_variable>");

    var _log_enabled = false;

    if (_log_item.getProperty("_variable_value", "") == "True") _log_enabled = true;

    //print log

    if (_log_enabled) CCO.Utilities.WriteDebug("_" + DateTime.Now.ToString("yyyy-MM-dd"), "_run_qry method begin");

     

    System.Diagnostics.Debugger.Launch();

     

    string State = this.getProperty("_state","");

     

    StringBuilder sql = new StringBuilder();

     

    sql.AppendLine("SELECT ID, g_code, config_id, state, Major_Rev, Generation, is_current FROM innovator.[***] 1 WHERE 1.generation =(SELECT MAX(generation) from innovator.[***] 2 where 2.state = 'Working' AND 2.config_id = 1.config_id)order by modified_on");

     

    Item _result = _innovator.applySQL(sql.ToString());

     

    System.Diagnostics.Debugger.Break();

     

    return _result;