Hi All,
We have a use case where goods owned by our clients come into our warehouse, we store them, and then ship them out to wherever when needed. We want to charge our clients for storage on a per-item per-day basis. The charge will be calculated daily, but billed monthly so I need a saved search to collect all of the data.
Each item has a unique daily storage rate and I have a several custom records setup to manage all of the items, the in and out movements of those items.
Ok... So, to charge storage per-item per-day I need the number of items on hand as of each day in a given month.
With some help from this thread https://usergroup.netsuite.com/users/showthread.php?t=22715&highlight=adding+transaction+type (thanks Oliver!), I've got the following formula working correctly in a saved search summed formula field to give me qty on hand:
sum(decode({type}, 'A', nvl({qty}, '0'),0) - sum(decode({type}, 'B', nvl({qty}, '0'),0) + sum(decode({type}, 'C', nvl({qty}, '0'),0)
This basically gives me "A-B+C" as a formula where A is inbound qty, B is outbound qty, C is adjusted qty so the summed total gives me Qty On Hand.. Works well, but only gives me Qty on Hand as at the time of the search...
Now, I need to find a way to build and compile that search 'as of' each day of the month for any given month (or Period) ie.
Day Of Month Formula To Calculate
1 A-B+C (as of day 1)
2 A-B+C (as of day 2)
3 A-B+C (as of day 3)
4 A-B+C (as of day 4)
5 A-B+C (as of day 5)
etc.
etc.
30 A-B+C (as of day 30)
I'm thinking something like a summed formula field:
CASE
WHEN {recorddate} is before {month day 1} THEN "A-B+C"
WHEN {recorddate} is before {month day 2} THEN "A-B+C"
WHEN {recorddate} is before {month day 3} THEN "A-B+C"
WHEN {recorddate} is before {month day 4} THEN "A-B+C"
WHEN {recorddate} is before {month day 5} THEN "A-B+C"
etc
etc
But I'm not sure how to go about getting the syntax right around the dates to build up the formula sequentially...
Any assistance would be greatly appreciated.
Dave