This article is relevant if you are seeking to produce purchase orders from a sales order but you need control over which vendors and prices are used when NetSuite’s default rules are not ideal.
Background
We have clients that are in industries that use both drop ship purchase orders and third party assembly operations. In many of these operations, the sales process is sufficiently complicated where every sales order requires consideration of one or more suppliers that will ultimately produce (an element of) the fulfillment. When creating the sales order, the target vendor is identified including the purchase order price for the item that will be delivered to the customer.
NetSuite has a great feature to generate purchase orders (typically drop shipped or special order) based on the line items that are on a sales order. The challenge is that the information on the line to generate a purchase order is based on the item record and may not be what you intend. NetSuite wants to use the preferred item purchase vendor and related price to act as defaults to automatically drive the creation of drop shipped / special order purchase orders.
In our use case, we do not want to use those “defaults”. Instead, we need to take control and target the information we want on the related purchase order(s).
Pattern to Target Purchase Orders from the Sales Orders
The first thing that is needed is two custom transaction columns which will be part of the Sales Order line definition:
- Target Vendor: link to a vendor that will supply the good / service.
- Target Price: price agreed to be paid to the vendor
NetSuite SuiteScript Code Pattern to Generate Linked Purchase Orders from a Sales Order
function prolecto_SalesOrder_AfterSubmit(type) {
//note, code has been adapted from working application and is for educational purposes only
nlapiLogExecution('AUDIT', 'prolecto_SalesOrder_AfterSubmit starting', type);
//only act on relevant records
if (type=='delete') return;
if (!(type=='create' || type=='edit' || type=='approve')) return;
var CONST_FORM_PURCH = "98"; // 98 is standard po
var tran_id = nlapiGetRecordId();
var order = nlapiLoadRecord('salesorder', tran_id);
var customer_id = order.getFieldValue('entity');
nlapiLogExecution('DEBUG', 'salesorder:'+tran_id + ' customerid:'+customer_id, 'customform:'+CONST_FORM_PURCH);
// get the order so we can create a unique list of target vendors
var vendor_array = new Array();
var lines = order.getLineItemCount('item');
for (var l=1; l<=lines; l++){
//note, we have a custom column on the transaction line to hold the target vendor
var vendor = order.getLineItemValue('item', 'custcol_prolecto_vendor', l).toString();
if (vendor.length > 0){
vendor = '_' + vendor; //helpful to ensure that our array is not using numbers
if (!(vendor in vendor_array)){
vendor_array[vendor] = "1";
}
}
}
// create the purchase orders; our goal is to create one PO for each vendor
// using the create PO syntax with parameters for special order will link it to the sales order;
// by default, netSuite will link all items to the a vendor when the po is initially created. Not quite right
// We will delete incorrectly placed vendor rows and keep creating another po until we have a po for all vendors in play
// produce an array list vendors
var keys = Object.keys(vendor_array);
var olen = keys.length;
nlapiLogExecution('DEBUG', 'vendor array list length', olen);
if (olen > 0){
for (var o = 0; o < olen; o++){
var key = keys[o];
var vendor_id = key.substring(1); //remove the '_';
nlapiLogExecution('DEBUG', o + '. vendor vendor_id:'+vendor_id);
// this is the special syntax to setup the po with a target vendor and link it to the SO
var new_po = nlapiCreateRecord('purchaseorder',
{ customform:CONST_FORM_PURCH,
soid:tran_id,
shipgroup:"1",
dropship:"T",
custid:customer_id,
entity:vendor_id } );
// now delete the lines that are not part of the target vendor
// as each po is created, it has one less vendor than the previous one for the one that already was created
// therefore we can delete the lines that don't match the vendor or if the vendor is empty
var num_lines = new_po.getLineItemCount('item');
nlapiLogExecution('DEBUG', 'analyze vendors num_lines:' + num_lines);
for (var d = num_lines; d >= 1; d--){
var vendortarget = new_po.getLineItemValue('item', 'custcol_prolecto_vendor', d).toString();
if (vendortarget.length == 0 || vendortarget != vendor_id){
//throw away the line; this target vendor is not in the current PO; we'll get it next time around
new_po.removeLineItem('item', d);
} else {
// right vendor; set the correct target rate as indicated on our sales order line
var rate = new_po.getLineItemValue('item', 'custcol_prolecto_item_rate', d);
new_po.setLineItemValue('item', 'rate', d, rate);
}
}
}
//commit the work
var num_lines = new_po.getLineItemCount('item');
if (num_lines > 0){
var po_id = nlapiSubmitRecord(new_po, false, true);
nlapiLogExecution('DEBUG', 'created a po', po_id);
}
new_po = null;
}
}
}
Hi Marty, this is a great article in regards to creating a dropship purchase order linked to a sales order but what about the Intercompany dropship process? I would like to automate the creation of the Paired intercompany Sales Order along with the dropship Purchase Order, it seems that I cannot set the fields intercostatus and intercotransaction I always get a currency error and I don’t know the intercostatus value translations either. Any help would be much appreciated.
Hi Daniel,
The truth is that you need to keep playing with the dynamic values to get this to work. I haven’t tried the intercompany one. But in theory, if you can reproduce via the UI, we should be able to get to it via the API. intercompany orders may have some dependencies on certain control customers / vendors that may make it impossible. Do let us know what you find.
Marty
Hi Marty, Is their a way to attach credit memos to a sales order? Ideally, we would like to show a running balance of invoiced items and sales order amounts to show how much is left to invoice.
Hi Christine,
Credit memos are typically attached to Return Authorizations which often are generated from a Sales Order. Or the Credit Memos may be generated from an Invoice which again may have been generated from a Sales Order. This data relationship is a bit distant for NetSuite to search due to one hop. You might be able to target all open invoices / credit memos and bring in credit memos in one search. However, using our CRE tool, we could easily get the data via multiple searches, if needed, and summarize. See this article for a primer on the tool: https://blog.prolecto.com/2015/06/01/supercharge-netsuite-advanced-pdfhtml-templates/
Hi marty,
Great article. I specifically interested in this part
var new_po = nlapiCreateRecord(‘purchaseorder’,
{ customform:CONST_FORM_PURCH,
soid:tran_id,
shipgroup:”1″,
dropship:”T”,
custid:customer_id,
entity:vendor_id } );
In my case it doesn’t copy over the line item to the new PO, and when i saved that PO, the created from is empty.
Did i miss something ?
Thanks
Hello Denny,
Did you replace the values tran_id, customer_id, and vendor_id with the proper integer references?
Marty
[…] Accrual process and it will automatically create a linked purchase order. See my article, “Learn the SuiteScript Pattern to Generate Target Purchase Orders from Sales Orders”, on the power of generating your own related purchase orders from Sales […]
Hi Marty,
Like Denny, i am also interested in this piece of code:
var new_po = nlapiCreateRecord(‘purchaseorder’,{ customform:CONST_FORM_PURCH,
soid:tran_id,
shipgroup:”1″,
dropship:”T”,
custid:customer_id,
entity:vendor_id } );
This was the first time i have seen these initialisation values. Do you know of a resource with all the possible initialisation properties?
I am looking for suitable properties for creating a IC SO from a IC PO.
Regards,
Jordan
Hi marty,
Just managed to reply now, no notification popup in my email, not sure why 😀
There is 1 properties missing in the example, here is the working piece of code for me. The poentity is missing
var newPo = nlapiCreateRecord(‘purchaseorder’,{
customform:78,
soid:soId,
shipgroup:”1″,
dropship:”T”,
custid:customerId,
entity:vid,
poentity:vid,
});
Hello Marty. Your blog is very useful. Thanks a lot for your great job!
Question:
var newPo = nlapiCreateRecord(‘purchaseorder’,{
customform:78,
soid:soId,
shipgroup:”1″,
dropship:”T”,
custid:customerId,
entity:vid,
poentity:vid,
});
How can i do the same with netsuite phptoolkit?
I mean add items to Sales Order and generate Purchase Order(s) with items linked bw SO and PO.
Thank You!
I haven’t worked with the phptoolkit. Yet I understand that the toolkit is really working with Web Services behind the scenes. Thus, the key is to understand if you can indeed send in default parameters during the record create function. I suspect these parameters could be set during your add or upsert operation. But I haven’t tried to confirm we can get the results that we have from SuiteScript.
Marty
Thanks a lot. Unfortunately i could not find this neither in phptoolkit docs nor in phptoolkit sources.
I have another question now. Where i can find all the list of this parameters?
And one more. I’ve created SO. Later I had to add two items in it. Both are of the same preferred vendor. But first with createPo=”_dropShipMent” and the second with createPo=”_specialOrder”. I want separate POs for each of this items. How can i do it using the code above (nlapiCreateRecord….). Or any othe way.
Thanks beforehand.
We had to experiment to find the parameters. Basically, study the URL in the UI to learn how NetSuite is passing parameters around.
This article demonstrates how to create multiple purchase orders through the mechanism of looping and throwing away lines. You should be able to distinguish line elements to know if you should be keeping or deleting the NetSuite generated line items. You then iterate until all your purchase orders are generated to your satisfaction.
Yes in url for creating PO from SO you can find all that options (soid, custid,entity and so). But i could not find parameter, which allows to pass item line numbers.(
I did it that way. Add one item to so. Update so (which hooks userevent with this part with nlapiCreateRecord…). Then add another item and again update SO (and same userevent script). Works like a charm. But i thought maybe there’s more accurate way.
Any idea how to create intercompany Sales Order by SuiteScript from the Purchase order and make them linked by PAIRED INTERCOMPANY TRANSACTION field, same as done in UI Page (Manage Intercompany Sales Orders)?
Hi Koby,
I haven’t tried this. But I speculate that NetSuite is smart enough by knowing that your vendor was set up as an intercompany vendor. This will then trigger the right linkage automatically. Does it not do that?
Marty
Hi Marty,
I tried to use the following code to generate drop ship PO from SO with issues. (script 2.0)
var recNewPO = record.create({
type: record.Type.PURCHASE_ORDER,
isDynamic: true,
defaultValues: {customform: 109, // Custom Purchase Order
soid: nSOID,
shipgroup: “1”,
dropship: true,
custid: nShipAccount,
entity: nVendor }
});
Issues:
1. It creates PO OK but no item lines. I have to add lines by script.
2. New POs do not tied to the original SO, even createdfrom field does showing SO internal ID.
What did I do wrong? Do you have updated code sample in 2.0?
Thanks in advance!
Peter
Hi Peter,
Thanks for the update. We haven’t yet tried this with SuiteScript 2.0. But it may make sense to have a 1.0 script do the tricky work and then have subsequent 2.0 code do your extra work.
Marty
Hi Marty,
I tried below code and PO created, but I didn’t see any links like Created From or Related Records.
Can you Suggest please.?
var purchase= nlapiCreateRecord(‘purchaseorder’,
{ customform:110,
soid:70174,
shipgroup:”1″,
specialorder:”T”,
custid:1735,
entity:1729 ,
poentity:1729 } );
purchase.selectNewLineItem(‘item’);
purchase.setCurrentLineItemValue(‘item’, ‘item’, 18704);
purchase.setCurrentLineItemValue(‘item’, ‘quantity’, 1);
purchase.commitLineItem(‘item’, true);
nlapiSubmitRecord(purchase,false,true);
Looking at your code, I would have expected that you would have a created from Sales Order ID 70174. What do you see in saved search? In addition, I would not add the new lines and instead, just see if you get the proper transformation.
var rec = record.create({
type: ‘purchaseorder’,
isDynamic: true,
defaultValues: {
soid: x,
custid: x,
specord: “T”,
customform: x,
poentity: x
}
});
var entity = rec.setValue(‘entity’,x)
Nice Fabian!
Marty
Marty, per this SuiteAnswer Answer Id: 71358 poentity:vid is supposed to pick the items from the SO lines that have that vendor selected. Current NS allows you to pick a vendor and a PO rate on the SO line (like your custom solution from 2016). When I try this script, the PO is coming-up empty. Can you explain the trick to get NS to pick just the drop-ship items from the SO lines that match the vendor selected?
Hello Nick,
In this algorithm, we are not using NetSuite’s drop ship mechanism. We are creating our own purchase orders using custom fields on the sales order lines. You can, however, get NetSuite to trigger the Drop Ship / Special Order algorithm. Study the URL that is behind the “Create PO” link. You can effectively get that algorithm to run by passing in defaults in the SuiteScrip record create function. Yet, under that approach, you are relying on NetSuite to ready the vendor and rate on the line value of the sales order.
Marty
We’re doing something similar. We inventory a lot of items but want POs automatically created for missing stock, to fulfill on-demand. We’ve added a custom field to transaction lines – `custcol_preferred_vendor` – that tracks the preferred vendor that an item should be bought from, along with a custom field on Purchase Orders – `custbody_sales_order` – that is used to link the PO back to the SO.
Here’s the code that auto-creates these POs, leaving the link here in case it helps anyone else: https://gist.github.com/charlie-s/8df64f0287e037d86e93fcc4e1451463
Hi Charlie,
Thank you for sharing the reference to your code. All good!
Marty
I am using you script. There is a small issue I am trying to get my head around.
even though I set rate and quantity the amount stays at 0. Therefore all other financial stuff is 0.
When I calculate the amount myself, rate * quantity right? I get TRANS_UNBALNCD error. I think this is weird.
Andre,
First, ensure you are not stuffing a value in the amount field. Next, consider using dynamic mode when constituting the transaction. This should get it. The TRANS_UNBALNCD error would lead me to believe something has been stuffed in fields including the header as well.
Marty
Hello Marty,
I have a not so different and tough issue generating POs for kits of special order items. The PO generated well at the SO creation but not when we are doing modification on the SO (if I had a kit to an existing SO). Even if I try to regenerate the PO through another item from the same vendor (generating the PO from the special order link of this other item).
Do you see any way to generate the PO of the components of the kit (which are special order items) using a script?
Thanks a lot.
Hello François,
While I haven’t tried, I doubt we can get NetSuite to automatically explode the kit items. However, I don’t see why we could not take matters into our hands and produce the lines we need. My firm has built many order generators, and none of these requests concern me when we are willing to take full control. Keep at it.
Marty
Hi Marty,
Hope you are doing well.
I need help from you: I have a sales order with multiple lines and each line has a different currency with a dropship link field, when I click on the dropship PO link I want to get the currency value from that particular sale order line.
Can you please help me here?
Helo Brus,
I will need a screenshot. Generally, transactions lines do not have different currencies. It may be the way you are modeling the sales order that requires more reflection.
Marty