The Scenario
A stakeholder email arrived yesterday asking why field workers are selecting from a facility list in CommCare that hasn't been updated since last April. You're the program data manager. You maintain the master facility list in an Excel workbook — it has 47 entries now, six of which are new clinics added during the expansion. The product catalog has also changed. CommCare's lookup tables are still running on the old version of both.
This isn't an emergency. It's the fourth time it's happened because nobody built a repeatable process for syncing the reference data.
The bad version:
- Open CommCare HQ, navigate to the lookup tables section, find the health facility table, click into it, and start adding the six new entries one row at a time through the UI form
- Realize the product catalog table has a slightly different column structure than the one in your workbook, spend time reconciling before you can enter anything
- Finish updating the facility table, remember the product catalog, go back in, discover a thirteenth entry in your workbook that somehow didn't make it in because you lost count
Reference data drift is the kind of problem that's invisible until a field worker selects the wrong clinic and it ends up in a case record.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Excel workbook. It reads the reference data you've been maintaining, talks to CommCare through its built-in integration, and creates or updates the lookup tables directly — no CommCare HQ navigation required.
The prompt for this task:
For each row in my Excel sheet, create a new CommCare lookup table in the domain listed in cell A1 using column A as the table ID and column B as the display name. Write the creation status and CommCare lookup table ID into column C.
What You Get
- CommCare lookup tables are created in the specified domain, one per row in the sheet.
- Column A values become the table IDs, column B values become the display names.
- Column C fills with the creation status and the CommCare-assigned table ID for each row.
- If a table with that ID already exists in the domain, the status column indicates it was found rather than created, so you know what's net-new.
What If the Data Is Not Quite Ready
The column structure in your workbook doesn't match what CommCare expects for an existing table
Compare the field names in column B of my 'Facility Table' sheet against the current column structure of the CommCare lookup table named 'health_facilities' in the domain in cell A1. Write any mismatches into column D — fields in the workbook but not in CommCare in one section, fields in CommCare but not in the workbook in another.
You want to populate the lookup table with data rows, not just define its structure
Create a CommCare lookup table named 'health_facilities' in the domain in cell A1, using columns A through E of my 'Facility List' sheet as the row data. Use row 1 as the column headers. Write the row-level status into column F.
Some table names in your workbook have changed since the last sync and you need to detect renames
For each unique table name in column A of my 'Lookup Tables' sheet, check whether a CommCare lookup table with that exact name exists in the domain. Write "Exists" or "New" into column D. For tables marked "New", create them using the field names in column B.
Full kill chain: check what exists, reconcile mismatches, create new tables, populate data rows
For each unique table name in column A of 'Lookup Tables': check whether it exists in CommCare for the domain in 'Config' A1. For existing tables, compare the field structure and write mismatches to column D. For new tables, create them using column B field names and populate them with the corresponding data rows from the sheet matching the table name. Write the final status into column E.
Keeping reference data in sync is a lot less painful when the sync is a single prompt.
Try It
Open your reference data workbook — facility list, product catalog, or any CommCare lookup table source — and Get the 7-day free trial of SheetXAI to push it into CommCare in one ask. See also: Submit Bulk Form Data to CommCare From an Excel workbook and the CommCare integration overview.