I'm not one to ask about writing said SQL query, however For the search approach, my instinct would be to run the first search, get an array of applicable item ids (those with a backorder for the specific location), then use that as a filter of the next search (inbound shipment). Filter would be similar to

{'item', 'anyof', ...[_arrayofitemids_]}

So... I asked chatGPT about that query, and even I know that this won't work, but it's the right idea if you decided to use

N/query

Assuming that the table names are

sales_orders

and

inbound_shipments

and they have columns named

location

,

item

,

quantity

, and

order_number

, the PL/SQL query for NetSuite that joins the two tables and matches the location and item on each table can be written as:

ChatGPT is pretty cool

You might want to have a look at this Chrome Extension. It combines and compares Google with ChatGPT search results: https://chrome.google.com/webstore/detail/chatgpt-for-google/jgjaeacdkonaoafenlfkkkmbaopkbilf/

This is what ChatGPT came up with when I queried it about nested saved searches:

// Define the inner saved search (nested search)
var nestedSearch = search.create({
    type: 'customer',
    filters: [['internalid', 'anyof', '123', '456']],
    columns: ['internalid', 'companyname']
});

// Define the outer saved search
var outerSearch = search.create({
    type: 'invoice',
    filters: [['mainline', 'is', 'T']],
    columns: ['internalid', 'tranid']
});

// Add the nested search as a subquery in the outer search
var sublist = outerSearch.createSublist({
    name: 'customers',
    join: 'customer',
    search: nestedSearch
});

// Add columns from the nested search to the outer search
sublist.addColumn({
    name: 'internalid',
    join: 'customer'
});

sublist.addColumn({
    name: 'companyname',
    join: 'customer'
});

// Run the search
var searchResults = outerSearch.run();

To create a nested saved search using search in NetSuite SuiteScript, you can follow these steps: 1. Define the inner saved search using the

search.create()

method, just as you would for any other saved search. This saved search will be the nested search that will be used as a subquery in the outer search. 2. Define the outer saved search using the

search.create()

method. You can use the

search.createFilter()

method to add filters to the outer search. 3. Use the

search.createColumn()

method to add any columns that you want to retrieve from the outer search. You can also use the

search.addColumn()

method to add columns from the nested search that you want to retrieve. 4. Add the nested search as a subquery in the outer search by using the

search.createSublist()

method. This method takes the nested search as its parameter and adds it as a sublist to the outer search. Example was the code above

yeah, I rest my case with that step-by-step it's provided. Oof