Pull itemFulfillment using SalesOrder information

Using the REST API, how do I pull all related item fulfillments for a given sales order?

Ultimately I need the ship date, ship method, carrier and tracking number and quantity fulfilled for each fulfillment, so I can report these fulfillments to another system.

Quantity fulfilled is the only one I’ve been able to find in the sales order under items.

Add Comment
3 Answer(s)
Best answer

Hello everyone. I’m a little late to the party, but I was quite disappointed to see such wacky responses here. There sure isn’t a lot of actual help for this being a NetSuite Professionals forum.

You can do this with SuiteQL. It is really straightforward.

SELECT *
FROM Transaction
INNER JOIN TransactionLine ON (TransactionLine.Transaction = Transaction.ID)
INNER JOIN Item ON (Item.ID = TransactionLine.Item)
WHERE Transaction.Type = 'ItemShip'
AND TransactionLine.CreatedFrom IN ({salesorder_ids})
GROUP BY Transaction.ID

This routine takes a set of SalesOrder internal ids and returns the ItemFulfillment associated with them. Simply replace {salesorder_ids} with (7129318,71290754) – or whatever your ids are. It can be one or more ids.

I also inner join TransactionLine and Item so I can have access to the item level detail for each element of that Transaction. This gives you everything you’d possibly need to work on for an ItemFulfillment operation.

I’m somewhat surprised there isn’t more consensus about this operation. In any event, there ya go. If anyone ever needs the ability to do this in the future, that is the way to do it.

Rookie Answered on April 15, 2021.

Thanks I really appreciate the response. I had similar thoughts as to how difficult it was to get useful information here, but I’m glad someone was able to give a clear, straightforward answer.

on April 15, 2021.
Add Comment

You have 3 options for searching: Record Collection FilteringSuiteQL Queries, or SuiteAnalytics Workbooks

Its unlikely that you will be able to use the createdFrom as a filter for record collection filtering. The metadata for item fulfillments lists the filterable fields in the x-ns-filterable  key and createdFrom is not a filterable field. For example, my account has the filterable fields as ["trandate","externalId","memo","id","postingperiod","status","createdDate","tranId","lastModifiedDate","entity"]. You might be able to do something like place the create from id value in a custom field and filter based off of that.

A SuiteAnalytic Workbook will not be able to be dynamic, you won’t be able to get information for a specified sales order since you cant add filters to the existing workbook.

That leaves SuiteQL as the least hacky solution.  I personally favor creating the dataset in the ui, then loading the dataset in suitescript to get the SuiteQL. The following SuiteQL is what NetSuite generates for a basic transaction query thats gets the internal id of transactions where the created from internal id is 1542 and the type is item fulfillment:

RE: Pull itemFulfillment using SalesOrder information

SELECT "TRANSACTION"."ID" AS idRAW /*{id#RAW}*/ FROM "TRANSACTION", PreviousTransactionLink WHERE "TRANSACTION"."ID" = PreviousTransactionLink.nextdoc(+) AND ((UPPER("TRANSACTION"."TYPE") IN ('ITEMSHIP') AND PreviousTransactionLink.previousdoc IN ('1542')))

You can create a similar query to get all the item fulfillents internal ids and get each individual one to get the information you need. Ideally you make the query also contain all the columns you need so that you dont need to get each item fulfillment.

 

Intermediate Answered on May 18, 2020.
Add Comment

You need to pull a search to find all Item Fulfillments where Created From is your Sales Order. The fields you seek are on the Item Fulfillment, not the SO.

Advanced Answered on May 15, 2020.

Thank you for pointing me in the right direction.  Could you elaborate on how to preform this search using the REST API?  I am unsure how to search for an embedded value.

…/inventoryItem?q=createdFrom.id IS 10000

How do I reference the ID under the createdForm?

on May 15, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor