Does anyone know if it's possible to create a runn...
# administrationm
mkomninos
02/22/2021, 8:47 PMDoes anyone know if it's possible to create a running balance in a saved search for a quantity field (actually a formula quantity field) like you can in some reports? Any formula you know that might work? Thanks in advance.
c
Chloƫ
02/22/2021, 9:26 PMYou can, but how you set it up will depend on the kind of search you're doing and what you're trying to achieve. If you're trying to just see the on hand running balance of items, you can create a Transaction Saved Search. In the Critiera, add 'Posting' = True, then add a Formula (Numeric) field. For the formula, enter: case when {account.number} = '(insert your item asset account)' then 1 else 0 end. Add 'Date' to your Available Filters. In the Results, add 'Item' (Group) and 'Quantity' (Sum). This will show the on hand running balance for items at all locations.
š 1
s
Sandii
02/22/2021, 9:27 PMk
KevinJ of Kansas
02/23/2021, 3:02 PMso I did something like this for a client.
KevinJ of Kansas
02/23/2021, 3:03 PMthe formula wound up being quite a bit more complex than theirs as a result.
KevinJ of Kansas
02/23/2021, 3:03 PMMainly because you have to accommodate for locations and different transaction types hitting it differently.
KevinJ of Kansas
02/23/2021, 3:04 PMbut that blog was hugely helpful in coming up with the structure of how to do it.
m
mkomninos
02/24/2021, 7:09 PMThank you all so much!!!
mkomninos
02/28/2021, 5:56 PMThank you for pointing me to that blog - that was the ticket. Love it! The solution only worked partway because I'm already using a formula within a case statement and that solution doesn't work when I added in the case statement depending on if it was a WO or a PO. Anyway, love the concept of having a running total so I'm sure I'll be able to use that for other solutions. Thank you all for responding!
4 Views