Bill.com Spend & Expense and NetSuite, coded to the project at source

#The situation

Technicians and project managers spend on company cards and claim reimbursements in Bill.com Spend & Expense. Finance then re-entered every card transaction and every approved reimbursement in NetSuite, and worked out afterwards which project each one belonged to. The project was the piece most often missing, because Bill.com had no list of projects to choose from.

#What we built

A two-way sync in three Map/Reduce jobs.

NetSuite to Bill.com. A job finds projects created or changed since the last run and looks each one up in Bill.com. It adds a missing open project to the right coding list (direct or indirect), and removes a project once it has a completion date, so staff pick from the current list when they code spend. A project that is already there and still open is left alone. The same job has flows for employees (as Bill.com users) and for service sales orders. Those two flows are built but switched off in the current release, and projects are the flow that runs today.

Bill.com to NetSuite, reimbursements. A job reads reimbursements that are ready to pay and creates NetSuite expense reports for the right employee, with the merchant name cleaned up and the Bill.com id stored on the record.

Bill.com to NetSuite, card transactions. A job reads card transactions and creates NetSuite credit card charges, linked to the service order where one was coded.

#What it does now

Spend arrives in NetSuite already coded, so finance no longer codes it after the fact. Before creating an expense report, the job searches for one that already carries the same Bill.com id, so a reimbursement is never booked twice.

Every run writes to a shared integration log with the flow, the result and the time of the last sync, and failures go there with their context. A test Suitelet lets an administrator try the API calls without running a full job.

#Under the hood

• The map stage compares NetSuite with Bill.com before sending anything, so only real differences go out.
• Bill.com ids are stored on NetSuite transactions and checked before creation, so an import can run again safely.
• The API token is a NetSuite API secret, referenced by name and never present in code.
• Bill.com reference data that cannot be looked up one record at a time is fetched once per run and shared through N/cache.
• Every flow reports into one integration log that the account's other integrations also use.