I'm trying to create a script that will notice when a user is inputting a UPC that is already attached to another item. Currently it is working so that a button is created when the item record is loaded, and upon clicking the button will alert the user that the UPC already exists.
What I'd like to do is change this so that it alerts the user either upon entering the UPC that there's a duplicate, or alerts the user upon clicking "Save" that there's a duplicate.
I do think that the way this is built currently doesn't support entering a UPC into a new item, since it is looking at an already existing UPC on the current record, so I'm also not really sure how to get around that.
Any thoughts?
User Event Script:
* @exports ess/add-button/UserEventScript
* @NApiVersion 2.x
* @NScriptType UserEventScript
* @NModuleScope SameAccount
*/
define(['N/render'], function(render) {
var exports = {};
/**
* <code>beforeLoad</code> event handler
*
* @gov 0
*
* @param context
* {Object}
*
* @param context.newRecord
* {record} The new record being loaded
*
* @param context.Type
* {UserEventType} The action type that triggered this event
*
* @param context.form
* {form} The current UI form
*
* @return {void}
*
* @static
* @function beforeLoad
*
*/
function beforeLoad(context) {
context.form.addButton({
id : 'custpage_print_picking_ticket',
label : 'Test UPC',
functionName : 'onButtonClick'
});
context.form.clientScriptModulePath = "SuiteScripts/duplicateUPC/duplicateUPC_client_script.js";
}
exports.beforeLoad = beforeLoad;
return exports;
});
Client Script
* @NApiVersion 2.x
* @NScriptType ClientScript
* @appliedtorecord Item
*/
define([
'N/url', 'N/currentRecord', 'N/record', 'N/search', 'N/query'
],
function(url, currentRecord, record, search, query) {
var exports = {};
/**
* <code>pageInit</code> event handler
*
* @gov XXX
*
* @param context
* {Object}
* @param context.mode
* {String} The access mode of the current record. will be one of
* <ul>
* <li>copy</li>
* <li>create</li>
* <li>edit</li>
* </ul>
*
* @return {void}
*
* @static
* @function pageInit
* @function onButtonClick
*/
function pageInit(context) {
}
function onButtonClick() {
log.debug("Test_CS");
var objRecord = currentRecord.get();
var currentID = objRecord.getValue({ fieldId: 'id' });
var currentItem = record.load({
type: record.Type.INVENTORY_ITEM,
id: currentID});
var itemid = currentItem.getText({ fieldId: 'id' });
log.debug(itemid);
var upc = currentItem.getValue({ fieldId: 'upccode' });
log.debug(upc);
var displayName = currentItem.getText({ fieldId: 'displayname' });
log.debug(displayName);
log.debug("Test_CS2");
function upcSearch(upcSQL) {
var sqlQuery = query.runSuiteQL
({
query: 'SELECT item.upccode FROM item WHERE item.id != ' + itemid + 'and item.upccode = \'' + upc + '\''
});
var resultSet = sqlQuery;
var results = resultSet.results;
var upcSQL = results[0].values;
log.debug(upcSQL);
return upcSQL;
};
function itemIDSearch(itemSQL) {
var sqlQuery = query.runSuiteQL
({
query: 'SELECT item.id FROM item WHERE item.id != ' + itemid + 'and item.upccode = \'' + upc + '\''
});
var resultSet = sqlQuery;
var results = resultSet.results;
var itemSQL = results[0].values;
log.debug(itemSQL);
return itemSQL;
};
function itemNameSearch(itemNameSQL) {
var sqlQuery = query.runSuiteQL
({
query: 'SELECT item.itemid FROM item WHERE item.id != ' + itemid + 'and item.upccode = \'' + upc + '\''
});
var resultSet = sqlQuery;
var results = resultSet.results;
var itemNameSQL = results[0].values;
log.debug(itemNameSQL);
return itemNameSQL;
};
var upcResults = upcSearch();
var idResults = itemIDSearch();
var itemNameResults = itemNameSearch();
if (idResults != itemid) {
window.alert("Error: UPC " + upcResults + " is already in use by item " + itemNameResults + " (Internal ID: " + idResults + ")");
}
log.debug(upcResults);
log.debug(idResults);
log.debug(itemNameResults);
return upcResults;
}
exports.onButtonClick = onButtonClick;
exports.pageInit = pageInit;
return exports;
})
Updated Client Script (No UE Script)
/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @appliedtorecord Item
*/
define([
'N/url', 'N/currentRecord', 'N/record', 'N/search', 'N/query'
],
function (url, currentRecord, record, search, query) {
var exports = {};
/**
* <code>pageInit</code> event handler
*
* @gov XXX
*
* @param context
* {Object}
* @param context.mode
* {String} The access mode of the current record. will be one of
* <ul>
* <li>copy</li>
* <li>create</li>
* <li>edit</li>
* </ul>
*
* @return {void}
*
* @static
* @function pageInit
* @function onButtonClick
*/
function pageInit(context) {
log.debug("Test_CS");
var objRecord = context.currentRecord;
log.debug(objRecord);
log.debug(objRecord.isNew);
log.debug("Test_CS2");
}
function fieldChanged(context) {
var currentRecord = context.currentRecord;
if (context.fieldId == 'upccode') {
var upc = currentRecord.getValue({
fieldId: 'upccode'
});
function upcSearch(upcSQL) {
var sqlQuery = query.runSuiteQL
({
query: 'SELECT item.upccode FROM item WHERE item.upccode = \'' + upc + '\' union all select null from dual'
});
var resultSet = sqlQuery;
var results = resultSet.results;
var upcSQL = results[0].values;
return upcSQL;
};
function itemIDSearch(itemSQL) {
var sqlQuery = query.runSuiteQL
({
query: 'SELECT item.id FROM item WHERE item.upccode = \'' + upc + '\' union all select null from dual'
});
var resultSet = sqlQuery;
var results = resultSet.results;
var itemSQL = results[0].values;
log.debug(itemSQL);
return itemSQL;
};
function itemNameSearch(itemNameSQL) {
var sqlQuery = query.runSuiteQL
({
query: 'SELECT item.itemid FROM item WHERE item.upccode = \'' + upc + '\' union all select null from dual'
});
var resultSet = sqlQuery;
var results = resultSet.results;
var itemNameSQL = results[0].values;
return itemNameSQL;
};
var upcResults = upcSearch();
var idResults = itemIDSearch();
var itemNameResults = itemNameSearch();
var objRecord = context.currentRecord;
if (objRecord.isNew) {
if (upc == upcResults) {
window.alert("Error: UPC " + upcResults + " is already in use by item " + itemNameResults + " (Internal ID: " + idResults + ")");
}
}
else {
var currentID = objRecord.getValue({ fieldId: 'id' });
var currentItem = record.load({
type: record.Type.INVENTORY_ITEM,
id: currentID
});
var itemid = currentItem.getText({ fieldId: 'id' });
function itemIDSearch2(itemSQL) {
var sqlQuery = query.runSuiteQL
({
query: 'SELECT item.id FROM item WHERE item.upccode = \'' + upc + '\' and item.id != \'' + itemid + '\' union all select null from dual'
});
var resultSet = sqlQuery;
var results = resultSet.results;
var itemSQL = results[0].values;
log.debug(itemSQL);
return itemSQL;
};
function itemNameSearch2(itemNameSQL) {
var sqlQuery = query.runSuiteQL
({
query: 'SELECT item.itemid FROM item WHERE item.upccode = \'' + upc + '\' and item.id != \'' + itemid + '\' union all select null from dual'
});
var resultSet = sqlQuery;
var results = resultSet.results;
var itemNameSQL = results[0].values;
return itemNameSQL;
};
var idResults2 = itemIDSearch2();
var itemNameResults2 = itemNameSearch2();
if (idResults2 != itemid && upc == upcResults) {
window.alert("Error: UPC " + upcResults + " is already in use by item " + itemNameResults2 + " (Internal ID: " + idResults2 + ")");
}
}
}
}
return {
pageInit: pageInit,
fieldChanged: fieldChanged
}
})
The approach I would suggest is using a client script fieldChanged() entry point function, targeting the UPC field. Anytime that field is changed run a search for item(s) with that UPC and if any results are returned display a message to the user.