CRM Average Response Time Report
As part of the ongoing strategy for better customer experience, my client wanted a report that would capture the date of all messages within every case record on NetSuite, and calculate the average response time for that entire case. Given that one case record could contain in excess of 70-100 incoming/outgoing messages, running a saved search to perform this calculation was far too complex.
Solution:
First: Build a custom Suitelet which offer business users the choice of report they want to run from a given date range. Second: After selecting the desired report on screen, the user should be able to trigger the generation and downloading of the report in CSV format.
Reflection
This particular task was quite challenging, given the business users were responding to approximately 100 cases per day and on average, each case had around 10-15 incoming and outgoing messages logged. Some of the main challenges included:
• Saved Search limitation: The 'nlapiSearchRecord' API was only able to retrieve search results up to 1000 rows. To exceed this limit, I had to use the nlobjSearch object as it supports unlimited rows in the search results when used in a loop.
• File Creation limitation: I found that the 'nlapiCreateFile' API only had a 10MB limitation to the size of the document that can be created. To overcome this problem, I generated the file in CSV format (rather than Excel format), as it holds plain text as a series of values separated by commas, meaning more plain-text data can be generated.
• Saved Search limitation: The 'nlapiSearchRecord' API was only able to retrieve search results up to 1000 rows. To exceed this limit, I had to use the nlobjSearch object as it supports unlimited rows in the search results when used in a loop.
• File Creation limitation: I found that the 'nlapiCreateFile' API only had a 10MB limitation to the size of the document that can be created. To overcome this problem, I generated the file in CSV format (rather than Excel format), as it holds plain text as a series of values separated by commas, meaning more plain-text data can be generated.