Calculations Sum, Select, _Thisrow, _This

[anonymous]

I am writing this seeking your kind support in writing the proper formula, after i spent 15 days trying to figure out the solution. and restructured my entire tables twice.
it is inventory app. it is multi companies, each has multiple stores - Engineers used to use the terminology “project” for the “project store”. Each company has list of materials shared across their entire store, but each store has his own quantity and starting balance for each material.

now, i have Company Table, Material table with reference to company table, project “Store” table and table for inventory movement structured as per the image

,

the enum col named [Movement] is a choice of four types:
Supply, Export, Transfer and Return.

i used to use separate table for each inventory movement " which was best for me, but since i couldn’t do the math so i re arranged the four tables into one [Movement].

i used the follows:

SUM(
SELECT(INVENTORY MOVEMENT[Quantity],
AND([Movement]=“Supply”,
[Project]=[_THISROW].[project],
[Material Name]=[_THISROW].[Material Name]
)))

but it is not working.

i used
IF(AND([Movement]=“توريدات”,[Project]=[_THIS],[Material Name]=[_THIS]),SUM(
INVENTORY MOVEMENT[Quantity]),“0”
)

i created a nice dashboard with filtered items but no calculations is their.

However, all i need is a table view to show as per the image.

i am using the same in Gsheet formulas as per the image, but i need it to be in app sheet level rather than gsheet formulas.

Please support in that if you can.
Thank you

[Kirk_Masden]

Hani_Ibrahim:

SUM(> SELECT(INVENTORY MOVEMENT[Quantity],> AND([Movement]=“Supply”,> [Project]=[_THISROW].[project],> [Material Name]=[_THISROW].[Material Name]> )))

First of all, happy new year!

Now, I’m trying to understand your problem. In regard to this SUM() expression, you wrote that it is not working. Does that mean that you get an error? What happens when you “test” it in the expression editor? I think it would help us understand it you could provide more detail about what is going wrong.

[anonymous]

Happy New Year

The Sum formula is not giving error, but it calculates the entire quantities where [movement]=“supply”. It gives one value for all rows, regardless the material name or project is.

But my target is to show the quantities as per the attached photo

respectively.

[Kirk_Masden]

I see. In this case, I think that all you need is as follows:

[Opening Qty]+[Supply Qty]+[Export Qty] etc.

[anonymous]

Thank you brother, but i dont have that values, i need to calculate, and that is my problem.

[Joseph_Seddik]

I ran into a seemingly similar issue with my app. Not 100% sure that it is the same as your problem, but just in case it might help.

The solution I found was to separate the selection expressions from the sum expression. So basically you first create a VC (or more), type List, for the values you want to sum, then another VC, type Number, that sums the values collected in the selection VC.

For me, at the verge of despair this solution worked perfectly. Try it please.

[Steve]

The Opening Qty, Supply Qty, Export Qty, Returned Qty, and Current Stock columns are all normal (not virtual) columns of existing rows. Computed values of normal columns of existing rows are only recalculated when the row is updated in some way through AppSheet, as when the user saves a form, or when an action is performed. If you want those columns to get updated values, you will need to either convert them to virtual columns, or add a bot to update them.

[anonymous]

Actually i am not able to segregate those values from the table. Thank you all for trying to help.

[anonymous]

I will do try it brother thank you so much