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
