For GSheets used solely as a datasource, it is recommended to define ALL columns as General Text. This avoids issues with certain UNIQUEID() function values getting automatically treated and converted in the data as Exponential Notations. If the value is used as a row key, it can mess up table relationships in the app.
For databases, the type varies some from db to db. The types between the db and AppSheet are independant. I am not certain what would happen if you defined all the types in the db as String/Text. Would it work the same as in GSheets and AppSheet can properly convert to its defined types? I don't know but i suspect it would work.
However, if you have defined column types in the db, then the types from AppSheet must correlate. For example, you can't insert an AppSheet Text column into a db Boolean column. You can't insert a Decimal AppSheet type into a db Integer column. You will get error messages on the attempts to save rows.
For types such Enum, EnumList, Image, File and any other non-corresponding types from AppSheet, I always use String/Text in the db.
There is no documentation I am aware of. So it may require some trial and error.
@Thulium wrote:
Does the data type in appsheet always "win"?
Win? There is no real winning. AppSheet has it's type db has its type. The two must be compatible such that AppSheet can accept the value from the DB into the AppSheet table AND vice-versa.
@Thulium wrote:
Is there a synchronization of data types between database and appsheet?
If yes: in one direction or in both?
No.
@Thulium wrote:
What happens if the data type is different? Does Appsheet then warn and suggest a change to the data type?
If the types defined are not compatible there will be an error message that the AppSheet row cannot be saved. It is up to the App Creator to understand which system needs adjusted.
@Thulium wrote:
Is it best practice to specify no data type at all in the data base (in Gsheets "none") or the most universal one (in Postgres)?
There is no such thing as "no type" in GSheets or a database. In GSheets, used only as a datasource, its best to define all columns as General Text. In a db, for at least some protection against garbage being inserted into the column, I recommend defining specific types where possible. The others would be String/Text.
@Thulium wrote:
Where can I find official information on this topic?
Some common DB's have documentation. PostgresSQL is one - see link below. But because AppSheet can support dozens of cloud-based and in-house databases through common protocol, they all could use their own set of column data types. So not all possible databases have documentation.
Use data from PostgresSQL
I hope this helps!
LinkedIn
Twitter
