CODEWITHSUNDEEP
netsuite
Nic
Nic

Reputation: 5

NetSuite Search formula for items that have no open transactions

I am trying to create a formula to obtain a list of items that have no open transactions.

I cant just filter out by status as this filters out transactions that are open, as opposed to showing me only items with nothing open.

So basically if an item has anything open then i dont want it on the search. I do need it on the search if it has all closed or it has no transactions at all.

Hoping someone can help put me in the right direction.

I am a little bit stuck at where to start with the formulas and tried a case formula.

Upvotes: 0

Views: 1098

Answers (4)

bknights
bknights

Reputation: 15447

I don't think this is the sort of thing you can do with a single saved search.

It would be fairly easy to do with SuiteQL though.

The script below runs in the console and finds items that are not on any Pending Billing Sales Orders. It's adapted from a script with a different purpose but illustrates the concept.

You can get a list of the status values to use by creating a saved search that finds all the transactions with open statuses you want to exclude , take note of that saved search's id and running the second script in the console

require(['N/query'], query => {


    const sqlStr = `
    select item.id, itemid, count(po.tranid) as po, count(bill.tranId) as bill, max(bill.tranDate) as lastBilled, count(sale.tranId) as sales, count(tran.tranId) as trans
    from item 
        left outer join transactionLine as line
            on line.item = item.id
        left outer join transaction as tran  on line.transaction = tran.id
        left outer join transaction as po on line.transaction = po.id and po.type = 'PurchOrd'
        left outer join transaction as bill on line.transaction = bill.id and bill.type = 'VendBill'
        left outer join transaction as sale on line.transaction = sale.id and sale.type in ('CustInvc', 'CashSale')
    where item.id  not in (select otl.item from transactionLine otl, transaction ot where
        otl.transaction = ot.id and ot.status in ('SalesOrd:F')) 
        group by item.id, item.itemid
`;

    console.log(sqlStr);

    console.log(query.runSuiteQL({
        query: sqlStr
    }).asMappedResults().map((r, idx)=>{
        if(!idx) console.log(JSON.stringify(r));
        return `${r.id}\t${r.itemid}\t${r.po}\t${r.bill}\t${r.lastBilled}\t${r.sales}\t${r.trans}`;
    }).join('\n'));

});

require(['N/search'], search=>{
    
const filters = search.load({id:304}).filters;
console.log(JSON.stringify(filters.find(f=>f.name == 'status'), null, ' '));
});

In terms of doing something with this you could run this in a saved search and email someone the results, show the results in a workbook in SuiteAnalytics or build a portlet to display the results - for this last Tim Dietrich has a nice write up on portlets and SuiteQL

Upvotes: 0

Simon Delicata
Simon Delicata

Reputation: 411

I think this is possible in a saved search, and requires a change in the way the filtering is done. Rather than filtering on the "Filters", using grouping and summary calculations to determine if an item qualifies, basically :

  1. Create the item saved search as you would normally, but don't include a "Standard" filter for the openness of the transaction.
  2. In the results, group by item name (or internalid), and another fields you want to include in the top-level results.
  3. In the Criteria - Summary list, add a Formula (Number) condition :
    • Summary Type= Sum (Count won't work here)
    • Formula = case when {transaction.status} = 'Open' then 1 else 0 end
    • Equal to 0

Whether this is more or less elegant than bknight's answer is debatable.

Upvotes: 0

SuiteStar
SuiteStar

Reputation: 132

To get the value of non transaction items as well, You need to check the check box use expression under criteria in standard subtab use parens() with OR expression.

And add one more condition as "Transaction Fields-Internal Id-anyOf-none with "Transaction Fields-status-anyOf-select all closed/rejected/declined statuses". Add both condition with OR logic.

It will work for both items condition if it has transaction status with closed or with none of transaction internal ids.

Thanks.

Upvotes: 0

SuiteStar
SuiteStar

Reputation: 132

You can use item saved search adding under criteria as "Transaction Fields-status-anyOf-select all closed/rejected/declined statuses" not in filter reason of saved search.

Thanks.

Upvotes: 0

Related Questions