error Bigquery Value of type System.Int64 cannot be used for a parameter of type Numeric

[Ruxandra]

Hello!

I have an app based on Bigquery tables; when I save the form in order to write data in my Bq table, I receive this error message: "Unable to add row to table 'bq_pv_santiereinterne'. → Value of type System.Int64 cannot be used for a parameter of type Numeric".

I don't understand which and where is the issue. Could someone give me some ideas which is the problem?

Thank you!

Regards!

Ruxandra 

[WillowMobileSys · Accepted solution]

---

@Ruxandra wrote:

I don't understand which and where is the issue. Could someone give me some ideas which is the problem?

---

I have been using BigQuery with Appsheet for the past 2 years.

You have mixed incompatible types between your AppSheet table columns and the the BigQuery table.  It is unfortunate that the naming conventions amongst systems are not consistent.

To match things up correctly:

AppSheet Type		BigQuery Type
Number	=	INTEGER
Decimal	=	NUMERIC
Price	=	NUMERIC
Date	=	DATE
Time	=	TIME
DateTime	=	DATETIME
Yes/No	=	BOOLEAN
Ref	=	STRING
Enum	=	STRING
EnumList	=	STRING
Text	=	STRING
LongText	=	STRING
*Duration	=	STRING
I believe all others (yet to test)	=	STRING

*I have found Duration to be problematic.  Although there is a DURATION type in BigQuery, AppSheet does not appear to transform correctly to the BiqQuery type.  Additionally, it doesn't seem that a STRING value from BigQuery is converted into Aduration at read time.  I have needed to store/read durations as Text/STRINGS and then use a Virtual Column defined as type Duration to convert the Text column to a Duration.  I have not re-tested in recent months to see if this has been addressed.

[Ruxandra]

Hello!

Thank you for sharing your experience regarding Appsheet with BigQuery. I finally understood which was the problem and I used int64 in BigQuery and Number in appsheet, it is ok now. 

Yes, you are right about the inconsistency of naming conventions amongst systems. 

I also have a problem with "regenerate schema" in appsheet, I have created another topic on this subject. Every time I regenerate the schema of my BQ table, all fields lose the check mark of "editable", so I have to check again and again (I have 150 columns ....🙈) .

Thank you!

Regards!

Ruxandra 

[WillowMobileSys]

---

@Ruxandra wrote:

Every time I regenerate the schema of my BQ table, all fields lose the check mark of "editable", so I have to check again and again (I have 150 columns ....🙈) .

---

Yes, I experience the same problem.  I have complained to AppSheet multiple times about this.  Please open a ticket to support.  Maybe with a complaint from a second person they will realize it is indeed an AppSheet issue.

A workaround is to implement an expression into the Editable property - apply TRUE to those that are editable but do not have specific criteria.  I do understand it is tedious to do this for a table with 150 columns but is worth preventing the future frustration - especially if you are still making table changes.

PRO TIP:  I would strongly encourage to look at the table with 150 columns and split it into smaller tables. There are most likely sets of data that should be in child tables.  By separating those sets into their own table, you will reduce column expression complexities and likely even overall data size as it is probable that many rows have at least some sets of columns as blank because they don't apply. 

This will also speed up the overall read efficiency as there is less data in each table to be read AND the tables can be read in parallel.

[Ruxandra]

Hello!

Yes, I saw if I had a formula, the field keeps its check mark, but I will rise a ticket to appsheet support, to improve this think; I worked with a SQL db and I didn't have this problem, so Appsheet team could improve it 😃...

Thank you!

Regards!