Unanswered

Sum Filter with 2 fields matching

(0) Share

Report

Posted on by ESCOFORMS

Hi everyone.

I am developing a PowerApps that does inventory management for products with LOT and EXPIRATION.

Currently I am stuck on an issue for displaying a live count of how many of a certain product we have.

In my Gallery that is linked with my table:
Sum(Filter(InventoryReceived,LotId=ThisItem.LotId),QuantityRecieved)

works like
Sum(Filter(Table,Column=ThisItem.Column),sum_this_column_if_match)

The issue is that that formula only gives me the SUM if the LOT matches the current item on the gallery. I would like it to match both LOT + EXPIRATION in the case of the possibility of receiving same LOT different EXP products.

I have tried adding the additional condition in the Filter formula, but it doesn't work since EXP column is a date? or something like that.

Sum(Filter(InventoryReceived, LotId=ThisItem.LotId, Expiration=ThisItem.Expiration),QuantityRecieved)
does not work and neither does the SUM+FILTER for expiration by itself
Sum(Filter(InventoryReceived, Expiration=ThisItem.Expiration),QuantityRecieved)

Delegation warning. May not work on large sets.
(my table only has 5 rows)

How can I get my total counts with both filters?

Here is a sample table

InventoryReceived

id	name	test	LotId	Expiration	QuantityReceived
1	BOB	Flu A Test	SNIR-244	1/1/2020	10
2	BOB	Flu A Test	UFFS-200	5/6/2023	5
3	BOB	Flu A Test	SNIR-244	9/9/1999	6
4	BOB	Flu A Test	SNIR-244	1/1/2020	20

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

TheRobRush 11,128 Moderator on at

Like (1)

Report
try

Sum( Filter(InventoryReceived, And(LotId=ThisItem.LotId, Expiration=ThisItem.Expiration ) ), QuantityRecieved)

Was this reply helpful? Yes No
ESCOFORMS 10 on at

Like (0)

Report

Thank you for your response.

However, this is the error I have when trying that

Was this reply helpful? Yes No
TheRobRush 11,128 Moderator on at

Like (0)

Report
Here, try it this way

Sum( AddColumns( Filter(InventoryReceived, And(LotId=ThisItem.LotId, Expiration=ThisItem.Expiration ) ), ,"qtyRec",QuantityRecieved), qtyRec)

Was this reply helpful? Yes No
ESCOFORMS 10 on at

Like (0)

Report
That exact code gives me

I figured maybe it is because of the extra comma at
Sum( AddColumns( Filter(InventoryReceived, And(LotId=ThisItem.LotId, Expiration=ThisItem.Expiration ) ), <-- I REMOVED THIS COMMA TO MAKE REMOVE THE ERROR ,"qtyRec",QuantityRecieved), qtyRec)

But then it returns an blank value with no text at all.

Was this reply helpful? Yes No
TheRobRush 11,128 Moderator on at

Like (0)

Report
Ah yah there was an extra comma, apologies. What kind of column is QuantityRecieved, because if it is just a number column, the above code shoul return a number. If its text you might need to change to

Sum( AddColumns( Filter(InventoryReceived, And(LotId=ThisItem.LotId, Expiration=ThisItem.Expiration ) ), "qtyRec",Value(QuantityRecieved)), qtyRec)

Was this reply helpful? Yes No
ESCOFORMS 10 on at

Like (0)

Report

It was varchar(25) before -
I had changed it into an (int) and refreshed the database to try out all variations of the methods above however I am still getting a blank text output for some reason.

Fixed the typo as well on all ends for received spelling 😅

I did a IsNumeric(ThisItem.QuantityReceived)
to test if it was a number, which I got true for all fields.

When I do ThisItem.QuantityReceived I get a direct number shown, but when plugging in the whole formulas needed to add to combine the LOT/EXP combo, it goes blank.🤔

Was this reply helpful? Yes No
ESCOFORMS 10 on at

Like (1)

Report
Thank you for your help @TheRobRush

I was able to get my sum to work by converting to text the expiration date like so
Sum(Filter(InventoryReceived,And(LotId = ThisItem.LotId),(Text(Expiration) = Text(ThisItem.Expiration))),QuantityReceived)

Was this reply helpful? Yes No