Inventory slice and calculations

[anonymous]

First Thank you for the marvelous app, i have an inventory app. it has multiple companies, each company has multiple stores. each company has its own products. a want to calculate the quantity of products per store and per company. i did table for companies, table for stores and table for products, then a table for received products from suppliers, table for products issued to contractors, table for returned products by contractors, table for transfer between stores.

i want to calculate the current products stock for each store individually and per company too
any support please

[Kirk_Masden]

Hi @Hani_Ibrahim !

Welcome to the community.

Are you familiar with SELECT()? There’s a good chance that you will want to use this to do your calculations, I think.

[anonymous]

Honestly thank you for your respected reply,
i am using a virtual column in received material table level with this expression

SUM(SELECT(التوريدات[الكمية],AND(([اسم الخامة]=[_THISROW].[اسم الخامة]),([المشروع]=[_THISROW].[المشروع]),TRUE)))

and i am using a slice to reflect the required column only,
i used a new ux view to show the result in “table” view not form view. it works with form view but not the table.

However, the result is still showing the required total but repeated many times as many as the input from received “التوريدات” table rows.

i need to see only one from the selected rows.

[Kirk_Masden]

Thank you for your explanation. I’m having difficulty understanding how your data leads to this problem and what might be done to fix it. Perhaps someone else can help. It might be necessary to show how your data is structured. Good luck! Sorry I don’t have enough knowledge to help.

[Steve]

Hani_Ibrahim:

SUM(SELECT(التوريدات[الكمية],AND(([اسم الخامة]=[_THISROW].[اسم الخامة]),([المشروع]=[_THISROW].[المشروع]),TRUE)))

Google Translate:

SUM(SELECT(supplies[quantity],AND([material name]=[_THISROW].[material name]),([project]=[_THISROW].[project]),TRUE)))

Your use of TRUE in the SELECT() expression above may introduce a problem: it will remove duplicate results, leaving only one result of each distinct value. This may result in an under-count.

Hani_Ibrahim:

التوريدات

Google Translate:

supplies

Hani_Ibrahim:

i need to see only one from the selected rows.

Please post both a screenshot and the text of your slice row filter expression.

[anonymous]

Thanks @Steve for your appreciated effort.
holla, here is a expression after omitting the TRUE parameter,

SUM(SELECT(التوريدات[الكمية],AND(([اسم الخامة]=[_THISROW].[اسم الخامة]),([المشروع]=[_THISROW].[المشروع]))))

it works with form view as shown in photo

However, it is showing all results in Table view, as shown in photo,

i need to show only one result for each material per each project and be able to do further math to calculate the over all current stock per project and per material.

here is the structure,

However, i have a central table for materials, and a table for projects “we can define it as individual store” sharing the same material table. each Store sharing a table of the follows:
Supply_tbl , Export_tbl , Returned_material_tbl , and transferred_material_tbl from store to other store.

my goal is to calculate the total of each material transaction from the 4 tables to define the Current Stock for each store individually.

I already calculated the current stock for the entire company base on Material Table.

i may show you exactly my target by showing a table in Gsheet but not the one used in this app as an example:

Photo showing the current project’s Stock

Photo showing the Export Table

Photo showing the SupplyTable