Update infotable rows thingworx 9.1.0

[VaibhavShinde]

Hi, 

I am looking to update infotable rows, as a input to service only holds single row(selected from Grid). below is my code but doesn't work . please suggest how to update infotable row without primary key. after updating infotable i need to set it to Datatable. below are the snaps to get it clearly.

I have infotable with fields...MachineName & UserID (no primary key)

Datatable with Fields ...Batch(string as Pkey) & Members(infotable) 

above full code here......

let query = {
"filters": {
"type": "EQ",
"fieldName": "Batch",
"value": batch
}
};
var result1=me.QueryDataTableEntries({
values: undefined /* INFOTABLE */,
maxItems: undefined /* NUMBER */,
tags: undefined /* TAGS */,
source: undefined /* STRING */,
query: query /* QUERY */
});
var ExistingBatch=result1[0].Members;

var mn=value.rows[0].MachineName;
var uid=value.rows[0].UserID;
var query1 = {
"filters": {
"type": "And",
"filters": [
{
"type": "EQ",
"fieldName": "MachineName",
"value": mn
},

{
"type": "EQ",
"fieldName": "UserID",
"value": uid
}
]
}
};

let params = {
t: ExistingBatch /* INFOTABLE */,
query: query1 /* QUERY */,
values:value
};

var data = Resources["InfoTableFunctions"].UpdateQuery(params);

ExistingBatch=data;

result1[0].Members = ExistingBatch;

me.AddOrUpdateDataTableEntries({
tags: undefined /* TAGS */,
location: undefined /* LOCATION */,
source: undefined /* STRING */,
sourceType: undefined /* STRING */,
values: result1 /* INFOTABLE */
});

Thanks,

[TonyZhang]

Hi,

UpdateQuery function will only return the row(s) that got updated not the entire infotable with updated records.

So you probably need to update every field one by one.

Please find below my code for reference:

//Step 1: Find the record with matching batch
let query = {
    "filters": {
        "type": "EQ",
        "fieldName": "Batch",
        "value": batch
    }
};

let matchingBatch = me.QueryDataTableEntries({
    values: undefined /* INFOTABLE */,
    maxItems: undefined /* NUMBER */,
    tags: undefined /* TAGS */,
    source: undefined /* STRING */,
    query: query /* QUERY */
});

//Step 2: Find the row of the "Members" infotable to update with matching MachineName & UserID
// Update every field of the row to the corresponding value of "value" input
let members = matchingBatch.Members,
    mn=value.rows[0].MachineName,
    uid=value.rows[0].UserID,
    valueCollection = new Object();
valueCollection.MachineName = mn;
valueCollection.UserID = uid;
// update the rest fields one by one
members.Find(valueCollection).FieldToUpdate = value.FieldToUpdate;

// Step 3: Create a single row of infotable with updated Members Info
let newEntry = {
    Batch: batch, // STRING [Primary Key]
    Members: members // INFOTABLE
};

let newRecord = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
    infoTableName: "InfoTable",
    dataShapeName: "DataTableShape"
});
newRecord.AddRow(newEntry);

// Step 4: Use AddOrUpdateDataTableEntry to update record with matching Batch which is the PK
me.AddOrUpdateDataTableEntry({
    sourceType: undefined /* STRING */,
    values: newRecord /* INFOTABLE */,
    location: undefined /* LOCATION */,
    source: undefined /* STRING */,
    tags: undefined /* TAGS */
});

[VaibhavShinde]

Hi @TonyZhang ,

Thanks for your reply, Actually i am getting below error when i try your code.

TypeError: Cannot set property "FieldToUpdate" of null to "undefined" (Update_Man_Machine#90)

Thanks,

[TonyZhang]

Hi @VaibhavShinde ,

You should make adjustments to below line of code instead of copying my code as "FieldToUpdate" is a field I made it up.

members.Find(valueCollection).FieldToUpdate = value.FieldToUpdate;

Make adjustment depending on how the datashape you defined for your "Members" infotable.

e.g If your Members has following structure:

MachineName  UserID  FieldA  FieldB

Then change to below code:

members.Find(valueCollection).FieldA = value.FieldA;

members.Find(valueCollection).FieldB = value.FieldB;

[VaibhavShinde · Accepted solution]

Hi @TonyZhang ,

Sorry, It's my bad. i have modified it but still unable to update infotable . but I have done workaround like..

1)get Members infotable stored in Datatable1.

2) add the Members infotable to Datatable2.

3)do update/delete operations on Datatable2.

4)get entries from Datatable2.

5)update at Datatable1.

and last purge the Datatable2 after update done. It worked for me.

Thanks,

[TonyZhang]

Hi @VaibhavShinede,

Sorry to hear that my approach didn't work out for your case.

Glad that you find your own solution!

Best regards,

Tony