Stream a Simpro build

A Simpro stream lands a Simpro build's jobs, quotes, leads, invoices, schedules and job costs in your warehouse, as often as every few minutes, with the customers, sites, staff and lists they refer to. Each run reads only what changed since the last run, and Simpro's own work in progress report, kept day by day.

Simpro is a published source, like Bite: sanda wrote the code that reads it and runs it as its own, so there is no connector for sam to write or for anyone to approve. You connect your build through Simpro's own sign-in, choose the company and what to read, and set a schedule.

Before you start

• Know your Simpro address. It is the address you sign in to Simpro at, such as yourcompany.simprosuite.com or yourcompany.simprocloud.com. The first part alone will do for a simprosuite.com build.
• Sign in as someone who can see the data. Simpro has no separate permissions for sanda: a stream sees what the Simpro user who connected the build sees, through their security group. Connect as someone whose security group can see jobs, quotes, invoices, schedules and the rest of what you want to read. Something their security group hides is skipped, and the run says so.
• One company at a time. A Multi-company build holds several companies, and each stream reads one of them. Make a stream for each company you want. A build with one company has just the one, numbered 0.
• In sanda, only owners and admins connect a build and make a stream on it, because sanda reads the build on the whole workspace's behalf.

Connect a build

sanda keeps the connection encrypted, and it is never shown, in the console or to sam. Connect again connects the build again: sign in to the same build, and every stream on it carries on. A connection nobody uses stops working after 14 days, so sanda renews each connected build's connection before then, whether or not a stream ran.

Disconnect forgets the connection in sanda. Simpro offers no way for sanda to end it from here, so to remove sanda's access in Simpro too, remove it there. The build's streams then wait until it is connected again.

Make a stream

What it reads

Each table is keyed by Simpro's own id. The editor marks the ones that land people's contact details.

Jobs, quotes, leads and the rest that change

Each run asks Simpro for what changed since the newest change the last run saw, less 10 minutes, so a change made while the last run was reading isn't missed. A record read twice is still one row, and one that hasn't changed costs nothing. Simpro lists them in order of their id, and each request asks for up to 250 records after the last one the request before saw, so a record that changes while a run reads can't push another out of the way.

A first run reads jobs, quotes, leads, invoices, schedules, job cost centres, purchase orders and contractor invoices changed since the history date. It reads all of the customers, sites, employees, contractors, catalogue items and customer assets, whatever their age, so every job's customer and site has its row.

Job cost centre totals

Simpro's list of job cost centres holds their names and ids but none of their figures. Job cost centre totals asks Simpro for each changed cost centre on its own: its stage, start and end dates, percent complete, what has been claimed, and its cost and margin totals. That is one request per cost centre, so a first run on a large build takes a while, which is why it is unticked to begin with. Each row also carries the jobid and sectionid of the job and section it sits under.

Work in progress

Work in progress is Simpro's cost to complete report: for each job, its contract total, what has been claimed to date, cost to date, cost to complete, percent complete and margins. sanda asks for it at most once every 60 minutes, as at that day's date in the company's own time zone, which it asks Simpro for first. If Simpro doesn't name a zone, the day is Sydney's. sanda keeps each day's report: a job's row is keyed by its job id and as_at, the day it was taken, and a later read on the same day updates that day's rows, so each day keeps its last read. Over time the table is a daily history of each job's work in progress. A job that leaves the report during a day keeps the row an earlier read gave it that day. sanda asks for it with Simpro's own defaults for variations, committed costs and overheads.

Staff and the lists

Cost centres, project tags and project custom fields carry no date of their own, so each run reads them whole. Staff do carry one, in datemodified, but the list is short, so each run reads it whole too and notices someone taken off it. A row the list no longer has is marked _deleted_at and kept, and reports and answers stop counting it.

Left out

• Pay and personal details. Employees' pay rates, bank details, tax numbers, birth dates, home addresses and emergency contacts, contractors' rates, bank details, tax numbers and addresses, and customers' bank details are never kept. Simpro sends some of them with a record sanda does read: a contractor invoice comes with its contractor's address, contact details and bank account, so sanda keeps only the contractor's id and name from it.
• Descriptions and notes. The free text on jobs, quotes, leads, sites and invoices stays in Simpro. So do the descriptions that come with an invoice's jobs and its recurring invoice and with a contractor invoice's lines, and the notes on a customer's profile.
• Timesheets. Simpro lists them one employee at a time, so reading them means a request for every employee on every run. Schedules holds the scheduled time instead.
• Job logs and stock on hand are not read. Stock is listed one storage location at a time, and sanda can't confirm the job log can be asked for only what changed.

What lands in your warehouse

Every field becomes a column, and the whole record is kept in _record, as for every pull. A few things are worth knowing when you model them:

• Every table has company_id: the company in the build its records came from, so two companies' streams can be joined without their ids colliding.
• Money is a JSON object where Simpro sends one: a job's total holds ExTax, Tax and IncTax, and its totals the estimated, revised, actual and committed costs, margins and invoiced value. A catalogue item's prices, a customer's amount owing and the work in progress figures are plain numbers.
• Dates are your build's own calendar days, such as a job's dateissued. datemodified is a time with your build's own offset, so it compares correctly whatever the warehouse's time zone.
• Ids are numbers, and the customer, site, salesperson and technicians on a job, quote or invoice are JSON objects holding each one's ID, which names a row in that list's table. A job's tags hold its project tags' ids and names.
• Deleted records. Simpro lists what changed, not what was deleted. A first run, and every Load everything again, reads each list in full (from the history date, for the lists that have one) and marks a row Simpro no longer has with _deleted_at. Between those, a record deleted in Simpro keeps its row.

Change a stream

Edit, on the stream's page, changes everything but the build, the company and the warehouse. Something you take off keeps its table, with what it landed. A new Read history from date takes effect the next time everything is read from the start: press Load everything again on the stream's page.

When something goes wrong

A failed run counts toward the stream pausing itself, as any stream's does. See runs.

Limits

• Simpro allows each integration 10 requests a second on one build, shared by everything sanda does with that build. A run makes at most 4 requests a second, and each request asks for up to 250 records.
• A connection nobody uses for 14 days stops working. sanda renews it before then.
• One run lands at most 20,000,000 records, so a long history is several runs.

A Simpro stream is billed like any pull: a charge for each run that does its work, and the rows it lands past what your edition includes. See what streams cost.