00:00:04 Introduction

I'm really excited to share with you today a preview of a big new feature that we've been working on for quite some time: spreadsheet-based data entry. I have some of our development team joining, Jamie, our engineering director, and Thomas, who has been doing most of the hands-on coding for this big release, and he will demo it for us. We are going to quickly put this in the context of the ActivityInfo 5.0 campaign that is coming up soon. I will just touch on some of the design goals around spreadsheet-based data entry, and then we will do a deep dive demo of the feature, constraints, benefits, tips, and tricks. Then, we will talk about what kind of feedback we still need from you before we go live.

00:01:09 ActivityInfo 5.0 context

Let's start with ActivityInfo 5.0. Don't worry, it is not going to be a huge disruption. If we take a trip down memory lane, some of you might still recognize ActivityInfo when it was blue. ActivityInfo 1.0 was released in 2009. In 2012, we did a big upgrade with a bunch of new reporting features, maps, pivot tables, and dashboards. We spent some years experimenting with a slightly different approach with ActivityInfo 3.0, which was never fully released, but we took everything that we learned from that and put it into the ActivityInfo that you know and love today, ActivityInfo 4.0. That really became a full relational database. We started work on that in 2018 and fully switched everyone over in 2020.

For the last five years, we have been developing quite a lot. Everything from the mobile app, more data control, reporting, visualization, dashboards, and translations. There has been a lot that has happened in five years. With this new spreadsheet-based data entry, it is a very visible upgrade. Together with all of the other features, we thought that it would be nice just to bump that version number to 5.0 this year. The spreadsheet-based data entry will be the most visible change when we go live. We are taking the time to really preview it, get lots of feedback, and make sure that it works properly for everybody before we flip the switch.

00:03:09 Design goals

Spreadsheet-based entry, including click-to-edit and typing inline, has been one of the most requested features. People love the flexibility of just going into a spreadsheet, clicking to edit, filling down, copying and pasting cell ranges, duplicating, and adding rows inline. It can make data entry, cleaning, and maintenance very fast rather than going into each individual form and making changes. It allows you to make a mess safely and then gradually clean it up as you work your way through it.

On the other hand, we have all the reasons people use ActivityInfo and not spreadsheets, which have to do with maintaining data control. We need to ensure that permissions are enforced, that data is valid, and that we don't end up with "Excel hell" in a database online. We have many different types of tools for controlling and validating data, and we wanted a design that allowed us to maintain that and provide real-time feedback to the user so that as you are editing, you know what is valid and what is not.

00:05:19 Demo: Spreadsheet-based data entry

I will be giving you a demo on this new feature, the new inline or spreadsheet table editor. I must note that this is still a preview version. You are encouraged to use it, try it out, and give us feedback to get involved in the finishing touches, but there will still be bugs. During this demo, I'll be working with the development assistance templates which you can find on our website, and I'll be setting up a project using this new spreadsheet editor for tracking tree farms.

First, I would like to add some farms to my registry. I'll go to the registry form, the farm form. What we see here directly is the new table. It looks slightly different, but there are a lot of similarities. You can directly edit these records inline, and it behaves just like a spreadsheet tool you would expect, but with all the power of ActivityInfo.

00:06:59 Working with drafts and validation

To insert new records, I can press the add record button in the top left, which now shows a new drop-down menu. The first option, "Enter data in form," will redirect to the data entry form we know, but we also have other options. I can insert how many rows I'd like to add. For example, let's add three at the top of my table.

I can press one of these cells, and by doing so, the cell is selected and I can start making changes. For example, I can type "West Forest Farm" and press enter. The blue cell indicates a draft value. It creates new drafts which you are allowed to edit. The field might be marked as invalid, and if I hover with my mouse, it gives me a message explaining why, such as the field being required. On the left, there is a new column showing icons for every record. This indicates an invalid draft created on my local machine, visible only to me.

We only create a draft once you actually start making edits. I can double-click or press enter on a cell to go to edit mode, which gives me an interface to select a date directly in my table. I can do that for all of these fields.

00:11:26 Pinning columns

If I scroll to the right to see other fields, I might lose view of the name of the farms. A very useful feature of this new table is that I can pin columns. By pressing the button on the column header, I can open the menu and press "Pin column." It moves to the left, and as I scroll to the right, the name of the farm stays in my view. This works in all directions.

00:12:34 Submitting drafts

Once records are valid, I can submit them to the server. Besides selecting a single cell, I can hold my mouse button and drag down to select multiple cells. In the record panel on the right, I have the option to submit drafted records to the server. Once pressed, these have been submitted and are now marked as valid saved records.

00:13:15 Copying and pasting from Excel

I already started this project in another spreadsheet editing tool like Excel, and I would like to move this to ActivityInfo. If I try to use the standard importer, I might get an error if required fields like "County" are missing in my source data. However, I can use the new table to do this. I will add three rows at the bottom. I go to my spreadsheet, copy the values, put my selection on the top left of the new rows in ActivityInfo, and press Control+V to paste the values directly into the table.

We directly start performing validations so you can resolve any mistakes. For example, if "None" is entered in a numeric field, I can change it to zero. I can also resolve invalid references. If a reference isn't found, I can double-press to see options and select the correct record.

00:16:00 Handling references and dependencies

If I have a draft reference—meaning I am referencing a record that hasn't been submitted to the server yet—it is marked specifically. If I want to submit a record that depends on a draft, I must also submit that referenced draft so others can see it. When I press "Submit to server," the system performs validation and gives an overview showing that there are valid drafts to be submitted, as well as dependent records in other forms that also need to be submitted.

00:18:40 Copying out of ActivityInfo

You can also make a selection in ActivityInfo, press Control+C to copy to your clipboard, and paste that data into any spreadsheet tool like Excel. You can expand your selection by holding down your mouse button, drag the borders to resize, or use the arrow keys and shift to modify the selection. Note that in this new version, you can only make a single rectangular selection; non-contiguous selection is no longer supported.

00:20:45 Editing existing records

We no longer show drafts in a separate table. We display them in the same table because you can now transform existing records into drafts. If I modify a saved record, it turns into an "edit draft." The field is marked with a blue indicator showing it is a draft value. You can view the previous saved value and, if necessary, revert to it.

00:22:38 Sorting

If I sort the form by date and then modify a date so it is out of order, the records do not jump around immediately. This prevents the view from changing while you are working. If you want the sorting to be applied properly again, you need to go into the menu and press the sort setting button.

00:23:32 Record panel actions and merging

The record panel on the right offers more functionalities. You can discard drafted values, which reverts the record to its valid saved state. You can also clear a field value entirely. Additionally, you can merge records. If I select multiple records, I have the option to merge them. This opens the merging interface where I can select which values to keep, effectively combining duplicates into one record and deleting the others.

00:25:16 Field-specific actions

If we scroll down to different fields, we see new buttons added to each field. For drafted values, there is a rollback icon to discard the draft. There is also an edit button that redirects you to the data entry interface for that specific field. Finally, navigation buttons allow you to jump to referenced records in their respective forms.

00:27:37 Fill down and data cleaning

I'd like to demonstrate how you can use the fill down functionality to clean your data. For example, if a question was misunderstood and needs to be corrected for multiple records, I can filter the view (e.g., by city). I can modify one value, then use the circle on the bottom right of the selection to drag down and insert this value into all selected records. This also works horizontally. You can also repeat patterns; if you select "No" and "Yes" and drag down, it will repeat that pattern over the records.

00:29:55 Working with subforms

Let's look at how we treat subforms. I will add a new individual record. If I navigate to the subform for this person, the form is initially empty. I can add records in the table. The parent field is read-only and automatically populated because we arrived via the parent record. If I submit a subform record to the server, the system validates dependencies. If the parent record is also a draft, or if there are other sub-records that need to be submitted to maintain consistency, the system will notify you and submit all necessary records together.

00:33:55 Locks and permissions

We fully support locks. If I add a rule-based lock—for example, locking records with a date before April 2021—and then return to the table, records matching that rule are grayed out. I cannot make changes to these locked records, clear them, merge them, or delete them. I can still select and copy them if I have view permissions. Permission support is being finalized; currently, you can make changes to records you don't have permission for, but they will be marked as invalid and cannot be submitted.

00:35:55 Release timeline and feedback

We are doing a preview because this is a complex feature that replaces the current table, and we don't want to disrupt workflows. We need your help to test this with your data. The preview is live now at preview.activityinfo.org. It connects to your real data, so changes apply to your live database. We plan to hold a "go/no-go" decision around June 16th. If ready, the earliest production release would be July 1st, but we will wait until it is stable.

00:39:30 Questions and answers

Permissions for copying and pasting: There are no plans to require special permissions for copying and pasting because it doesn't allow users to do anything they can't already do via the importer or data entry forms. However, existing permissions regarding who can input data and where will absolutely carry over to the new table.

Offline work and attachments: The new table works offline. You can create drafts offline and submit them when you have a connection. Regarding attachments, we recently released a version that allows for basic offline attachments for drafts, specifically in the mobile app collection links.

"Add Record" button workflow: Feedback was provided that the new drop-down menu on the "Add Record" button might confuse users trained on the old workflow. We will look into making this a split button or ensuring the data entry form remains easily accessible to smooth the transition.

Performance: The preview version currently loads all data into the browser, which can be slow for large datasets. We are working on optimizations to ensure only data in view is loaded, aiming to match or exceed current performance for datasets up to one million records.

Metadata columns: You can view metadata like "Last edit time" by going to the View settings and selecting columns. The history tab remains available for a full track record of changes.

Calculated fields: Calculated fields cannot be modified directly in the table as they are computed via formulas. They will appear as uneditable values.

Duplicating records: You can duplicate records by selecting the record status on the left to select the entire row, then using the fill down handle to create copies. You can also copy a selection and paste it into a larger selection to repeat the pattern.

Merging records: The merge records button is only available if you select submitted records (not drafts) and if you have the "Resolve duplicates" permission enabled in your user role settings.

Filtering by quantity: Filtering by quantity fields is not currently included in this update, though it is a noted request.

Switching between versions: The old version is still live at activityinfo.org. The new version is at preview.activityinfo.org. You can use both, but once the release goes live, the new table will replace the old one.