Filling a form from a spreadsheet

A form can be filled in a spreadsheet rather than on screen: download the workbook, fill it in wherever you are, and upload it back.

This is the route for a shift's readings taken away from a signal, for figures somebody else keeps in Excel already, and for keying a backlog quickly — a keyboard and a grid beat a form on a phone when there are two hundred numbers to enter.

Downloading the workbook

From the form. You get a workbook laid out as the form is: a details sheet for the ordinary sections, and a sheet per repeating section with a block per item.

It comes back carrying what is already recorded

A period the form has already been filled in for downloads with those figures in place, not as a blank grid. Correcting one number does not mean re-keying the other forty.

The workbook is formatted to be read as a document — headings in caps over a filled band, columns sized to their content, every cell in the used range ruled. The greyed italic cells are how Trackapt finds its way around on the way back in; leave them alone.

A whole period in one workbook

Where a form's recording cadence allows it, you can download one workbook covering a period rather than one per entry — a month of daily sheets in a single file.

You are asked for the period, and told how many sheets it will produce before anything downloads. That count is the honest answer to "how big is this file", and it can legitimately be zero: a weekly form whose week start has not been declared cannot be laid out over a period at all, so set that in the form builder first.

Not every form can be scaffolded this way. One whose cadence is non-periodic — including every dynamic form — has no grid of slots to lay out, so the option is not offered.

Uploading it back

Upload the workbook against the form.

You are shown what it holds before anything is changed. The values land in a live form under the unsaved-changes guard, so a half-applied import is awkward to undo — you get to see what will happen and say no.

The report tells you what was found, what could not be read, and why. Unresolved rows are listed rather than dumped: a wall of them is not a report.

What can stop a value being read

What happened	What to do
A name that matches no item	Usually a misspelling, a value typed over the picker's, or an item renamed since the workbook was downloaded. Correct the name, or pick it from the list.
A key row that cannot be found	The sheet's machine-readable header row was deleted or moved. Download a fresh workbook and transfer your figures into it.
A time that is not a time	Excel is fussy about time cells. Format the cell as a time rather than as text.

Never delete the greyed rows and columns

Trackapt locates every column by its marker in the key row rather than by its position, which is what lets you widen a column, colour a cell or reorder nothing at all without a figure landing in the wrong field. Delete the markers and the sheet stops being readable.

Reference fields in a workbook

A reference field stores an item's id so the link survives a rename, but a workbook shows the item's name — nobody can fill in a column of ids by hand.

So the import turns the name back into the id. Where it cannot, the value is reported rather than silently dropped, and you can pick the right one from a list instead of retyping it.

That matters more than it sounds: three operator names typed slightly wrong would otherwise be three empty fields with nothing to say so.

What is recorded about the import

An entry filled from a workbook keeps a note of which file it came from — the file name, its size, and a checksum.

This is provenance, not custody: the workbook itself is not stored. What the checksum settles, months later, is "is the copy I am holding the one these figures came from" — which is the question that actually gets asked when a figure is disputed.

Working without a connection

Downloading the workbook, filling it in and uploading it back is one way to work offline. The other is simply to fill the form — see Working offline, which queues the entry on the device and sends it when there is signal.

Use the workbook where somebody else is doing the keying, or where the figures already live in a spreadsheet. Use offline filing where the person capturing is the person with the app.