Intermittent Ref columns changing to 1900 dates and causing “Invalid request provided to AppSheet database”

[Sherin_Premkumar]

Hello,

I’m experiencing an intermittent issue with an AppSheet app using AppSheet Database as the data source.

Occasionally, while a user is editing a record through the standard AppSheet Detail → Edit form, values in unrelated Ref columns unexpectedly become dates such as:

02/14/1900
01/04/1900

For example, in one occurrence:

Referral Source → Ref to ReferralSourcesMaster
Referenced key: Source ID (Text)

Primary Insurance Name → Ref to InsuranceMaster
Referenced key: Insurance Master ID (Text)

Both Ref columns are physical columns. They have:

• No App Formula
• No Initial Value
• No Valid If
• No Suggested Values
• Reset on edit OFF
• Correct Text keys in their referenced tables

The issue is intermittent and has occurred in different Ref columns. It does not happen on every edit.

Audit History shows the 1900 date values in the Edit row Start payload, and the subsequent update fails with:

Unable to update row in table 'Referrals'. → Invalid request provided to AppSheet database

The stack trace includes:

AsdbProvider.AsdbTable.UpdateRowsAsync

The affected users are using the browser and editing through the normal AppSheet form. There are no Quick Edit columns involved.

Current sync settings:

• Server caching: ON
• Delta sync: OFF
• Quick sync: ON
• Sync on start: ON
• Delayed sync: OFF
• Automatic updates: ON

We have also cleared AppSheet browser/site data for an affected user, but the issue occurred again.

In one failed request, Audit History showed two unrelated Ref fields containing 1900 dates in the same submitted row.

Has anyone experienced Ref values intermittently being converted/interpreted as 1900-era dates when using AppSheet Database?

Could this be related to Quick Sync/client row state, or is there another AppSheet Database/Ref configuration I should investigate?

Any suggestions for additional Audit History information I should capture would be appreciated.

[WillowMobileSys]

The 1900 year mostly likely indicates that the desired date is not being constructed as you are expecting.

I had a similar problem recently and realized that I was sending a data column that I thought was a full date but was actually TIME only. The date was set to the the default date with no error reported.

CAREFULLY look over the date column AND any other Date and/or Time columns being used to construct the desired Date and verify they are all properly DEFINED and assigned.

Of course, if you can include your expression here, we would be happy to look it over.

[Sherin_Premkumar]

thank you so much for helping ,

Actually I am not constructing date here in these fields.

these are ref columns to Master table.

These columns have no formula.

The key in those master tables are type text (with numbers 1,2,3 serialized)

[WillowMobileSys]

Sorry, I misunderstood this part…

@Sherin_Premkumar

values in unrelated Ref columns unexpectedly become dates

Can you show an example so we can fully understand WHERE in your data this is being seen?

You are using AppSheet Database. Make sure the column data type in the db matches the app data type.

[Sherin_Premkumar]

so I did not have any Valid If conditions earlier in the column

(lets call it table A. column type is ref to table B)

The key of the table B was IDs such as 1, 2, 3.

so since I did not have any Valid If .. these IDs were being converted to dates automatically and very randomly then it would change to IDs again in the database.

Now I added a valid if condition in the ref column as TableB[ID] and its working fine now.

thank you @WillowMobileSys

[Sherin_Premkumar]

@Jose_Arteaga hi,

The issue has happened again just now. The Key IDs are configured as Text, but numeric IDs are occasionally being converted into dates on the client side.

For example:

• Key ID 67 is being converted to 03/07/1900
• Key ID 23 is being converted to 01/03/1900

The values are still stored correctly as 67 and 23 in the AppSheet database, but the client is converting them to dates when processing the Ref fields. This then causes the row update to fail because the converted date is not a valid Ref key.

This is still happening even after adding Valid If validation.

[WillowMobileSys]

@Sherin_Premkumar

The values are still stored correctly as 67 and 23 in the AppSheet database,

The data type in BOTH the AppSheet Database and the app Table is defined as Text?

@Sherin_Premkumar

but the client is converting them to dates when processing the Ref fields

And I assume what you mean by this is that you have SECOND table with a REF column and You have the PRIMARY type of that column defined as “REF”? And it’s source table is set as the FIRST table mentioned above with the Text based key column?

Lastly, HOW are you assigning the values to this REF column? What expression are you using?

---

Giving us visual details of your issue and how you have things defidne will go a LONG way in us being able to more accurately AND more quickly help you resolve this issue.

[Sherin_Premkumar]

Thank you of course

The data type in BOTH the AppSheet Database and the app Table is defined as Text? - Yes

And I assume what you mean by this is that you have SECOND table with a REF column and You have the PRIMARY type of that column defined as “REF”? And it’s source table is set as the FIRST table mentioned above with the Text based key column? - Yes

Lastly, HOW are you assigning the values to this REF column? What expression are you using? -

The values are not being assigned using an expression. The Ref columns are selected directly by the user from the dropdown.

Insurance Name is a Ref to InsuranceMaster, where Insurance Master ID is the Key and is Type Text.

[WillowMobileSys]

@Sherin_Premkumar

The values are not being assigned using an expression. The Ref columns are selected directly by the user from the dropdown.

Ok, a COMMON mistake made with REF columns is the Valid_If expression assigns a list of values INSTEAD of ROW KEYS. A REF column MUST BE assigned row keys

In order, to show a HUMAN READABLE value for a REF column, the source table needs to set the LABEL property of the table being referenced. AppSheet will AUTOMATICALLY display the Label value instead of actual row keys

---

Below is an example of REF column to a Clients table where I want the user to see the “Company Name” column in the dropdown

Example Clients REF column in Form View showing ALL Company Names for the Clients

Client table with the Label property set to “Company Name” column

Column definition for the Client REF Column

I hope this finally resolves the issue!!

[Sherin_Premkumar]

I think I have it exactly in the same way.?

[Sherin_Premkumar]

@WillowMobileSys @Jose_Arteaga @Steve

Quick Sync feature error

I recently started using the quick sync feature, which is a great addition. On random occasions while editing a row, certain columns will get filled with the date 01/01/1890. Even thought the column type is set to number. Some of the columns are not even visible to the user and then gives them an error with invalid entries in the column. Not sure how to fix this, I tried regenerating the structure and checking all auto computes / initial values. Thanks Kyle

Do you think it is a bug?

Cause I am also using Quick Sync!

[Steve]

It certainly could be related. It’s clearly a bug. I recommend you engage Support for help. Someone will have to look into this on the server side. In the meantime, turn off Quick Sync.

[Sherin_Premkumar]

Thank you

It also just not happen to me in this column alone but in 3-4 Ref columns.