cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Help us improve the PTC Community by taking this short Community Survey! X

How to use variable in "VALUES.<COLUMN>" as the <COLUMN>? (i.e. VALUES.variable=1)

chrish
1-Newbie

How to use variable in "VALUES.<COLUMN>" as the <COLUMN>? (i.e. VALUES.variable=1)

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaiChung
22-Sapphire I
(To:chrish)

oops that's right I was thinking Thing[ThingName](PropertyName)

try values[pkName] instead?

View solution in original post

6 REPLIES 6
PaiChung
22-Sapphire I
(To:chrish)

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.

PaiChung
22-Sapphire I
(To:chrish)

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

PaiChung
22-Sapphire I
(To:chrish)

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!

Top Tags