Different timezones in parent table key and child table reference

[nhcohen]

I have a parent table in which I want to use the row-add time as a key. The key field is named Timestamp, and I gave it the initial value NOW(). There is a child table with a column case data that is meant to contain a reference to the corresponding row of the parent table. I have a bot invoked when a row is added to the parent table, which adds a row to the child table, with the child ref case data set to the parent key value [Timestamp].

I found that the Timestamp field in the parent table was set using my local time zone (UTC+3, since daylight saving time is now in effect here), but the case data field that is supposed to reference the added parent row is set to the same time in UTC. As a result, the reference doesn't work. (When I manually adjust the Timestamp field to UTC by subtracting 3 hours, the reference works.)

I changed the initial value of the Timestamp field from NOW() to UTCNOW(). (A UTC timestamp is a safer key than a local-time timestamp anyway, since when the local-time clock is turned back an hour at the end of daylight saving time, an hour's worth of local times occurs twice, possibly resulting in duplicate keys.) However, this had no effect! The Timestamp field is still being filled with the local row-add time!

In summary, there are two issues:

1. The UTCNOW() function is returning the local time.
2. When the Timestamp key field in the parent table is set to the local time, the case data reference field in the child table, supposedly set to [Timestamp], is actually set to the corresponding UTC time.

[SkrOYC]

---

@nhcohen wrote:

I have a parent table in which I want to use the row-add time as a key

---

That's the problem

[nhcohen]

Please elaborate. I see no prohibition in the Appsheet Help page "What is a key?" on using a timestamp as a key. In any event, it seems strange that UTCNOW() should return the local time rather than UTC, or that a timedate value should undergo an implicit timezone conversion when assigned to a reference.

I tried adding a column with initial value UNIQUEID() and marking it as a key field (while keeping the timestamp column for application use). I followed the instructions in the "Manual key selection" section of the Appsheet Help page "What is a key?", but after I click on "Regenerate Structure," the UNIQUEID() column I added is no longer marked as a key  field. Instead, a new virtual field _ComputedKey is added, consisting of the concatenation of the UNIQUEID() field and the timestamp field. Is there a way to override this behavior?

[SkrOYC]

Make sure that the new column on your worksheet doesn't have a formula, instead the plain pasted-values generated manually.

Also, when changing the key column, you should only select one. If you select more than that, a _ComputedKey virtual column is created

[nhcohen]

Make sure that the new column on your worksheet doesn't have a formula, instead the plain pasted-values generated manually.

I don't understand what you mean by "the plain pasted-values generated manually." The new column does not have a formula, but it does have the initial value UNIQUEID(). Isn't that the right way to get the corresponding field of a row set to the value of a new call on UNIQUEID() when the row is added?

Also, when changing the key column, you should only select one. If you select more than that, a _ComputedKey virtual column is created

I had only one column (the new one) checked as a key column in the editor, but I noticed that the comment in the timestamp column in row 1 of the source spreadsheet said "IsKey":false. When I manually changed false to true in the spreadsheet comment and regenerated the column structure, it worked the way it was supposed to, without generating a computed key. 

[SkrOYC]

---

@nhcohen wrote:

have a bot invoked when a row is added to the parent table, which adds a row to the child table, with the child ref case data set to the parent key value [Timestamp].

---

Bots are executed on server, is your [case data] value changed via bot using an expression like NOW() as well or it's taken with a dereference or similar?

[nhcohen]

It's just a dereference, [Timestamp].

[SkrOYC]

Why are you adding a child record via bot? You need an automatic way of adding at least a children like on one-to-one? Try using Actions instead of a bot and see if the problem is still there

[nhcohen]

The parent record represents a case and the child records represent log entries for that case. When a new case is opened, a parent record is created, and one child record is created, representing an initial log entry that says "Case opened". Later child entries representing additional log entries for the case will be created by actions.

[SkrOYC]

You can make an action that adds a log right after you hit "save" on the device when making the parent record instead of waiting for a bot that runs on server.

Now, the problem related to different timezones is still a mystery for me, but it seems it's not actually a problem for you if there is a more robust config around the usage of key values

[nhcohen]

Correct, I now want to use just the value of UNIQUEID() as the key for the parent record. I tried the following steps:

1. Add a new column internal case ID to the source spreadsheet.
2. Click "Regenerate Structure", which adds the row to the editor.
3. In the KEY column of the column editor, remove the checkmark from _RowNumber and add it to internal case ID, change the type of internal case ID to Text, uncheck SHOW, check REQUIRE?, and set the INITIAL VALUE to UNIQUEID().
4. Save the changes.

However, when I add a new row, the internal case ID cell remains empty. What am I missing?

[SkrOYC]

First, if you already have rows on that table, you will need to manually create key values for all of them. The editor should be giving warnings about it.

Second, [Internal case ID] is the only empty column, right?

[nhcohen]

I didn't notice any warnings, but I did manually create key values for the existing rows, as described in "Manually generate UNIQUEID() key values".

All the other columns are filled in as expected when I add a new row (through Google Forms). Only the [internal case ID] column is not filled in. FWIW, I notice that in row 1 of the source spreadsheet, there is no comment in the [internal case ID] column, but there is for the other columns.

[SkrOYC]

---

@nhcohen wrote:

(through Google Forms)

---

Man, you should have mentioned this before.

Well, I cannot provide so much considering that you need this working from outside AppSheet.

You should ask help from support and wait for other to help as well

[nhcohen · Accepted solution]

See the reply from AppSheet support, and my workaround solution, here.