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.
LinkedIn
Twitter
