Overview:
This article provides a set of SuiteScript helper functions that:
- Build a global version -> date map from system notes (for an item, any price level, quantity = 0), where:
version 1-> oldest change dateversion 2-> next change date- …
version -1-> latest change date (current)
- Pull raw price history from
InvtItemPriceHistoryusing SuiteQL, filtered by:
- Item Internal ID
- In-scope Price Levels
- Quantity = 0
- Active Price Levels only
- Merge and clean the data to keep only rows where the price actually changed, with the effective date derived from the global version map.
Finally, you get a consolidated, descending-by-date list of price changes across price levels.
Prerequisites:
- SuiteScript 2.x enabled.
- Modules:
N/searchN/queryN/log(optional for debugging)- Permissions:
- Access to
ItemsandSystem Notes - Access to the
InvtItemPriceHistorydataset via SuiteAnalytics/Query - Ability to run SuiteQL (SuiteAnalytics Workbook / Query permissions)
- Known constraints:
- This logic assumes Quantity = 0 for price history (list/base pricing). Adjust if you track quantity-tiered pricing.
- It uses global versioning per item;
-1denotes current price.
High-Level Flow:
- Get version-date map (global for the item, any price level, qty=0) via
N/searchon system notes. - Query price history via SuiteQL from
InvtItemPriceHistorywith selected price levels. - Group by price level, sort by version, and keep entries only when the price value changes.
- Attach dates from the version map; sort final output by date DESC.
Code:
Note: Embed the following in a module (e.g., PriceChangeService.js) and expose getPriceChangesDetails(itemId) for use in your scripts (Map/Reduce, Suitelet, Scheduled Script, etc.)
/**
* Price Change History Service
* SuiteScript 2.x
*/
define([‘N/search’, ‘N/query’, ‘N/log’], function(search, query, log) {
return {
/**
* Get ALL price change dates (for the item, qty 0, any price level)
* and build a global version -> date map:
* version 1 -> oldest date
* version 2 -> next date
* …
* version -1 -> latest date
*/
getGlobalVersionDateMap(itemId) {
const itemSearchObj = search.create({
type: ‘item’,
filters: [
[‘systemnotes.field’, ‘anyof’, ‘INVTITEM.PRICELIST’],
‘AND’,
[‘systemnotes.type’, ‘is’, ‘F’], // ‘F’ = Field change
‘AND’,
[‘internalid’, ‘anyof’, itemId],
‘AND’,
[‘pricing.minimumquantity’, ‘equalto’, ‘0’]
// NOTE: no pricing.pricelevel filter here – global for the item
],
columns: [
search.createColumn({
name: ‘date’,
join: ‘systemNotes’,
sort: search.Sort.ASC
})
]
});
const dates = [];
itemSearchObj.run().each(result => {
const date = result.getValue({ name: ‘date’, join: ‘systemNotes’ });
dates.push(date);
return true;
});
// Build version -> date map using version rules
const versionDateMap = {};
// versions 1..N
for (let i = 0; i < dates.length; i++) {
const version = (i + 1).toString(); // 1-based
versionDateMap[version] = dates[i];
}
// current version -1 -> last date
if (dates.length > 0) {
versionDateMap[‘-1’] = dates[dates.length – 1];
}
return versionDateMap;
},
/**
* Fetch price history from InvtItemPriceHistory (includes version -1)
* @param {number|string} itemId – Item internal ID
* @param {number[]} priceLevelIds – Array of price level internal IDs
*/
getPriceHistory(itemId, priceLevelIds) {
const inClause = priceLevelIds.join(‘,’);
const suiteQL = `
SELECT
ph.version,
ph.pricetype,
BUILTIN.DF(ph.pricetype) AS pricelevel,
ph.price
FROM
item AS im
JOIN InvtItemPriceHistory AS ph ON ph.item = im.id
JOIN priceLevel AS pl ON ph.pricetype = pl.id
WHERE
im.id = ?
AND pl.isInactive = ‘F’
AND ph.quantity = 0
AND ph.pricetype IN (${inClause})
ORDER BY
ph.pricetype, ph.version ASC
`;
return query.runSuiteQL({ query: suiteQL, params: [itemId] }).asMappedResults();
},
/**
* Helper to fetch price level dictionary: { [id]: ‘Label’ }
* If you already have this elsewhere, replace with your own logic.
*/
getPricelevel() {
const levels = {};
const s = search.create({
type: ‘priceLevel’,
filters: [[‘isinactive’, ‘is’, ‘F’]],
columns: [‘internalid’, ‘name’]
});
s.run().each(r => {
levels[r.getValue(‘internalid’)] = r.getValue(‘name’);
return true;
});
return levels;
},
/**
* Merge price changes and filter only where price changed,
* using the global versionDateMap.
*/
mergeCleanChanges(priceHistory, versionDateMap) {
const merged = [];
const PRICELEVELS = this.getPricelevel();
// Group price history by price level
const groupedHistory = {};
priceHistory.forEach(ph => {
const levelId = ph.pricetype.toString();
if (!groupedHistory[levelId]) groupedHistory[levelId] = [];
groupedHistory[levelId].push(ph);
});
Object.keys(groupedHistory).forEach(levelId => {
const priceLevelLabel = PRICELEVELS[levelId] || `Price Level ${levelId}`;
// Sort price history by version (treat -1 as latest)
const sorted = groupedHistory[levelId].slice().sort((a, b) => {
const vA = parseInt(a.version, 10);
const vB = parseInt(b.version, 10);
const nA = vA === -1 ? 9999 : vA;
const nB = vB === -1 ? 9999 : vB;
return nA – nB;
});
if (sorted.length === 0) return;
// Initial price: 0 -> first version
const first = sorted[0];
merged.push({
priceLevel: priceLevelLabel,
oldPrice: 0,
newPrice: parseFloat(first.price),
date: versionDateMap[first.version] || ‘Unknown’
});
// Compare version[i] -> version[i+1]
for (let i = 0; i < sorted.length – 1; i++) {
const oldRec = sorted[i];
const newRec = sorted[i + 1];
const oldPrice = parseFloat(oldRec.price);
const newPrice = parseFloat(newRec.price);
if (oldPrice !== newPrice) {
merged.push({
priceLevel: priceLevelLabel,
oldPrice,
newPrice,
date: versionDateMap[newRec.version] || ‘Unknown’
});
}
}
});
// Sort final output by date DESC
merged.sort((a, b) => new Date(b.date) – new Date(a.date));
return merged;
},
/**
* Public method: Get consolidated price change history
* @returns Array<{priceLevel, oldPrice, newPrice, date}>
*/
getPriceChangesDetails(itemId) {
try {
const versionDateMap = this.getGlobalVersionDateMap(itemId);
log.debug(‘Global versionDateMap’, versionDateMap);
// Adjust the included price levels as needed (example: 1, 12)
const priceHistory = this.getPriceHistory(itemId, [1, 12]);
log.debug(‘priceHistory’, priceHistory);
const finalMerged = this.mergeCleanChanges(priceHistory, versionDateMap);
log.debug(‘Final Price Change History’, finalMerged);
return finalMerged;
} catch (error) {
log.error(“Error @ getPriceChangesDetails”, error);
return [];
}
}
};
});
Configuration Notes:
- Price Levels: In
getPriceChangesDetails, change[1, 12]to the internal IDs of the price levels you want to include. You can querypriceLevelrecords or usegetPricelevel()to map IDs to labels. - Quantity Filter: The logic uses
ph.quantity = 0. If your pricing changes are tiered by quantity, adapt filters and the system notes search accordingly. - Date Resolution: The version -> date map uses system notes for
INVTITEM.PRICELISTchanges, sorted ASC. Version-1is mapped to the latest date in the list.
Troubleshooting:
- Empty Results
- Verify the item has pricing changes and the correct Internal ID.
- Confirm the selected price levels are active (
pl.isInactive = 'F'). - Ensure SuiteAnalytics/Query is enabled and accessible for your role.
- Unknown Dates
- If a merged row shows
date: 'Unknown', it means the version wasn’t found inversionDateMap. Check system notes visibility and filters (especiallyminimumquantity = 0andsystemnotes.field).
- Mismatched Versions
- The algorithm assumes versions increment with each change globally per item and
-1is the current. If your account has customizations impacting version semantics, adjust the mapping logic.
- Performance/Governance
N/searchandN/queryare efficient, but large item histories may require pagination or batching.- Consider running this in a Scheduled Script or Map/Reduce if you process many items.
Security & Access:
- Limit execution to roles with appropriate Item and Analytics permissions.
- Avoid exposing raw SuiteQL externally.
- Log outputs (
log.debug) may contain pricing—sanitize before sharing.
FAQs:
Q: Can I include all price levels automatically?
A: Yes. Replace the IN (${inClause}) with a subquery or fetch all active priceLevel IDs via search and pass them to getPriceHistory.
Q: How do I handle quantity-tiered prices?
A: Remove ph.quantity = 0 filter and include logic to group/compare by quantity tiers. You may also need to adjust the system notes filters and the version-date map to include quantity-specific changes.
Q: What is the meaning of version -1?
A: It represents the current/latest version for that price level. The script maps -1 to the last change date from system notes.