Community Tip - Stay updated on what is happening on the PTC Community by subscribing to PTC Community Announcements. X
I'm trying to create a service that I can globally put in a ThingShape; then reference inside every new DataTable that I create. This service will need to find the PK of each new data table and ensure that it is populated correctly.
The code snippet below gets the PK FieldName of any table dynamically (pkName).
QUESTION...I need to use the variable value (type: STRING) of pkName dynamically as the ID varaiable in the VALUES and updateVALUES params of UpdateDataTableEntriesWithQuery(params). See red below...What's the syntax to accomplish this?
NOTE **IF YOU SEE SOMETHING ELSE WRONG BELOW, please help!? **
var values = me.CreateValues(); // INFOTABLE
var updateValues = me.CreateValues(); // INFOTABLE
var dsName = me.GetDataShape(); // DataShapeName -> auto-converts to STRING
var ID=eventData.ID
var pkName="findIT" // STRING
var ds = DataShapes[dsName]; // DataShape
//Dubug logging for variable verification
logger.warn("Thing: " + me.name + " || VARIABLE data BEFORE logic || dsName="+dsName + " newID="+newID + " pkName="+pkName + " ds="+ds);
var fieldInfo = ds.GetDataShapeMetadataAsJSON().fieldDefinitions;
if(originaID==0) { // I only need to run logic if the PK is at default of 0
for (var property in fieldInfo) {
if (fieldInfo.hasOwnProperty(property)) {
if (fieldInfo[property].aspects) {
if (fieldInfo[property].aspects.isPrimaryKey) {
pkName = fieldInfo[property].name;
}
}
}
}
}
//Dubug logging for variable verification and logic check.
logger.warn("Thing: " + me.name + " || VARIABLE data AFTER logic || dsName="+dsName + " newID="+newID + " pkName="+pkName + " ds="+ds);
// Insert SEARCH values mapping here...PRIMARY_KEY MUST BE SET
values.?pkName.VALUE? = ID; //NUMBER [Primary Key]
// Insert UPDATE values mapping here...
updateValues.?pkName.VALUE? = ID+1; //NUMBER [Primary Key]..updated PK
// Set params variable used to insert record into actual data table
var params = {
sourceType: undefined /* STRING */,
values: values /* INFOTABLE*/,
query: undefined /* QUERY */,
location: undefined /* LOCATION */,
source: undefined /* STRING */,
updateValues: updateValues /* INFOTABLE*/,
tags: undefined /* TAGS */
};
//Update row data with INSERT timestamp and log user
me.UpdateDataTableEntriesWithQuery(params);
Solved! Go to Solution.
oops that's right I was thinking Thing[ThingName](PropertyName)
try values[pkName] instead?
You can use values(pkName)
Hey Pai!! Good to hear from you!
Can you paste your suggestion back into my script and post here? I'm getting "Error on Subscription on Add Event: values is not a function, it is object." when trying this.
oops that's right I was thinking Thing[ThingName](PropertyName)
try values[pkName] instead?
Well!! That seemed to work! However, now I'm finding out you can't update the PK field for a data table! LOL!!
ERROR: java.lang.Exception: Cannot Update Primary Key Field [ID] in UpdataDataTableEntriesFromQuery for testDataTable
Once a Primary Key is filled in, that is indeed it. That's why they are primary keys.
If you need to update your 'PK' field, you may want to add yet another 'the real' Primary Key.
So you add a field that is a GUID which is the actual PK and then you can update the other field as you need.
Also then I recommend of course indexing that field for faster performance.
My plan is for this service to be subscribed to an event on data table and simply delete original record and replace it with the newly updated PK record. Thanks for the help Pai!