Null input data in bot

squatch4014 · 11-03-2021 11:18 AM

So I’m running into an issue when a bot executes whilst saving record against a table.

Background… My app is displaying data via an API that is in front of my database. I want my initial view to show rows by their numeric ID in descending order, I created a database VIEW. This view accomplished what I wanted.

Process… On my table view, I have the following action.

This process loads the form correct and allows me to input data. I selected this process because I have a database trigger that adds the primary key to the table. With my database I can’t insert a record in the view because my primary key is null. I have to insert the data into the primary table. That’s fine. I thought I would create a new action and call the form that is linked to the primary table and add data that way.

Problem… Based upon a selection on the form I need to modify a couple of columns before the data is “synced” with my database. I created a bot to accomplish this. I have the bot defined correctly and it’s getting called as I can see in the AppSheet Monitoring page. However, the data coming over from the bot is null for all values. See below image.

Is there any idea why the payload going into the bot would be null for my values? I find this odd because my data does post to the backend correctly. I would assume that it’s a timing issue.

Steve

If the database table’s primary key is the same as the app table’s key, your approach will not work. The app MUST set the app table’s key value.

squatch4014

The app is setting the PK, as far as it’s concerned.

The app posts the data to my API during the on the save action. I intercept the post request from the app to the backend and remove the key from that payload. By doing this it allows my database to use the trigger and set the PK on insert. The payload coming back from the API then has the correct PK as assigned by the database.

This functionality is working as expected; with the bot enabled or disabled.

Steve

What you’re doing is entirely unsupported. I got nothing for you.

squatch4014

Thx

WillowMobileSys

I believe…

you have a fraction of time where the Primary keys do not match. Keep in mind that the AppSheet servers “collect” all the datasets to operate on. At a minimum, there is one that represents the app data table and one that represent the datasource data table (database in your case).

If the App is setting a primary key and then the database is replacing that key, there will be a moment of processing time where the AppSheet server representation of the app’s table and the representation (on the server) of the database table are out of sync. If the server tries to access the database data row via the primary key it THINKS is the correct key, it will not find the row.

I believe that is what is contributing to the row values reflecting as null, the row is simply not found. Just a guess…your process is probably skirting the normal validation of row existence which is why no error is reported. This use case simply has not been accounted for.

Now, once the database has replaced the primary key AND the usual Sync takes place, all is probably well because AppSheet will have updated the row key to match the database.

If you MUST assign a Primary key from the database, I might suggest trying to add the AppSheet key as a secondary key rather than replacing it. I am not completely certain if it is possible to have a different column act as the Primary key in database versus the AppSheet table. I would think the AppSheet server would marry the AppSheet table to the database table via the specific column rather than by the column tagged as the Primary key. In which case you can have a different Primary key in the AppSheet table versus the database table. Both systems simply track the others PK as a secondary key.

Steve

It is.