Data field values are 'null' in the 'Add new row' BOT Trigger event if the primary key value is UUID (?)

AppSheet AppSheet Q&A
1

Hi,
I spent half of my day trying to figure out what could be wrong with my BOT Triggering event (Add new row), but it seems that I could find strong correlation between a given table’s key type and the issue that I encountered with.

I made a quick dummy app - data source: neondb pg - to test my theory and this is what I’ve observed:

  1. IF field key is UUID type >> ‘Add new row’ Trigger event’s data only return ‘null’ values (Update event seemed working fine)
  1. When I changed the field key to numeric >> Trigger event had the actual data

Did anyone encounter with similar issue and has a solution?
Can someone confirm that it is a bug and if yes, when can we expect the fix?

Thanks for the help in advance!
Geri

1 Like
2

“pg” meaning postgres? I use it on a couple apps.

I don’t understand what you’re showing with the bot returns, what kind of bot do you have set up and what is it supposed to be doing? Is there an actual issue or error happening here with the data or the app, or is it just showing something unexpected in the bot monitor?

Is there a particular reason you need to use UUID instead of just a basic uniqueid? What is the formula in that numeric id_2 column?

1 Like
3

Hi,
Thanks for the questions!

  • Yes, I ment postgres.
  • My issue:
    The bot trigger happened properly on the addition of a new record but the trigger event’s load only contains null values. It happes when I set the field with UUID as primary key field.
    {
    ”id”: null,
    ”id_2”:null,
    ”name”:null … etc
    }
    However, when I change the primary key field back to a field that has numeric values the event’s load contains the data of the newly created record that triggered the event.
    {
    ”id”: [UUID],
    ”id_2”:”2”,
    ”name”:”Parent2” … etc
    }
  • The action of the bot doesn’t really matter I don’t have any problem with the part that is after the event (except that it doesn’t get usable data from the event)
  • I decided to use UUID instead of serial to prevent the possibility of id collision in case the users need to create new records when their devices are offline. Instead of basic uniqueid I went with the gen4 UUID standard because I wanted to use an ID that can be easily generated by the DB too in case later other applications would write to the DB too. (However, I am pretty new with DB handling it can happen that I can generate similar 8 char random strings by the DB as the UNIQUEID() function does in AppSheets by default)
  • Formula: MAX(SELECT(parent[id_2],TRUE))+1
1 Like
4

Update:
(Maybe it will be useful for someone in the future. Plus support is already involved in the investigation)

I narrowed down the issue.

The problem occurs when in the Postgres DB the field type where I want to store the UUIDs is set to type: UUID.

When I use type: text, AppSheet handles well the UUID values in the record creation’s event.

2 Likes
5

Kudos to the Support team!

They find that AppSheets generates the UUID uppercase, while Postgers converts it to lower case when the field type is UUID.

Solution:
AppSheet key generation >> lower(uniqueid(“UUID”))

2 Likes