https://netsuiteprofessionals.com logo
Join Slack
Powered by
How can I get the current bill of materials revisi...
# suitescript
m
How can I get the current bill of materials revision from the assembly item record? Right now i'm just using suitescript in the browser console. After I get the assembly item record, what function can i call to get the current bill of materials revision?
s
You could probably create a search where you use the Internal ID of the Assembly Item as the filter and bring in the Member Item as columns Member Quantity.
So basically, it would look something like this: var itemSearchObj = search.create({ type: "item", filters: [ ["internalidnumber","equalto",/*Put in the Assembly Item Internal ID here*/] ], columns: [ "memberitem", "memberquantity" ] }); var searchResultCount = itemSearchObj.runPaged().count; log.debug("itemSearchObj result count",searchResultCount); itemSearchObj.run().each(function(result){ // .run().each has a limit of 4,000 results return true; });
You could then set a variable to the results like: var membItem = result.getValue('name":"memberitem");
Or something like that - hope I made sense!
m
hmm, member item and member quantity are blank for all items...
s
Hm, they definitely should not be. Let's confirm that we're pulling in the right Item. I imagine earlier in the code you can extract the Assembly Item Internal ID, right? So let's say for example, you did something like: var newRec = record.load({ type: scriptContext.newRecord.type, id: scriptContext.newRecord.id }) var assemblyIID = newRec.getValue({ fieldId: 'id' }); On the filter in the search above, it should look like this: ["internalidnumber","equalto",Number(assemblyIID)]
m
yes, i have
Copy code
var itemSearchObj = search.create({
   type: "item",
   filters:
   [
      ["internalidnumber","equalto","12968"]
   ],
   columns:
   [
      search.createColumn({
         name: "itemid",
         sort: search.Sort.ASC,
         label: "Name"
      }),
      search.createColumn({name: "memberitem", label: "Member Item"}),
      search.createColumn({name: "memberquantity", label: "Member Quantity"})
   ]
});
var searchResultCount = itemSearchObj.runPaged().count;
log.debug("itemSearchObj result count",searchResultCount);
itemSearchObj.run().each(function(result){
   console.log(result.getAllValues());
   return true;
});
where 12968 is the internal id of my assembly item record
s
That's very strange... Can you try that search in the UI and see if you get any results?
Hm I'm wondering if also the script is considering "12968" as string, whereas it should look for a number
m
All of the member columns are blank. its also blank for all assembly items. But we for sure have bill of materials and bill of materials revisions on many items.
s
So when you say that all of the member columns are blank - you are referring a search you created in the UI, filtering ONLY on that Item 12968 and bringing in the Member Item + Member Quantity... Also, we don't have Advanced Bill of Materials enabled, so there might be some differences there
s
advanced BOM is very different
the boms are entirely different records and are not simply joined to the item masters
s
Oh wow
Sorry about that @makon I didn't mean to lead you astray!
s
@makon I find it easier to retrieve bom information using the analytics/query module rather than saved searches since you need multiple levels of joins to make sure you are on the current bom revision. (when using advanced bom)
m
@Sandii Would you happen to have an example handy? I haven't dug into the query module yet.
s
Copy code
function createAdvancedBomQuery() {
        var queryMain = query.create({
            type: 'bomrevision'
        });
        var conditionOne = queryMain.createCondition({
            fieldId: 'billofmaterials.bomassemblylocation.ismasterdefaultbom',
            operator: <http://query.Operator.IS|query.Operator.IS>,
            values: true
        });
        var conditionTwo = queryMain.createCondition({
            formula: 'CASE WHEN NVL({effectivestartdate},CURRENT_DATE) <= CURRENT_DATE AND NVL({effectiveenddate},CURRENT_DATE) >= CURRENT_DATE THEN 1 ELSE 0 END',
            operator: query.Operator.EQUAL,
            values: 1,
            type: query.ReturnType.INTEGER
        });

        queryMain.condition = queryMain.and(conditionOne, conditionTwo);
        var tempColumns = [
            {
                fieldId: 'billofmaterials.bomassemblylocation.assembly'
            },
            {
                fieldId: 'component.item'
            },
            {
                fieldId: 'component.bomquantity'
            },
            {
                fieldId: 'component.item^item.itemtype'
            }
        ]
        queryMain.columns = [];
        for (var i in tempColumns) {
            queryMain.columns.push(queryMain.createColumn(tempColumns[i]));
        }
        return queryMain;
I start at the bomrevision record and join upwards
@makon Additionally, you can give each object in that 
tempColumns
array an 
alias
property, and it will make reading the results easier. You can use 
queryMain.run().asMappedResults()
and itll turn your resultSet into a readable object with properties of whatever aliases you gave each column
m
Okay, thanks. I'll give this approach a try. Can I ask what you use this for? I'm trying to build a report to show product cost based on the most recent purchase of sub items at a certain location. Then if the sub item price is too old, get someone to get an update price.
s
We use the bom information for a couple of reasons, some for display purposes (suitelet on a tab on a record) and for some calculation of bottlenecks, true lead time, etc. Not for pure reporting at all.
2 Views
Open in Slack
PreviousNext
Powered by