This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

Thanks! Easy enough to test out, I think, given I'm so close to it already. PS I hate that I even have to ask this question -- formulas this big are way too unwieldy, but I'm building off of a saved search that was included with a SuiteSuccess bundle (for calculating FIFO Inventory Aging when not using Lot-Numbering or Serialization)

Yeah, I've encountered it once before - and I was like "Maybe I should try a different approach". I got a warning saying I blew past the limit by like 600 characters.

If you inspect the area where you enter the formula, you can see the 4000 mentioned

ah there it is indeed - i should have looked at that myself

yeah and it actually won't let you paste in a larger formula anyway so it kind of stops you in your tracks

Is it even maintainable at 3500 chars? Seems pretty crazy

I dont think ive ever seen one even close to that size so i'm curious

the search works - but i had to basically reverse-engineer the algorithm that was used to generate the formulas in order to even iterate on it further

it's a transaction search that gives you columns for various buckets, the goal of which is to give you the "age" of your current inventory, in a location, for each item

since all inventory goes into a general bucket unless it's lotted or serialized, the assumption here is that the warehouse is using strict FIFO and that they always ship the oldest product first

so it uses date ranges to look at transaction lines and compares that to the current location on hand qty for the item

the problem is that each subsequent bucket needs to take the previous buckets into account for the formulae

here's what i came up with after reverse-engineering:

Untitled.txt

So, B1 winds up being: `CASE WHEN MAX({item.locationquantityonhand}) &lt;= NVL(SUM(CASE WHEN TRUNC({today})-{trandate} &lt; 42 AND {location} = {item.inventorylocation} THEN {quantity} END),0) THEN MAX({item.locationquantityonhand}) ELSE NVL(SUM(CASE WHEN TRUNC({today})-{trandate} &lt; 42 AND {location} = {item.inventorylocation} THEN {quantity} END),0) END`

and when you get to B4, you're around 3500 characters

i'm noticing some small optimizations that could be used to cut down on the number of characters, but i'm not sure even given that, that it would be possible to reduce B5 down to &lt; 4000 characters

i could put `{location} = {item.inventorylocation}` into a criteria filter for starters

If only you could reference formula fields in other formula fields.

yeah - that would be the key here for sure

That would be a game changer for some many instances.

agreed - your better BI applications will let you do that, too

Would this be something to do in something like a map/reduce or suitelet with multiple smaller searches that you can combine and spit out the report?

yeah or i could see doing this in SQL too - a specific table/view of this aged data that is calculated daily, and iteratively