Appsheet not calculating columns separated by blank rows

[Purple_Gallery]

Hi Community
Need a little help here.

I’m trying to make an app for e-commerce.
There are 2 tables ORDERS and SALES, basically ORDERS Table is the summary of SALES Table. In 1 column of the ORDERS table, i am using an app formula as below :

SUM( Select( Sales[Margin] ; [Order ID] = [_THISROW].[Order ID] ))

Works most of the time, but when i delete any input in the app, it messes up the calculation. I see whenever i delete something, appsheet doesn’t delete the row in the spreadsheet, it leaves an empty row instead. So the formula above doesn’t calculate the separated rows even though it has the same Order ID.

Is there any way to make this formula works the way i need it to be?
Thanks to anyone answering

[Marc_Dillon]

Blank rows shouldn’t affect anything. How do you think they are affecting your calculation?

[Purple_Gallery]

Hi Marc, thx for answering

I thought so, but it’s not what happened in my app.
The column in the app doesn’t return the correct sum values.
It only returns the values in the red box below, the ones after blank rows not included in the calculation.

Here’s what it looks like in the app :

Any idea?

Actually, it’s not just that specific column, but the entire worksheet

[Marc_Dillon]

I’ve no idea, but here are some steps I would start with to troubleshoot:

What is the result of this expression, when using “Test” from the expression assistant

Select( Sales[Margin] ; [Order ID] = [_THISROW].[Order ID] )

What shows up when you click “View Data” from the Table with the blank rows?

Where do you have your SUM(SELECT()) expression, in a Virtual Column?

[Purple_Gallery]

Select( Sales[Margin] ; [Order ID] = [_THISROW].[Order ID] )

It says “The expression is valid but its result type ‘List’ is not one of the expected types: Number”
and an error when i click the save button.

This is the result of the view data :

And it is not a virtual column

[Purple_Gallery · Accepted solution]

Marc, i tried to make those 3 columns that has app formula in it into a virtual columns, and it works fine!
It does ignores the blank rows. Thank you so much!

Does it mean i can only use virtual column for calculation in appsheet?

[Steve]

Virtual columns are recomputed whenever the app syncs, or when the row the virtual column is a part of is edited in a form view or modified by an action. And App formula for a non-virtual column is only updated when its row is edited in a form or updated by an action.

[Purple_Gallery]

Good to know this, Steve.

Thank you both!
@Marc_Dillon @Steve