Calculations between 2 child tables

[connex91]

Hi all,

I am building a sales tracking tool. The parent table is called AgentWorkDay.

In the morning an agent is given a few different products to sell: DeliveryNoteDetails table, is part of AgentWorkDays
In the evening an agent brings the left over products: StockReturnDetails table, is part of AgentWorkDays

My issue:
I would like to display an inline table called StockSold in AgentWorkDay with the count of products sold. For each product in DeliveryNoteDetail, find the corresponding product in StockReturnDetail and display: ProductID, StockSold = DeliveryNoteDetails[Quantity] - StockReturnDetails[Quantity] , AgentWorkDayID

How can I achieve that result?

Thank you

[AlexM · Accepted solution]

Have this column in StockSold

SUM(SELECT(DeliveryNoteDetails[Quantity],AND([product_id]=[_THISROW].[product_id],[agentworkday_id]=[_THISROW].[agentworkday_id])))  -

SUM(SELECT(StockReturnDetails[Quantity],AND([product_id]=[_THISROW].[product_id],[agentworkday_id]=[_THISROW].[agentworkday_id])))

This would do sum of all qty for a product from all DeliveryNotesDetails for a specific day- sum of all qty for the same product from all StockReturnDetails for the same day.

Then get the StockSold pupolated using an action to add a new row to a different table on the StockRetun_Form behavior and get the StockSold populated each time you enter a StockReturn

[connex91]

Thanks for the prompt response @AlexM . I have never done Actions so let me try this and revert

[connex91]

Thank you @AlexM your suggestion for automation worked great!

On the DeliveryNoteDetails table I created an action and a Bot  that adds a row for the corresponding ProductID to the StockSoldDetails table every time a DeliveryNoteDetails row is added. 

On the StockReturnDetails table I created an action to select the corresponding row (ProductID, AgentWeekDayID) in the StockSoldDetails and another action that updates the column Quantity in StockSoldDetails. I added a bot that runs on every add or update of StockReturnDetails.

Thank you!