I’m trying to make monthly reports of a few thousands entries.
However my script never stops, and not sure what the mistake is.
I’m pretty new to JS and not sure if it’s my loop within a loop that is the problems?
let wip, wipOrderView ,tableWIP, queryWIP
let cost, costReviewView, costTable, date, customer, garment, switchs, totalFactCost, costTime, estTotalCost, price
let currentMonth
wip='Work in Progress'
wipOrderView='Monthly_order_review'
cost = 'Costing'
costReviewView = 'Monthly_Overview'
date='Date'
customer='Customer'
garment = 'Garment'
switchs = 'Switch'
totalFactCost = 'Total fact cost'
costTime = 'Time'
estTotalCost = 'estTotalCost'
price = 'priceScripting'
tableWIP = base.getTable(wip).getView(wipOrderView)
costTable = base.getTable(cost).getView(costReviewView)
/////////////////////////////////////EVERY MONTHLY REVIEW //////////////////////////////////
let everyMonth = async () =>{
output.text(`This will take a little time as the system is compiling thousands of records...`)
let monthlyCostQuery, months, allUniqueMonths, allUniqeGarments, garmentTotals
monthlyCostQuery = await costTable.selectRecordsAsync()
months = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'November', 'December']
// Find all unique [Years and Months] and unique [Customer:Garment]
allUniqueMonths = []
allUniqeGarments=[]
monthlyCostQuery.records.forEach(el=> {
//Filter out unique [Year:Month]
let getMonth = new Date(el.getCellValue(date)).getMonth()
let getYear = new Date(el.getCellValue(date)).getFullYear()
let dateObj = {'year':getYear, 'month':months[getMonth]}
if(!allUniqueMonths.some(obj => obj['year']===getYear && obj['month']===months[getMonth])){
allUniqueMonths.push(dateObj)
}
//Filter out unique [Customer:Garment:EstimateCost:Price]
let getCustomer = el.getCellValueAsString(customer)
let getGarment = el.getCellValueAsString(garment)
let estCost = el.getCellValue(estTotalCost)[0]
let garPrice = 0
//rollup numbers - avoid if no number rolled up
if(el.getCellValue(price)){
garPrice = el.getCellValue(price)[0]
}
let garmentObj = {'customer':getCustomer, 'garment': getGarment, 'totalEst': estCost, 'price':garPrice}
if(!allUniqeGarments.some(obj=> obj['customer']===getCustomer && obj['garment']===getGarment && obj['totalEst']===estCost && obj['price'])){
allUniqeGarments.push(garmentObj)
}
})
//get total time, cost, staffcost, materialcost
garmentTotals = []
allUniqeGarments.forEach(obj=>{
let custArr = obj['customer']
let garmArr = obj['garment']
let estArr = obj['totalEst']
let priceArr = obj['price']
let totalTime = 0
let costStaff = 0
let costMaterial = 0
monthlyCostQuery.records.forEach(el => {
let custCost = el.getCellValueAsString(customer)
let garmCost = el.getCellValueAsString(garment)
let estCost = el.getCellValue(estTotalCost)[0]
let priceCost = 0
//rollup number issue when no number is rolled up
if(el.getCellValueAsString(price)){
priceCost = el.getCellValue(price)[0]
}
if(custArr === custCost && garmArr===garmCost && estArr === estCost && priceArr === priceCost){
if(el.getCellValueAsString(switchs)==='Material' || el.getCellValueAsString(switchs)==='Leather' ){
costMaterial += el.getCellValue(totalFactCost)
}else if(el.getCellValueAsString(switchs)==='Staff'){
costStaff += el.getCellValue(totalFactCost)
totalTime += el.getCellValue(costTime)
}
}
})
garmentTotals.push({'customer': custArr, 'garment':garmArr,'price':priceArr, 'timeTotal': totalTime, 'estimate':estArr, 'totalCost':+costStaff+costMaterial, 'staffCost':costStaff, 'materialCost':costMaterial})
})
console.log(garmentTotals)
}
await everyMonth()