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