NetSuite Transaction Dates

Question

There are lots of dates to choose from in a NetSuite transaction, specifically a sales order. I'm using SuiteTalk to synchronize shipping information from NetSuite to a legacy system. Which date is the correct date to determine when activity has taken place against a sales order to indicate something shipped?

Answer 1

After reviewing and comparing all date values in all sales orders, I've concluded that there is no clear winner which shows the last date/time it had activity. A formula field was the best solution. Here is a saved search that demonstrates that formula.

Notice the formula below that finds the maximum date from all relevant dates.

This calculated date can also be used in a filter to find only recently modified sales orders items, specifically those that shipped along with tracking numbers and quantities.

Here is the formula in a format which can be cut and pasted.

GREATEST({trandate},{lastmodifieddate},{linelastmodifieddate},{billingtransaction.trandate},{billingtransaction.lastmodifieddate},{billingtransaction.linelastmodifieddate})

Answer 2

See if you can find {shipdate} or {actualshipdate}

Answer 3

After contacting NetSuite support, I learned that the {linelastmodifieddate} is not available via the SuiteTalk API. That left me with the following dates:

{trandate}
{lastmodifieddate}
{billingtransaction.trandate}
{billingtransaction.lastmodifieddate}

In my case, I'm monitoring all sales orders to see when something ships. So I want to know when we fulfill a line item in-house or when we drop ship an item. The problem I had was that these dates are all over the place. I wanted to include {linelastmodifieddate}, because there were times when it was the most recent.

Since we invoice when items ship or drop ship, the {billingtransaction.trandate} represents an accurate ship date. {billingtransaction.quantity} represents and accurate quantity shipped. And {billingtransaction.trackingnumbers} includes a list of tracking numbers for that shipment. That's all the info I need to see the "Ship status" of every line item in a sales order.

Here is some sample code that I started with to identify sales orders that have recently had a shipment.

        service.searchPreferences = new SearchPreferences();
        service.searchPreferences.bodyFieldsOnly = false;
        service.searchPreferences.returnSearchColumns = true;

        TransactionSearchAdvanced customSearch = new TransactionSearchAdvanced()
        {
            savedSearchScriptId = "customsearch_[your saved search here]"
            ,criteria = new TransactionSearch()
            {
               [your criteria here]
            }

        };

        Console.WriteLine("Querying NetSuite");
        SearchResult res = service.search(customSearch);

        Console.WriteLine("\nThe search() operation completed successfully.");
        Console.WriteLine("  Total Records = " + res.totalRecords);
        Console.WriteLine("  Total Pages = " + res.totalPages);
        Console.WriteLine("  Page Size = " + res.pageSize);
        Console.WriteLine("  Current Page Index = " + res.pageIndex);

        List<TransactionSearchRow> tsRows = new List<TransactionSearchRow>();

        // Page through all the results
        while (res.searchRowList.Length > 0)
        {
            foreach (TransactionSearchRow transactionRow in res.searchRowList)
            {
                tsRows.Add(transactionRow);
            }

            Console.WriteLine("\nQuerying NetSuite again...");
            res = service.searchMore(++res.pageIndex);
        }

        // Sort the results
        tsRows.Sort(delegate (TransactionSearchRow x, TransactionSearchRow y)
        {
            return x.basic.tranId[0].searchValue.CompareTo(y.basic.tranId[0].searchValue);
        });

        int i = 1;

        // Parse the results
        foreach (TransactionSearchRow tsRow in tsRows)
        {
            TransactionSearchRowBasic transactionRowBasic = tsRow.basic;
            ItemSearchRowBasic itemRowBasic = tsRow.itemJoin;
            TransactionSearchRowBasic billingRowBasic = tsRow.billingTransactionJoin;

            string itemItemId = "";
            string itemDesc = "";
            double reqQty = 0;
            string billTranId = "";
            double billQty = 0;
            string billTrackNo = "";

            try { itemItemId = itemRowBasic.itemId[0].searchValue; } catch { }
            try { itemDesc = itemRowBasic.salesDescription[0].searchValue; } catch { }
            try { reqQty = transactionRowBasic.quantity[0].searchValue; } catch { }
            try { billTranId = billingRowBasic.tranId[0].searchValue; } catch { }
            try { billQty = billingRowBasic.quantity[0].searchValue; } catch { }
            try { billTrackNo = billingRowBasic.trackingNumbers[0].searchValue; } catch { }

            DateTime trandate = DateTime.MinValue;
            DateTime lastmodifieddate = DateTime.MinValue;
            DateTime billtrandate = DateTime.MinValue;
            DateTime billlastmodifieddate = DateTime.MinValue;

            try { trandate = transactionRowBasic.tranDate[0].searchValue; } catch { }
            try { lastmodifieddate = transactionRowBasic.lastModifiedDate[0].searchValue; } catch { }
            try { billtrandate = billingRowBasic.tranDate[0].searchValue; } catch { }
            try { billlastmodifieddate = billingRowBasic.lastModifiedDate[0].searchValue; } catch { }

            var list = new List<DateTime>();
            list.Add(Convert.ToDateTime(trandate));
            list.Add(Convert.ToDateTime(lastmodifieddate));
            list.Add(Convert.ToDateTime(billtrandate));
            list.Add(Convert.ToDateTime(billlastmodifieddate));
            DateTime maxdate = list.Max(date => date);


            Console.WriteLine(
                $"\n {i++} of {tsRows.Count}" +
                $"\n    Document Number: {transactionRowBasic.tranId[0].searchValue}" +
                $"\n    SO Date: {trandate}" +
                $"\n    SO Modified: {lastmodifieddate}" +
                $"\n    Invoice Date: {billtrandate}" +
                $"\n    Invoice Modified: {billlastmodifieddate}" +
                $"\n    +++Max Date: {maxdate}" +
                $"\n    Item Name: {itemItemId}" +
                $"\n    Item Description: {itemDesc}" +
                $"\n    Bill Doc Number: {billTranId}" +
                $"\n    Requested Qty: {reqQty}" +
                $"\n    Bill Qty: {billQty}" +
                $"\n    Tracking Nos: {billTrackNo}"
            );

        }

        service.logout();
        Console.WriteLine("\n\nHit Enter to close this window.");
        Console.ReadLine();