Develop a user event script to display the line items in the Project Record by pulling data from the linked Sales Order.
An Item subtab has been created in the project record to display the linked sales order item details.
The sales order details are:
- Item
- Rate
- Amount
- Linked sales order
/**
*@NApiVersion 2.1
*@NScriptType UserEventScript
*/
/*****************************************************************************************************************************************
* RSP DESIGN CONSULTANTS (INDIA) PRIVATE LIMITED-IN-NS
*
* ${RSPDC-1557} : ${Add line items display in Project Record - Draft Proforma Invoices }
*
*******************************************************************************************************************************************
*
* Author: Jobin & Jismi
*
* Date Created : 23-Oct-2024
*
* Description : Ths is a user event script, works before load context which is build for displaying formated itemlist.
*
* REVISION HISTORY
*
* @version 1.0 RSPDC-1557 : 23-Oct-2024 : Created the initial build by JJ0348
*********************************************************************************************************************************************/
define(['N/ui/serverWidget', 'N/search', 'N/record', 'N/format'],
function (serverWidget, search, record, format) {
/**
* Function to get the transaction details from sales orders based on the project ID.
* @param {number} projectId - The internal ID of the project.
* @returns {Array} - List of sales order line details.
*/
function fetchSalesOrderlineDetails(projectId) {
try {
let results = [];
let salesorderSearchObj = search.create({
type: "salesorder",
settings: [{ "name": "consolidationtype", "value": "ACCTTYPE" }],
filters: [
["type", "anyof", "SalesOrd"],
"AND",
["mainline", "is", "F"],
"AND",
["taxline", "is", "F"],
"AND",
["cogs", "is", "F"],
"AND",
["custbody_jj_project", "anyof", projectId]
],
columns: [
search.createColumn({ name: "item", label: "Item" }),
search.createColumn({ name: "amount", label: "Amount" }),
search.createColumn({ name: "rate", label: "Rate" }),
search.createColumn({
name: "internalid",
join: "job",
label: "Internal ID"
}),
search.createColumn({ name: "internalid", label: "Internal ID" })
]
});
let searchResultCount = salesorderSearchObj.runPaged().count;
log.debug("salesorderSearchObj result count", searchResultCount);
salesorderSearchObj.run().each(function (result) {
let item = result.getText({ name: "item" });
let amount = result.getValue({ name: "amount" });
let rate = result.getValue({ name: "rate" });
let projectID = result.getValue({ name: "internalid", join: "job" });
let soID = result.getValue({ name: "internalid" });
// Get the absolute value of the amount
amount = Math.abs(parseFloat(amount));
rate = Math.abs(parseFloat(rate));
results.push({
item: item,
amount: amount,
rate: rate,
projectID: projectID,
soID: soID
});
return true;
});
log.debug("results", results);
return results;
} catch (err) {
log.error("Error in fetchSalesOrderlineDetails", err);
return [];
}
}
/**
* @description Function to create sublist columns.
* @param {Object} columnObjValue - Field id and field object values to be created.
* @param {Object} sublistObj - Sublist object created.
* @returns {Object} - Field creation object.
*/
function createSublist(columnObjValue, sublistObj) {
try {
let fieldCreationObj = {};
for (let column in columnObjValue) {
let field = sublistObj.addField({
id: columnObjValue[column].id,
label: columnObjValue[column].label,
type: columnObjValue[column].type,
source: columnObjValue[column].source // Adding source if needed
});
// Disable the 'Sales Order ID' field by updating its display type
if (columnObjValue[column].id === 'custpage_jj_so_id') {
field.updateDisplayType({
displayType: serverWidget.FieldDisplayType.DISABLED
});
}
fieldCreationObj[columnObjValue[column].id] = field;
}
return fieldCreationObj;
} catch (err) {
log.error("Error in createSublist", err);
return {};
}
}
/**
* @description Sublist columns and body fields required for the record.
* @returns {Object} - Object containing sublist column definitions.
*/
function sublistColumn() {
try {
return {
columnFields: {
itemLine: { id: 'custpage_jj_item_line', type: serverWidget.FieldType.TEXT, label: 'Item' },
rate: { id: 'custpage_jj_rate', type: serverWidget.FieldType.TEXT, label: 'Rate' },
amount: { id: 'custpage_jj_amount', type: serverWidget.FieldType.TEXT, label: 'Amount' },
salesOrderID: { id: 'custpage_jj_so_id', type: serverWidget.FieldType.TEXT, label: 'Sales Order', source: 'transaction' }
}
};
} catch (err) {
log.error("Error in sublistColumn", err);
return {};
}
}
// Helper function to create the URL
function createLink(recordType, recordId) {
if (recordId) {
return `/app/accounting/transactions/${recordType}.nl?id=${recordId}`;
}
return "";
}
/**
* Function executed before the record is loaded.
* @param {Object} context - The context object.
* @param {Record} context.newRecord - The new record object.
* @param {string} context.type - The type of event triggering the script.
* @param {Form} context.form - The form object.
*/
function beforeLoad(context) {
try {
if (context.type == context.UserEventType.VIEW || context.type == context.UserEventType.EDIT || context.type == context.UserEventType.COPY) {
let form = context.form;
log.debug("form", form);
let projectRecId = context.newRecord.id;
log.debug("projectRecId", projectRecId);
form.addTab({
id: 'custpage_jj_so_print_tab',
label: 'Project360'
});
let itemSublist = form.addSublist({
id: 'custpage_jj_sales_order_item_sublist',
type: serverWidget.SublistType.LIST,
label: 'Sales Order Item Lines',
tab: 'custpage_jj_so_print_tab'
});
let sublistFieldFetch = sublistColumn().columnFields;
log.debug("sublistFieldFetch", sublistFieldFetch);
let createdColumnFieldObj = createSublist(sublistFieldFetch, itemSublist);
log.debug("createdColumnFieldObj", createdColumnFieldObj);
let getItemResult = fetchSalesOrderlineDetails(projectRecId);
log.debug("getItemResult", getItemResult);
getItemResult.forEach(function (result, index) {
itemSublist.setSublistValue({ id: 'custpage_jj_item_line', line: index, value: result.item || ' ' });
itemSublist.setSublistValue({ id: 'custpage_jj_rate', line: index, value: result.rate || ' ' });
itemSublist.setSublistValue({ id: 'custpage_jj_amount', line: index, value: result.amount || ' ' });
// itemSublist.setSublistValue({ id: 'custpage_jj_so_id', line: index, value: result.soID || ' ' });
const salesOrderUrl = createLink("salesord", result.soID); // Use "salesord" for sales order record type
searchObject = search.lookupFields({
type: search.Type.SALES_ORDER,
id: result.soID,
columns: ['tranid']
});
log.debug('searchObject',searchObject)
if (salesOrderUrl) {
itemSublist.setSublistValue({
id: 'custpage_jj_so_id',
line: index,
value: `<a href="${salesOrderUrl}" target="_blank">${searchObject.tranid}</a>` || ' '
});
}
});
addTransactionSublist(form, 'invoice', 'custpage_invoice_list', 'Invoice Item Lines', 'custpage_jj_so_print_tab', projectRecId);
addTransactionSublist(form, 'creditmemo', 'custpage_creditmemo_list', 'Credit Memo Item Lines', 'custpage_jj_so_print_tab', projectRecId);
addTransactionSublist(form, 'purchaseorder', 'custpage_purchase_list', 'Purchase Order Item Lines', 'custpage_jj_so_print_tab', projectRecId);
addProformaInvoiceSublist(form, 'custpage_proforma_invoice_list', 'Proforma Invoices Item Lines', 'custpage_jj_so_print_tab', projectRecId);
}
} catch (err) {
log.error('Error in beforeLoad', err.message);
}
}
/**
* Function to add a transaction sublist to the form.
* @param {Object} form - The current form object.
* @param {string} transactionType - The type of transaction (invoice, creditmemo, purchaseorder).
* @param {string} sublistId - The unique ID for the sublist.
* @param {string} label - The label of the sublist.
* @param {string} tabId - The ID of the tab to which the sublist is added.
* @param {number} projectId - The ID of the project record.
*/
function addTransactionSublist(form, transactionType, sublistId, label, tabId, projectId) {
try {
log.debug('projectId', projectId);
let sublist = form.addSublist({
id: sublistId,
type: serverWidget.SublistType.LIST,
label: label,
tab: tabId
});
// Define the fields to be added to the sublist
let fields = [];
if(transactionType=='creditmemo'){
fields.push(
{ id: '_jj_transaction_id', label: 'Transaction ID', type: serverWidget.FieldType.TEXT },
{ id: '_jj_legal_number', label: 'Legal Number', type: serverWidget.FieldType.TEXT },
{ id: '_jj_item', label: 'Item', type: serverWidget.FieldType.TEXT },
{ id: '_jj_quantity', label: 'Quantity', type: serverWidget.FieldType.FLOAT },
{ id: '_jj_project_task', label: 'Project Task', type: serverWidget.FieldType.TEXT },
{ id: '_jj_billing_schedule', label: 'Billing Schedule', type: serverWidget.FieldType.TEXT },
{ id: '_jj_payment_schedule', label: 'Payment Schedule', type: serverWidget.FieldType.TEXT },
{ id: '_jj_creditmemo_type', label: 'Credit Memo Type', type: serverWidget.FieldType.TEXT},
{ id: '_jj_amount', label: 'Amount', type: serverWidget.FieldType.CURRENCY }
)
}else if(transactionType=='purchaseorder'){
fields.push(
{ id: '_jj_transaction_id', label: 'Transaction ID', type: serverWidget.FieldType.TEXT },
{ id: '_jj_item', label: 'Item', type: serverWidget.FieldType.TEXT },
{ id: '_jj_quantity', label: 'Quantity', type: serverWidget.FieldType.FLOAT },
{ id: '_jj_project_task', label: 'Project Task', type: serverWidget.FieldType.TEXT },
{ id: '_jj_billing_schedule', label: 'Billing Schedule', type: serverWidget.FieldType.TEXT },
{ id: '_jj_payment_schedule', label: 'Payment Schedule', type: serverWidget.FieldType.TEXT },
{ id: '_jj_amount', label: 'Amount', type: serverWidget.FieldType.CURRENCY }
)
}else{
fields.push(
{ id: '_jj_transaction_id', label: 'Transaction ID', type: serverWidget.FieldType.TEXT },
{ id: '_jj_legal_number', label: 'Legal Number', type: serverWidget.FieldType.TEXT },
{ id: '_jj_item', label: 'Item', type: serverWidget.FieldType.TEXT },
{ id: '_jj_quantity', label: 'Quantity', type: serverWidget.FieldType.FLOAT },
{ id: '_jj_project_task', label: 'Project Task', type: serverWidget.FieldType.TEXT },
{ id: '_jj_billing_schedule', label: 'Billing Schedule', type: serverWidget.FieldType.TEXT },
{ id: '_jj_payment_schedule', label: 'Payment Schedule', type: serverWidget.FieldType.TEXT },
{ id: '_jj_amount', label: 'Amount', type: serverWidget.FieldType.CURRENCY }
)
}
// Add fields to the sublist dynamically
fields.forEach(function (field) {
sublist.addField({
id: field.id,
type: field.type,
label: field.label
});
});
let lineItems = getTransactions(projectId, transactionType);
log.debug('lineItems', lineItems);
if (lineItems.length !== 0) {
let sumAmount = 0;
let line;
lineItems.forEach(function (lineItem, index) {
fields.forEach(function (field) {
let value = lineItem[field.id];
log.debug('value', value);
if (field.id === '_jj_amount' && value) {
sumAmount += parseFloat(value);
}
// If the field is '_jj_transaction_id', make it a hyperlink
if (field.id === '_jj_transaction_id' && lineItem._jj_transaction_id) {
var transactionUrl;
if(transactionType ==='invoice'){
transactionUrl = createLink("custinvc", lineItem._jj_transaction_id_internalid);
log.debug("transactionUrl1", transactionUrl)
}
if(transactionType ==='creditmemo'){
transactionUrl = createLink("custcred", lineItem._jj_transaction_id_internalid);
log.debug("transactionUrl2", transactionUrl)
}
if(transactionType ==='purchaseorder'){
transactionUrl = createLink("purchord", lineItem._jj_transaction_id_internalid);
log.debug("transactionUrl3", transactionUrl)
}
value = transactionUrl
? `<a href="${transactionUrl}" target="_blank">${lineItem._jj_transaction_id}</a>`
: lineItem._jj_transaction_id;
}
if (value !== null && value !== undefined && value !== "") {
sublist.setSublistValue({
id: field.id,
line: index,
value: value || ""
});
}
});
line = parseInt(index);
});
line += 1;
log.debug('line', line);
log.debug('sumAmount', sumAmount);
fields.forEach(function (field) {
if (field.id === '_jj_transaction_id') {
sublist.setSublistValue({
id: field.id,
line: line,
value: `<b>Total Amount<b/>`
});
} else if (field.id === '_jj_amount') {
sublist.setSublistValue({
id: field.id,
line: line,
value: sumAmount
});
}
});
}
} catch (error) {
log.error({
title: `Error adding sublist for ${transactionType}`,
details: error.toString()
});
}
}
/**
* Function to retrieve transactions for a given project ID and transaction type.
* @param {number} projectId - The ID of the project.
* @param {string} transactionType - The transaction type (invoice, creditmemo, etc.).
* @returns {Array} - List of transaction details.
*/
function getTransactions(projectId, transactionType) {
try {
let results = [];
let invoiceSearchObj = search.create({
type: transactionType,
settings: [{ "name": "consolidationtype", "value": "ACCTTYPE" }],
filters: [
["custbody_jj_project", "anyof", projectId],
"AND",
["mainline", "is", "F"],
"AND",
["taxline", "is", "F"],
"AND",
["cogs", "is", "F"]
],
columns: [
search.createColumn({ name: "internalid", label: "Internal ID" }), // Internal ID for hyperlink
search.createColumn({ name: "type", label: "Type" }),
search.createColumn({ name: "transactionname", label: "Transaction Name" }),
search.createColumn({name: "custbody_jj_legal_number", label: "Legal Number"}),
search.createColumn({ name: "item", label: "Item" }),
search.createColumn({ name: "quantity", label: "Quantity" }),
search.createColumn({ name: "amount", label: "Amount" }),
search.createColumn({ name: "custcol_jj_project_task", label: "PROJECT TASK" }),
search.createColumn({ name: "custcol_jj_payment_schedule", label: "PAYMENT SCHEDULE" }),
search.createColumn({ name: "custcol_jj_billing_schedule", label: "BILLING SCHEDULE" }),
search.createColumn({name: "custbody_jj_credit_note_type", label: "Credit Note Type"})
]
});
let searchResultCount = invoiceSearchObj.runPaged().count;
log.debug("invoiceSearchObj result count", searchResultCount);
let count =0
invoiceSearchObj.run().each(function (result) {
results.push({
_jj_type: result.getValue({ name: "type", label: "Type" }),
_jj_transaction_id_internalid: result.getValue({ name: "internalid", label: "Internal ID" }), // Internal
_jj_transaction_id: result.getValue({ name: "transactionname", label: "Transaction Name" }) || "",
_jj_legal_number: result.getValue({name: "custbody_jj_legal_number", label: "Legal Number"}),
_jj_item: result.getText({ name: "item", label: "Item" }) || "",
_jj_quantity: Math.abs(result.getValue({ name: "quantity", label: "Quantity" })) || "",
_jj_amount: Math.abs(result.getValue({ name: "amount", label: "Amount" })) || "",
_jj_project_task: result.getText({ name: "custcol_jj_project_task", label: "PROJECT TASK" }) || "",
_jj_billing_schedule: result.getText({ name: "custcol_jj_payment_schedule", label: "PAYMENT SCHEDULE" }) || "",
_jj_payment_schedule: result.getText({ name: "custcol_jj_billing_schedule", label: "BILLING SCHEDULE" }) || ""
});
if(transactionType==='creditmemo'){
results[count]._jj_creditmemo_type =result.getText({name: "custbody_jj_credit_note_type", label: "Credit Note Type"}|| "")
}else if(transactionType==='purchaseorder'){
delete results._jj_legal_number;
}
count=count+1
return true;
});
return results;
} catch (error) {
log.error('Error in getTransactions', error);
return [];
}
}
/**
* Function to add the Proforma Invoice sublist to the form.
* @param {Object} form - The current form object.
* @param {string} sublistId - The unique ID for the sublist.
* @param {string} label - The label of the sublist.
* @param {string} tabId - The ID of the tab to which the sublist is added.
* @param {number} projectId - The ID of the project record.
*/
function addProformaInvoiceSublist(form, sublistId, label, tabId, projectId) {
try {
let sublist = form.addSublist({
id: sublistId,
type: serverWidget.SublistType.LIST,
label: label,
tab: tabId
});
let fields = [
{ id: '_jj_proforma_id', label: 'Transaction ID', type: serverWidget.FieldType.TEXT },
{ id: '_jj_proforma_date_created', label: 'Date Created', type: serverWidget.FieldType.DATE },
{ id: '_jj_proforma_status', label: 'Invoice Status', type: serverWidget.FieldType.TEXT },
{ id: '_jj_proforma_items', label: 'Items', type: serverWidget.FieldType.TEXT },
{ id: '_jj_proforma_quantity', label: 'Quantity', type: serverWidget.FieldType.FLOAT },
{ id: '_jj_proforma_projtask', label: 'Project Task', type: serverWidget.FieldType.TEXT },
{ id: '_jj_proforma_bill_schedule', label: 'Billing Schedule', type: serverWidget.FieldType.TEXT },
{ id: '_jj_proforma_pay_schedule', label: 'Payment Schedule', type: serverWidget.FieldType.TEXT },
{ id: '_jj_proforma_amount', label: 'Amount', type: serverWidget.FieldType.CURRENCY }
];
fields.forEach(function (field) {
sublist.addField({
id: field.id,
type: field.type,
label: field.label
});
});
let proformaInvoices = fetchProformaInvoices(projectId);
let totalAmount = 0;
proformaInvoices.forEach(function (result, index) {
sublist.setSublistValue({ id: '_jj_proforma_date_created', line: index, value: result.created || ' ' });
// sublist.setSublistValue({ id: '_jj_proforma_id', line: index, value: result.name || ' ' });
// recTypeObject = search.lookupFields({
// type:'customrecord_jj_proforma_invoices',
// id: result.id,
// columns: ['rectype']
// });
// log.debug('recTypeObject',recTypeObject)
const proformaUrl = createProformaLink("custrecordentry", result.id);
// Helper function to create the URL
function createProformaLink(recordType, recordId) {
if (recordId) {
return `/app/common/custom/${recordType}.nl?rectype=1151&id=${recordId}`;
}
return "";
}
if (proformaUrl) {
sublist.setSublistValue({
id: '_jj_proforma_id',
line: index,
value: `<a href="${proformaUrl}" target="_blank">${result.name}</a>` || ' '
});
}
sublist.setSublistValue({ id: '_jj_proforma_status', line: index, value: result.status || ' ' });
sublist.setSublistValue({ id: '_jj_proforma_items', line: index, value: result.items || ' ' });
sublist.setSublistValue({ id: '_jj_proforma_quantity', line: index, value: result.quantity || ' ' });
sublist.setSublistValue({ id: '_jj_proforma_projtask', line: index, value: result.task || ' ' });
sublist.setSublistValue({ id: '_jj_proforma_bill_schedule', line: index, value: result.bill || ' ' });
sublist.setSublistValue({ id: '_jj_proforma_pay_schedule', line: index, value: result.pay || ' ' });
sublist.setSublistValue({ id: '_jj_proforma_amount', line: index, value: result.amount || ' ' });
totalAmount += parseFloat(result.amount || 0);
});
if (proformaInvoices.length > 0) {
let totalLineIndex = proformaInvoices.length;
sublist.setSublistValue({ id: '_jj_proforma_id', line: totalLineIndex, value: '<b>Total Amount<b/>' });
sublist.setSublistValue({ id: '_jj_proforma_amount', line: totalLineIndex, value: totalAmount.toFixed(2) });
}
} catch (error) {
log.error({
title: 'Error adding Proforma Invoice sublist',
details: error.toString()
});
}
}
/**
* Function to fetch Proforma Invoices for a specific project.
* @param {number} projectId - The ID of the project.
* @returns {Array} - List of proforma invoice details.
*/
function fetchProformaInvoices(projectId) {
let results = [];
try {
let proformaSearchObj = search.create({
type: "customrecord_jj_proforma_invoices",
filters: [
["custrecord_jj_project", "anyof", projectId]
],
columns: [
search.createColumn({ name: "created", label: "Date Created" }),
search.createColumn({ name: "name", label: "Name" }),
search.createColumn({ name: "internalid", label: "ID" }),
search.createColumn({ name: "custrecord_jj_invoice_approval_status", label: "Custom Invoice Status" }),
search.createColumn({
name: "custrecord_jj_line_item_amount",
join: "CUSTRECORD_JJ_PROFORMA_INVOICES_ITEMS",
label: "Amount"
}),
search.createColumn({
name: "custrecord_jj_line_item_items",
join: "CUSTRECORD_JJ_PROFORMA_INVOICES_ITEMS",
label: "Items"
}),
search.createColumn({
name: "custrecord_jj_line_item_quantity",
join: "CUSTRECORD_JJ_PROFORMA_INVOICES_ITEMS",
label: "Quantity"
}),
search.createColumn({
name: "custrecord_jj_billing_schedule",
join: "CUSTRECORD_JJ_PROFORMA_INVOICES_ITEMS",
label: "Billing Schedule"
}),
search.createColumn({
name: "custrecord_jj_payment_schedule",
join: "CUSTRECORD_JJ_PROFORMA_INVOICES_ITEMS",
label: "Payment Schedule"
}),
search.createColumn({
name: "custrecord_jj_project_task",
join: "CUSTRECORD_JJ_PROFORMA_INVOICES_ITEMS",
label: "Project Task"
})
]
});
proformaSearchObj.run().each(function (result) {
let createdDate = result.getValue({ name: "created" });
let formattedDate = format.format({
value: format.parse({
value: createdDate,
type: format.Type.DATETIME
}),
type: format.Type.DATE
});
results.push({
created: formattedDate,
id: result.getValue({ name: "internalid" }),
name: result.getValue({ name: "name" }),
status: result.getText({ name: "custrecord_jj_invoice_approval_status" }),
items: result.getText({
name: "custrecord_jj_line_item_items",
join: "CUSTRECORD_JJ_PROFORMA_INVOICES_ITEMS"
}),
quantity: result.getValue({
name: "custrecord_jj_line_item_quantity",
join: "CUSTRECORD_JJ_PROFORMA_INVOICES_ITEMS"
}),
task: result.getText({
name: "custrecord_jj_project_task",
join: "CUSTRECORD_JJ_PROFORMA_INVOICES_ITEMS"
}),
bill: result.getText({
name: "custrecord_jj_billing_schedule",
join: "CUSTRECORD_JJ_PROFORMA_INVOICES_ITEMS"
}),
pay: result.getText({
name: "custrecord_jj_payment_schedule",
join: "CUSTRECORD_JJ_PROFORMA_INVOICES_ITEMS"
}),
amount: result.getValue({
name: "custrecord_jj_line_item_amount",
join: "CUSTRECORD_JJ_PROFORMA_INVOICES_ITEMS"
})
});
return true;
});
} catch (err) {
log.error("Error in fetchProformaInvoices", err);
return [];
}
return results;
}
return {
beforeLoad: beforeLoad
};
});