Here is my test:

Sheldon_Lee-Wen · 01-12-2022 06:52 AM

Hello,

My backend is currently a google spreadsheet that has customer registration data populated from an external website. Here is the process:

Customer submits a contact request via the website which goes into the google sheet with a status of NEW.
I talk to the customer and if they are interested, I send them the registration link. This is automated as soon as a send the link via appsheet and updates the status to SENT INFO.
When they fill out the registration form from the website it updates the google sheet directly and applies a status of WAITLIST to their record.

The challenge I’m having is that if I speak to the customer and send them the information, the app will apply the status of SENT INFO the next time it syncs. HOWEVER, if they fill out the website form before the app syncs, the record will have been updated with a status of WAITLIST in the google sheet, but then the mobile app will stomp on top of that update and apply the SENT INFO status and then I never see the customer order for work.

Is there a way that I can have the appsheet app recheck the status field before applying the update at sync time? Any ideas how I can prevent this?

Thanks!

Marc_Dillon

Last update wins.

I’d recommend making sure to sync immediately after the “Sent info” change. Either that, or forget about making that change at all.

Marc_Dillon

Additional suggestion. Have a child Table for “history” or “actions” of the main record. Instead of editing the main record, have new child records created for each of the “sent info” and “waitlist” steps. This way no data is ever overwritten.

Sheldon_Lee-Wen

I was thinking that might be what I need to do, but was hoping there was some simple mechanism already built in where I could just say if [status] != “waitlist” then update the status, else do nothing.

Joseph_Seddik

I used to have a similar issue caused by the app when the user saves a form that updates more than one table at the same time, and I couldn’t tell the app which table should be saved before which. I could even see this stomping live in the sheet, where one record is created with an initial value, then the value is updated correctly through the form, then the last one to be save was the table that contains that column and it is changed back to its initial value after being updated.

I solved it by adding the restricting expression into the Editable_if field. You can add the following condition for example: "NEW" = [status]. In this way, appsheet will change the status to “SENT INFO” only if the current value is “NEW”.

May be it will work for you.

Marc_Dillon

Not to my knowledge.

The problem with this would be that the app doesn’t know that the value has changed, until it syncs. But if the app is syncing, then it is already pushing its own data changes in. Sync has 4 stages, local data changes are sent to the database first, and then the entire database is loaded back in locally.

Joseph_Seddik

I understand, and I’m not sure if my suggestion would work for Sheldon and that it is against the syncing process as written. Yet, at least in my example it worked.

I’ve also just tested it again, I have a form that is supposed to change an enum column value to another based on the current value. This is controlled by an expression and closed to the possible enum values. I’ve opened the form, it showed the current value in the sheet and the new value to be written.

Just before saving the form, I went to the sheet, and manually changed the column value to “dummy” which is outside the allowed enum range and is not managed by the expression. Upon the saving the form, the “dummy” value remained unchanged. It seems that there is an additional database reading hapenning before the changes are applied. I don’t know and can’t say. I’m just recounting the events.

Marc_Dillon

Interesting. Could you add any more details about your test?

Here is my test:

I start with this data, in the GSheet, and the app:

In the GSheet, I set the value of [text] to “test”

Then, in the app, without syncing. I save the value of 5 to [number] ***

After the sync happens, you see the actual data in the GSheet, the “test” value in [text] is gone, while [number] now equals 5.

This happens because “Last Update Wins”. The more recent update, to the entire row, has [text]="" and [number]=5.

So far, all this shows is just the basic working principle, but doesn’t take into account any additional logic. So now to up the ante:

I’ve added an editable_if constraint to both [number] and [text] of [text] <> "test"
Then I repeated the exact same steps, and exactly the same result occurs.

`***` You may be wondering why I did my example this way, where my in-app change was a different column to the in-GSheet change. I did that to illustrate an even stronger point (or perhaps just more details), because I’ve answered this exact question in other threads. Even if a column does not have a value in the app, but the actual data does, the blank value will still “win”. A typical “human” response may be something like “but only the [number] column was changed in the app, so why did it also change the [text] column”, and that’s because Appsheet applies changes per the entire record/row, not just per column.

Joseph_Seddik

Sure, I’ll start with the case where I discovered this behaviour and I’m currently using in the app, then I will also detail the test.

Case of actual use

The same three tables: Contracts, Movements, Transactions that you helped me with earlier

In this topic:

Workaround for turning a virtual columns into a normal one? - #5 by KJS

Here is the scenario:

The Contracts table has a column named depositState (Enum) with three possible values: Paid, Reimbursed, or Retained. When a Contract is first created, this column is empty.
Table Movements has a reference column named Contract, pointing to Contracts table. Another column also in Movements table, contractDepositState had this app formula: Contracts[contractID].[depositState].
Table Movements also has a Transaction column which is a reference to table Transactions.

Then:

The user opens a form of Movements table, to mark the Start of a Contract. The form allows the user to choose an existing non-started Contract or create a new one, through the reference to Contracts table. A Contract has references to Clients and Services that can also be chosen or created by the user at the time of creating the Movement.
The user also creates a new Transaction in Transactions table to register the payment of the Deposit.
When the form is saved at least two records are created: in Movements and Transactions.
A Bot is triggered upon the creation of a new Transaction row. The process identifies the chain of related tables and update several columns in them, including setting depositState to Paid.

What is the problem?

Now back to step number 1. above. The column depositState is updated correctly by the Bot, and its value is now: Paid

However, in step number 2. contractDepositState remained empty.

After a lot of failed troubleshooting, I remember just staring at the sheet while the app was syncing, then I noticed this strange behaviour. I saw contractDepositState changing to Paid then changing back to empty. While depositState kept its new Paid value.

I could think of different reasons for how this is happening inside AppSheet’s blackbox but of course I would never know. So I focused on finding a solution.

The solution:

Add this condition in Editable_if: ISBLANK([_THIS])

The Test:

I had to repeat it to write an accurate account

Normal Behaviour:

A Contract can have a contractStatus of any these values: New, Open, Closed.
I launched a Movement to END the contract. The Movement form shows only Contracts with status Open so the user can choose from. The table has also a column named movContractStatus with app formula [contractID].[contractStatus]
The user also from the Movement form creates a Transaction which includes an additional check that the contract is Open. The table has a column named transContractStatus with app formula [movID].[contractID].[contractStatus]
Upon saving the form, two rows are created: in Movements and Transactions.
A Bot launches an action triggered by a new Transaction record to update several columns including contractStatus based on conditions in Movements and Transactions.

What I did:

Just before saving the form, I manually changed the value of contractStatus in the sheet to “dummy” then saved the form via the app.

What happened:

Final values in the sheet:

contractStatus is: “Closed”
movContractStatus = [contractID].[contractStatus]is: "dummy"
transContractStatus = [movID].[contractID].[contractStatus] is: “Closed”

Marc_Dillon

I will have to read this again tomorrow (and where are the screenshots?)

But just for now, this isn’t a correct expression:

Is that your actual expression, or just a typo?

Joseph_Seddik

Tomorrow I’ll repeat the test and capture the screenshots for you
I’ll also try to mess-up my app again and capture a video showing the sheet values appearing and disappearing during sinc. so you can enjoy it live

Yeah that was long ago, and I replaced with just [contractID].[depositState]. But Contracts[contractID].[depositState] WORKS THE SAME.

Steve

If I recall correctly, this:

Contracts[contractID].[depositState]

is equivalent to this:

[contractID].[depositState]

The leading table name is entirely ignored. For instance, the following is also equivalent:

MooseNuggets[contractID].[depositState]

Joseph_Seddik

Haven’t got the time but haven’t forgotten

Sheldon_Lee-Wen

Unfortunately that is not always possible as the user may not have data connectivity.

Steve

AppSheet is not designed to share the spreadsheets it uses with other, non-AppSheet processes.

Sheldon_Lee-Wen

I get that, unfortunately that’s not really realistic in the real world where you need to integrate with things.

Stomping on table updates

Here is my test:

So far, all this shows is just the basic working principle, but doesn’t take into account any additional logic. So now to up the ante:

Case of actual use

The Test: