how can i create a saved search in netsuite that compares the sales order total to the sum of all invoices against this sales order and return only sales orders where these numbers do not match. I am looking for orders where the invoice total is more or less than the sales order total.

AI generated answer (using internal Slack threads):

To create a saved search in NetSuite that compares the sales order total to the sum of all invoices against this sales order and return only sales orders where these numbers do not match, you can follow these steps: 1. Navigate to Reports > Saved Searches > All Saved Searches > New. 2. Select Transaction from the list. 3. On the Criteria subtab, set the following filters: - Type = Sales Order - Main Line = True - Formula (Numeric) != 0 (This is where you will compare the sales order total to the sum of all invoices. See step 4 for the formula details) 4. For the Formula (Numeric), you can use a SQL case statement to compare the sales order total to the sum of all invoices. The formula might look something like this:

CASE WHEN {amount} != (SELECT SUM({applyingtransaction.amount}) FROM Transaction WHERE {type} = 'Invoice' AND {mainline} = TRUE AND {createdfrom.id} = {id}) THEN 1 ELSE 0 END

5. On the Results subtab, you can choose the fields you want to display in your search results. For example, you might want to include Sales Order: Internal ID, Sales Order: Total, and a Formula (Currency) field with the formula

SELECT SUM({applyingtransaction.amount}) FROM Transaction WHERE {type} = 'Invoice' AND {mainline} = TRUE AND {createdfrom.id} = {id}

to display the sum of all invoices against the sales order. 6. Save the search. Please note that this is a complex saved search and may require some adjustments based on your specific NetSuite setup and requirements. The SQL case statement in the Formula (Numeric) field is used to compare the sales order total to the sum of all invoices, and the search will only return sales orders where these numbers do not match. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******