NetSuite: Item Saved Search With Three Dimensions

Okay, so here's an interesting dilemma. A customer created an item saved search using the joined fields Inventory Number/Bin on Hand and, Inventory Number. He wanted to retrieve the Expiration date from the Lot Number Record, but that resulted to each Lot Number Record compare itself to each Bin. This in turn bloated the results from 8 rows to 40.

Pulling 3 dimensions to a search, can get messy, since search results are only capable of returning 2 dimensions. Luckily, there's a solution. It seems you have to find fields from each record type that return the same value. This would be your primary key. Then, you use that key as a condition on a case when statement.

• case when {inventorynumber.inventorynumber} = {inventorynumberbinonhand.inventorynumber} then {inventorynumber.expirationdate} end

Also set the Summary Type to Maxmimum for that formula, then Group the rest of the fields.

The down side is, since setting summary types forces drill down reports; you can't easily access the related records in a single click. You will need a Formula (HTML) field with an anchor tag to get to the record as a workaround. I tried doing that as I normally did, but then I realized that the results were all over the place. The links took me to the wrong records because they were pulling the wrong internal IDs. This was again caused by the 3 dimensions.

How did I get around it? Again, I used a case when formula to return only the internal IDs that matched the primary key. The result worked!

• case when {inventorynumber.inventorynumber} = {inventorynumberbinonhand.inventorynumber} then '<a href=https://<accountID>.app.netsuite.com/app/accounting/transactions/inventory/inventorynumberrecord.nl?id='||{inventorynumber.internalid}||'>'||{inventorynumberbinonhand.inventorynumber}||'</a>' end

Well I wasn't really able to do this with all other fields, since our QA environment kicked me out and prevented me from logging back in. But I guess it would just be the same logic for those other fields. Anyway, I don't recommend doing this for all the fields because you can't really sort columns if you use several Formula (HTML) fields.

So what did we learn? You can use 3 dimensions in your search, but since results are only shown in 2 dimensions, you will need to use primary keys via case whens appropriately.