Help with automation: How to update parent with child record based on an IF condition.

[STC]

Hello,

I have the following 2 related tables, Inventory and Tally Sheet.

Inventory:  

Tally sheet: 

I want to add the tally sheet's 'TallyQty', to 'ItemSumQty' in Inventory if the values is In at 'InOrOut', and minus 'ItemSumQty' if the value of 'InOrOut' is Out.

I think this post is quite similar to my problem, but I have a hard time coming up with the equation.

All help is greatly appreciated, Thank you in advance.

[lynchk21 · Accepted solution]

I would try to get all the positive values and negative values seperately to check the formulas and then combined them

To get positive values:

SUM(SELECT(Tally Sheet[TallyQty],AND([InOrOut]="In",[ItemName]=[_thisRow].[ItemName])))

To get negative values:

(SUM(SELECT(Tally Sheet[TallyQty],AND([InOrOut]="Out",[ItemName]=[_thisRow].[ItemName])))*-1)

If these are not calculating correctly, you should be able to figure out what it wrong

[lynchk21]

Try:

SUM(SELECT(Tally Sheet[TallyQty],AND([InOrOut]="In",[ItemName]=[_thisRow].[ItemName])))+(SUM(SELECT(Tally Sheet[TallyQty],AND([InOrOut]="Out",[ItemName]=[_thisRow].[ItemName])))*-1)

[STC]

Thanks, but the cell now always returns the value as a negative number.

SUM(Select(Tally[TallyQty, [ItemName] = [_Thisrow].[ItemName]))
NUMBER(ANY(
SELECT(
Inventory[ItemSumQty],
AND(
TEXT(Tally[Customer])=[_THISROW].[Customer],TEXT(Tally[ItemName])=[_THISROW].[ItemName],FALSE
)))
+ NUMBER(IF((TEXT(Tally[InOrOut])="In"),(+Tally[TallyQty]) ,(-Tally[TallyQty])))

I tired using this formula but it returned the value "0" instead
I think Its better to take the "ItemSumQty" and minus or plus the "TallyQty", Since "ItemSumQty" is always going to be larger

[lynchk21 · Accepted solution]

I would try to get all the positive values and negative values seperately to check the formulas and then combined them

To get positive values:

SUM(SELECT(Tally Sheet[TallyQty],AND([InOrOut]="In",[ItemName]=[_thisRow].[ItemName])))

To get negative values:

(SUM(SELECT(Tally Sheet[TallyQty],AND([InOrOut]="Out",[ItemName]=[_thisRow].[ItemName])))*-1)

If these are not calculating correctly, you should be able to figure out what it wrong

[STC · Accepted solution]

Hi, Thank you so much for the help.

Thanks to your help I was able to get this formula which worked
"[InitialStock] +
SUM(SELECT(Tally[TallyQty],AND([InOrOut]="In",[ItemName]=[_THISROW].[ItemName],[LotNumber]=[_THISROW].[LotNumber],[Customer]=[_THISROW].[Customer])))+
(SUM(SELECT(Tally[TallyQty],AND([InOrOut]="Out",[ItemName]=[_THISROW].[ItemName],[LotNumber]=[_THISROW].[LotNumber],[Customer]=[_THISROW].[Customer])))*-1)"

I added a few changes and more data but thanks again for helping me.