CODEWITHSUNDEEP
netsuitesaved-searches
jlbriggs
jlbriggs

Reputation: 17791

NetSuite Saved Search - compare fields when when used MAXIMUM and one uses SUM?

I have a custom record search with a link to a transaction. The custom record may have many records for each transaction. I am returning a summarized result where the quantity from the transaction line uses MAXIMUM, and the quantity line from the custom record(s) uses SUM. I need to display the qty from the line vs the total qty of the related custom record. So far so good.

But I want to add a field that calculates the difference, and I can't figure out how. I can't just compare the values, as I would then have to choose to either sum or max the results of that formula.

For example: my transaction line has a qty of 100. I have 3 instances of the custom record, each with 100 as well. My search shows: Qty: 100 (the MAXIMUM value of the 3 repeated rows of 100); Total Custom Qty: 300 (the SUM of the 3 distinct rows with 100 each).

How can I create a formula for a 3rd field that will show me the difference of 200 despite the two different summary types in play?

Upvotes: 1

Views: 1610

Answers (1)

jlbriggs
jlbriggs

Reputation: 17791

OK, I had to tweak the formula I was using a bit more. This worked to return the difference between the total of the multiple custom record rows and the original transaction row (with a Field of Formula (numeric) and a Summary Type of MINIMUM):

SUM({custrecord_quantity})-MIN({custrecord_po.quantity}-{custrecord_po.quantityshiprecv})

So for example, I have results like:

PO Qty: 100 / CR Total Qty: 300 / Difference: 200

Upvotes: 0

Related Questions