Problem: Action is randomly removing data from a column (and it shouldn't)

[thelimp]

Hi. 

I have a problem with an action which removes a value in a cell it's not supposed to, but I can't understand why. 

I have a table called PROMOTION, this is the one my users are updating during their promotion day. The day after in the morning at 6am, I need to have a bot that check that they updated all the required columns on the PROMOTION table (that they completed their report), and if yes, switch the [Status] to "CLOSED".

I have Appsheet starter, therefore I created a cronjob which daily send at 6am an API request to Appsheet on a TRIGGER table i created especially for this purpose. Once a day, Cronjob send an API request to execute an action (Set value to of column TRIGGER[1st_check] to NOW()). Here:

{
"Action": "1st check datachange",
"Properties": {
"Locale": "fr-CH",
"Timezone": "W. Europe Standard Time"
},
"Rows": [

{
"Title" : "1st_check"
}
]
}

This data change triggers a bot (let's call it BOT_1) that "run a task - Call a webhook" on the PROMOTION table, check the rows that should be closed (PROMOTIONS from yesterday) and set the value of a column [Trigger_bot] to NOW(), which will then trigger another bot that will check if the report is done. 

This is the webhook:

{
"Action": "Edit",
"Properties": {
"Locale": "fr-CH",
"Timezone": "W. Europe Standard Time"
},
"Rows": [
<<Start:SELECT(Promotions[id],AND([Status]="OPEN",[Promotion Date]=TODAY()-1,ISBLANK([not present?])))>>
{
"id" : "<<[id]>>",
"Trigger_bot" : "<<now()>>"
}
<<End>>
]
}

So this action triggers the bot (BOT_2) that checks if the reports are fully completed and if yes, changes the [STATUS] to "CLOSED".

now, my problem is that the action triggered by BOT_1 randomly delete another column from the rows.

Let me explain, on the PROMOTION row, i have some columns to let the users fill in their travel expenses. They select [CHARGE 1 TYPE] which can be "no charge", "public transportation" or car", and then they need to select their [CHARGE AMOUNT]. If they select "NO CHARGE", the [CHARGE AMOUNT] is reset to 0. If they select "public transportation", they can enter manually the [charge amount], and if they select "Car", then a dropdown ([CHARGE 1 KM]) appears to select how many KM which will automatically calculate the [CHARGE AMOUNT CAR].

PROBLEM: the BOT_1 action, which only updates some rows [TRIGGER_BOT] column to NOW(), also randomly erase the [CHARGE 1 KM] to "" (blank), as well as the  [CHARGE AMOUNT CAR ]. it's weird because some of the rows keeps the values entered in the columns, but some of them no! I really don't understand why. [CHARGE 1 TYPE] never gets removed however.

Here is what I tested so far:

• intially, I didn't use the webhook, but the API called a group action on table TRIGGER, which includes an action as follow: "Execute an action on a set of rows". Referenced Table: "Promotions". Referenced rows: 

  SELECT(Promotions[id], AND([Status]="OPEN",[Promotion Date]=TODAY()-1,ISBLANK([not present?])))

  Referenced action: an action that sets Promotions column [TRIGGER_BOT] to NOW(). 
  Same problem, when the action is triggered, it randomly removes to blank the columns related to the charges.

• If i make a test, fill in the report and then shortly after test the API CALL, it works perfectly. If i wait overnight and that the conjob call the API at 6am (therefore long time after the report has been filled in), then the problem occurs. 
• I tried to manually click on the grouped action, it sets to [Triggers_BOT] to NOW() but also randomly erase the charges column.

Here is the process: 
on the PROMOTION row detail view, thex can click on the bag of money:

 
Which is an action "Go to another view within this app" which redirects to LINKTOROW([_THISROW],"Charges edit | reporting not done"), which is a form including only the charges related columns from the row.

This is the view of the form 

I don't know if the problem is because of the formulas from the columns, the fact that the columns are updated through a form and the rest from quick edit columns or something else. What I really don't understand is that sometimes the action don't remove the columns values, and sometimes yes.

Here are all the colums details: 

• [Charge 1 type]
  - Show: not(OR([User Role]="Fix Promoter",AND(CONTEXT("viewtype")="detail",ISBLANK([Charge 1 type]))))
  - Type: ENUM
  - Values: No Charge, Car, Public transportation, BASE: TEXT
  - Initial value: if(
  [_THISROW].[User Role]="Fix Promoter", "No Charge","") --> This is because fix promoters don't fill in their charges
• [Charge 1 km]
  - Show: [Charge 1 type]="Car"
  - Type: ENUM
  - Values: 0-50,51-100,101-150,151,200,  BASE: TEXT
  - Require?: [charge 1 type]="Car"
  - Reset on edit?: [Charge 1 type]<>"Car"
• [Charge 1 amount]
  - Show: AND([Charge 1 type]<>"No Charge",[Charge 1 type]<>"Car",isnotblank([Charge 1 type]))
  - Type: Price
• [Charge 1 amount car]
  - Show: if([Charge 1 type]="Car",true,false)
  - Type: Price
  - App Formula:IFS([Charge 1 type]="Car",ifs(
  [Charge 1 km]="0-50",10,
  [Charge 1 km]="51-100",20,
  [Charge 1 km]="101-150",30,
  [Charge 1 km]="151-200",40,
  [Charge 1 km]="201-250",50),
  )

Many thanks for your help, I know there are quite a lot of info but i tried to give you everything as I can't figure out why the action removes another column it is not asked to touch. It's like Appsheet forgot the column has a value and rewrite the whole row without the KM selected.

Hope you can find what is my problem, most probably I missed something easy! I hope you can help me out. don't hesitate if you have questions!

Thanks,David

[Joseph_Seddik]

Check if the Reset on edit property of your columns is active. This needs to be disabled.

[thelimp]

Hello,

Yes, reset on edit is disabled.. Checked this morning the reports from this weekend, on 16 rows with expenses, 10 have been removed.

[Joseph_Seddik]

What I can think of, is checking whether there are other bots that are also triggered by a data change in the same table. I might also try disabling the "trigger other bots" option in the bot. 

[thelimp]

Even if i disable the bots, it is when the action is triggered that the data are removed.

[dntrplytch]

>> I tried to manually click on the grouped action, it sets to [Triggers_BOT] to NOW() but also randomly erase the charges column.

It seems you can readily reproduce the issue? If so, could you explain a bit more about the Grouped action? What are the actions in the group actions?  Do any of these set values in columns that seem to get overwritten?

[thelimp]

The group action is only here because of the API. can't call a "data: execute an action on a set of rows" action through the API so I wrapped the "data: execute an action on a set of rows" in a grouped action.

So the grouped action triggers a "data: execute an action on a set of rows" action:

Referenced table: PROMOTIONS
Referenced Rows:

SELECT(Promotions[id],
 AND([Status]="OPEN",
    [Promotion Date]=TODAY()-1,
      ISBLANK([not present?])
 )
)  

--> it finds the rows that should be checked

Referenced Action: "Increment bot_trigger column"

• Increment bot_trigger column:
  DATA: Set the values of some columns in this row
  Set these columns: Trigger_bot to NOW()

And here sometimes the values get erased. 

[dntrplytch]

Would it be possible to provide an example of data prior to the action being manually clicked and the data after the action is executed?

Also, is the data being changed outside of the Appsheet app?

Also, how exactly is the column "not present?" set?

Lastly, is there a reason you have the 

Reset on edit?: [Charge 1 type]<>"Car"

on the "Charge 1 km" column?

[thelimp]

Here is a screenshot of the data before / after the action being triggered:

And after:

I also made a video: Here is when I call the API: https://drive.google.com/file/d/1onuc_gGfgIqHCKe3MDDvsf_BIgNPXyCP/view?usp=drive_link 
You can see the date disapearing. Here is an overview of that are the actions: https://drive.google.com/file/d/1WhpRwoZSnRXFE2vHOq5oLfsBnuE8e587/view?usp=sharing 

The "reset on edit" for [Charge 1 km] is in case they make a mistake --> they select car, then choose some km, then switch to "Public transportation". If i don't have this reset on edit, they will have the refunded amout for the km they selected + the public transportation amount. Do you think this is the problem? I can maybe try to delete this condition, and instead change the App Formula in the [Charge 1 amount car] and that put 0 if something different than "CAR" is selected..

EDTI: just thought about it, it can't be the reset on edit? condition, as the [Charge 1 amount] is deleted as well when public transportation charge is uploaded...

[dntrplytch]

The overview of the video for actions appears to be the same as the video where the data vanishes (the first video). Would you please doublecheck and upload the Actions video?

It may also be helpful to have information about how the columns are configured in AppSheet. 

[thelimp]

Ohh sorry I edited the link.

I have this for the column detail:

---

@thelimp wrote:

• [Charge 1 type]
  - Show: not(OR([User Role]="Fix Promoter",AND(CONTEXT("viewtype")="detail",ISBLANK([Charge 1 type]))))
  - Type: ENUM
  - Values: No Charge, Car, Public transportation, BASE: TEXT
  - Initial value: if(
  [_THISROW].[User Role]="Fix Promoter", "No Charge","") --> This is because fix promoters don't fill in their charges
• [Charge 1 km]
  - Show: [Charge 1 type]="Car"
  - Type: ENUM
  - Values: 0-50,51-100,101-150,151,200,  BASE: TEXT
  - Require?: [charge 1 type]="Car"
  - Reset on edit?: [Charge 1 type]<>"Car"
• [Charge 1 amount]
  - Show: AND([Charge 1 type]<>"No Charge",[Charge 1 type]<>"Car",isnotblank([Charge 1 type]))
  - Type: Price
• [Charge 1 amount car]
  - Show: if([Charge 1 type]="Car",true,false)
  - Type: Price
  - App Formula:IFS([Charge 1 type]="Car",ifs(
  [Charge 1 km]="0-50",10,
  [Charge 1 km]="51-100",20,
  [Charge 1 km]="101-150",30,
  [Charge 1 km]="151-200",40,
  [Charge 1 km]="201-250",50),
  )

---

Do? you need more information.

[dntrplytch]

I am sorry I do not quite understand why this is happening.

As suggested earlier, please see if you can disable all bots for the period of time that you are running the action from the cron job. I am grasping at straws here, but could you have another live app running on the same data that is potentially resetting the data in the columns?

To continue troubleshooting, I would suggest working on a test Promotion record directly ( to remove some of the distractions) and running the "Increment Bot_trigger column" action on the one record to see what the outcome is. You would need to unhide the action (if it affects your production users, consider adding a display clause so only your login can see the action).

[dntrplytch]

Could you try one other thing? I just encountered a similar issue where data is vanishing. My scenario may be a little different. I have a bot updating some columns. If I then click on an action in the app, the action works well, but it does clear the data the bot had set. Presumably, the app must have a "cached" version of the data.

The data is retained if I follow this sequence:
* run the bot to update certain column values
* manually sync the app
* invoke the action from the app
* the app works (sets other columns) as well retains the columns set by the bot

Admittedly, I am running the steps above in close proximity - but I suspect the behavior may not be different if I were to wait for an extended period of time.

For your issue, could you manually sync the app before running the Action? Does that make a difference?

[thelimp]

The thing is that the data is updated by the user, in the same app. therefore the app must be "aware" a value has been entered and therefore there should not remove the value.

unfortunately i don't have a way to manually sync the app before, as the idea is to execute this action everyday, early in the morning. And i would like it to be automated, no manual actions 😉

[Joseph_Seddik · Accepted solution]

It is clear that your [Charge 1 km] and [Charge 1 amount car] column values will be set to blank, with any change in the corresponding row, if [Charge 1 type] in the same row is not "car". 

Charge 1 km:

• This column has a reset on edit parameter enabled, and since you have no initial value it will be set to blank each time any column in the row is edited (if [Charge 1 type] in the same row is not "car").
• You should disable this, and you don't actually need it. Instead, just ignore the column in your logic and in your views based on [Charge 1 type]. In case you need to change its value, do it through actions instead of reset on edit.

Charge 1 amount car:

• Your IFS() statement lacks a default condition. I've seen many problems arising from this condition. It also has an extra coma.
• Better change your expression to: 
  IF( NOT([Charge 1 type] = "Car"), [_This],
    SWITCH([Charge 1 km], 
      "0-50",    10,
      "51-100",  20,
      "101-150", 30,
      "151-200", 40,
      50
    )
  )

[thelimp]

hi Joseph,

Thanks for your input. 

---

@Joseph_Seddik wrote:

It is clear that your [Charge 1 km] and [Charge 1 amount car] column values will be set to blank, with any change in the corresponding row, if [Charge 1 type] in the same row is not "car". 

---

Well, this is what I want. If they don't select "CAR" in [Charge 1 type], [Charge 1 km] and [charge 1 amount car] should be empty as they didn't take the car. However, it is true that there will be very few cases where they will select car, enter some km, then switch back to "public transportation" and add an amout to [charge 1 amount]. Even if they do so, the formula in [charge 1 amount car] won't take into consideration the KM as the [charge 1 type] is not car. Therefore i can erase this reset on edit. Actually I did it yesterday, entered a few test charges and this morning it seemed to be working. 

I'll enter a few more test reports today and see if it's still ok tomorrow. 

---

@Joseph_Seddik wrote:

Better change your expression to: 
IF( NOT([Charge 1 type]) = "Car", [_This],
  SWITCH([Charge 1 km], 
    "0-50",    10,
    "51-100",  20,
    "101-150", 30,
    "151-200", 40,
    50
  )
)

---

I'll try with this formula as well and see tomorrow. I'll come back to you.

Many thanks for the input!

[thelimp]

Well, i checked the reports from this weekend and it seems to be working now.

I don't know if it's the formula or the reset on edit condition though. I also modified the currency symbol to match the locale of the Gsheet document (don't think it has an impact).

Thank you everyone for your help!

[Joseph_Seddik]

---

@thelimp wrote:

I also modified the currency symbol to match the locale of the Gsheet document (don't think it has an impact).

---

You are right, the currency symbol doesn't matter; since in both sides it is merely a visual format. What does matter though is that you set both, the GSheet and your AppSheet table settings to the same locale/regional settings.