I would like to extract the quantity on hand on a date with the lot number. I uses the formula
@NF($D6,"Net Change","Date Filter",NP("DateFilter",,$G$3))
Being D6 is the NL("Rows","Item") and G3 is the date.
But I could not extract the lot number that contain the balance. Any solution?
If you need to separate the quantities by the lots, you will need a additional lot numbers generated. Probably as a grouping to list each item and then listing all lot numbers for that item or you can use a field cache to pull back all unique combinations of items and lot numbers but that may lead to a massive report. =@NL("Rows","Item",{"No.","Lot Nos."})
Yes. my issue was it became a massive report and kept running. also like to enquire about the formula U spell in there with "{}" I did not come across this formula, what does that mean?
That is a technique called Field Cache https://support.jetglobal.com/hc/en-us/articles/360011030333-Jet-Reports-Field-Caches
It is used to return unique combinations of the supplied values. Ideally, you should probably find a way to return only the items and lot numbers you need rather than every item and every lot number. This would best worked through with a consultant.