A Datatable Saga

This is a story about how we built 73 datatables with react-table and next.js for a dashboard. The problem was well defined and we had a set of well understood constraints, so the goal was not to create a solution that works for everything but one that works well for our context.

The problem

The Mashonaland West Provincial Education Director need a reporting dashboard for the province. There are 8 districts with hundreds of schools within them and they all submit termly reports to the province. The province in turn then sends a collated report to the Permanent Secretary at national level. These reports contain mostly tabular data covering almost all aspects of running a primary or secondary school in Zimbabwe.

Brainstorming

Right off, we knew that we are going to be creating a tonne of datatables. I had never worked with tables before, most of the times if I need a table layout, I would just use a grid. However in this case, that wasn’t an option.

The report contains a lot of structured data, and is almost all number inputs. It can all be put into 6 categories, namely

• Registration
• Enrollment
• Staffing
• Services
• Business Ventures
• Infrastructure

We went ahead and designed all the tables in Excel to get an idea of what we will be looking at when we start building them in code(yes building the whole platform in Excel wasn’t an option, it was a good prototype though). I quickly realised there was no way we were going to be writing code for every individual form with a datatable. This got me thinking, what if I could write a declarative definition for all the datatables and then have components and utilities that will build the form.

This not only makes it faster for us to build, but makes it easier to react and adapt to changing requirements.

The Datatable form

We knew that every datatable form could be described as having columns, rows, a title and an optional caption(we called it a message)

type DatatableForm = {
  columns: string[];
  rowTitles: string[];
  formTitle?: string;
  tableTitleColumnLabel?: string;
  message?: string;
};

However, as we can see, the columns is not compatible with react-table Column. We need to do some grappling and make it compatible and then figure out how to build a datatable with the provided props. We also know the column title is going to be written in English and not in programmer casing(pascal, snake, bla bla). In addition we also know the column can have either a text label cell or a number input cell.

const makeColumnsForDatatable = (data: {
  title?: string;
  columns: string[];
}): [] => {
  const newColumns = [
    {
      header: data.title,
      accessorKey: "title",
      cell: "label",
      meta: {
        type: "text",
      },
    },
  ];
  return newColumns.concat(
    data.columns.map((column) => ({
      header: column,
      // create a key in snake casing from the column string
      accessorKey: column.toLowerCase().split(" ").join("_") as string,
      cell: "input",
      meta: {
        type: "number",
      },
    }))
  );
};

This makeColumnsForDatatable would be used to columns with type compatible with a custom type that we will then use to create the actual react-table columns. We also need to create the initial data for the table to initialize the rows and give them titles using the rowTitles field.

export const makeDatatableInitialDataFromColumnsAndRowTitles = (
  columns: TColumn[],
  rowTitles: string[]
) => {
  return rowTitles.map((title) => {
    const rowData: { [key: string]: any } = {};
    columns.forEach((column) => {
      rowData[column.accessorKey] = 0;
    });
    rowData.title = title;
    return rowData;
  });
};

These two functions would be use in the DatatableForm like this

function DatatableForm(props: DatatableFormProps) {
  const columns = makeColumnsForDatatable({
    title: props.tableTitleColumnLabel,
    columns: props.columns,
  });
  const [data, setData] = useState(
    makeDatatableInitialDataFromColumnsAndRowTitles(columns, props.rowTitles)
  );

  // ...rest of the code

  return (
    <form>
      {
        // rest of the code
      }
      <Datatable
        withTotalColumn
        data={data}
        columns={columns}
        onUpdate={onUpdate}
        message={props.message}
      />
      {
        // rest of the code
      }
    </form>
  );
}

The datatable and helpers

The table itself was built with shadcn table components and react-table. It has a InputCell and a LabelCell for labels and inputs(lol). It accepts props with a generic in the data field, all described by

type DatatableProps<T> = {
  data: T[];
  columns: TColumn[];
  message?: string;
  withTotalColumn?: boolean;
  withTotalRow?: boolean;
  onUpdate?: (index: number, columnId: string, value: any) => void;
};

Inside the datatable we do the heavylifting of creating the react-table columns by using a utility function. It accepts an optional param withTotal which tells it whether to add a Total column at the end of the table.

const createColumns = <T extends unknown>(
  columns: TColumn[],
  withTotal?: boolean
): any[] => {
  const columnHelper = createColumnHelper<T>();
  const accessorColumns = columns.map((column): any => {
    const { cell, header, meta, accessorKey } = column;
    return columnHelper.accessor(accessorKey, {
      cell: cell === "input" ? EditableTableCell : TableLabelCell,
      header: header,
      meta: meta,
      width: 150,
    });
  });
  if (withTotal) {
    const totalColumn: ColumnDef<T> = {
      id: "total",
      header: () => <span className="flex justify-end w-full">Total</span>,
      accessorFn: (row: T) => {
        const values = getAllValuesOfRowObject(row as any);
        return calculateArraySum(values);
      },
      cell: (props) => (
        <TableLabelCell {...props} className={"justify-end w-full flex"} />
      ),
    };

    return [...accessorColumns, totalColumn];
  }

  return accessorColumns;
};

Then we build the actual Datatable component

const Datatable = <T extends object>(
  props: PropsWithChildren<DatatableProps<T>>
) => {
  const [data, setData] = useState<T[]>(() => [...props.data]);
  const [editedRows, setEditedRows] = useState({});

  const columns = useMemo(
    () => createColumns<T>(props.columns, props.withTotalColumn),
    [props.columns, props.withTotalColumn]
  );

  const table = useReactTable({
    data,
    columns,
    getCoreRowModel: getCoreRowModel(),
    meta: {
      editedRows,
      setEditedRows,
      updateData: props.onUpdate,
    },
  });

  return (
    <div>
      {
        // ...rest of the code
      }
      <Table>
        <TableHeader>
          {table.getHeaderGroups().map((headerGroup) => (
            <TableRow key={headerGroup.id}>
              {headerGroup.headers.map((header) => (
                <TableHead key={header.id}>
                  {header.isPlaceholder
                    ? null
                    : flexRender(
                        header.column.columnDef.header,
                        header.getContext()
                      )}
                </TableHead>
              ))}
            </TableRow>
          ))}
        </TableHeader>
        <TableBody>
          {table.getRowModel().rows.map((row) => (
            <TableRow key={row.id}>
              {row.getVisibleCells().map((cell) => (
                <TableCell key={cell.id}>
                  {flexRender(cell.column.columnDef.cell, cell.getContext())}
                </TableCell>
              ))}
            </TableRow>
          ))}
        </TableBody>
      </Table>
    </div>
  );
};

Was it worth it?

Absolutely. The above code allowed us to create our tables in an object that looks something like this

const registrationForms: {
  audits: DatatableForm;
  school_registration: DatatableForm;
} = {
  audits: {
    columns: ["Infant satelite", "Primary", "Secondary"],
    tableTitleColumnLabel: "Status",
    rowTitles: ["Audited", "Not Audited"],
    formTitle: "Audits carried out",
    message: "All input to be provided as numbers",
  },
  school_registration: {
    columns: ["Infant satelite", "Primary", "Secondary"],
    tableTitleColumnLabel: "Status",
    rowTitles: ["Registered", "Not Registered"],
    formTitle: "Number of schools registered",
    message: "All input to be provided as numbers",
  },
};

const venturesForm: {
  overview: DatatableForm;
} = {
  overview: {
    columns: ["Number of ventures", "Value"],
    tableTitleColumnLabel: "",
    rowTitles: ["Primary", "Secondary"],
    formTitle: "Business ventures carried out",
    message: "All input to be provided as numbers",
  },
};

const tabularForms = {
  registration: registrationForms,
  ventures: venturesForm,
};

export default tabularForms;

Which we would then use to create the forms like this

export default function Audits() {
  return (
    <FormWithDatatable
      form={tabularForms.registration.audits}
      nextPage={"/some-page"}
    />
  );
}

At the end of the day, we could delegate the work of creating the forms to a less experienced dev and allow the top talent to handle more mentally demanding tasks.

Note If you are using react-table and having issues with sum coumns, there is an open issue concerning that and it seems to be a bug. Here’s a link to the issue.

More Links

• React Table
• Shadcn Datatable