App Formula incorrect when row is updated via API call (value is CORRECT when updated within the app

[rhynot_ryan]

Data source: Google Sheets
Column with the incorrect calculation, [Line Expense] is a column which exists in the data source document. This column is Price type; the app formula for the [Line Expense] column is:
[Total Line Cost] * ( 1 - [Line Rate])

[Total Line Cost] is a virtual column of type Price.
[Line Rate] is a virtual column of type Percent.

For context, [Line Total Cost] is the raw cost of the line item. [Line Rate] is the percent of the total raw cost which is to be billed to a third party. Thus, [Line Expense] is the remaining expense realized after accounting for the portion billed to the third party.

Example: 
Let's assume for this example that:
[Line Total Cost] is 100.00
[Line Rate] is 25%
Accordingly, the expected result to be saved in the [Line Expense] column should be (100 * (1 - .25)) = 75.00
When the row is updated via the app itself, this value is correctly calculated and saved in the cell.

HOWEVER, when that row is updated via API call, the value saved in the [Line Expense] cell is incorrect; the value is updated to the same as [Line Total Cost].

In an effort to debug this error, I created another virtual column, [vLineExpense]. [vLineExpense] is Price type, with app formula
[Total Line Cost] * ( 1 - [Line Rate])
This is the same formula used for the [Line Expense] column.

When a row is updated via an API call, the returned JSON which contains all of the data for the updated row(s) displays the correct and expected value for the [vLineExpense] virtual column.

Continuing to troubleshoot, I set the app formula for the [Line Expense] column to simply refer to the newly-created virtual column (e.g. the app formula for [Line Expense] is simply [vLineExpense]). Again, updating this row within the app calculates and stores the value [Line Expense] value correctly as expected; [Line Expense] and [vLineExpense] are identical. When that same row is updated via API call, [vLineExpense] is correctly calculated (as it is returned in the JSON response), but [Line Expense] - which has the app formula of [vLineExpense] and thus would be expected to be the same as the column from which it is to get its own value - is again/still incorrectly reporting the same value as [Total Line Cost].

I've also tried using the DECIMAL() function to wrap the referenced columns in the app formula - ex: DECIMAL([Line Total Cost]) * ( 1 - DECIMAL([Line Rate])) - but this has not made any difference; the recalculated value is still incorrectly calculated/saved.

I'm out of ideas to try... Please help! I've tried to be as thorough as possible with this explanation, but please let me know if you require any clarifying information or additional details of any kind.

UPDATE: For what it's worth, the issue MAY be related to the [Line Rate] field. This field's app formula is as follows:
IF([Override] = TRUE(),
  DECIMAL([Override Rate]),
  SWITCH([Sample Reason].[Category Class],
    "A",DECIMAL([Supp-Mkt-Smp-Rate].[a-rate]),
    "B",DECIMAL([Supp-Mkt-Smp-Rate].[b-rate]),
    "C",DECIMAL([Supp-Mkt-Smp-Rate].[c-rate]),
    "Always 100",DECIMAL(1),
    "Always zero",DECIMAL(0),
    DECIMAL(0)
  )
)

(Context: [Sample Reason] and [Supp-Mkt-Smp-Rate] are both Ref fields) While the value of the [Line Rate] field is properly calculated in the app and the JSON returned after the API call ALSO displays the correct value for the field, I'm wondering if there is a bug of some sort related to the dereferences contained within the formula? The reason for this guess is because when the [Override] col (type is Yes/No) is set to Yes/True, the [Line Expense] col is properly set after an update to the row via API - in this situation, none of the dereferences are being used. However, in any other circumstance, with when the IF function is false & therefore the SWITCH function is used, it seems to always be using the default result within the SWITCH statement. *shrug* Just trying to give as much info/background/clues/etc as possible (because this is driving me slightly batty).

Thanks in advance for any assistance possible.

[Marc_Dillon]

Isolate where the issue is by replacing the [Line Rate] expression with a simple single hard-coded percent value, and test it again.

TRUE is not a function. Replace "TRUE()" with "TRUE".

DECIMAL(0) can just be written as 0.0. Same for 1.0

What kind of API call are you making?

These are all real columns, right? (besides the test virtual column)

Can you show some screenshots?

[rhynot_ryan]

Hi Mark, thanks for the reply.

I corrected the TRUE expression in the formula of [Line Rate] (it was originally simply "TRUE"; i had changed to "TRUE()" as part of testing and trying to determine what was going on). I DO still believe the issue is with this column - as I mentioned, when [Override] is TRUE for the given row, [Line Expense] is correctly set. It's when [Override] is FALSE is when the error occurs. HOWEVER, the value returned in the JSON response DOES show the correct and proper value for [Line Rate] regardless of the value of [Override].

All of the columns mentioned are 'real' columns which exist in the data source WITH THE EXCEPTION OF: 

• [Total Line Cost] is a virtual column of type Price with an app formula of "[Qty] * [Product Code].[unit-val]"
• [Line Rate] is a virtual column of type Percent with an app formula of "IF([Override]=TRUE,DECIMAL([Override Rate]),SWITCH([Sample Reason].[Category Class],"A",DECIMAL([Supp-Mkt-Smp-Rate].[a-rate]),"B",DECIMAL([Supp-Mkt-Smp-Rate].[b-rate]),"C",DECIMAL([Supp-Mkt-Smp-Rate].[c-rate]),"Always 100",1.0,"Always zero",0.0,0.0))"
• [Supp-Mkt-Smp-Rate] is a virtual column of type Ref with an app formula of "CONCATENATE([Product Code].[pri-supplier-key],[Market])"
• [vLineExpense] is a virtual column of type Price with an app formula of "[Line Total Cost] * ( 1 - [Line Rate])"

Remember, the formula for [Line Expense] (the real column) is the same app formula as the app formula for virtual column [vLineExpense]: "[Line Total Cost] * ( 1 - [Line Rate])"

The API calls made against the table are Update actions, setting the value of [Exported] which is a real col of type DateTime.

Overall, the app is for users creating orders; there is a table for the order, and the table which I'm having the issue with is the order line table. Users create orders & order lines via the app, then once done, use an action to 'submit' the order. The submit action sets a data field in the order header table (indicating the order is now submitted [this then prevents the user from being able to edit the order or its lines) and then makes an HTTP API request to a deployed Apps Script bound to the data source. Upon receiving this call from AppSheet, the Apps Script uses data in the order and order line tables to produce a flat file which will be sent to our ERP system for fulfillment. Once the flat file has been generated, the Apps Script code makes an API call to AppSheet to update the order header record to mark the order as 'exported', then does the same with the order line records. It is in this final call - Apps Script sending the Update API call to the order line table to mark the applicable lines as exported - that the value of the [Line Expense] column is improperly set. Due to security filters on the data source, the API calls to AppSheet from Apps Script are sent with the RunAsUserEmail property so the update is performed with the same user context as the user who created/submitted the order and its lines.

So... Let's say I've used the app GUI and created my order with one line. Prior to submitting the order, here's the row saved to the Google Sheets backend:

And if I read the same row via the AppSheet API:

• JSON body of request: 
  {"Action":"Find","Properties":{"Locale":"en-US","RunAsUserEmail":"r****@v*****.com"},"Rows":[{"Order ID":"36ade69d05c89766","Line Num":"ccbe9744"}]}
  
• JSON response:
  [
  {
  "_RowNumber": "9481",
  "Order ID": "36ade69d05c89766",
  "Line Num": "ccbe9744",
  "Product Code": "ABCM21CHSB A0",
  "Qty": "1",
  "UM": "Btl",
  "Sample Reason": "CustSample",
  "Samp Cust": "300255",
  "Override Rate": "",
  "Override": "N",
  "Override Auth": "",
  "Comment": "test 1",
  "Exported": "",
  "Requester": "r****@v*****.com",
  "Line Expense": "0",
  "Market": "PA",
  "_ComputedKey": "36ade69d05c89766: ccbe9744",
  "Reason Description": "",
  "RepUserID": "R****",
  "Status": "Draft",
  "Line Total Cost": "11.86",
  "Line Rate": "1",
  "SubmittedTest": "",
  "Smp Billing Cust": "100011",
  "Proc Thru PLCB": "Y",
  "Line SLO Code": "646967",
  "Valid Item": "Y",
  "Valid Reason": "Y",
  "Supp-Mkt-Smp-Rate": "00002413PA",
  "Prod Attributes": "",
  "Valid Market": "Y",
  "Line-Supp-Req-Apprvl": "N",
  "Line Req Approval": "N",
  "vLineExpense": "0"
  }
  ]

You see in the example response that the [Line Rate] value is 100% (1) - this is correct because that's the proper value returned by the dereferenced value within the SWITCH statement for the row where [Override] is FALSE.

I'll then submit the order. After the export file is created via the App Script API call FROM AppSheet, the Apps Script code then makes an UPDATE API call TO AppSheet to set a value in the [Exported] col of the applicable row, marking those lines as exported.

• JSON body of UPDATE request:{"Action":"Edit","Properties":{"Locale":"en-US","RunAsUserEmail":"r****@v*****.com"},"Rows":[{"Order ID":"36ade69d05c89766","Line Num":"ccbe9744","Exported":"4/6/2023 11:02:40"}]}
• JSON response: 
  {
  "Rows": [
  {
  "_RowNumber": "9481",
  "Order ID": "36ade69d05c89766",
  "Line Num": "ccbe9744",
  "Product Code": "ABCM21CHSB A0",
  "Qty": "1",
  "UM": "Btl",
  "Sample Reason": "CustSample",
  "Samp Cust": "300255",
  "Override Rate": "",
  "Override": "N",
  "Override Auth": "",
  "Comment": "test 1",
  "Exported": "04/06/2023 11:02:40",
  "Requester": "r****@v*****.com",
  "Line Expense": "11.86",
  "Market": "PA",
  "_ComputedKey": "36ade69d05c89766: ccbe9744",
  "Reason Description": "",
  "RepUserID": "R****",
  "Status": "Being Processed by Apprise",
  "Line Total Cost": "11.86",
  "Line Rate": "1",
  "SubmittedTest": "04/06/2023 11:02:40",
  "Smp Billing Cust": "100011",
  "Proc Thru PLCB": "Y",
  "Line SLO Code": "646967",
  "Valid Item": "Y",
  "Valid Reason": "Y",
  "Supp-Mkt-Smp-Rate": "00002413PA",
  "Prod Attributes": "",
  "Valid Market": "Y",
  "Line-Supp-Req-Apprvl": "N",
  "Line Req Approval": "N",
  "vLineExpense": "0"
  }
  ]
  }

Notice after updating the record via the API call that the [Line Expense] col value is incorrect - it has changed from 0 to 11.86 - but the [vLineExpense] col is completely correct - and these two columns have the EXACT SAME FORMULA!

And here's the record in the Google Sheet back end after the update:

Please do let me know if you have additional questions.

[Marc_Dillon]

I will try to produce an MRE on my end later, but first please try my suggestion to isolate the issue so that I know whether to set up that part or not. I did set up a super simple version last night and so far did not see any issue.

---

@Marc_Dillon wrote:

Isolate where the issue is by replacing the [Line Rate] expression with a simple single hard-coded percent value, and test it again

---

[rhynot_ryan]

Hi Mark, I believed I had addressed this, I apologize for leaving any uncertainty.

If the [Line Rate] app formula is replaced with a constant, there is no issue.

Furthermore, as mentioned in the original post as well as my earlier reply, when [Override] is TRUE - thus the value of [Line Rate] is derived from the value of [Override Rate] as per the defined app formula for [Line Rate] - there appears to be no issue.

Please do let me know if any further clarification is required.

[Marc_Dillon]

Ok, so then pull out that SWITCH expression and put it in its own virtual column and do the same tests and see if it returns differently.

[rhynot_ryan]

Hey Marc - 

OK, so I went a little crazy creating multiple versions of various columns in order to simultaneously try multiple versions of (what SHOULD be) the same effective formula and creating numerous virtual columns to break the calculations out into different steps to review the intermediate results.

After extensive testing, there is no indication the SWITCH statement seems to be the problem. I cannot determine any thyme or reason for the error at this point, as the additional testing has killed my prior theories.

Where I'm at now:

I created a new real column, [lineExp4], with an app formula nearly identical to that for [Line Expense] except referencing the new virtual column. Both columns are real columns, of type Price, and have all other attributes identical (Show, Require, Editable, etc).

[Line Expense] = [Line Total Cost] * ( 1 - [Line Rate] )
[lineExp4] = [Line Total Cost] * ( 1 - [lineRateTest1] )

I created a new virtual column, [vLineRateTest1] with an app formula identical to that for [Line Rate]. Both columns are virtual, of type "Percent", and have all other attributes identical (Show, Editable, Require, etc) according to the app editor.

[Line Rate] = IF([Override] = TRUE,DECIMAL([Override Rate]),SWITCH([Sample Reason].[Category Class],"A",DECIMAL([Supp-Mkt-Smp-Rate].[a-rate]),"B",DECIMAL([Supp-Mkt-Smp-Rate].[b-rate]),"C",DECIMAL([Supp-Mkt-Smp-Rate].[c-rate]),"Always 100",1.0,"Always zero",0.0,0.0))
[lineRateTest1] = IF([Override] = TRUE,DECIMAL([Override Rate]),SWITCH([Sample Reason].[Category Class],"A",DECIMAL([Supp-Mkt-Smp-Rate].[a-rate]),"B",DECIMAL([Supp-Mkt-Smp-Rate].[b-rate]),"C",DECIMAL([Supp-Mkt-Smp-Rate].[c-rate]),"Always 100",1.0,"Always zero",0.0,0.0))

See below for the columns' 'app definition' according to the 'documentation' page - notice there is a Reset_If formula property for the [Line Rate] column - but there is no way to view/modify the 'Reset on edit' property within the app editor - since virtual columns are defined with an app formula and thus are inherently editable and would inherently recalculate on any change of the row (as I suspect they are calculated on the fly as opposed to being stored values) - so I'm not even sure how this 'Reset on edit' constraint could even have been added!

Any suggestion on how I might remove this phantom 'Reset on Edit' criteria to see if that fixes the problem? Notice there is no Update Behavior section for virtual cols as there is for real cols.

[Marc_Dillon]

That's weird. I'd recommend removing all existing and related virtual columns, and rebuilding them from scratch.

I'd also recommend making as many of them into real columns as possible. From what I've seen, I think you should be able to convert all of them to real columns.

[rhynot_ryan]

Thanks, Marc - After discovering the "Reset_If" ("Reset on Edit") constraint on the virtual column yesterday, I contacted Google support to request assistance (hoping they have a means of removing the constraint, since it cannot be edited within the app editor gui). The matter has been escalated and I'm currently awaiting a response from the support team. If I can get the immediate matter (of the erroneous value) resolved without any schema changes, that'd be ideal (so I don't create any sync issues for end users due to schema changes), then I'll review all of the virtual columns in the table and see which ones make sense to change to real columns, incorporating such changes into a 'major version app upgrade'.

My goal with the virtual columns was to keep down the amount of "actual" data synced (e.g. columns/rows in the data source) for performance reasons, though I do understand those virtual columns have their own performance caveats. 

I greatly appreciate your help pushing me to continue testing to track down the root of the erroneous results.

Once I hear back from Google's support (and assuming the incorrect value error is resolved), I'll post a followup to confirm the resolution and close out the topic.

[rhynot_ryan · Accepted solution]

Following up as promised; I've now received a response from the AppSheet support team:

Yes you are correct that the ResetOnEdit field is the root of the problem and incorrect. We are working to fix the problem. Also, thank you so much for the incredibly detailed information and thorough debugging. The issue would not have been solvable without this.

---

 (Yes, I included the second half of the message as a small pat on the back for myself 😉).

In any case, hopefully they'll be able to correct the error/bug in the near future.

@Marc_Dillon, thank you again for your assistance with your troubleshooting suggestions, your help is very much appreciated.

[rhynot_ryan]

Just closing the loop on this; Google Support has removed the ResetOnEdit criteria for the virtual column [Line Rate]; after removing this, updates via API call no longer result in an erroneous value for the virtual column.

The bug (virtual column [Line Rate] producing incorrect values when the row is updated via AppSheet API call) was indeed caused by the existence of ResetOnEdit criteria on the virtual column. I still have no idea how such criteria got there to begin with (as there is no means to set/change ResetOnEdit criteria on a virtual column via the app builder GUI).