Set DateTime to null programmatically

[AS_10340651]

Hey there,

i have an infotable where i set the datetime and at a specific point this datetime need to be set to "null" or just unset programmatically. I tried to set to null, to Zero (still a datetime and not unset).

And then i found this: https://www.ptc.com/en/support/article/CS253788

I dont like to delete and insert again, i prefer to update. And it seems like there is no other solution than that.

For alot of my problems i see always only a workaround and no real solution and after a workaround is applied then noone cares anymore about this problem and this workaround will exist like for ever.

how is it possible that this workaround exists almost 2 years and no real solution got applied?

[Sathishkumar_C]

tried with ThingWorx 9.1.11

let params = {
	infoTableName: undefined /* STRING */
};
let info = Resources["InfoTableFunctions"].CreateInfoTable(params);
let newField = {
    name: 'dt',
    baseType: 'DATETIME'
};
info.AddField(newField);

info.AddRow({"dt": new Date()});
info.AddRow({"dt": new Date()});
info.AddRow({"dt": new Date()});

info.rows[0].dt = null;
info.rows[1].dt = 0;

result = info;

got result as following,

[AS_10340651]

Thanks for your try. I tried this aswell.

Try to save it to an Infotable and then try to use "UpdateDataTableEntry". You will realize the infotable wont accept the "null" parameter and it will keep the old value.

[Sathishkumar_C]

Yeah, that's correct. It's not updating as NULL in the DataTable. Let you know if i got anything related to that, other than delete and insert.

[jensc]

Hello,

Just wanted to say that I logged out the values and saw that the value is actually undefined and not null;

And it seems like using the AddOrUpdateDataTableEntries allows this undefined value (as long as its not the key I would assume, I added a string to use as key for testing).

As the value is not "null", I would assume that when trying to update the value that already exists in the DB, it doesn't as there is no value to replace it.

But this is just my assumption, someone from PTC would have to confirm. 

As a datetime that is "0" starts at 1970 that doesn't work either. You could possibly try to first convert the datetime to a string and set that string to empty.

Not sure if this would work though as some conversion might see that as a 1970 as well.

Regards,

Jens

[AS_10340651]

Hello Jens,

thanks for your investigation. I guess this is not on priority at PTC. sadly.

I solved my issue with this solution "get the values of the row, and delete the date field then delete the row and add a new entry with the deleted field".

As i said, i dont like to delete and add. There is a reason why we have update functions and i would be  happy if PTC would pay attention to this issue.

[wcui · Accepted solution]

I did below test with latest TW9.3.7.

1. create a DataShape with Datetime field [DT] (not key field).
2. create a DataTable with above DataShape
3. run below service, got below results.

As we dont have plan to accept null or undefined for UpdateDataTableEntry service, I also suggest to set to a special value instead of null.

Please let us know if this workaround does not work for you and share more business background so we can help.

--------------------- service code --------------------

let dt1 = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({

infoTableName: "InfoTable",
dataShapeName: "cuiDS1"
});

let newEntry = {
ID: "1", // STRING [Primary Key]
Value: "1", // STRING
DT: *** // DATETIME 
};

dt1.AddRow(newEntry);

Things["cuiDT1"].UpdateDataTableEntry({
sourceType: undefined /* STRING */,
values: dt1 /* INFOTABLE */,
location: undefined /* LOCATION */,
source: undefined /* STRING */,
tags: undefined /* TAGS */
});

-----------result of GetDataTableEntries ----------

"2000-1-2" --->  2000-01-02 08:00:00.000

null              --->  failed to update

undefined  --->  failed to update

0                  --->  1970-01-01 08:00:00.000