https://netsuiteprofessionals.com logo
Join Slack
Powered by
hi if we have a custom col on sublist how would I ...
# suitescript
s
hi if we have a custom col on sublist how would I access that field via script ? is this correct and would dynamic true or false make a diffrecne
Copy code
var quantity = bill.getSublistValue({
        sublistId: "item",
        fieldId: "custcol6",
        line: i,
      });
m
s
thanx,, I'm trying to create an inbound shipment and the value for the p.o. is that the internal id or document number?
Copy code
inboundShipment.setSublistValue({
        sublistId: "items",
        fieldId: "purchaseorder",
        value:  the value,
        line: i,
      });
m
internalid
of the record.
s
string or integer ?
m
Should be an integer, but in practice both usually seem to work.
s
not sure why it breaks on these lines
m
What is the error?
s
nothing just stops working when it gets to those lines crashes with no error output
m
There should be some form of error. Even if it's the unhelpful 
Unexpected error
.
s
i added a try-catch now got the error
this is what i hate about netsuite if it blows up let me know with out try catch
are both inbound shipment and bill/p.o. zero indexed ?
m
I think most transaction lines are zero indexed when your looping through them.
s
this is the error
Copy code
"You have attempted an invalid sublist or line item operation. You are either trying to access a field on a non-existent line or you are trying to add or remove lines from a static sublist.",
i made progress the current error
Copy code
message: "Invalid shipmentitem reference key 32880932."
I would assume for shipmentitem it would be the internal id of the item but doesn't work
m
I would try it in the UI first and make sure the item works there first.
s
it does
m
I would try casting to string or integer and seeing if that changes things.
s
I might have figured it out just guessing though . Maybe
Copy code
lineuniquekey of purchase order line item NOT intenal id of item or lineuniquekey from line of bill
@battk can you shed some light
b
what does the code look like
s
Copy code
for (var i = 0; i <= itemLineCount; i++) {
        var item = bill.getSublistValue({
          sublistId: "item",
          fieldId: "item",
          line: i,
        });
        var quantity = bill.getSublistValue({
          sublistId: "item",
          fieldId: "quantity",
          line: i,
        });

        // var lineuniquekey = bill.getSublistValue({
        //   sublistId: "item",
        //   fieldId: "lineuniquekey",
        //   line: i,
        // });
        var ppnum = bill.getSublistValue({
          sublistId: "item",
          fieldId: "orderdoc",
          line: i,
        });
        var pcustcol6 = bill.getSublistValue({
          sublistId: "item",
          fieldId: "custcol6",
          line: i,
        });

        let POid = parseInt(ppnum);
        let itemId = parseInt(item);
        log.debug(typeof ppnum, {
          ppnum,
          pcustcol6,

          POid,
          itemId,
          item,
        });
        inboundShipment.selectNewLine({
          sublistId: "items",
        });
        inboundShipment.setCurrentSublistValue({
          sublistId: "items",
          fieldId: "purchaseorder",
          value: POid,
        });
        inboundShipment.setCurrentSublistValue({
          sublistId: "items",
          fieldId: "shipmentitem",
          value: "31454852",
        });
        inboundShipment.commitLine({
          sublistId: "items",
        });
Copy code
var inboundShipment = record.create({
        type: record.Type.INBOUND_SHIPMENT,
        isDynamic: true,
      });
      var bill = record.load({
        type: record.Type.VENDOR_BILL,
        id: id,
        isDynamic: false,
      });
context: we need to create the shipment off of the venodr bill which can be many p.o.
b
your for loop loops too many times
i also doubt an inbound shipment lets you create duplicate lines
s
how is it looping to many times
b
Copy code
for (var i = 0; i <= itemLineCount; i++)
if itemLineCount is 5, how many times would the loop run
s
ooh
Copy code
for (var i = 0; i < itemLineCount; i++)
i still get this error
Copy code
"Invalid shipmentitem reference key 26777."
b
stop trying to add a duplicate line
one unique shipment item per inbound shipment
s
im not understanding
Copy code
`/**
 * @NApiVersion 2.1
 * @NScriptType ClientScript
 * @NModuleScope SameAccount
 */

define(["N/record"], function (record) {
  function pageInit() {
    console.log("pageInit");
  }
  //seearch for inbound shipment before create then items on bill
  function createinbound() {
    try {
      /// skip if status of bill is wrong //
      var id = document.forms["main_form"].id.value;
      var inboundShipment = record.create({
        type: record.Type.INBOUND_SHIPMENT,
        isDynamic: true,
      });
      var bill = record.load({
        type: record.Type.VENDOR_BILL,
        id: id,
        isDynamic: false,
      });

      let vendor = bill.getValue({ fieldId: "entityname" });
      let container = bill.getValue({ fieldId: "custbody4" });
      let entry_number = bill.getValue({ fieldId: "custbody34" });
      let oceanbol = bill.getValue({ fieldId: "custbody33" });
      let bol = bill.getValue({ fieldId: "custbody8" });
      let fda_pedgree = bill.getValue({ fieldId: "custbody5" });
      let fda_status = bill.getValue({ fieldId: "custbody32" });
      log.debug({ container, oceanbol, bol });
      inboundShipment.setValue({
        fieldId: "billoflading",
        value: bol,
      });
      inboundShipment.setValue({
        fieldId: "custrecord154",
        value: oceanbol,
      });
      // inboundShipment.setValue({
      //   fieldId: "custrecord145",
      //   value: fda_status,
      // });
      inboundShipment.setValue({
        fieldId: "custrecord149",
        value: container,
      });
      inboundShipment.setValue({
        fieldId: "billoflading",
        value: bol,
      });
      // inboundShipment.setValue({
      //   fieldId: "custrecord152",
      //   value: vendor,
      // });
      inboundShipment.setValue({
        fieldId: "shipmentmemo",
        value: entry_number,
      });

      var itemLineCount = bill.getLineCount({
        sublistId: "item",
      });
      for (var i = 0; i < itemLineCount; i++) {
        var item = bill.getSublistValue({
          sublistId: "item",
          fieldId: "item",
          line: i,
        });
        var quantity = bill.getSublistValue({
          sublistId: "item",
          fieldId: "quantity",
          line: i,
        });

        // var lineuniquekey = bill.getSublistValue({
        //   sublistId: "item",
        //   fieldId: "lineuniquekey",
        //   line: i,
        // });
        var ppnum = bill.getSublistValue({
          sublistId: "item",
          fieldId: "orderdoc",
          line: i,
        });
        var pcustcol6 = bill.getSublistValue({
          sublistId: "item",
          fieldId: "custcol6",
          line: i,
        });

        let POid = parseInt(ppnum);
        let itemId = parseInt(item);
        log.debug(typeof ppnum, {
          ppnum,
          pcustcol6,

          POid,
          itemId,
          item,
        });
        inboundShipment.selectNewLine({
          sublistId: "items",
        });
        inboundShipment.setCurrentSublistValue({
          sublistId: "items",
          fieldId: "purchaseorder",
          value: POid,
        });
        inboundShipment.setCurrentSublistValue({
          sublistId: "items",
          fieldId: "shipmentitem",
          value: itemId,
        });
        inboundShipment.commitLine({
          sublistId: "items",
        });
        
      }
      log.debug("test", "this line fails");
b
your code before was setting the same shipment item
s
oh that was a typo this is the current code
b
this one isnt following the rules of inbound shipments
make the inbound shipment that you want in the ui first
s
which rule ?
b
set the same fields in the same order that you would in the ui
s
the line items or the main ?
b
both honestly
s
the main there is no order
b
wont particularly matter
make the inbound shipment that you want in the ui first
make not of the order you set the fields
s
i did that
b
you will be setting the same fields in the same order
with the same values
s
yup
b
the values you can get afterwards
but the order you will need to keep track of
s
for the line items, i use the name of the item
b
once you create the inbound shipment, you want to load it in script so you can inspect which values actually set in those fields
usually only matters for select type fields since the internal id isnt really visible
so load the inbound shipment and get the values in the purchaseorder and shipmentitem fields
s
per bussniess needs i working off a bill not p.o.
which is many p.o.
b
that will matter latter
but not for learning how to create an inbound shipment
s
this is the out put for created i ui
Copy code
shipmentitem: {
               legacyStringValue: "32654346"
            },
            shipmentitem_display: {
               legacyStringValue: "583300"
            },
the top number is not the internal id but the unique line key from po
b
yup
its not sane to identify a line by the item
its not unique
s
ok I have a workaround when the bill is created, we have a field for the po number i want to add this to the bill line item
Copy code
lineuniquekey:"26741487"
b
its probably easier just to do a search or query to get the line unique key
s
wouldnt that slow down the script
b
you wouldnt be loading the bill if you wanted faster
s
lol
i would need to build query input p.o. id and item id will out put unique ?
b
depends on how you build the query
either way, you should only need to do 1 query
s
one query per line of bill
b
you really would not want to do 1 query per line
thats incredibly wasteful
s
yup that is where my query skills are lacking
b
start with a query that gets the bill
then add to it so that you get the purchase orders related to the bill
s
instead of loading it
b
then the lines of the purchase orders related to the bill
you can do the same thing with a search if you wished
s
Copy code
var bill = record.load({
        type: record.Type.VENDOR_BILL,
        id: id,
        isDynamic: false,
      });
i have all the info of the bill , and p.o. numbers already
b
you can get away with not loading the record
if you still want to load the record, then you skip to step 2 of my outline and instead make a filter for those purchase orders
s
that is where im stuck on
this currently gives me the po number
Copy code
var ppnum = bill.getSublistValue({
          sublistId: "item",
          fieldId: "orderdoc",
          line: i,
        });
b
make an array of purchase order internal ids
s
so still lopop thru
b
then make a transaction query for transactions (or transaction lines if you want) whose id is in that list
s
i can't just search based on p.o. as some items on the p.o. may not be filled so its p.o. and item
b
you add more filters to limit the purchase order columns to the ones you want
s
I'm taking your advice and starting over
first query the bill
"then add to it so that you get the purchase orders related to the bill" i need to create a join ?
b
yes
s
on which filed id ?
b
usually you figure it out from the record catalog, or from building the dataset in the workbook analytics ui
s
@battk so far this is my query for which gives me the bill and the bill transactions lines, im I on the right track, or did I make a mistake already . Do i join on the poId and then po transaction lines to get the transcation lines ?
Copy code
let myCustomerQuery = query.create({
        type: query.Type.TRANSACTION,
      });
      let joincopponent = myCustomerQuery.autoJoin({
        fieldId: "transactionLines",
      });
      // let podata = joincopponent.autoJoin({ fieldId: "orderdoc" });

      myCustomerQuery.condition = myCustomerQuery.and(
     myCustomerQuery.createCondition({
        fieldId: "id",
        operator: query.Operator.EQUAL,
        values: id,
      }),
     myCustomerQuery.createCondition({
        fieldId: "custbody31",
        operator: <http://query.Operator.IS|query.Operator.IS>,
        values: true,
      })
     )
m
When joining in columns or filters there is a shortcut. Whether it comforms to Netsuite's best practices I don't know but it works for me.
Copy code
myCustomerQuery.columns = [
    myCustomerQuery.createColumn({
        fieldId: 'transactionlines.quantity'
    })
]
You can pull those ID's out of analytics when you are interacting with the dataset by clicking on the information icon next to the field you want to use.
s
you mean instead of joining ?
m
Instead of declaring the join the way it's documented. I just use 
transactionlines.quantity
syntax in the field id.
Like I said. Not promising it conforms to Netsuite best practice. But haven't had it not work yet.
s
that helps
@Marvin let me explain my question what im trying to figure out : i have the id of bill i need the p.o. id (which can be many) and the p.o. line items
b
it usually helps to build the dataset for query in the ui first
you can see what columns you have access to, and which data is inside them
m
What battk said. It will make your scripting much quicker.
s
i have never used the workbook either yet
m
Going to be critical to learning in order to use N/query
s
Yeah I see back to learning and Google
b
you only really need to learn half of workbook analytics, the dataset part
💯 1
s
Ok thanx
how can i export the dtaset not just excel
i m still not sure how to join the transaction line for the bill on the po this is what i have so far
Copy code
SELECT
transaction.ID as transaction_id,  
transactionLine.ID as transactionLine_id, 
transactionLine.item as item_id,
transactionLine.custcol6 as po_number,
FROM transaction
JOIN  transactionLine ON transaction.ID = transactionLine.transaction
 WHERE transaction.ID = 13636399 AND transactionLine.item  IS NOT NULL
m
Might want to ask your question in the #C2A1ZEMF0 channel. They will probably be more able to answer your questions on joins and how to do your query.
s
@battk seemed to have an idea
b
use the next or previous transaction joins to find related transactions
keep in mind that this relationship can exist at both the transaction and transaction line level
s
so let say i m joining on the transaction line
so now i got the po id
Copy code
SELECT
transaction.ID as transaction_id,  
transactionLine.ID as transactionLine_id, 
transactionLine.item as item_id,
transactionLine.custcol6 as po_number,
PreviousTransactionLineLink.previousdoc 
FROM transaction
JOIN  transactionLine ON transaction.ID = transactionLine.transaction
JOIN PreviousTransactionLineLink ON PreviousTransactionLineLink.nextline = transactionLine.id AND
PreviousTransactionLineLink.nextdoc = transactionLine.transaction
WHERE transaction.ID = 13636399 AND transactionLine.item  IS NOT NULL
b
you now have the option of joining from the previous transaction line to either the transaction or transaction line
you will likely want the transaction line over the transaction, though that advice relies understanding on why you would want one over the other
s
I need the lineunnique key from transaction line of the po
b
using the transaction from the previous transaction means your next step is getting the transaction lines of that transaction, which can include unrelated lines
s
But not every item on po just the 16( example)
b
using the transaction lines from the previous transaction join will only get you lines that are related
the advice remains, build the dataset in the ui first
you can load it afterwards to copy into your script
s
I haven't figured out how to extract from dataset
b
s
Ok will check later thanx
@battk are you saying join PreviousTransactionLineLink or PreviousTransactionLink
b
you are at the point where you really need to build the dataset in the ui first
s
that itired dont seem to be able to
b
its unlikely that you would get this working if you cant in the ui
s
I realized I need to just work backward start from the PO --> bill
Copy code
SELECT
transactionLine.transaction,
transactionLine.memo,
transactionLine.item as item_id,
transactionLine.uniquekey,
transactionLine.quantityonshipments
FROM transactionLine
INNER JOIN NextTransactionLineLink ON
NextTransactionLineLink.previousline = transactionLine.id AND
NextTransactionLineLink.previousdoc = transactionLine.transaction
WHERE NextTransactionLineLink.nextdoc = 13636399
69 Views
Open in Slack
PreviousNext
Powered by