NetSuite Mass Update Converts Problematic Timesheet Entries

Problems from QuickBooks Upgrade

In our post QuickBooks Data Conversions to NetSuite, we discussed how clean the conversion went.  Well, there is some fallout.  After five years of professional services where timesheets represent the structure of our internal billing, we had a number of entries that did not come over perfectly referenced within NetSuite.

Even through we billed the majority of those old timesheets entries in QuickBooks (in our case, over 18,000 entries), as they exported to NetSuite, they were not marked as Billed.  This is most likely due to the lack of reference between the timesheet entries and the ulitmate invoice they were billed on.

NetSuite has an enhanced capacity to “approve” time entries before they can be billed.  In our case, we have over 18,000 time entries to approve and it made the traditional process slow because the way the form loads by default.  We could just approve everything if we were acting hasty.  However, if we would have approved these old entries as is, we would have run the risk of double billing our clients as they would have appeared as unbilled billable time entries.  Now that would break trust because every communication with a client, including billing, is an opportunity to demonstrate superior care.

Billable Billed Time Entry Resolution

To solve the issue, we leveraged NetSuite’s built in “Mass Update” feature.  The power of this tool is needed for these kind of situations — in traditional environments, we might go directly to the database to modify records to “get rid of them”; in NetSuite, you can’t edit the database directly.  You get to use a innovative tool to bulk change records.

We leveraged our knowledge of Oracle PL SQL as the Mass Update feature allows us to specify a formula to update the tables.   In our case, we wanted to modify the time entry memo description to reflect the original values referenced when they were created in QuickBooks.  Here is what we did:

1. Create a Mass Update query to find the Time records.  In our case, we said selected all records older than 02/09/09 where the approval flag is “no”.
2. We then modified the mass update memo field with this formula: “‘[QB Billable is:’ || {billable} || ‘]: ‘ || {note}”.  It returns a result like this: “[QB Billable is:F]: xxxx” where xxxx is the original note.  See attachment.
3. Mark the “billable” field to “false” to ensure that we don’t bill the entries in the future.

After we committed the request, our ~18,000 records were now ready to be approved.  This part is cumbersome because the standard feature allows only 100 time sheet records to be approved at one time.  That means we would have needed to do this over 180 times.   Not fun but also not worth the time to write a program that will allow this to be done in one shot.    Note, you probably are thinking “why could you not have used the Mass Update to flip the approval flag to true?”.  We agree, why can’t you?  Well, there are some fields in this utility that you simply don’t get to update easily.  Still, we utilized a an open source utility called “AutoHotKey” that effectively allowed us to press the keys and click the mouse in the proper sequence 180 times making this a relative breeze.

We like NetSuite’s Mass Update capability even though there are limitations to the number of fields you can get to.  We have attached some screen shots to illustrate what we did.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube