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
Solved! Go to 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?
Blank rows shouldnโt affect anything. How do you think they are affecting your calculation?
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
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?
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
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?
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.
Good to know this, Steve.
Thank you both!
@Marc_Dillon @Steve
User | Count |
---|---|
43 | |
28 | |
24 | |
24 | |
13 |