API: Frontend out of sync with backend?

behavior
workflow
(Michael) #1

Two tables; Jobs (parent) & StopTime (child)

StopTime has two relevant columns; [StopBegin] & [StopEnd]

In a detail view of the Jobs table I have an action button, “Add Stop” that triggers a webhook which creates a row in the StopTime table. This newly created row will have the [StopBegin] column filled with the current time. The [StopEnd] column will be blank.

At this point, an action button to “End Stop” should be visible. The condition to show this button relies on there being a related row in the StopEnd table with a blank [StopEnd] column. This will always be true if the webhook ran successfully.

My app has ‘Delayed Sync’ disabled. Any change the user makes causes the app to sync immediately. When the user clicks the ‘Add Stop’ action, the app beings to sync and the row is created in the StopTime table. When the app returns from syncing, the ‘End Stop’ button SHOULD be shown because a new row with a blank [StopEnd] column was just created. Problem is, the button is not shown. Its as if the condition for this button was not met before the app finished syncing and the view is shown.

If I re-sync the app, the condition to show the button is met and the button is shown.

I’d appreciate any help or opinions.

@LeventK @praveen @Aleksi

(Levent Kulacoglu) #2

@Michael
With the Delayed Sync, you are possibly using the Background Updates also. And as you are creating the additional row with the API, the app will first record the parent record and than create the child record. So it’s inevitable that you won’t be seeing the action button till the background sync finalizes because, as there won’t be any child records in the table yet, the conditional rule for that action button will not be met.

This is not a bug but by-nature the sequence is like this. However I might propose a workaround:

In the parent record table, create an action with Data: go to another view in the app and use this expression:

LINKTOVIEW("TheViewNameYouWantToShowAfterFormSaved") & "&at=" & (NOW() + 1)

and create a Grouped: execute a sequence of actions action together with your Add Stop action, this action being the first one to be executed. This will force a hard sync of the app, when synced you will notice the End Stop action as the child record will already be created.

4 Likes
#3

@LeventK - nice!

(skinee dog) #4

@LeventK The ‘Add Stop’ is an action which triggers a web hook, and is shown to the user from the parent table. This web hook adds a row to the child table with no other action from the user. The user is never shown the child table.

How can I group actions which target two different tables?

(Michael) #5

@LeventK The ‘Add Stop’ is an action which triggers a web hook, and is shown to the user from the parent table. This web hook adds a row to the child table with no other action from the user. The user is never shown the child table.

How can I group actions which target two different tables?

(Levent Kulacoglu) #6

@Michael
I should say “parent” table but unintentionally mentioned the “child” table, sorry. Edited my post and corrected my mistake.

(Michael) #7

@LeventK I tried that yesterday but wasn’t having any luck. Tried again this morning and Success! Thank you, again!

If it’s not too much to ask, could you explain what " & “&at=” & (NOW() + 1)" is doing? And, should adding this to any ‘LinkTo’ expression do the same? Force a sync…

(Levent Kulacoglu) #8

You’re very welcome @Michael, truly my pleasure to be helped of. Glad to hear that your problem is solved.

The suffix at the end of LINKTOVIEW(...) deep link will force your app to perform an immediate sync (via NOW() + 1) right before displaying the view you have set. So basically it will work with every deep link action depending on where and how you have used it.

(Michael) #9

@LeventK, I may have spoken too soon. It seems to not work consistently.

The expression I use to determine whether the button is shown:
COUNT(SELECT(StopTime[StopID],AND([JobID]=[_THISROW],ISBLANK([StopEnd]))))=1

The ‘Add Stop’ button adds a row to StopTime with the [StopEnd] column being blank so, if the action was fired, this statement would be true once the record is created in the StopTime table.

If I manually re-sync, the button is shown. Its as if the previously forced sync you suggested has no effect. The sync completes before the row is created therefore the statement COUNT(SELECT(StopTime[StopID],AND([JobID]=[_THISROW],[Reason]=PAUSED,ISBLANK([StopEnd]))))=1 is false.

(Michael) #10

@LeventK I am certain the issue is that, even though a sync is being forced, the front end syncing is completed and displayed BEFORE the creation of the related child row is completed.

(Michael) #11

@praveen @LeventK @Phil
I’m calling this a bug. The way the API web hook seems to be working, the sync is unaware of the transaction. My understanding of the API was to not only, in this case add a row to another table, but to also make the app aware of this transaction.

When the app syncs, the web hook is triggered but the sync is unaware of this.

(Praveen Seshadri (AppSheet)) #12

I need to check with @Phil about webhook firing and what this means for data consistency.

However, the heart of the problem is that you have to use the API at all. You should be able to make all the desired changes directly via AppSheet actions. The problem is that we do not have a “CreateRow” action and so you have to jump thru hoops (the REST API) to achieve it.

(Michael) #13

@praveen Thank you for saying that… It’s exactly what I was thinking but, I love AppSheet and didn’t want to seem too critical.

With the following please keep in mind that, I am not a developer/programmer and may have no idea what I’m talking about:

My understanding of the purpose of exposing the API is

  1. so that other platforms can interact with AppSheet apps
  2. adds the ability of adding or editing data in other tables

While this seems to work as expected, what value does this have if the app whose data was changed is unaware of this change. I would think that triggering an API call would signal the app that a change was made and a sync is needed. This doesn’t seem to happen. The change isn’t reflected until some other reason for syncing takes place.

This is a separate but related issue, of course. In this post: Background syncing, I talk about the syncing not taking place at any interval unless a change is triggered in app. No other user’s (or API) change causes a user’s app to sync.

(Philip Garrett) #14

HI Michael,

I can investigate if you provide:

  1. Your account id
  2. The app name
  3. The exact steps to reproduce the problem.
  4. You permission to perform the repro steps while I use the debugger to see what is happening. This will almost certainly require that I add records or make updates while I do the debugging. If you prefer, you can make a simple test case that reproduces the problem, or you can make a copy of the app that reproduces the problem, and I can debug that.

The webhook is normally invoked synchronously, so I am not certain why the client is not seeing the newly added record.

The Audit History includes records reflecting:

  1. The original change that triggers the workflow.
  2. The workflow rule and its outcome.
  3. The API call and its outcome.
  4. The client Sync call to read the final results.

I am not sure if you have looked at the Audit History, but it may shed light on what is happening. All of the Audit History records are timestamped so you can see the order in which things occurred. I will be looking at the Audit History in addition to trying to reproduce the problem.

There is currently no mechanism to send a “data has changed notification” to all clients when a data change has occurred. This is true whether the data change originates:

  1. From another AppSheet user performing an update via their client device.
  2. From Zapier invoking the API.
  3. From a free standing app invoking the API.
  4. From a webhook invoking the API through workflow.
  5. From changes made directly to the Google sheet.

We have talked about adding such a capability, but it is a fair amount of work.

(Michael) #15

@Phil
Thank you for taking the time to look into this.

Because I found a suitable workaround after my initial posting, the issue was no longer present in that particular app. As such, I created a test app in an attempt to re-create the issue for troubleshooting. However, it seems that either something on AppSheet’s side has been changed which resolved the issue or I have corrected something that I initially had wrong in the first app. In any event, the issue seems to be resolved. Newly created rows are showing up in the app that made the change within a minute or so and I see that the clients are syncing in the background within about 30 minutes, where previously no sync was taking place.

In an attempt to understand how the API process works, I make the following assumption. Please correct me if I am wrong:

A change is made via an API call which is recorded with a timestamp on the AppSheet server. At approximate 30 minute intervals, the client checks it’s table timestamps against the server’s timestamps with differences triggering the client to sync in the background. Any change made outside the client or without the use of the API would make the AppSheet servers unaware of the change and therefore when the client checks, there are no differences between the app and Appsheet server’s table timestamps.

Thirty minutes is a very long time to wait for background syncing. What is preventing a simple notification from being sent to the clients that simply tells the client that the API has made a data change, the client should compare its timestamps now rather than waiting the full 30 minutes…

In any case, you and AppSheet are doing phenomenal work. Thank you.

(Philip Garrett) #16

If the API is invoked from a webhook, that API update should occur synchronously. As a result the client should see the result of the webhook API update when the client call completes. I am not sure why you were not seeing that. That was my motivation for asking for the test case that reproduces the problem. Having a test case would allow me to investigate why the client is not seeing the results of the webhook API update. If you see the problem again, I am happy to investigate further.

There is currently no mechanism to send a “data has changed notification” to all clients when a data change has occurred. This is true whether the data change originates:

  1. From another AppSheet user performing an update via their client device.
  2. From Zapier invoking the API.
  3. From a free standing app invoking the API.
  4. From a webhook invoking the API through workflow that is triggered by a update from a different client device.
  5. From changes made directly to the Google sheet, Excel worksheet, Smartsheet, SQL Database, or other store.

We have talked about adding such a capability, but it is a fair amount of work. Sending client notifications is complex. Efficiently detecting changes to Google sheets, Excel worksheets, Smartsheets, SQL Databases, or whatever is very complex, especially when the changes do not go through AppSheet.